Reading Excel spreadsheet as a "square" range (C#)

[sporadic]

Tough to describe in a title. :D

 

When using the oledb method of "selecting" data from a sheet it will return it to you in a nice "square" table format.

IE: it somehow automatically finds the maximum row and column and returns to you a nice table of that width/height.

 

How can I do this with interop?

 

Here's what I know about the spreadsheets I will have to read:

-they will not all be the same width or height

-they can have empty rows or columns

-column A may be longer than B and so forth. I have no definite points to find a max/min points across all sheets that come through this app.

-no column names gauranteed.

 

So basically I need it to do the exact same thing as the oledb will do.

I can't use the oledb because I need everything returned as a string value and I can't have it guessing my data types and returning 2.03+10E or whatever it does.

 

Thanks everyone, great site you have!

 

Mark

[herilane]

Simplest solution: xlSheet.UsedRange returns an Excel.Range object that contains the entire used range in a sheet.

 

However it does have some shortcomings. For example if you have 10 rows of data in your sheet, and then go and change the formatting of row 5000 (without putting any data there), UsedRange will return 5000 rows.

 

If this would be a problem, you can use a slightly longer but much more reliable approach. Example, assuming ws is an Excel.Worksheet object and all the r variables are declared as Excel.Range:

r = ws.Cells
rLastCol = r.Find("*", after:=r.Cells(1, 1), searchorder:=Excel.XlSearchOrder.xlByColumns, _
   searchdirection:=Excel.XlSearchDirection.xlPrevious)
rLastRow = r.Find("*", after:=r.Cells(1, 1), searchorder:=Excel.XlSearchOrder.xlByRows, _
   searchdirection:=Excel.XlSearchDirection.xlPrevious)

rLast = ws.Cells(rLastRow.Row, rLastCol.Column)
rAllUsed = ws.Range(ws.Cells(1, 1), rLast)

(Caution: untested code, typed more or less from memory)