C# 和 Excel 互操作
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1111935/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
C# and Excel interop
提问by Flack
One of my users is having an issue when trying to open an Excel file through my C# app.
我的一位用户在尝试通过我的 C# 应用程序打开 Excel 文件时遇到问题。
Everything works ok when I run it from my machine and it works for other users. I am no Excel interop expert so hopefully you guys can help me out.
当我从我的机器上运行它时一切正常，它适用于其他用户。我不是 Excel 互操作专家，所以希望你们能帮助我。
Here is how it is set up:
这是它的设置方式：
I added a reference to my app to Microsoft.Office.Interop.Excel.dll, version 10.0.4504.0 (which I believe is Excel 2002). On my machine I have installed Excel 2007. In my code I try to open a worksheet like so:
我添加了对 Microsoft.Office.Interop.Excel.dll 版本 10.0.4504.0（我认为是 Excel 2002）的应用程序的引用。在我的机器上，我安装了 Excel 2007。在我的代码中，我尝试打开一个工作表，如下所示：
using Microsoft.Office.Interop
...
Microsoft.Office.Interop.Excel.ApplicationClass _excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook excelWorkbook = _excelApp.Workbooks.Open(workbookPath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", false, false, 0, true, false, false);
Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item(1);
I logged the users version as Excel 9.0 (which is 2000). The error that the user gets is:
我将用户版本记录为 Excel 9.0（即 2000）。用户得到的错误是：
Exception='System.AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
at Microsoft.Office.Interop.Excel.Workbooks.Open(String Filename, Object UpdateLinks, Object ReadOnly, Object Format, Object Password, Object WriteResPassword, Object IgnoreReadOnlyRecommended, Object Origin, Object Delimiter, Object Editable, Object Notify, Object Converter, Object AddToMru, Object Local, Object CorruptLoad)
It looks like the workbook cannot be opened. The file was confirmed to exist and was just loose on the user's PC at C:. I thought by using the PIA I wouldn't have to worry about Excel version issues. I know there are other users using Excel 2000, and it works on my development machine.
工作簿似乎无法打开。该文件被确认存在并且只是松散在用户 PC 上的 C: 处。我认为通过使用 PIA，我不必担心 Excel 版本问题。我知道还有其他用户在使用 Excel 2000，它可以在我的开发机器上运行。
Any ideas? Maybe my calls to open the Excel file should be changed? The unfortunate thing is that I am unable to reproduce it.
有任何想法吗？也许我应该更改打开 Excel 文件的调用？不幸的是，我无法重现它。
采纳答案by Mike Rosenblum
"I thought by using the PIA I wouldn't have to worry about Excel version issues. I know there are other users using Excel 2000, and it works on my dev machine."
“我认为通过使用 PIA，我不必担心 Excel 版本问题。我知道还有其他用户在使用 Excel 2000，而且它可以在我的开发机器上运行。”
Almost true, but not quite.
几乎是真的，但不完全是。
By developing against the PIA for Excel 2002, your program will be compatible for all versions of Excel 2002 (10.0) and above. The failing machine, however, is running with Excel 2000 (9.0), which is a version belowthe version that you have developed against.
通过针对 Excel 2002 的 PIA 进行开发，您的程序将兼容 Excel 2002 (10.0) 及更高版本的所有版本。但是，故障机器运行的是 Excel 2000 (9.0)，该版本低于您开发的版本。
There is no officially supported PIA below Excel 2002, so if you need your program to run on Excel 2000 (9.0) then you will have to create your own custom interop assembly. You can use TlbImp.exe to create an interop assembly for Excel 9.0, as explained in the article Achieving Backward Compatibility with .NET Interop: Excel as Case Study.
Excel 2002 下没有官方支持的 PIA，因此如果您需要您的程序在 Excel 2000 (9.0) 上运行，那么您必须创建自己的自定义互操作程序集。您可以使用 TlbImp.exe 为 Excel 9.0 创建互操作程序集，如文章实现与 .NET 互操作的向后兼容性：Excel 作为案例研究 中所述。
If your assembly is strong-named, then you need to create a strong-named interop assembly. This can be done by providing the name of your .pfx or .snk file via the /keyfile switch, as demonstrated in the article How to create a Primary Interop Assembly (PIA). That article is also making use of the /primary switch in order to create a "primary interop assembly". Making the interop assembly primary is not really needed in your case, but does not hurt. What the article omits, however, is the use of the /sysarray switch, which you should use.
如果您的程序集是强命名的，那么您需要创建一个强命名的互操作程序集。这可以通过通过 /keyfile 开关提供 .pfx 或 .snk 文件的名称来完成，如文章如何创建主互操作程序集 (PIA) 中所示。该文章还使用 /primary 开关来创建“主要互操作程序集”。在您的情况下，并不真正需要使互操作程序集成为主要的，但不会造成伤害。然而，本文忽略的是 /sysarray 开关的使用，您应该使用它。
Making a strong-named interop assembly does have some complexities to consider, however. For some more details, have a read of Using TLBIMP.exe to create Strong Named Interop Assemblies. (Among other things, this article explains the need for the /sysarray switch.)
然而，制作强命名互操作程序集确实需要考虑一些复杂性。有关更多详细信息，请阅读Using TLBIMP.exe to create Strong Named Interop Assemblies。（除其他外，本文还解释了 /sysarray 开关的必要性。）
In short, making a custom interop assembly is very simple if your assembly is not strong named. It is more complicated if your assembly is strong-named and, therefore, you need to create a strong-named interop assembly. But hopefully these articles should get you going.
简而言之，如果您的程序集不是强命名的，则制作自定义互操作程序集非常简单。如果您的程序集是强命名的，则情况会更加复杂，因此，您需要创建一个强命名的互操作程序集。但希望这些文章能让你前进。
-- Mike
——迈克
回答by SwDevMan81
I'm using Microsoft.Office.Interop.Excel.dll: Runtime version: v1.1.4322 Version: 12.0.0.0
我正在使用 Microsoft.Office.Interop.Excel.dll：运行时版本：v1.1.4322 版本：12.0.0.0
Could try this:
可以试试这个：
private object mMissingValue = System.Reflection.Missing.Value;
private void CreateWorkbook(string fileName)
{
if (File.Exists(fileName))
{
// Create the new excel application reference
mExcelApplication = new Application();
// Open the file
Workbook excel_workbook = mExcelApplication.Workbooks.Open(templatePath,
mMissingValue, mMissingValue, mMissingValue, mMissingValue, mMissingValue,
mMissingValue, mMissingValue, mMissingValue, mMissingValue, mMissingValue,
mMissingValue, mMissingValue, mMissingValue, mMissingValue);
Worksheet sheet = (Worksheet)mExcelWorkbook.Worksheets[1];
}
}
回答by quillbreaker
I've come to prefer saving Excel files as XML spreadsheets and then opening them up with System.Xml objects. XML I use a lot for all sorts of things, while Interop is something of a black art to me. It may not be good enough for your needs, but if it is, it is probably a lot easier than tangling with the Interop libraries.
我开始更喜欢将 Excel 文件保存为 XML 电子表格，然后用 System.Xml 对象打开它们。XML 我经常用于各种各样的事情，而 Interop 对我来说是一种黑色艺术。它可能不足以满足您的需求，但如果是，它可能比与 Interop 库纠缠在一起要容易得多。
