VSTO : Creating and retrieving Custom XML part in Excel

February 12, 2013



Custom XML allows one to store XML in the desired format inside an Office document. For example, if we open Excel Workbook using 7-zip the custom XML folder is right at the root level. 
Default Folder Structure


string xmlString1 =
          "<?xml version=\"1.0\" encoding=\"utf-8\" ?>" +
          "<employees xmlns=\"http://schemas.microsoft.com/vsto/samplestest\">" +
              "<employee>" +
                  "<name>Surender G</name>" +
                  "<hireDate>1999-04-01</hireDate>" +
                  "<title>Manager</title>" +
              "</employee>" +
          "</employees>";
Office.CustomXMLPart employeeXMLPart = Globals.ThisAddIn.Application.ActiveWorkbook.CustomXMLParts.Add(xmlString1, missing);


After executing the above code two files (item4.xml, itemProps4.xml) get added to the folder.

itemProps4.xml
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<ds:datastoreItem ds:itemID="{8905F770-7F58-4172-B707-34A283DF8527}" xmlns:ds="http://schemas.openxmlformats.org/officeDocument/2006/customXml><ds:schemaRefs>
<ds:schemaRef ds:uri="http://schemas.microsoft.com/vsto/samplestest"/></ds:schemaRefs>
</ds:datastoreItem>

item4.xml
<?xml version="1.0" encoding="utf-8"?>
<employees xmlns="http://schemas.microsoft.com/vsto/samplestest">
<employee>
<name>Pradeep G</name>
<hireDate>1999-09-01</hireDate>
<title>Manager</title>
</employee>
</employees>


Folder structure after adding Custom XML
















Now, we use the highlighted itemID in itemProps4.xml to retrieve the stored XML (Don't miss parenthesis).  
string str = "{8905F770-7F58-4172-B707-34A283DF8527}";
                    Office.CustomXMLPart obj = Globals.ThisAddIn.Application.ActiveWorkbook.CustomXMLParts.SelectByID(str);
MessageBox.Show("XML ::::"+obj.XML);

Result




















We have successfully retrieved the XML.

References:
Using Custom XML Part as a data store

You Might Also Like

0 comments

Popular Posts

Twitter