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

greetings stackoverflow'lings,

let me explain predicament:

lets have 3 columns (9 in total merged in numbers of 3) in 1 row each of 3 columns has data in in row directly beneath have 9 columns each cell having data in it.

the idea read first row 3 columns, , find out how long columns span can assign correct values when creating resulting xml. heres rough sketch of trying achieve:

row 1 column 1 (range merged on 3 columns) = football teams row 2 columns 1, 2, 3 each hold different data football teams row 1 column 2 (range merged on 3 columns) = football players row 2 columns 4, 5, 6 each hold different data football players row 1 column 3 (range merged on 3 columns) = football finances row 2 columns 7, 8, 9 each hold different data football finances

for reason when read first row getting values:

[0] = "football teams" [1] = null [2] = null [4] = "football players"

but when read row 2 full array (9) of columns! code using can 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 range object array object[,] datavalues = (object[,])datarange.value2; 

any help/guidance/slap in right direction appreciated!

when have merged cells, cells still there (in terms of code), it's have null values. first cell in merged cells group have value.

after reading comments, , rereading code, believe problem how pick last column:

range datarange = worksheet.get_range("a1", missing.value); datarange = datarange.get_end(xldirection.xlright); 

i find doing 'end' right doesn't last item on right, if there spaces between cells (which case here, because cells merged). might better (with cleanup save lines) - 'xfd' last column in excel 2007 , above:

range lastcell = worksheet.get_range("xfd1").get_end(xldirection.xlleft); range datarange = worksheet.get_range("a1", lastcell); object[,] datavalues = (object[,])datarange.value2; 

note, code not last 2 nulls in third group - way explicitly set range this:

range daterange = worksheet.get_range("a1", "i1");