How to: Insert Custom XML to an Office Open XML Package by Using the Open XML API

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

The Office Open XML Package specification defines a set of XML files that contain the content and define the relationships for all of the document parts stored in a single package. These packages combine the parts that comprise the document files for Microsoft® Office Excel® 2007, Microsoft Office PowerPoint® 2007, and Microsoft Office Word 2007. The Open XML Application Programming Interface (API) allows you to create packages and manipulate the files that comprise the packages. This topic walks through the code and steps to add a custom XML document part (file) to an Office Open XML package in Office Excel 2007, although the steps are the same for each of the three 2007 Microsoft Office system programs that support the Office Open XML Format.

Note
The code samples in this topic are in Microsoft Visual Basic® .NET and Microsoft Visual C#®. You can use them in an add-in created in Microsoft Visual Studio® 2008. For more information about how to create an add-in in Visual Studio 2008, see Getting Started with the Open XML Format SDK 1.0.

Inserting Custom XML to an Office Open XML Package

In the following code, you insert a CustomXMLPart part into the SpreadsheetDocument package:

' How to insert a CustomXMLPart part.
Public Sub XLInsertCustomXml(ByVal fileName As String, ByVal customXML As String)
   Dim xlPackage As SpreadsheetDocument = SpreadsheetDocument.Open(fileName, True)
   Using (xlPackage)
      '  Add a new custom XML part. 
      Dim workbookPart As WorkbookPart = xlPackage.WorkbookPart
      Dim xmlPart As CustomXmlPart = workbookPart.AddNewPart(Of CustomXmlPart)()
      ' Copy the XML into the new XML part.
      Dim outputStream As Stream = xmlPart.GetStream
      Dim ts As StreamWriter = New StreamWriter(outputStream)
      Using (ts)
         ts.Write(customXML)
      End Using
   End Using
End Sub

// How to insert a custom XML part.
public void XLInsertCustomXml(string fileName, string customXML)
{
    using (SpreadsheetDocument xlPackage = SpreadsheetDocument.Open(fileName, true))
    {
        //  Add a new custom XML part. 
        WorkbookPart workbookPart = xlPackage.WorkbookPart;
        CustomXmlPart xmlPart = workbookPart.AddNewPart<CustomXmlPart>();

        //  Copy the XML into the new part.
        using (Stream outputStream = xmlPart.GetStream())
        {
            using (StreamWriter ts = new StreamWriter(outputStream))
            {
                ts.Write(customXML);
            }
        }
        xlPackage.Save();
    }
}

To insert a CustomXMLPart part into the SpreadsheetDocument package

1. First, you pass in parameters representing the path to and the name of the source Excel 2007 workbook and the file containing the custom XML.

2. Then, you open the document as a SpreadsheetDocument object.

3. Next, you create a reference to the WorkbookPart part and add a custom XML part (named item.xml) to the package.

4. Finally, you read the contents of the external file containing the custom XML and write it to the CustomXmlPart part.