Reading data from Excel [modified]

[JamesParsons]

Hello! I'm trying to read some data in from Excel. Now the spreadsheets that I'm accessing contain columns of data, and these columns may sometimes be blank, the main table might start a few columns in and so on. A raggedy old spreadsheet! In addition to this I don't know how many rows or columns there will be, so I need to be able to handle this. All the examples I've seen in the searches I've made have hard-coded limits. For example my table might look like this (can't quite format this right, but you'll get the idea):

DEPT.M	LLD.OHMM	MSFL.OHMM	RHOB.G/CM3	NO SONIC	IGLOO	FISH#
1450	12.4303	2.279	2.3188			
1450.25	12.4494	5.0599	2.3323			
1450.5	14.7747	2.0675	2.2776			
1450.75	14.768	1.2149	2.217		0.3974	
1451	13.1767				0.4151	
1451.25	16.8165				0.2921	0.3974
1451.5	29.4913					0.4151
1451.75	48.8114					0.2921
1452	44.7025					
1452.25	28.3492					
1452.5	19.2471	2.2749	2.1967	13.1371	0.3974	
1452.75	13.4801	1.8502	2.232	13.3881	0.4151	
1453		3.2926	2.3583	13.8325	0.2921	
1453.25		3.6341	2.4099			
1453.5		4.1754	2.356	102.0509	16.9615	10.3197
1453.75	16.9615	5.3556	2.3608	106.603	18.0078	10.7229
1454	18.0078	4.9902	2.3258	105.3982	17.1981	10.2939
1454.25	17.1981	4.6068	2.249	108.3167	16.2081	9.6294
1454.5	16.2081	3.389	2.2199			
1454.75	15.7188	1.9904	2.1252			
1455	18.0351	128.8776	1.7491			
1455.25	28.9451	656.4312	1.4863			
1455.5	43.2879	65.2774	1.6644			
1455.75	31.8807	12.309	2.182			
1456	18.3605	9.6465	2.3213			
1456.25	19.039	7.1053	2.1123			
1456.5	25.825	9.4629	1.6977			

I have this kind of example below, but the limits are hard-coded. How can I get the correct limits so that I can read the whole sheet, regardless of it's size? Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1); // this gets me a 10x10 block from my worksheet: for (int i = 1; i <= 10; i++) { Excel.Range range = worksheet.get_Range("A"+i.ToString(), "J" + i.ToString()); System.Array myvalues = (System.Array)range.Cells.Value2; // convert the output to something we can use.. string[] strArray = ConvertToStringArray(myvalues); ... etc } But what I really want is (pseudo-code in CAPS): for (int i = 1; i <= worksheet.NUMBER_OF_ROWS; i++) { Excel.Range range = worksheet.get_Range(THE_WHOLE_ROW[i]); System.Array myvalues = (System.Array)range.Cells.Value2; string[] strArray = ConvertToStringArray(myvalues); ..

[Jimmanuel]

Hopefully this is still relevant to you . . . to do worksheet.get_Range(THE_WHOLE_ROW[i]) we use this snippet:

int lastUsedColumn = worksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).Column;

Range firstCell = (Range)worksheet.Cells[rowNumber, 1];
Range lastCell = (Range)worksheet.Cells[rowNumber, lastUsedColumn];

Range wholeRow = worksheet.get_Range(firstCell, lastCell);

[JamesParsons]

Thanks! That's great!!