Excel - Reading Merged Cells (rows) in .NET using C#

Question

Greetings StackOverflow'lings,

Let me explain my predicament:

Lets just say I have 3 columns (9 in total as they are merged in numbers of 3) in one row each of the 3 columns has data in it In the row directly beneath I have 9 columns with each cell having data in it.

The idea is to read the first row with the 3 columns, and then find out how long those columns span for so I can assign the correct values to the when I am creating the resulting xml. Heres a rough sketch of what I am trying to achieve:

Row 1 Column 1 (range is merged over 3 columns) = Football Teams Row 2 Columns 1, 2, 3 will each hold different data about Football Teams Row 1 Column 2 (range is merged over 3 columns) = Football Players Row 2 Columns 4, 5, 6 will each hold different data about Football Players Row 1 Column 3 (range is merged over 3 columns) = Football Finances Row 2 Columns 7, 8, 9 will each hold different data about Football Finances

For some reason when I read the first row I am getting the values:

[0] = "Football Teams" [1] = null [2] = null [4] = "Football Players"

But when I read row two I get the full array (9) of columns! The code I am using can be seen below:

Range dataRange = worksheet.get_Range("A1", Missing.Value);
dataRange = dataRange.get_End(XlDirection.xlRight);
string dataAddress = dataRange.get_Address(
        false, false, XlReferenceStyle.xlA1,
        Type.Missing, Type.Missing);

dataRange = worksheet.get_Range("A1", dataAddress);
// Cast the range into an object array
object[,] dataValues = (object[,])dataRange.Value2;

Any help/guidance/slap in the right direction would be greatly appreciated!

Dave Arkell · Accepted Answer · 2010-11-17 11:41:13Z

4

When you have merged cells, the cells are still there (in terms of your code), it's just they have null values. So the first cell in the merged cells group will have the value.

After reading your comments, and rereading your code, I believe the problem is with how you pick up the last column:

Range dataRange = worksheet.get_Range("A1", Missing.Value);
dataRange = dataRange.get_End(XlDirection.xlRight);

I often find that doing an 'End' to the right doesn't necessarily get the last item on the right, especially if there are spaces between cells (which is the case here, because the cells are merged). So it might be better to do this (with some cleanup to save some lines) - 'XFD' is the last column in Excel 2007 and above:

Range lastCell = worksheet.get_Range("XFD1").get_End(XlDirection.xlLeft);
Range dataRange = worksheet.get_Range("A1", lastCell);
object[,] dataValues = (object[,])dataRange.Value2;

Note, that this code will not get the last 2 nulls in the third group - the only way to do that is to explicitly set the range like this:

Range dateRange = worksheet.get_Range("A1", "I1");

edited Nov 17, 2010 at 11:41

answered Nov 16, 2010 at 10:28

Dave Arkell

3,9902 gold badges24 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

GoodAtScrabble Over a year ago

See comment below, accidently pasted the comment into the field beneath this one. Apologies.

GoodAtScrabble Over a year ago

I see, but I am also unable to get the final cell, the only values that are actually returned are the ones I have stated namely Football Teams, null, null, Football Players. I would have expected Football Teams, null, null, Football Players, null, null, Football Finances, null, null.

Collectives™ on Stack Overflow

Excel - Reading Merged Cells (rows) in .NET using C#

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related