Read Excel files in .NET

First of all, some people may have a chance to work with excel files. However, to working with it for both reading and writing it has some difficulties e.g. how many ways to access excel files especially if you are not Windows users, which use Microsoft Excel in daily life. To solve this problem, what we need is a cross platform tool to access excel data.

Here, it is a tool for Excel Data Reader to read excel files in CodePlex (to use this tool in Linux, you must install Mono).

First, Know your format first, different version of Excel using different class.

FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
// or
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

Second, Load it as a DataSet, also includes the feature select the first row to be the column name.

//3. DataSet - Create column names from first row (true/false)
excelReader.IsFirstRowAsColumnNames = true;
//DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result = excelReader.AsDataSet();

Third, Get the data from DataTable.

foreach(DataRow row in result.Rows)
{
     // Do something
}

Finally, Don't forget to free resources (IExcelDataReader is IDisposable)

excelReader.Close();

More at: http://exceldatareader.codeplex.com

There are also many ways to access Excel files. e.g. using Microsoft.Jet.OLEDB, Microsoft.Office.Interop.Excel.

For example: using Microsoft.Jet.OLEDB to access a excel file.

String filePath = "somewhere";
// Load connection string
OleDbConnection con = new OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", filePath));
// select [column_name], ... from [sheet_name]$[range]
OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$A1:C3]", con);
// Load data into DataTable
DataTable dt = new DataTable();
da.Fill(dt);

foreach (DataRow row in dt.Rows)
{
     // Do something
}

excelReader.Close();