In business applications very often have the functionality of the import / Export data from / do Excel. One way to retrieve data from Excel is to use OLE DB as a data provider. It’s not the most optimal solution, but sometimes it is not possible to influence its choice. In order to take advantage of this method to define the connection string. And here there is a problem because in order to work this way, Excel must be installed. In addition, the definition of the connection string depends on the version of Excel installed on your computer. Both of these conditions necessitate check this before attempting to retrieve data from Excel.
Not once in the course of writing the program is skipped, it being understood, on each computer that is running Excel. Additionally, you can also find quiet assumption, that to import XLS files, use the connection string to the version of Excel 2003, and XLSX files – to version 2007. While the first case the event should not be a problem because both the Excel 2007 i Excel 2010 contain the appropriate components, that allow for this type of access. While the second – XLSX import pliku – is not so rosy. This only works on a computer with Excel 2007. If anyone has Excel installed 2010 This exception is thrown
To correctly solve this problem, before starting the import file:
- check whether Excel is installed on your computer,
- check what version of Excel is installed on your computer.
Both conditions can be checked in much the same way.
Let’s start with the first. Will present here two solutions. The first is based on the type associated with downloading the application will check (in our case it is Excel.Application). While the second reads the information from the registry Excel.
Let me start by checking, or Excel installed on your computer:
The first way:
try
{
Type officeType = Type.GetTypeFromProgID("Excel.Application");
if (officeType == null)
{
MessageBox.Show("Excel is missing");
}
else
{
MessageBox.Show("Excel is present");
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
And the second way:
RegistryKey subKey = Registry.ClassesRoot.OpenSubKey(@"Excel.Application\CurVer");
if (subKey == null)
{
MessageBox.Show("Excel is missing");
}
else
{
MessageBox.Show("Excel is present");
}
The second element is to check, which version of Excel is installed on your computer. If you use the first method you can do this by checking the following property:
officeType.Assembly.FullName
In the case of my computer, I received the following value of this property:
Microsoft.Office.Interop.Excel, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
In the second method, use the following command:
subKey.GetValue("")
In this case, will be given only version of Excel, that is:
Excel.Application.12
Just remember to Excel version of the mapping:
– Excel 2003 – version 11,
– Excel 2007 – version 12,
– Excel 2010 – version 14.
I prefer the second method and immediately check the two conditions. For this purpose, you can create the following function:
private int GetExcelVersion()
{
RegistryKey subKey = Registry.ClassesRoot.OpenSubKey(@"Excel.Application\CurVer");
if (subKey != null)
{
try
{
string[] temp = ((string)(subKey.GetValue(""))).Split('.');
int excelVersion;
if (int.TryParse(temp[temp.Length - 1], out excelVersion))
{
return excelVersion;
}
return 0;
}
catch
{
return 0;
}
}
return 0;
}
In the case, if Excel is not installed, it returns the value 0. In any other case, the current version of Excel.
Leave A Comment