Generating Excel Documents

Excel documents can be generated in two formats:

Excel 2003/2004 SpreadsheetML - The Microsoft Excel 2003/2004 XML Spreadsheet format used by Architect Loader Spreadsheets.
Excel 2003/2004 SpreadsheetML with conversion to XLSX - A document generated in the Excel 2003/2004 SpreadsheetML format and then converted to the .xlsx format.

Excel 2003/2004 SpreadsheetML with conversion to XLSX capabilities

When a document is generated in the "Excel 2003/2004 SpreadsheetML with conversion to XLSX" format, the document is generated as a "Excel 2003/2004 SpreadsheetML" document and then a post-processing step is applied to convert the spreadsheet to the modern .xlsx format while preserving formatting and features.

The converter provides support for SpreadsheetML features including:

Cell Content and Formatting:

Cell data with proper type handling (String, Number, DateTime, Boolean)
Style-based formatting (Font properties: name, size, bold, italic, underline, strikethrough, color, vertical alignment)
Alignment (horizontal, vertical, wrap text, rotation, indentation)
Borders (all four sides with weight, line style, and color)
Fill/Interior (solid colors and all pattern types including stripes, diagonals, and crosses)
Number formats
Rich text formatting (HTML tags: , , , <S>, , , )

Worksheet Structure:

Column dimensions (width, hidden, auto-fit, span)
Row dimensions (height, hidden, auto-fit)
Cell merging (MergeAcross, MergeDown)
Hyperlinks (both internal worksheet references and external URLs)
Formulas (converted from R1C1 to A1 notation)
AutoFilter settings
Named ranges (workbook-scoped, with R1C1 to A1 conversion)

Print Settings:

Page scaling (zoom percentage)
Fit to pages (width and height)
Paper size selection
Print resolution (horizontal and vertical DPI)
Comments layout (at end or as displayed)
Error display options
Page order (left-to-right or top-to-bottom)
Black and white printing
Draft quality printing
Fit to page option
Print gridlines
Print row/column headings

Page Setup:

Page orientation (portrait/landscape)
Headers and footers
Page margins (left, right, top, bottom)
Freeze panes
Zoom settings and page break preview

Basic generation

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="http://www.w3.org/TR/REC-html40">

    <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>

HTML Content in Excel Cells

The formatting options of Excel allow all the contents of a cell to be bold in colored font etc but if you want to have mixed formatting in cells or to render HTML content into a cell then you must make use of the ability to embed HTML content in Excel cells.

To begin you must have the following declaration at the start of your template to define the namespace for HTML content:

<?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="http://www.w3.org/TR/REC-html40"> <!-- vital line!!  -->
 <!-- ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ -->

The xmlns:html="http://www.w3.org/TR/REC-html40" is vital to ensure that Excel will interpret HTML content correctly, without it the html content will be rendered as normal cell text.

Next, when you want to have HTML content in a cell you must declare on the Data element that the content contains HTML:

<Row>
    <Cell><Data ss:Type="String">Ordinary Cell</Data></Cell>
    <Cell><Data ss:Type="String" xmlns="http://www.w3.org/TR/REC-html40"><B>HTML</B> Mixed <I>Cell</I></Data></Cell>
                           <!--  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^  -->
</Row>

Again xmlns:html="http://www.w3.org/TR/REC-html40" declaration on the Data element is vital to ensure that Excel will interpret HTML content correctly, without it the html content will be rendered as normal cell text and some of it may not be displayed by Excel.

Allowed HTML Content in Excel Cells

The following HTML content is allowed in Excel cells:

Tag	Description
B	Bold
I	Italic
S	Strikethrough
Sub	Subscript
Sup	Superscript
Font	Set font. Attributes allowed are html:Size="size in points" and html:Color="#RRGGBB" R = Red, G = Green B = Blue in hexadecimal format. 00=0 FF=255

Note that this is a fairly limited set. All other HTML content is invalid and will be ignored by Excel.

The convert_html Filter

Sometimes you may have HTML content stored in attributes of draft objects. In order to convert this content to something that can be displayed in Excel you can use the convert_html filter:

<!-- Loop through all custom objects-->
{%- for co in draft.customobject_set.filter(custom_object_definition__name="Report Specification").order_by('identifier') -%}
<Row>
    <Cell><Data ss:Type="String">{{ co.identifier }}</Data></Cell>
    <Cell><Data ss:Type="String" xmlns="http://www.w3.org/TR/REC-html40">{{ co.properties["html content"].value|convert_html }}</Data></Cell>
</Row>
{%- endfor -%}

This filter will attempt to make conversions for the following html tags:

Tag	Description
a	Hyperlink. Converts <a href="https://www.example.com">A link</a> to A link [https://www.example.com]
b	Converts to B (bold) tag
br	Newline. Converts to a #&10; entity which forces a new-line inside an Excel cell.
em	Emphasis. Converts to I (Italic) tag
font	Converts color attribute to html:Color="#RRGGBB" format. Color names like "skyblue" are converted to their #RRGGBB equivalent. Converts size attribute to html:Size="size in points". <font size="12px">Content</font> will be converted to <Font html:Size="12">Content</Font>
h1	Heading1. Converts to <Font html:Size="20">..heading content...</Font>
h2	Heading2. Converts to <Font html:Size="18">..heading content...</Font>
h3	Heading3. Converts to <Font html:Size="16">..heading content...</Font>
h4	Heading4. Converts to <Font html:Size="14">..heading content...</Font>
h5	Heading5. Converts to <Font html:Size="12">..heading content...</Font>
i	Converts to I (Italic) tag
ol	Ordered list. li tags within the ordered list are turned into a numbered text, "1. Item" separated by #&10; newlines to simulate a list
p	Paragraph. Converts to a #&10; entity which forces a new-line inside an Excel cell.
s	Converts to S (Strikethrough) tag
sub	Converts to Sub (Subscript) tag
sup	Converts to Sup (Superscript) tag
ul	Unordered list. li tags within the ordered list are turned into a bullet text, "○ Item" separated by #&10; newlines to simulate a list

Understanding HTML Content Handling in Jinja2 Templates

When generating SpreadsheetML documents using Jinja2 templates, it's important to understand how HTML content is processed. The behavior depends on whether you use Jinja2's |safe filter and whether you declare the HTML namespace on the Data element.

There are three distinct scenarios:

Scenario 1: Display Literal HTML Tags as Text (Default Jinja2 Behavior)

When to use: You want HTML tags like Bold to appear as visible text in the Excel cell.

By default, Jinja2 automatically escapes HTML special characters (< becomes <, > becomes >). This is actually helpful when you want to display HTML tags as literal text:

{%- for field in fields -%}
<Row>
    <Cell><Data ss:Type="String">{{ field.PreText }}</Data></Cell>
</Row>
{%- endfor -%}

If field.PreText contains "Bold Text", Jinja2 will automatically escape it in the XML output as Bold Text. When Excel parses the XML, these entities are decoded back to < and > characters, and the cell displays:

Bold Text

The angle brackets appear as literal text characters in Excel, not as formatting.

Note

This is Jinja2's default behavior and happens automatically for any variable content. You do NOT need to use Django's escape() function - Jinja2 handles this for you.

Scenario 2: Plain Text Only (Using |safe Without HTML Namespace)

When to use: You have HTML content but want only the text extracted, with all HTML tags removed.

If you use the |safe filter to prevent Jinja2 from escaping, but do NOT declare the HTML namespace, the XML parser will strip all HTML tags, leaving only the text content:

{%- for field in fields -%}
<Row>
    <Cell><Data ss:Type="String">{{ field.PreText|safe }}</Data></Cell>
</Row>
{%- endfor -%}

If field.PreText contains "Bold Text", the XML will contain the unescaped tags Bold Text. The XML parser treats these as markup tags (not text) and strips them, leaving only:

Bold Text

The HTML tags are completely removed, and only the text between them appears in Excel.

Warning

Without the HTML namespace declaration, HTML tags are treated as invalid markup and discarded. If you want rich formatting, you must use Scenario 3.

Scenario 3: Rich Text Formatting (Using |safe With HTML Namespace)

When to use: You want HTML tags to create actual formatting in Excel (bold, italic, colored text, etc.).

To enable rich text formatting, you must:

Use the |safe filter (or |convert_html filter for automatic conversion)
Declare the HTML namespace on the Data element: xmlns="http://www.w3.org/TR/REC-html40"

{%- for field in fields -%}
<Row>
    <Cell><Data ss:Type="String" xmlns="http://www.w3.org/TR/REC-html40">{{ field.PreText|safe }}</Data></Cell>
</Row>
{%- endfor -%}

If field.PreText contains "Bold Text", Excel will interpret the  tag and render:

Bold Text

The word "Bold" appears in bold formatting, and "Text" appears in normal formatting.

Alternatively, use the convert_html filter which handles more HTML tags:

<Cell><Data ss:Type="String" xmlns="http://www.w3.org/TR/REC-html40">{{ field.PreText|convert_html }}</Data></Cell>

The convert_html filter converts common HTML tags (like ,  , <h1>-<h6>, <a>, <ul>, <ol>) to their SpreadsheetML equivalents.

Summary Table

Template Code	Input Value	Excel Cell Shows	Use Case
`{{ value }}`	`<B>Bold</B> Text`	`<B>Bold</B> Text` (literal)	Display HTML code as text
`{{ value\|safe }}`	`<B>Bold</B> Text`	`Bold Text` (tags stripped)	Extract text only
`{{ value\|safe }}` with HTML namespace	`<B>Bold</B> Text`	Bold Text (formatted)	Rich text formatting
`{{ value\|convert_html }}` with HTML namespace	`<p>Paragraph</p>`	Paragraph (with newline)	Convert complex HTML

Common Pitfall

A common mistake is to use |safe thinking it will enable formatting, but forgetting to add the HTML namespace declaration. This results in all HTML tags being stripped:

<!-- WRONG - tags will be stripped! -->
<Cell><Data ss:Type="String">{{ content|safe }}</Data></Cell>

<!-- CORRECT - tags will create formatting -->
<Cell><Data ss:Type="String" xmlns="http://www.w3.org/TR/REC-html40">{{ content|safe }}</Data></Cell>

Hyperlinks in Cells

You can add clickable hyperlinks to cells using the ss:HRef attribute on the Cell element. This creates a proper hyperlink that users can click to navigate to external URLs or to other locations within the workbook.

External Hyperlinks

External hyperlinks link to web URLs or other external resources. Simply specify the URL in the ss:HRef attribute:

<Row>
    <Cell ss:HRef="https://www.example.com">
        <Data ss:Type="String">Visit Example Website</Data>
    </Cell>
    <Cell ss:HRef="mailto:support@example.com">
        <Data ss:Type="String">Email Support</Data>
    </Cell>
</Row>

When users click on these cells in Excel, the hyperlink will open in their default web browser or email client.

Internal Hyperlinks

Internal hyperlinks link to other sheets or cells within the same workbook. Use the format #SheetName!CellAddress in the ss:HRef attribute:

<Worksheet ss:Name="Summary">
    <Table>
        <Row>
            <Cell ss:HRef="#Details!A1">
                <Data ss:Type="String">Go to Details Sheet</Data>
            </Cell>
            <Cell ss:HRef="#Summary!B10">
                <Data ss:Type="String">Jump to Cell B10</Data>
            </Cell>
        </Row>
    </Table>
</Worksheet>

<Worksheet ss:Name="Details">
    <Table>
        <Row>
            <Cell><Data ss:Type="String">Target Cell on Details Sheet</Data></Cell>
        </Row>
    </Table>
</Worksheet>

The # prefix is required for internal hyperlinks to distinguish them from external URLs. When clicked, Excel will navigate to the specified sheet and cell location within the workbook.

Note

The ss:HRef attribute creates native Excel hyperlinks that are distinct from the convert_html filter's handling of HTML <a> tags. The filter converts <a> tags to plain text with the URL in brackets (e.g., "link [https://...]"), while ss:HRef makes the content of the cell a clickable hyperlink that Excel recognizes and handles natively.

Named Ranges

Named ranges allow you to assign meaningful names to cells or cell ranges, which can then be referenced in formulas. Named ranges are defined at the workbook level and are converted to the .xlsx format with automatic R1C1 to A1 notation conversion.

Basic Named Range Example:

<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
          xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">

    <!-- Define named ranges at the workbook level -->
    <ss:Names>
        <ss:NamedRange ss:Name="TotalValue" ss:RefersTo="=Sheet1!R5C3"/>
        <ss:NamedRange ss:Name="DataRange" ss:RefersTo="=Sheet1!R2C1:R10C5"/>
        <ss:NamedRange ss:Name="HiddenCalc" ss:RefersTo="=Sheet1!R1C10" ss:Hidden="1"/>
    </ss:Names>

    <Worksheet ss:Name="Sheet1">
        <Table>
            <Row>
                <Cell><Data ss:Type="String">Item</Data></Cell>
                <Cell><Data ss:Type="String">Quantity</Data></Cell>
                <Cell><Data ss:Type="String">Price</Data></Cell>
            </Row>
            <Row>
                <Cell><Data ss:Type="String">Widget A</Data></Cell>
                <Cell><Data ss:Type="Number">10</Data></Cell>
                <Cell><Data ss:Type="Number">50</Data></Cell>
            </Row>
            <Row>
                <Cell><Data ss:Type="String">Total</Data></Cell>
                <Cell />
                <Cell ss:Formula="=SUM(TotalValue, 10)"/>
            </Row>
        </Table>
    </Worksheet>
</Workbook>

Named Range Attributes:

ss:Name (required) - The name of the named range. Must be a valid Excel name (no spaces, doesn't start with a number).
ss:RefersTo (optional) - The cell reference in R1C1 notation (e.g., =Sheet1!R1C3 for a single cell or =Sheet1!R2C1:R10C5 for a range). This will be automatically converted to A1 notation (e.g., Sheet1!C1 or Sheet1!A2:E10) in the .xlsx output.
ss:Hidden (optional) - Set to 1 to hide the named range from the Name Manager in Excel. Defaults to 0 (visible).

Using Named Ranges in Formulas:

Once defined, named ranges can be referenced in cell formulas just like regular cell references:

<Cell ss:Formula="=SUM(DataRange)"/>
<Cell ss:Formula="=TotalValue * 2"/>
<Cell ss:Formula="=AVERAGE(DataRange) + HiddenCalc"/>

The converter automatically handles the R1C1 to A1 conversion for both the named range definitions and any formulas that reference them.