Reading data from Excel [modified]
-
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.6977I 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); .. -
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.6977I 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); ..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);
-
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);
Thanks! That's great!!