Manish Sharma : Querying Excel11 and Excel12 files

Querying Excel11 and Excel12 files

An excel11 (.xls) file can be very easily queried using "Jet OLEDB 4.0" provider. C# snippet code for quering excel11 files is as follows,

String strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Sample.xls;Extended Properties=Excel 8.0";

OleDbConnection objConn = new OleDbConnection(strConn);

DataTable dtObject = new DataTable();

objConn.Open();

OleDbCommand objComm = new OleDbCommand("select * from [SummarySheet$A48:M500]", objConn);

OleDbDataReader objReader = objComm.ExecuteReader();

dtObject.Load(objReader);

Similarly excel12(.xlsx) files can also be queried using OLEDB Access Engine (ACE). Code snippet to query excel12 file is as follows,

String strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Sample.xlsx;Extended Properties=Excel 12.0";

OleDbConnection objConn = new OleDbConnection(strConn);

DataTable dtObject = new DataTable();

objConn.Open();

OleDbCommand objComm = new OleDbCommand("select * from [SummarySheet$A48:M500]", objConn);

OleDbDataReader objReader = objComm.ExecuteReader();

dtObject.Load(objReader);

Some really helpful snippets are also available at following location,