How to access rows and columns in Excel file using .Net

In my previous post, I described How to open excel file using .Net with COM Interop. Now that you have opened the workbook, next you want to access data in rows and columns. If you are working with some tabular form of data then sometime you want to figure out how many rows and columns are there in the worksheet and how you can iterate over each of those. There are properties of Worksheet object that you can use to access row and columns. But there are little confusing.

There are properties named Rows and Cells on Worksheet object. But these properties represent all the rows and cells used and unused. What this means is that the value returned by these properties represent the maximum rows and cells for worksheet. What we are interested in are rows and cells that have data. There is a property UsedRange that returns all the rows and cells that have been used. This property returns Range object that you can query to get count of Rows and then on each row you can query Cells.

Following code snippet shows how to access rows and cells.

Worksheet currentPriceSheet = workbook.Worksheets["12-13-2010"];
if (null != currentPriceSheet)
{
  Console.WriteLine("Number of rows: {0}", currentPriceSheet.Rows.Count);
  Microsoft.Office.Interop.Excel.Range usedRange = currentPriceSheet.UsedRange;
  if (null != usedRange)
  {
    Microsoft.Office.Interop.Excel.Range rows = usedRange.Rows;
    Console.WriteLine("Row Count: {0}", rows.Count);
    for (var i = 1; i <= rows.Count; i++)
    {
      Microsoft.Office.Interop.Excel.Range row = rows[i, Type.Missing];
      Microsoft.Office.Interop.Excel.Range cells = row.Cells;
      Console.WriteLine("Column Count: {0}", cells.Count);
    }
  }
}