以编程方式检索Excel库的版本 [英] Retrieving the Version of the Excel Library Programmatically

问题描述

有没有办法编程检索C＃中使用的Excel Interop库的版本？

我知道我可能会在注册表中查找安装的Microsoft Office实例，但如果Excel库包含此信息，我很好奇。

当我引用库时，我知道这个信息包含在Visual Studio中，但是在运行时我看不到这些信息。

我问这个是因为它在保存工作簿时指定文件扩展名，并且如果使用'.xls'保存了2007工作簿，则2007会向扩展名指出

解决方案

简单的答案是这样的：你不需要知道加载哪个PIA版本来解决你的问题。你想知道的是哪个版本的Excel正在运行。虽然运行的Excel版本和PIA版本通常相同，但不一定是，在这种情况下，它是您关心的Excel版本，而不是PIA版本。 / p>

确定哪个Excel版本正在运行并不困难，但它不是那么容易，因为它应该是因为 Excel.Application.Version 属性可以返回诸如11.0或9.0a等字符串，因此不一定能直接将其解析为整数值。您可以依赖于小数点左边的所有内容（。）都是版本号，但下列代码适用于所有版本：

  Excel.Application excelApp = new Excel.Application（）; 
 
 string versionName = excelApp.Version; 
 int length = versionName.IndexOf（'。'）; 
 versionName = versionName.Substring（0，length）; 
 
 // int.parse需要使用美国文化。 
 int versionNumber = int.Parse（versionName，CultureInfo.GetCultureInfo（en-US））; 
 
 if（versionNumber> = 12）
 {
 // Excel 2007或更高版本。 
} 
 else 
 {
 // Excel 2003或更低版本。 
} 
  

对于PIA vs. Excel对象模型问题，哪个主要互操作程序集（ PIA）您的程序集引用在开发时与哪个PIA在运行时实际加载到目标机器上可能是不同的。例如，如果引用Excel 2002 PIA并且目标机器正在使用Excel 2007，那么（通常），Excel 2007 PIAs将被加载。规则是在目标机器上可用的最高版本的Office PIA在运行时被加载。

它变得更复杂，因为它是可能的（虽然，绝对不可能）目标机器，例如，Excel 2007加载在目标机器上，但最高可用的PIA将用于Excel 2003.在这种情况下，Excel 2007将加载，但您的代码将针对Excel 2003 PIA 。相反也可以发生：Excel 2007 PIA可用，但机器上安装的最高实际版本是Excel 2003 - 在这种情况下，Excel 2003将加载，但您的代码将对Excel 2007 PIA进行工作。 p>

这些场景在标准设置中是不太可能的。最有可能发生在开发人员的机器上，其中（1）多台Excel版本同时存在于机器上，或者（2）已经添加和删除了Excel版本，但是没有删除它们的PIA这本身也不太可能，因为我相信自动卸载PIA，但我可能会错误的）。

有关更多信息，请参阅Andrew Whitechapel的文章为什么VS开发不支持多个版本的Office？

虽然所有这些场景都听起来有点吓人，Excel中的界面对象模型非常一致，因此几乎100％向后兼容。一般来说，如果绑定到给定的Excel PIA版本，那么您的代码将以该版本的Excel或更高版本成功运行，无论涉及到哪些场景都是如此。尽管如此，您已经发现了一些怪癖，但关键是要知道哪个Excel版本正在运行。了解哪个PIA正在运行并不重要 - 只要您开发的PIA版本（或更高版本）可用，那么PIA本身不应该导致任何问题。

编辑：追溯到phsr的评论：

当我保存工作簿I
生成，我确实需要知道PIA I
正在反对，因为它将以该格式保存
。如果我获得
版本的PIA，我可以给
文件名来更正扩展名（xls
vsxlsx）。即使2007年工作簿
保存为xls，它将给出
警告，说明是与文件扩展名指定的不同的格式
。
我想用
隐藏该警告，使用正确的扩展名。你的
点是正确的，因为它真的是
最终没关系（文件
仍然会打开），但是在一些
实例（像这样）

我知道这可能是感觉这是PIA的问题，但它是Excel对象模型实际上加载在这里重要，而不是PIA。此外，Excel应用程序版本号有相当于PIA版本的机率为99.9％。它们是不同寻常的。而且它们的区别在于，Excel版本重要，而不是PIA（只要PIA与您开发的PIA版本相同或更高）。

为了弄清楚这一点，PIAs只指定涉及的接口，而不是功能。 Excel 2003 SaveAs方法与 Excel 2007 SaveAs方法具有完全相同的参数签名a>，所以PIAs在这里没有区别。 >

为了解决你的问题，我可以建议采取两种可能的行动方案之一：

（1）尝试我以上给出的代码来确定正在运行的Excel版本。如果你这样做，并相应地调整你的代码，它将工作，我保证。如果它失败，显示你的代码，我确定我们可以让它工作。

（2）不要在你给的工作簿名称中指定扩展名它。让Excel应用程序为您添加默认扩展名。你是正确的，如果你在Excel 2007中保存时指定一个.xls扩展名，它将会遵守这个扩展名，而不是工作簿版本。然而，最简单的是在保存工作簿时省略扩展名，Excel应用程序会自动将.xls或.xlsx附加到工作簿名称，具体取决于当前正在运行的Excel版本：

  Excel.Application excelApp = new Excel.Application（）; 
 Excel.Workbook工作簿= excelApp.Workbooks.Add（Type.Missing）; 
 
 workbook.SaveAs（
MyWorkbook，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing，
 Excel.XlSaveAsAccessMode.xlNoChange， Type.Missing，Type.Missing，Type.Missing，
 Type.Missing，Type.Missing）; 
  

上面的结果是一个名为MyWorkbook.xls或MyWorkbook.xlsx的工作簿，具体取决于运行的是哪个版本的Excel。简而言之，让Excel为您做的工作，以便您不用担心。

Is there a way to programatically retrieve the version of the Excel Interop Libraries being utilized with C#?

I know I could probably look in the registry to figure out the installed instance of Microsoft Office, but I was curious if the Excel Libraries contain this information.

I know this information is contained in Visual Studio when you reference the library, but I can't see that information at runtime.

I am asking this because it dictates the file extension when you save the workbook, and if you save a 2007 workbook with '.xls', 2007 complains about the extension being incorrect.

解决方案

The short answer is this: you do not need to know which PIA version is loaded to solve your problem. What you want to know is which version of Excel is actually running. Although the running Excel version and the PIA version are usually the same, they do not have to be, and, in this case, it is the Excel version that you care about, not the PIA version.

Determining which Excel version is running is not hard, but it is not quite as easy as it should be because the Excel.Application.Version property can return strings such as "11.0" or "9.0a" or the like, so one cannot necessarily directly parse it into an integer value. You can rely on the fact that everything to the left of the decimal point (".") is the version number, however, so the following code works for all versions:

Excel.Application excelApp = new Excel.Application();

string versionName = excelApp.Version;
int length = versionName.IndexOf('.');
versionName = versionName.Substring(0, length);

// int.parse needs to be done using US Culture.
int versionNumber = int.Parse(versionName, CultureInfo.GetCultureInfo("en-US"));

if (versionNumber >= 12)
{
    // Excel 2007 or above.
}
else
{
    // Excel 2003 or below.
}

As for the PIA vs. Excel object model issue, which Primary Interop Assembly (PIA) your assembly references when developing versus which PIA is actually loaded at runtime on the target machine can be different. For example, if you reference the Excel 2002 PIAs and the target machine is using Excel 2007, then (generally) the Excel 2007 PIAs will get loaded. The rule is that the highest-versioned Office PIA available on the target machine gets loaded at runtime.

It gets even more complicated in that it is possible (although, definitely not likely) for the target machine to have, say, Excel 2007 loaded on the target machine, but the highest-available PIAs be for Excel 2003. In this case, Excel 2007 would load, but your code would be working against the Excel 2003 PIAs. The reverse can occur as well: the Excel 2007 PIAs are available, but the highest actual version installed on the machine is Excel 2003 -- in this case Excel 2003 will load, but your code will be working against the Excel 2007 PIAs.

These scenarios are very unlikely in a standard setup. It is most likely to occur on a developer's machine where either (1) more than one Excel version is present on the machine at the same time, or (2) Excel versions have been added and removed, but the PIAs not removed with it (which, in itself, is also unlikely, as I believe the PIAs are uninstalled automatically, but I could be wrong about this).

For more on this, see Andrew Whitechapel's article Why is VS development not supported with multiple versions of Office?

While all these scenarios sound a bit scary, the interfaces in Excel's object model are extremely consistent and therefore are nearly 100% backward-compatible. In general, if you bind to a given Excel PIA version, then your code will run successfully against that version of Excel or higher, pretty much regardless of the scenio involved. There are a few quirks though, as you have discovered, but the key is to know which Excel version is actually running. Knowing which PIA is running is not generally important -- as long as the PIA version you developed against (or higher) is available, then the PIA itself should not cause any issues.

Edit: Follow up to phsr's comment:

When I am saving the Workbook I generate, I do need to know what PIA I am working against, because it will be saved in that format. If I get the version of the PIA, I can give the file name to correct extension ("xls" vs "xlsx"). Even if a 2007 workbook is saved as 'xls', it will give the warning about being a different format than specified by the file extension. I would like to hide that warning by using the correct extension. Your point is correct in that it really doesn't matter in the end (the file will still open) but at in some instances (like this one) it does.

I know that it might "feel like" it is the PIA that is the issue, but it is Excel object model that is actually loaded that matters here, not the PIA. In addition, there is a 99.9% chance that the Excel application version number is the SAME as the PIA version. It is extremely rare that they would differ. And where they do differ, it would be the Excel version that matters, not the PIA (so long as the PIA is the same version as the PIA you developed against, or higher).

To try to clarify this, the PIAs only specify the interfaces involved, not the functionality. The Excel 2003 SaveAs method has exactly the same parameter signature as the Excel 2007 SaveAs method, so the PIAs do not differ here. How the SaveAs method is executed, however, depends on which version of the Excel object model is actually running.

To fix your problem, I can suggest taking one of two possible courses of action:

(1) Try the code I gave above to determine the Excel version that is running. If you do this and adjust your code accordingly it will work, I promise. If it fails, show your code and I'm sure we can get it to work.

(2) Don't specify the extension in the workbook name that you give it. Let the Excel application add the default extension for you. You are correct in that if you specify a ".xls" extension when saving in Excel 2007, it will respect that extension, but not the workbook version. Easiest here, however, is to simply omit the extension when saving your workbook and the Excel application will automatically append ".xls" or ".xlsx" to the workbook name, depending on which Excel version is currently running:

Excel.Application excelApp = new Excel.Application();    
Excel.Workbook workbook = excelApp.Workbooks.Add(Type.Missing);

workbook.SaveAs(
    "MyWorkbook", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
     Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, 
     Type.Missing, Type.Missing);

The result of the above is a workbook named either "MyWorkbook.xls" or "MyWorkbook.xlsx", depending on which version of Excel is running. In short, let Excel do the work for you so that you don't have to worry about it.

这篇关于以编程方式检索Excel库的版本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！