如何使用c#向打开的Excel 2007工作簿中添加自定义XML ?

I'm trying to add a custom XML to an open Excel 2007 workbook using C#. I'm using Microsoft.Office.Interop.Excel as an interface. I've discovered there's a CustomXMLPart class but I can't figure out how to use it. Initially I expected the code to be something simple like:

我正在尝试使用c#将一个自定义的XML添加到一个开放的Excel 2007工作簿中。我用Microsoft.Office.Interop。Excel作为接口。我发现有一个CustomXMLPart类，但是我不知道如何使用它。一开始我以为代码很简单:

CustomXMLPart myXMLPart = new CustomXMLPart(xmlString);

CustomXMLPart xmlmypart = new CustomXMLPart(xmlString);

myWorkBook.XMLCustomParts.Add(myXMLPart);

myWorkBook.XMLCustomParts.Add(myXMLPart);

but that isn't close to working.

但这离工作还远着呢。

I've tried finding examples online but they are bafflingly complex talking about Packages, Addins, OpenXML, VSTO streams etc. I've unzipped a suitable workbook (xlsx) and found it has docProps/custom.xml element.

我曾尝试在网上寻找示例，但它们非常复杂，难以理解地讨论包、Addins、OpenXML、VSTO流等等。我解压缩了一个合适的工作簿(xlsx)，发现它有docProps/custom。xml元素。

I just want to add a similar custom.xml to a new workbook (2007) before saving it. Is this possible? Please note I can't install any additional packages or libraries.

我只是想添加一个类似的自定义。将xml保存到新工作簿(2007)之前。这是可能的吗?请注意，我不能安装任何额外的软件包或库。

Edit: I've made a tiny bit more progress investigating this issue. I am confident I have the correct Office reference (Microsoft Office 12.0 Object Library under COM) and Excel interop reference (Microsoft.Office.Interop.Excel under GAC).

我在调查这个问题上已经取得了一点进展。我相信我有正确的Office参考(Microsoft Office 12.0 Object Library under COM)和Excel interop参考(Microsoft.Office. interop)。Excel在GAC)。

The declaration

《宣言》

Microsoft.Office.Core.CustomXMLParts myCustomXMLParts;

compiles, however

然而,编译

Microsoft.Office.Core.CustomXMLParts myCustomXMLParts =
    Microsoft.Office.Interop.Excel.Workbook.CustomXMLParts;

results in the error message: Error CS0029: Cannot implicitly convert type 'Microsoft.Office.Core.CustomXMLParts' to 'Microsoft.Office.Core.CustomXMLParts.

错误消息中的结果:error CS0029:不能隐式地转换类型'Microsoft.Office.Core。Microsoft.Office.Core.CustomXMLParts CustomXMLParts’”。

When I hover over the Excel CustomXMLParts property it claims to return a CustomXMLParts object which is in some sense, a Microsoft.Office.Core.CustomXMLParts object but not quite the same as in the Office assembly. So there's clearly some incompatability here but I can't resolve it. I have Microsoft Office Professional Plus 2007 (12.0.6612.1000) and Office 2007 Primary Interop Assemblies (12.0.4518.1014) installed.

当我悬停在Excel CustomXMLParts属性上时，它会返回一个CustomXMLParts对象，在某种意义上，它是一个Microsoft.Office.Core。CustomXMLParts对象，但与Office程序集中不完全相同。这里显然有一些不完整的地方，但我不能解决它。我安装了Microsoft Office Professional Plus 2007(12.0.6612.1000)和Office 2007主对讲程序集(12.0.4518.1014)。

Edit: I am fairly certain that it's the Office DLL that's the problem. On Add-Reference I see "Microsoft Office 12.0 Object Library" and can add it without any error. It appears as simply "Office" under References. However it seems to be invisible to the compiler while still claims Microsoft.Office.Core is defined in an assembly that is not referenced and specifies assembly 'office', Version=12.0.0.0.

编辑:我很确定问题出在Office DLL上。在add - reference上，我看到了“Microsoft Office 12.0对象库”，可以添加它，没有任何错误。在参考文献中，它仅仅是“办公室”。然而，编译器似乎看不到它，但它仍然声称是Microsoft.Office。Core在未引用的程序集中定义，并指定程序集“office”，版本=12.0.0.0。

My Office reference is linked to MSO.DLL under Microsoft Shared/OFFICE12 and has Major Version 2 Minor Version 4 under Properties. Does this matter? Or is the error message just indicating it isn't processing this referenced for some reason?

我的办公室推荐信与MSO有关联。在微软共享/OFFICE12下的DLL文件，在属性下有主版本2的小版本4。这有关系吗?或者错误消息只是表示它不处理这个引用的原因?

Edit: Adding the COM object "Microsoft Office 12.0 Object Library" definitely seems to be the problem. From other forum posts I've discovered that other people see a reference of "Microsoft.Office.Core" appear but I only see "Office". I've tried editing the .csproj file directly and that does give a "Microsoft.Office.Core" but it still doesn't work. The only conclusion I can really make is that my MSO.DLL for Office 12 Professional Plus (version (12.0.6612.1000) doesn't actually contain Microsoft.Office.Core assemblies, or at any rate doesn't expose them properly.

编辑:添加COM对象“Microsoft Office 12.0对象库”显然是问题所在。从其他论坛的帖子中，我发现其他人看到的是“微软办公室”。核心出现，但我只看到“办公室”。我已经尝试过直接编辑.csproj文件，这确实提供了一个“Microsoft.Office”。但它仍然不起作用。我能得出的唯一结论就是我的MSO。Office 12 Professional Plus的DLL(版本12.0.6612.1000)实际上并不包含Microsoft.Office。核心程序集，或者无论如何不能正确地公开它们。

3 个解决方案

#1

The MSDN Docs give an example using VSTO and I've adapted this to work with a Winform application.

MSDN文档给出了一个使用VSTO的示例，我将其应用于Winform应用程序。

The trick (in this situation) is to reference the PIA see the tooltip path of the Excel Reference in the screenshot below. NOTE: I didn't use the .Net or COM reference tabs, I had to "Browse" for the Excel DLL.

诀窍(在这种情况下)是在下面的屏幕截图中引用PIA，查看Excel引用的工具提示路径。注意:我没有使用。net或COM引用选项卡，我必须“浏览”Excel DLL。

---

Below is the Winform Code of a working example using the Excel PIA (also version 12.0.4518.1014). See screenshot for more detailed info of the Book1.xlsx renamed to a zip and extracted after I ran the code, along with the resulting item1.xml file in the CustomXML folder:

private void button1_Click(object sender, EventArgs e)
{
    string path = @"c:\temp\test\Book1.xlsx";
    var xlApp = new Microsoft.Office.Interop.Excel.Application();
    Workbook wb = xlApp.Workbooks.Open(path);

    string xmlString =
    "<?xml version=\"1.0\" encoding=\"utf-8\" ?>" +
    "<employees xmlns=\"http://schemas.microsoft.com/vsto/samples\">" +
        "<employee>" +
            "<name>Karina Leal</name>" +
            "<hireDate>1999-04-01</hireDate>" +
            "<title>Manager</title>" +
        "</employee>" +
    "</employees>";

    wb.CustomXMLParts.Add(xmlString, Type.Missing);
    wb.Save();
}

Large view of screenshot: http://i.stack.imgur.com/O8Qhm.png

截图的大视图:http://i.stack.imgur.com/O8Qhm.png

If you want to fetch customXML from a Workbook see this answer: https://*.com/a/8488072/495455.

如果您想从工作簿中获取customXML，请查看以下答案:https://*.com/a/8488072/495455。

---

EDIT:

编辑:

I also have Microsoft.Office.Core ActiveX referenced from the GAC: C:\Windows\assembly\GAC_MSIL\Office\15.0.0.0__71e9bce111e9429c\Office.dll and Microsoft VBIDE.Interop from the GAC as well C:\Windows\assembly\GAC_MSIL\Microsoft.Vbe.Interop\15.0.0.0__71e9bce111e9429c\Microsoft.Vbe.Interop.dll.

我也有Microsoft.Office。从海关总署核心ActiveX引用:C:\Windows\assembly\GAC_MSIL\Office\ 15.0.0.0__71e9bce111e9429c \办公室。dll和微软VBIDE。从海关总署互操作C:\Windows\assembly\GAC_MSIL\ Microsoft.Vbe.Interop \ 15.0.0.0__71e9bce111e9429c \ Microsoft.Vbe.Interop.dll。

Here is my project, please try it out and hopefully you can see whats missing from your solution vs my one that works: http://JeremyThompson.Net/Rocks/OfficeExcelCustomXML.zip

这是我的项目，请您尝试一下，希望您能看到您的解决方案与我的方案中缺少什么:http://JeremyThompson.Net/Rocks/OfficeExcelCustomXML.zip

Please note the zip contains the GAC DLLs in the Bin\Debug folder.

请注意，zip中包含了Bin\Debug文件夹中的GAC dll。

#2

I think it would be even more suitable for you to completly abandon the way via excel automation. Probably it would be enough to open the excel file via System.IO.Packaging Namespace. Here you can find a complete sample: Add Custom XML Parts to Documents Without Starting Microsoft Office

我认为您更适合完全放弃excel自动化的方式。也许通过System.IO打开excel文件就足够了。包装的名称空间。在这里，您可以找到一个完整的示例:在不启动Microsoft Office的情况下向文档添加自定义XML部件

In contrast to the other posted answers this solution does not need any references to the Office PIAs. You just need to add a reference to WindowsBase, which is included in .NET. It can even handle other OpenXML document formats like docx, pptx...

与其他已发布的答案相比，此解决方案不需要任何对Office PIAs的引用。您只需添加一个对WindowsBase的引用，它包含在。net中。它甚至可以处理其他的OpenXML文档格式，如docx、pptx…

#3

In you second snippet, Microsoft.Office.Interop.Excel.Workbook.CustomXMLParts is a type, you can not assign it to variable myCustomXMLParts. You would have to have

在你的第二段中，是Microsoft.Office.Interop.Excel.Workbook。CustomXMLParts是一种类型，您不能将其分配给变量myCustomXMLParts。你必须有

Microsoft.Office.Interop.Excel.Workbook myWorkbook = <some appropriate constructor>;
...
Microsoft.Office.Interop.Excel.Workbook.CustomXMLParts myParts = myWorkbook.CustomXMLParts;