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