VSTO : Creating and retrieving Custom XML part in Excel



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

Comments

Popular posts from this blog

How to prepare your LOB app for Intune?

Information Architecture - Setup your term store to scale

Generate token signing .CER from ADFS Federation Metadata XML