Here in this article I am describing how we can code dynamically against Excel 2007 and Excel 2003 i.e. if 2007 is there, we will work with that otherwise we need to code against 2003. How we can achieve it. The full source code is attached along with this. To attain this goal, use below steps
1. Keep the connections strings of both 2007 and 2003 in app.config like below
<add key="XLConnectionString" value="Provider=Microsoft.Jet.OLEDB.4.0;Data
Source={0};Extended Properties='Excel 8.0;HDR=Yes;'"/>
<add key="XLXConnectionString" value="Provider=Microsoft.ACE.OLEDB.12.0;Data
Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES;'"/>
Now we have both connections strings. Next step is to load this connection strings based on Excel version. How we can find the excel version?
2. Find the Excel version by reading the registry. I have used GetExcelRegistryPath() which will read the registry path of current user for excel.exe. If could not track, again will read the registry path of current machine scope for same excel.exe. Once you have the path get the major version number by FileVersionInfo.GetVersionInfo(). Method GetExcelRegistryPath() making the path search based on a root registry path, @"Software\Microsoft\Windows\CurrentVersion\App Paths". The entry "Excel.exe" will be searched under this path for both CurrentUser and CurentMachine based on availability.
I have a property named XLConnectionString which will dynamically return the connection string like below
private string XLConnectionString
{
get
{
if (GetExcelVersion().Equals(OfficeVersion.Office2003))
{
return ConfigurationManager.AppSettings["XLConnectionString"];
}
else if (GetExcelVersion().Equals(OfficeVersion.Office2007))
{
return ConfigurationManager.AppSettings["XLXConnectionString"];
}
else
{
return string.Empty;
}
}
}
3. New excel sheet will be created based on above mentioned dynamic connection strings. I used managed OLEDB provider. This provider is the main difference between 2007 and 2003 connection strings. As we have OLEDB connection, we can use the SQLQuery to create a new excel sheet just like creating a new table, using SELECT statement. The query I used is as below
"CREATE TABLE " + sheetName + "
([Column1] string, [Column2] string)"
Variable "sheetName" will be passed dynamically during call. So logically each sheet will be considered as separate tables by OLEDB.
4. The interface to test this is very simple. You have 2 buttons to create each versions of excel sheet like below
Here some additional checking also added like, once you click on "Create XL 2007 File" button, it will check whether excel 2007 is available or not. Modifications you can do like make this logic little more vibrant by adding other office products version checking e.g. Word, Access etc... The base registry path remains same. You only need to change the .exe name inside GetExcelRegistryPath().