How to have an excel addin read rows from a worksheet until no more data?

Question

I've started writing a Com addin for Excel 2003 using C#. I'm looking for a code example showing how to read in cell data from the active worksheet. I've seen that you can write code like this:

Excel.Range firstCell = ws.get_Range("A1", Type.Missing);
Excel.Range lastCell = ws.get_Range("A10", Type.Missing);
Excel.Range worksheetCells = ws.get_Range(firstCell, lastCell);

to grab a range of cells. What I could use help with is how to read the cell data when you don't know how many rows of data there are. I may be able to determine the starting row that the data will be begin at, but there will be an unkown number of rows of data to read.

Could someone provide me w/ an example of how to read rows from the worksheet until you come across a row of empty cells?

Also does anyone know how to grab the range of cells the user has selected?

Any help would be greatly appreciated. This seems like a powerful dev tool, but I'm having trouble finding detailed documentation to help me learn it :)

Answer 1

I can think of 2 approaches. One first option is to use a named range, rather than the position of the cells. In this case, you could for instance name A1:A10 "MyList", and read the contents into an array using

Excel.Range range = worksheet.get_Range("MyList", Type.Missing);
object[,] data = (object[,])range.Value2;

The benefit is that if you resize the named range (by inserting rows or columns in your worksheet for instance) this will still work just fine.
You could also read until you encounter a null or empty cell, but you will have to read cell by cell. It's not an issue if you have few cells, but if you are dealing with larger amounts of data (say a 100 cells), reading cell by cell will cost you in speed. I haven't tried the code but something along these lines should work to start from startRow and go on until an empty row is found:

     int startRow = 1;
     bool hasContent = false;

     int row = startRow;
     do
     {
        var cell = (Excel.Range)sheet.Cells[row,1];
        if (cell.Value2 != null)
        {
           hasContent = true;
           row++;
        }
     }
     while (hasContent);