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,


http://www.microsoft.com/downloads/details.aspx?FamilyID=8D46C01F-E3F6-4069-869D-90B8B096B556&displaylang=en


 

Comments (0)

Skip to main content