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!

Solution

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");

Comments

[GoodAtScrabble]

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

[GoodAtScrabble]

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.