Export to XML

A quick reference for exporting to an XML document through SheetKraft

Any form of organized or unorganized data can be represented as XML, which is a standard for storing a.k.a. serializing information. In SheetKraft this is achieved through a template file which acts as a blueprint for the final output.

Guide to writing a template file

Let's start with a sample output file. We will modify this file to act as a template for SheetKraft.

<?xml version="1.0" encoding="UTF-8"?>
<UpdateData>
    <Date>2 Feb 2020</Date>
    <Header Entries="2"/>
    <DataRow Index="1">
        <F1>John Doe</F1>
        <F2>12</F2>
    </DataRow>
    <DataRow Index="2">
        <F1>Jane Doe</F1>
        <F2>23</F2>
    </DataRow>
    <Footer>Net quantity is 35.</Footer>
</UpdateData>

First and foremost it is absolutely essential to include a namespace definition for SheetKraft schema xmlns:sk="http://www.sheetkraft.com/xml-transform". This must be placed in the root node.

<?xml version="1.0" encoding="UTF-8"?>
<UpdateData xmlns:sk="http://www.sheetkraft.com/xml-transform">
...
</UpdateData>

Now let's identify the data which changes but does not repeat. We'll be referring to this data as static fields. From the above example, we identify 3 such fields namely the Date, Entries (within Header) and Footer. To mark this data, we wrap sk:Replace tags around it and name it with a tag attribute in the following manner.

...
<Date>
    <sk:Replace tag="date">2 Feb 2020</sk:Replace>
</Date>
...

The Entries field is an attribute which must be updated. For this, we put our sk:Replace as an immediate child of the Header tag. We then assign an additional attribute, attribute to it with its value being the name of the attribute to be modified, in this case Entries. Make sure to remove this attribute from its original tag.

...
<Header>
    <sk:Replace tag="entries" attribute="Entries">2</sk:Replace>
</Header>
...

Additionaly you can edit a specific part of the text by specifying the entire text in format attribute and replacing the part to edit with {0}. For further read, you can check out this webpage.

...
<Footer>
    <sk:Replace tag="footer" format="Net quantity is {0}.">35</sk:Replace>
</Footer>
...

For repeating fields, edit out all instances of the repeating XML portion except one. Now put the attribute sk:Repeat="true" in the repeating element.

...
<DataRow Index="1" sk:Repeat="true">
...
</DataRow>
...

Now repeat the procedure for sk:Replace as demonstrated in earlier examples. Using tags for replace elements within repeating fields is redundant as only order is considered. Note that nested elements are given priority while deciding order. Thus, the final template will look like

<?xml version="1.0" encoding="UTF-8"?>
<UpdateData xmlns:sk="http://www.sheetkraft.com/xml-transform">
    <Date>
        <sk:Replace tag="date">2 Feb 2020</sk:Replace>
    </Date>
    <Header>
        <sk:Replace tag="entries" attribute="Entries">2</sk:Replace>
    </Header>
    <DataRow sk:Repeat="true">
        <sk:Replace attribute="Index">1</sk:Replace>
        <F1>
            <sk:Replace>John Doe</sk:Replace>
        </F1>
        <F2>
            <sk:Replace>12</sk:Replace>
        </F2>
    </DataRow>
    <Footer>
        <sk:Replace tag="footer" format="Net quantity is {0}.">35</sk:Replace>
    </Footer>
</UpdateData>

Consolidating it all in formula

Provide the template and output file paths in first two arguments of ExportToXml.SK. Now provide the vertical array references of static tag names in the argument corresponding to static keys and similarly the respective values to be updated in static values. Finally, for repeating ranges select the table containing all the data to be updated within that repeat field, and specify the columns to be reported in the next argument using Report.SK. For example, if the range is RegionDown.SK(A1:D1) and replace values lie in column A, C and D, the report argument would be Report.SK(A1, C1, D1).

Fin.