When i was first presented with this problem, I had no idea how i was going to go about doing it, searching the internet for a little while is always my first step. Even when i get to one solution i usually keep researching to see if there is anything better, that way i at least have a few choices before I go coding in the wrong direction.
In any event, the task was to integrate a Windows Form with excel, to import data easily into another application or a database. There are a few problems with the thought of this, “how do i interact with excel” and “how do i deal with different versions?”
First lets deal with the interaction, this may not work for all version but it does with 2007 and 2003, which i can test from my laptop.
Excel is basically like using a SQL database, there are some differences on how to do things, but its easiest I found if you just import to a data table and work with the data from there, rather than trying to work through excel. (Why do that if you could just open excel anyway?) So to get it started were going to use the System.Data.OledDb assembly, make sure to add that in the “using” statements at the top.
Once we have that we want to create a new OleDbConnection:
private OleDbConnection conn;
Then down a little bit more in the constructor we want to check the version of excel they are using, i just do this by simply making a bool and basing it off of the file extension. “.xls” is 2003 and “.xlsx” is 2007, i just check that and set a string for 2007 or 2004. Once we get that out of the way we can make the connection string:
if(excelVer == "2007") {
conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + file + ";" + "Extended Properties=Excel 12.0;");
}
else {
conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + file + ";" + "Extended Properties=Excel 8.0;");
}
As you can see above there are two different connection strings for the respective versions of Excel. Based on what file they are using were going to want to change that connection string, so if you allow you user to change the file whenever they want in your application, you need to change this with it!
After that its as simple as SQL. Lets create a OleDbDataAdapter:
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [" + worksheetName + "$]", conn);
Then we just use da.Fill(dataTable) and we can bind that to a data grid or do whatever with it! Thats it! If you need any help, just leave a comment and i’ll do what i can to get back to you!
Wade Lasson 