Best practice for writing Excel XML

Always prepare and completely finalize the excel template, in excel and on production machines. Once you start implementing, there's no going back.

The generic structure which we usually use is as follows:

• static.xsl, containing two variables - themes and styles, with contents of respective .xml files
• template.xsl, containing the actual transformation:

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:include href="engine/2012.engine.xsl" />
    <xsl:include href="static.xsl" />
    
    <!-- put your data-preprocessing in here -->
    
    <xsl:variable name="images">
        <!-- generate <image> structure in here based on data -->
    </xsl:variable>
    
    <xsl:variable name="contents">
        <!-- put your worksheet generation in here -->
    </xsl:variable>

    <xsl:template match="/">
        <xsl:call-template name="generate_excel">
            <xsl:with-param name="author">ACME Corp.</xsl:with-param>
            <xsl:with-param name="themes"><xsl:copy-of select="$themes" /></xsl:with-param>
            <xsl:with-param name="styles"><xsl:copy-of select="$styles" /></xsl:with-param>
            <xsl:with-param name="images"><xsl:copy-of select="$images" /></xsl:with-param>
            <xsl:with-param name="sheetContents"><xsl:copy-of select="$contents" /></xsl:with-param>
        </xsl:call-template>
    </xsl:template>
</xsl:stylesheet>

The worksheet generation

When generating the worksheet, the structure of office XML is pretty simple:

<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" mc:Ignorable="x14ac">
    <sheetPr codeName="Sheet2"/>
    <dimension>
        <xsl:attribute name="ref">
            <xsl:text>A1:</xsl:text>
            
            <!-- calculate bottom right corner here. In our case it would be always AE<something> -->
            <xsl:text>AE</xsl:text>
            <xsl:value-of select="count(data/data) * 2 + 10" />
        </xsl:attribute>
    </dimension>
    <!-- <sheetViews>, <sheetFormatPr>, <cols> parameters here.
         copy/pasted from the template excel -->
    <sheetData>
        <xsl:for-each select="data/data">
            <xsl:variable name="rowNr" select="(position()-1) * 2 + 10" /> <!-- one row of data is two rows within excel, starting from row 10 -->

            <row r="{$rowNr}" spans="{$rowNr}:31" ht="27" customHeight="1"> <!-- the span :31 exuals to AE column -->
                <c r="A{$rowNr}" s="12" t="inlineStr">
                    <is><t>Static text</t></is>
                </c>

                <c r="B{$rowNr}" s="12" t="inlineStr">
                    <is><t><xsl:value-of select="some_node" /></t></is>
                </c>

                <!-- column C contains the current date -->
                <c r="C{$rowNr}" s="41">
                    <f ca="1">
                        <xsl:text>DATEVALUE("</xsl:text>
                        <xsl:value-of select="format-dateTime(current-dateTime(), '[Y0001]-[M01]-[D01]')" />
                        <xsl:text>")</xsl:text>
                    </f>
                </c>

                <!-- And column D contains any other function -->
                <c r="D{$rowNr}" s="42">
                    <f ca="1">
                        <xsl:text>RAND()</xsl:text>
                    </f>
                </c>
            </row>

            <!-- The data row is always separated by blank row -->
            <row r="{$rowNr + 1}" spans="1:31" ht="41.25" customHeight="1" thickBot="1" x14ac:dyDescent="0.3"/>
        </xsl:for-each>
    </sheetData>
    
    <!-- If you're merging cells, you will want the following: -->
    <mergeCells count="{count(data/data)}">
        <xsl:for-each select="data/data">
            <xsl:variable name="rowNr" select="(position()-1) * 2 + 10" />
            <mergeCell ref="{E$rowNr}:G{$rowNr}"/>
        </xsl:for-each>
    </mergeCells>
    
    <!-- Following is static. DON'T CHANGE THE ORDER OF ARGUMENTS!! -->
    <phoneticPr fontId="9" type="noConversion"/>
    <pageMargins left="0.19685039370078741" right="0" top="0.23622047244094491" bottom="0" header="0" footer="0"/>
    <pageSetup paperSize="9" scale="70" fitToHeight="10000" orientation="landscape" r:id="rId2"/>
    <drawing r:id="rId1"/>
</worksheet>

You don't want to change order of xml nodes! Yeah, it's xml, but it's microsoft's XML implementation. In example, following will result in invalid document:

    <drawing r:id="rId1"/>
    <pageSetup paperSize="9" scale="70" fitToHeight="10000" orientation="landscape" r:id="rId2"/>

While the following code is ok:

    <pageSetup paperSize="9" scale="70" fitToHeight="10000" orientation="landscape" r:id="rId2"/>
    <drawing r:id="rId1"/>

So much for microsoft.

Navigation

1. Preparing excel files
2. Preparing the first output
3. Putting plain data into excel sheet
4. Appending images
5. Creating multiple worksheets
6. Printer settings
7. Macro-enabled workbooks
8. Under the hood
9. Engine parameters documentation
10. Best practice for writing Excel XML
11. Authors, license, contributing