How do I add a custom XML to an open Excel 2007 workbook using C#?

Question

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:

CustomXMLPart myXMLPart = new CustomXMLPart(xmlString);

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.

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.

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).

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.

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.

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.

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?

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.

Answer 1

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

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.

---

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: https://i.sstatic.net/O8Qhm.png

If you want to fetch customXML from a Workbook see this answer: https://stackoverflow.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.

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

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

Answer 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

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...

Answer 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 myWorkbook = <some appropriate constructor>;
...
Microsoft.Office.Interop.Excel.Workbook.CustomXMLParts myParts = myWorkbook.CustomXMLParts;