Generating Excel Documents

Text Document Templates can be used to create Microsoft Excel format files in the Microsoft Excel 2003/2004 XML Spreadsheet format used by Architect Loader Spreadsheets.

These spreadsheets may have multiple sheets, include bold text etc.

This example document generates a simple Excel file.

<?xml version="1.0" encoding="UTF-8"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
          xmlns:x="urn:schemas-microsoft-com:office:excel"
          xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
          xmlns:html="https://www.w3.org/TR/html401/">

    <Styles>

      <!-- Define a style called "Default" -->
      <Style ss:ID="Default" ss:Name="Normal">
       <Alignment ss:Vertical="Bottom"/>
       <Borders/>
       <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
       <Interior/>
       <NumberFormat/>
       <Protection/>
      </Style>

      <!-- Define a bold style which also has a text wrapping -->
      <Style ss:ID="s63">
       <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
       <Font ss:FontName="Arial" x:Family="Swiss" ss:Bold="1"/>
       <NumberFormat/>
       <Protection/>
      </Style>
    </Styles>

    <!-- Create a sheet called "Forms" -->
    <Worksheet ss:Name="Forms">
        <Table>
            <Column ss:Index="1" ss:AutoFitWidth="0" ss:Width="110"/>
            <Row>
                <Cell ss:StyleID="s63"><Data ss:Type="String">Form Name</Data></Cell>
            </Row>

            <!-- Loop through all forms, ordering by Form Ordinal and create a row for each -->
            {%- for form in draft.als_forms_set.all().order_by('Ordinal') -%}
            <Row>
                <Cell><Data ss:Type="String">{{ form.DraftFormName }}</Data></Cell>
            </Row>
            {%- endfor -%}
        </Table>
    </Worksheet>

    <!-- Create a sheet called "Folders" -->
    <Worksheet ss:Name="Folders">
        <Table>
            <Column ss:Index="1" ss:AutoFitWidth="0" ss:Width="110"/>
            <Row>
                <Cell ss:StyleID="s63"><Data ss:Type="String">Folder Name</Data></Cell>
                <Cell ss:StyleID="s63"><Data ss:Type="String">Folder OID</Data></Cell>
            </Row>
            <!-- Loop through all Folders, ordering by Folder Ordinal and create a row for each -->
            {%- for folder in draft.als_folders_set.all().order_by('Ordinal') -%}
            <Row>
                <Cell><Data ss:Type="String">{{ folder.FolderName }}</Data></Cell>
                <Cell><Data ss:Type="String">{{ folder.OID }}</Data></Cell>
            </Row>
            {%- endfor -%}
        </Table>

        <!-- Set an autofilter from Row 1 Column 1 to Row 9999 and column 2 -->
        <AutoFilter xmlns="urn:schemas-microsoft-com:office:excel" x:Range="R1C1:R9999C2"/>

    </Worksheet>

</Workbook>

Validating Excel Files

When generating Excel files a "Validate Source" option becomes available in the Document Template testing page. Once the source is generated (by selecting a testing source such as a Draft and clicking the Run button) the Validate Source button will become enabled. Clicking it will validate the generated file against the XML Schema for the Excel 2003/SpreadsheetML format and any validation errors will be shown.

This validation is useful for finding errors in the generated XML but can be very strict, it expects to find elements in exactly the order below (where elements are present, many are optional) and will complain if they are in a different order.

Many spreadsheet programs, including Excel itself, are not so strict on this ordering so an error reported here may not prevent your generated file from loading but this is a useful tool to track down problems in files which will not load correctly in desktop Excel or the web version of Excel.

<ss:Workbook>
    <ss:Styles>
        <ss:Style>
            <ss:Alignment/>
            <ss:Borders>
                <ss:Border/>
            </ss:Borders>
            <ss:Font/>
            <ss:Interior/>
            <ss:NumberFormat/>
            <ss:Protection/>
        </ss:Style>
    </ss:Styles>
    <ss:Names>
        <ss:NamedRange/>
    </ss:Names>
    <ss:Worksheet>
        <ss:Names>
            <ss:NamedRange/>
        </ss:Names>
        <ss:Table>
            <ss:Column/>
            <ss:Row>
                <ss:Cell>
                    <ss:NamedCell/>
                    <ss:Data>
                        <Font/>
                        <B/>
                        <I/>
                        <U/>
                        <S/>
                        <Sub/>
                        <Sup/>
                        <Span/>
                    </ss:Data>
                    <x:PhoneticText/>
                    <ss:Comment>
                        <ss:Data>
                            <Font/>
                            <B/>
                            <I/>
                            <U/>
                            <S/>
                            <Sub/>
                            <Sup/>
                            <Span/>
                        </ss:Data>
                    </ss:Comment>
                    <o:SmartTags>
                        <stN:SmartTag/>
                    </o:SmartTags>
                </ss:Cell>
            </ss:Row>
        </ss:Table>
        <c:WorksheetOptions>
            <c:DisplayCustomHeaders/>
        </c:WorksheetOptions>
        <x:WorksheetOptions>
            <x:PageSetup>
                <x:Layout/>
                <x:PageMargins/>
                <x:Header/>
                <x:Footer/>
            </x:PageSetup>
        </x:WorksheetOptions>
        <x:AutoFilter>
            <x:AutoFilterColumn>
                <x:AutoFilterCondition/>
                <x:AutoFilterAnd>
                    <x:AutoFilterCondition/>
                </x:AutoFilterAnd>
                <x:AutoFilterOr>
                    <x:AutoFilterCondition/>
                </x:AutoFilterOr>
            </x:AutoFilterColumn>
        </x:AutoFilter>
    </ss:Worksheet>
    <c:ComponentOptions>
        <c:Toolbar>
            <c:HideOfficeLogo/>
        </c:Toolbar>
    </c:ComponentOptions>
    <o:SmartTagType/>
</ss:Workbook>