Finding and extracting data from Excel

Question

I'm trying to write an app that will open an excel spreadsheet find the worksheet with the correct name and iterate through the rows until I find the cell at column 0 that contains the text "Cont Date" and then read through until I find the first blank cell (column 0 as well). I'm getting hung up on how to iterate through the rows.

Here's what I have so far:

public static void LoadFromFile(FileInfo fi)
{
    Application ExcelObj = new Application();

    if (ExcelObj != null)
    {
        Workbook wb = ExcelObj.Workbooks.Open(fi.FullName,
             Type.Missing, true, Type.Missing, Type.Missing,
             Type.Missing, Type.Missing, Type.Missing, Type.Missing,
             Type.Missing, Type.Missing, Type.Missing, Type.Missing,
             Type.Missing, Type.Missing);

        Sheets sheets = wb.Worksheets;

        foreach (Worksheet ws in sheets)
        {
            if (ws.Name == "Raw Data")
                LoadFromWorkSheet(ws);
        }

        wb.Close(false, Type.Missing, Type.Missing);
    }
}

public static void LoadFromWorkSheet(Worksheet ws)
{
    int start = 0;
    int end = 0;

    // Iterate through all rows at column 0 and find the cell with "Cont Date"
}

Apparently you can't

foreach(Row row in worksheet.Rows)
{

}

EDIT::

What I did was this:

for (int r = 0; r < 65536; r++)
{
    string value = ws.Cells[r, 0].Value;
}

Which gives me the following exception when trying to read the value of the cell:

Exception from HRESULT: 0x800A03EC

Answer 1

You can use the Cells property and since columns start with 1, I think you meant column 1:

int contDateRow=0;
int firstBlankRowAfterContDate=0;

for (int row=1;row<=woksheet.Rows.Count;++row)
  if (worksheet.Cells[row,1].Value=="Cont Date")
  {
    contDateRow=row; 
    break;
  }

if (contDateRow!=0)
{
  for (int row=contDateRow;row<=woksheet.Rows.Count;++row)
    if (worksheet.Cells[row,1].Value=="")
    {
      firstBlankRowAfterContDate=row; 
      break;
    }
}

// Do something with contDateRow and firstBlankRowAfterContDate...

Answer 2

OK... a few things...

First, get yourself a "Range" to work with. For your purposes, try this:

Microsoft.Office.Interop.Excel range = worksheet.get_Range("A1");

Now that you have your range, you can find the extent of each column and row with a function, like so:

private Point GetSheetBounds(Excel.Range range)
{
    int maxY = range.get_End(Excel.XlDirection.xlDown).Row;
    int maxX = range.get_End(Excel.XlDirection.xlToRight).Column;

    return new Point(maxX, maxY);
}

This will tell you how far you have to loop so that you're not going from 0 to infinity. :P

Now you can do something like this to loop through the rows in the columns:

for (int i = 1; i < this.GetSheetBounds(range).Y; i++) //i = 1 because Excel doesn't use zero-based indexes
{
   if (range[i, 1] != null && range[i, 1].Value != null && range[i, 1].Value == "Cont Date")
   {
      //do whatever you need to do
   }
}

Finally, as you're using COM, make sure you dispose EVERYTHING you create with a function kind of like this:

private void ReleaseObject(object obj)
{
    if (obj != null)
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        obj = null;
        GC.Collect();
    }
}