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:-
|public static DataTable ReadExcelContents(string fileName)|
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);
connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source="+ metadataFile); //Excel 97-2003, .xls
string excelQuery = @"Select
where LTRIM(RTRIM([Name]))<>'' &
OleDbCommand cmd = new OleDbCommand(excelQuery, connection);
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = cmd;
DataSet ds = new DataSet();
DataTable dt = ds.Tables;
MessageBox.Show("Sorry! "+ex.Message, "Please Note", MessageBoxButtons.OK, MessageBoxIcon.Error);
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.