# Generating Excel Documents
Excel documents can be generated in two formats:
1. Excel 2003/2004 SpreadsheetML - The [Microsoft Excel 2003/2004 XML Spreadsheet format](https://docs.microsoft.com/en-us/previous-versions/office/developer/office-xp/aa140066(v=office.10)) used by Architect Loader Spreadsheets.
2. 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: , , , , , , )
**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
| Form Name |
{%- for form in draft.als_forms_set.all().order_by('Ordinal') -%}
| {{ form.DraftFormName }} |
{%- endfor -%}
| Folder Name |
Folder OID |
{%- for folder in draft.als_folders_set.all().order_by('Ordinal') -%}
| {{ folder.FolderName }} |
{{ folder.OID }} |
{%- endfor -%}
```
## 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.
```xml
| Ordinary Cell |
HTML Mixed Cell |
```
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:
```jinja
{%- for co in draft.customobject_set.filter(custom_object_definition__name="Report Specification").order_by('identifier') -%}
| {{ co.identifier }} |
{{ co.properties["html content"].value|convert_html }} |
{%- endfor -%}
```
This filter will attempt to make conversions for the following html tags:
| **Tag** | **Description** |
| --- | --- |
| a | Hyperlink. Converts *A link* 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". *Content* will be converted to *Content* |
| h1 | Heading1. Converts to ..heading content... |
| h2 | Heading2. Converts to ..heading content... |
| h3 | Heading3. Converts to ..heading content... |
| h4 | Heading4. Converts to ..heading content... |
| h5 | Heading5. Converts to ..heading content... |
| 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:
```jinja
{%- for field in fields -%}
| {{ field.PreText }} |
{%- endfor -%}
```
If `field.PreText` contains `"Bold Text"`, Jinja2 will automatically escape it in the XML output as `<B>Bold</B> 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:
```jinja
{%- for field in fields -%}
| {{ field.PreText|safe }} |
{%- 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:
1. Use the `|safe` filter (or `|convert_html` filter for automatic conversion)
2. Declare the HTML namespace on the Data element: `xmlns="http://www.w3.org/TR/REC-html40"`
```jinja
{%- for field in fields -%}
| {{ field.PreText|safe }} |
{%- 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:
```jinja
{{ field.PreText|convert_html }} |
```
The `convert_html` filter converts common HTML tags (like ``, `
`, `