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