Read an .xls file using OleDb in seconds

How often we have been involved with Microsoft's Office applications like MS Word, MS Excel in our day-to-day life. Every time we hear about importing/exporting the contents of an Excel file. I fortunately have been involved with both the tasks.

Here, I would like to show a fast way to read an excel file using OleDb. I have tried to make it compatible with excel 2003(.xls) as well as 2007(.xlsx) formats.

Add these references at the top of the class file:-

using System; using System.Text; using System.Data; using System.Data.OleDb;

Create a method, name it ReadExcelContents and pass the fileName as a parameter to that function.

public static DataTable ReadExcelContents(string fileName) { try { OleDbConnection connection = new OleDbConnection(); if (fileName.ToLower().IndexOf(".xlsx") != -1)//Checking source file for Excel 2007 { if (CheckOfficeVersion.ExcelVersion() == 2007)//Checking if Excel 2007 is installed or not { connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source="+ fileName); //Excel 2007, .xlsx } else//if Excel 2003 or other are installed { MessageBox.Show("Sorry! You do not have Excel 2007 installed on your machine.", "Please Note", MessageBoxButtons.OK, MessageBoxIcon.Error); } } else { connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source="+ metadataFile); //Excel 97-2003, .xls } string excelQuery = @"Select [Name], [Address], [Age], [Occupation], [Degree] FROM [Sheet1$] where LTRIM(RTRIM([Name]))<>'' & LTRIM(RTRIM([Degree]))<>'' connection.Open(); OleDbCommand cmd = new OleDbCommand(excelQuery, connection); OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.SelectCommand = cmd; DataSet ds = new DataSet(); adapter.Fill(ds); DataTable dt = ds.Tables[0]; connection.Close(); return dt; } catch(Exception ex) { MessageBox.Show("Sorry! "+ex.Message, "Please Note", MessageBoxButtons.OK, MessageBoxIcon.Error); return null; } }

Explanation:

• 
  
  At first, we are opening an OleDb connection, checking the file extenstion to see if the selected file is having an Excel 2007(.xlsx) format. If this is the case, we are checking the system HKLM(Local machine level) registry to see if Excel 2007 is even installed on the client system or not?
• 
  
  If present we are setting the connectionString properties according to Excel 2007 where
  Provider: Microsoft.ACE.OLEDB.12.0 andExtended Properties: Excel 12.0Otherwise, if the user has excel 2003 & has selected an Excel 2007 file, we are throwing an error message, else, we know that the user has selected an Excel 2003 file, so we set the Provider: Microsoft.Jet.OLEDB.4.0 and Extended Properties: Excel 8.0
• 
  
  Alongwith this we need to pass the filename as the DataSource.Now that you must have understood that it is very similar to Ado.net, our next step should be to write the query. In the query we need to pass the ColumnNames same as the ones present in xl column headers. You can provide all clauses such as where, having, etc.
• 
  
  Rest is straight forward for any .Net developer as to opening a connection, setting up a CommandObject with the excelQuery & connectionString. Create an OleDbAdapter object and set its command property. Execute the adapter.Fill(ds) to put the contents read in a DataSet.
• 
  
  Close the connection after reading is over so that there are no fileOpen conflicts and return the datatable.

If you felt that this post has been of help to you, please drop in a small note. It would be a big encouragement for me to write more.