Working with Microsoft Excel using C#

Library

To programmatically access MS Excel, you will need Microsoft Excel Extension library usually comes with Visual Studio: Microsoft.Office.Interop.Excel. I usually include it in header like this:

    using Excel = Microsoft.Office.Interop.Excel;

Open an Excel file

 

You can code like this:

  

  var app = new Excel.Application();

  var book = app.Workbooks.Open(fileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

Or simply like this:

     var book = app.Workbooks.Open(@"c:\test\data.xlsx");

Access a sheet, Range and cell

To access a sheet, say the first sheet (please note sheet index is 1-based rather than 0-based):

  var sheets = book.Worksheets as Excel.Sheets;

  var sheet = (Excel.Worksheet)sheets.get_Item(1);

To reach to a cell, you will do it through excel object Range, like this:

  var range = sheet.UsedRange;

Or for multiple cells:

  var range  = (Excel.Range) sheet.get_Range(sheet.Cells[1, 1], sheet.Cells[3,3]);

Or for single cell:

  var range = (Excel.Range) sheet.Cells[1, 1];

You got it, they are all 1-based. To access a cell:

 var range = sheet.UsedRange;

 for (int i = 1; i <= range.Rows.Count; i++)

 {

    for(int j=1; j <= range.Columns.Count; j++)

    var s = (string) (range.Cells[i, j] as Excel.Range).Value2;

               // ProcessString(s);

 }

Clean up

Make sure to clean up after use. Here's how and don't forget to add try catch around it.

 book.Close(false, Type.Missing, Type.Missing);

 System.Runtime.InteropServices.Marshal.FinalReleaseComObject(book);

 System.Runtime.InteropServices.Marshal.FinalReleaseComObject(app);