Finding and extracting data from Excel

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

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...

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();
    }
}