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,