CODEWITHSUNDEEP
c#excelc#-4.0office-interop
JimS-CLT
JimS-CLT

Reputation: 675

What type does Excel Worksheet.Usedrange return

I am converting an app from vba to C#. It's going slowly. Here's my first challenge...

In vba, I can assign an Excel range to a vba two-dimensional array:

Dim strSheetArray as Variant
strSheetArray = xlSH.UsedRange
...
debug.print strSheetArray[1,2]     'etc.

When I convert to C#, the "UsedRange" property seems to return an object. I can't seem to use that object like an array, getting code-time errors. So, how do I refer to the returned object so I can iterate through it?

And, as long as I'm at it, the cells have no type, so how do I use them (some are number, others are text, etc.) It was pretty easy in vba....

Thanks for the help.

Upvotes: 0

Views: 10191

Answers (1)

Jetti
Jetti

Reputation: 2458

It returns a Range Interface. You can read more about it at MSDN.

And, as long as I'm at it, the cells have no type, so how do I use them (some are number, others are text, etc.) It was pretty easy in vba....

The cells do have a type, but they are just objects.You will have to cast to what you want it to be. So if the cell is text, you will have to do something like:

(string)xlSheet.Cells[row,col].Value2

or

xlSheet.Cells[row,col].Value2.ToString();

You would have to cast to a numeric type as well if you knew they were numbers.

Edit based comments:

So if you want to get the used range you would do this:

using Excel = Microsoft.Office.Interop.Excel;

Excel.Range range = xlSheet.UsedRange;

This will give you everything from the worksheet (I used the variable xlSheet, which is Excel.Worksheet).

Now we can iterate over this by doing:

int rows = range.Rows.Count;
int cols = range.Columns.Count;

for(int r=1; r <= rows; r++)
{
    for(int c=1; c <= cols; c++)
    {
        Console.Write(range.Cells[r,c].Value2.ToString());
    }
    Console.WriteLine();
}

This will iterate over the range and basically replicate what is in your spreadsheet in the console window. I hope this helps. I also wrote a blog post on reading Excel spreadsheets using C# which you can read here if you want.

Upvotes: 2

Related Questions