One of the feature of the application I’m working on these days is allowing the user to import data from Excel 2003 and 2007 files. After some research I found out that OLEDB is the only way to read both Excel formats without any problems.
For this example I split the code into two functions. The first is OpenExcelFile which opens the Excel file and sets the string array data field workSheetNames to the names of the sheets in the file. This is done by reading the names of the tables in the schema, because OLEDB opens the whole file as a schema and the sheets in the file as tables in the schema. The function takes one argument called isOpenXMLFormat, that is set to true to open a 2007 file and false to open a 2003 or previous file. The only difference between the two formats is the connection string used.
public void OpenExcelFile(bool isOpenXMLFormat)
{
//open the excel file using OLEDB
OleDbConnection con;
if (isOpenXMLFormat)
//read a 2007 file
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
fileName + ";Extended Properties=\"Excel 8.0;HDR=YES;\"";
else
//read a 97-2003 file
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
fileName + ";Extended Properties=Excel 8.0;";
con = new OleDbConnection(connectionString);
con.Open();
//get all the available sheets
System.Data.DataTable dataSet = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//get the number of sheets in the file
workSheetNames = new String[dataSet.Rows.Count];
int i = 0;
foreach (DataRow row in dataSet.Rows)
{
//insert the sheet's name in the current element of the array
//and remove the $ sign at the end
workSheetNames[i] = row["TABLE_NAME"].ToString().Trim(new[] { '$' });
i++;
}
if (con != null)
{
con.Close();
con.Dispose();
}
if (dataSet != null)
dataSet.Dispose();
}
The second function is GetWorksheet, and it takes the name of the sheet as an argument. Here the file is opened again using the connection string set by OpenExcelFile. And executes a SQL SELECT on the name of the sheet passed in the argument, and returns the result as a DataTable.
public System.Data.DataTable GetWorksheet(string worksheetName)
{
OleDbConnection con = new System.Data.OleDb.OleDbConnection(connectionString);
OleDbDataAdapter cmd = new System.Data.OleDb.OleDbDataAdapter(
"select * from [" + worksheetName + "$]", con);
con.Open();
System.Data.DataSet excelDataSet = new DataSet();
cmd.Fill(excelDataSet);
con.Close();
return excelDataSet.Tables[0];
}
Download Source Code
This is what I’m looking for. Thank you very much.
You are welcome Dagu. I appreciate the comment.
Reading Excel 2003 and 2007 Files Using OLEDB | CodeHill…
Thank you for submitting this cool story – Trackback from DotNetShoutout…
Hi.
I have been successful in reading all the sheets in an excel book. But after I read the excel, it becomes locked. So I can only open it in the read only mode.
I have googled. But very thin voices heared over there. Any idea would be much useful.
With appreciation. Thanks.
you can use only
connectionString = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” +
fileName + “;Extended Properties=\”Excel 8.0;HDR=YES;\”";
for both types of excel file
Hi ivan,
I used:
connectionString = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” +
fileName + “;Extended Properties=\”Excel 8.0;HDR=YES;\””;
And now there is no problem of locking excel files when text is extracted from them.
For, if they works for both, i have saved one excel file with xls format and this has worked. I haven’t tested from excel 2003 iteself. However, I hope this should work. Thank you!
Gr8 post. this is what I was looking for, exactly according to my need. Thank you very much. but… what I want othe than this is, I want to read the comments from each cell, is it possible ?
Hai, It’s solve my all excel issues. Thank you very
much.
thanks its solved my issue… good post keep it up.
i wanna run a “complex” query like the following:
SELECT *
FROM ( SELECT customerNo, rate, dateApproved
FROM ['worksheet$']
WHERE dateCreate = (SELECT MAX(dateCreate) FROM ['worksheet$'] )
AND status = ‘Approved’) AS T1
WHERE dateApproved = SELECT MAX(dateApproved) FROM T1
this way, it does not recognise the T1 object.Is there a way to achieve this?
I am getting error in this line…
workSheetNames[i] = row["TABLE_NAME"].ToString().Trim(new[] { ‘$’ });
Please guide me how to put sheet name.
prashant, the posting is missing a string array declaration. Add “String[]” before “workSheetNames[i].
amgad, thanks for the excellent post.
Beto
Thank YOU Beto for dropping by and commenting
This code is most of what I need but would it be possible to take a dataset or worksheet from one and write to a worksheet in another file as a whole unit? I would like to avoid insert as it would require name value pairs and their is a lot of formatting and images in the orginal.
Thanks,
When I read this I see here are people who know a lot more then about locking and connecting problems. Maybe it is “not done” to place my question (sorry), but I’m a little out off options and couldn’t get answers on other forums. If it’s a problem let me know I delete the question.
We have a Excel 2007 database with information about suppliers. When we make a new document in Word, with mail-merge the information from a supplier is put in the document (it uses ODBC connection).
This worked fine in Office 97, but not anymore in 2007.
* I like to make the Excel file a template, everyone can see it (excel file made from template) and the original file is good protected against changes. Sadly it is not possible to use a template as a data source in mail merge.
* When I use a xlsx (2007) file and one user mail merge, the excel file is locked for all other users, this doesn’t work.
* When I save the file in “old format” (xls) and give it “read only” it works fine for the users.
The problem with the last solution is with editing the Excel file. When no one is using it (no active mail merge) it can be opened for editing and still others can then mail merge with it. But when someone has then created a open mail merge, the original file (with changes) can’t be saved in Excel.
A year ago I spend a couple of day’s with this problem and asked in forum’s, but no luck. The past year we got more and more frustrated with it, people must stay at work to make changes to the Excel file after working hours. And we are working with XLS for this file, it all sucks.
So I looked at it the last 2 day’s again and tried many things (share, read only, csv, different connection types, etc.) but no luck.
Does anyone have a solution or a hint to solve this problem?
Many thanks
Peter
Hi Peter,
I understand your problem, unfortunately I don’t use Office that much so I don’t know how to solve it. But I hope one of my readers would take the time to answer it.
Hi,
Love you’re article, and I use it for reading .xls and .xlsx files to a dataset.
I tried to turn it around because I want to write also. Do you have some tips? maybe some code
Greetz Jonathan
[...] in the Excel file, those can be different each time. First I read an excel file with this code: http://codehill.com/2009/01/reading-…s-using-oledb/ I change some things in the DataTable and then I want to write to a copy of the first excel file. [...]
[...] February 22, 2010 by Basketman http://codehill.com/2009/01/reading-excel-2003-and-2007-files-using-oledb/ [...]
Thanks for the Post . works perfectly. Just what I needed in my code.
thanks for the post. Really saved my day