Reading data from Excel in C#

You can use ADO.Net to read data from Excel - almost using it like a database with each sheet as a table. But there are some pre-requisites and quirks.

If you have 32 bit office installed, you'll need to install 32 bit providers to connect to it and if you have 64 bit office, you'll need the 64 bit drivers. You can get the drivers from here:

The connection string that you use to connect to the excel file depends on what kind of excel file it is:

Also from the same link above :
File Type (extension)                                                             Extended Properties
--------------------------------------------------------------------------------------------------
Excel 97-2003 Workbook (.xls)                                             "Excel 8.0"
Excel 2007-2010 Workbook (.xlsx)                                       "Excel 12.0 Xml"
Excel 2007-2010 Macro-enabled workbook (.xlsm)             "Excel 12.0 Macro"
Excel 2007-2010 Non-XML binary workbook (.xlsb)            "Excel 12.0"

This goes at the end of the connection string. The whole connection string would look something like this: "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\full\path\to\filename.xlsx; Extended Properties=Excel 12.0 Xml;"

The connection string that you use to connect to the excel file depends on what kind of excel file it is:

The code to read from a sheet looks like this (from here):

var fileName = string.Format("{0}\\fileNameHere", Directory.GetCurrentDirectory());

var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\\full\\path\\to\\filename.xlsx; Extended Properties=Excel 12.0 Xml;";

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

var ds = new DataSet();

adapter.Fill(ds, "anyNameHere");

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

var data = ds.Tables["anyNameHere"].AsEnumerable();

var query = data.Where(x => x.Field<string>("phoneNumber") != string.Empty).Select(x =>

    new MyContact

    {

    firstName= x.Field<string>("First Name"),

    lastName = x.Field<string>("Last Name"),

    phoneNumber =x.Field<string>("Phone Number"),

    });

Reading data like this has some horrible quirks and is not reliable. I wouldn't recommend this approach. From here the worst part is: It tries to guess at the type of a column based on the values in the first few rows. This is by default the first 8 rows (but can be anywhere between 1 and 16 or you can make it use all of 16384 rows) - all of this is based on registry entries and not the connection string! There suggestion from that link (which made me laugh out loud when I read it) "use a decent API like the Jakarta POI"