Read Excel file from C#

Common way is:

var fileName = string.Format("{0}\\fileNameHere", Directory.GetCurrentDirectory());
var connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", fileName);

var adapter = new OleDbDataAdapter("SELECT * FROM [workSheetNameHere$]", connectionString);
var ds = new DataSet();

adapter.Fill(ds, "anyNameHere");

DataTable data = ds.Tables["anyNameHere"];

see details at: http://stackoverflow.com/questions/15828/reading-excel-files-from-c-sharp

 

the connect string is: 

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;

Extended Properties="Excel 12.0 Xml;HDR=YES";

(HDR=YES: read the header)

see the connect strings of Excel: http://www.connectionstrings.com/excel-2007/

 

There is an error if your system is your system is 64bit: 

Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

Change the application to 32bit to avoid this problem, see: http://stackoverflow.com/questions/238625/microsoft-ace-oledb-12-0-provider-is-not-registered

 

If using this code to read Excel file on Windows Azure, we can't change it to 32bit, so we need find another way: 

http://stackoverflow.com/questions/3663245/read-excel-file-and-insert-records-in-database-in-c-windows-azure

that is: 

For the time being you are basically restricted to .NET-only options:

• EPPlus for XLSX
• NPOI for XLS

 

EPPlus examples: 

http://www.codeproject.com/Articles/680421/Create-Read-Edit-Advance-Excel-2007-2010-Report-in#1

http://blog.fryhard.com/archive/2010/10/28/reading-xlsx-files-using-c-and-epplus.aspx

 

NPOI source: 

https://github.com/tonyqus/npoi