Working with Excel Templates
- Fabian Schaich
- Andreas Wepler
- Lukas Knecht
Principles of Operation
The Excel Channel renders supported nodes from the abstract report by copying cell patterns from the Excel template(s) and “stamping” them on the Excel report:
The cell style is taken from the template.
The cell value is taken from data in the report.
Unsupported nodes are silently ignored, thus a report intended for Word or online should always (at least partially) be convertible to an Excel document.
Cell Patterns
A cell pattern is a single, rectangular range of cells in a template with a name assigned to it. Names are assigned in Excel either by selecting a cell or cell range, then defining a name for it, or in the Name Manager.
See Working with Excel Templates | Supported Node Types for the names to be assigned.
Style and Language Specific Cell Patterns
Whenever a pattern is searched for a node with a specific style, the following names are checked in order:
<style>.<pattern>.<reportLanguage><style>.<pattern><pattern>.<reportLanguage><pattern>
Example: the header pattern when rendering a node with style MyStyle in language de will be the first existing one of MyStyle.header.de, MyStyle.header,header.de, header.
Stamping Cell Patterns
Cell patterns are stamped as follows:
The style of the pattern cells is copied to the next rows (or columns for generic tables) in the Excel report. This includes format, font, row height and column width, alignment, wrapping, colors, conditional formatting, merging, etc.
The value of the pattern cells is evaluated, then copied. All
${...}expressions in the value will be treated as JavaScript expressions on report specificdata. The expression can access the following identifiers:
Identifier | Description | Example |
|---|---|---|
| The data to be rendered. This is e.g. a node, a row header, a table fact or a report specific data object. See Working with Excel Templates | Supported Node Types for details. |
|
| Render inline language dependent content. Takes an |
|
| Formatter for dates and date periods, where the Excel formats cannot be used. |
|
Some patterns are optional. These are only rendered if defined in the template.
Templates
The template handling of the Excel channel is very similar to the Word Channel. Each Excel report needs at least a main template to render correctly:
The main template’s name is taken from the
styleSheetproperty of the report type’s"excel"outputFormat. If not specified, the defaultexcel_standard.xlsxis assumed.The main template can contain any number of cell patterns.
The first worksheet in the main template defines the page setup (page size, margins, page header and footer) of each worksheet in the resulting report.
The cell patterns defined in a template can be distributed over multiple worksheets to facilitate their organisation. Apart from that and the special role of the first worksheet, the worksheets in a template are not related to the worksheets in the rendered Excel.
Cinnamon may also be configured to support additional templates providing installation wide defaults for cell patterns, using the otherExcelTemplateIds configuration option.
As with Word templates, the actual file name of the template is prefixed by template_. The file name of the default template is therefore template_excel_standard.xlsx.
Language Specific Templates
Each template used (the main template and the configured additional templates) can be present in language specific variants. When e.g. rendering in language de, a styleSheet: "abc.xlsx" will select the template file template_abc.de.xlsx if present, otherwise the language independent file template_abc.xlsx. Language specific templates often add more redundancy and maintenance to a setup, but may be easier to manage than language specific cell patterns in some cases.
Page headers and footers
Page headers and footers set in the first worksheet of the main template are copied to all worksheets produced. They may contain ${...} expressions, like cell patterns. The data attributes supported are the same as for header and footer in Working with Excel Templates | Common Cell Patterns.
Supported Node Types
The Excel Channel supports a limited set of report nodes. The other node types do not produce output in the Excel document.
Common Cell Patterns
For all supported nodes, the following cell patterns may be rendered:
Pattern Name | Description | Optional | Example Data |
|---|---|---|---|
| Rows at the top of a worksheet. Value data is taken from the node creating the new worksheet and the report: data: {
mainTitle?: string;
subTitle?: string;
reportName: string;
params: object // Execution Parameters
} |
| "data": {
"mainTitle": "Rückforderbare Quellensteuern",
"subTitle": "Vom 01.01.2024 bis 31.03.2024",
"reportName": "Rückforderbare Quellensteuern",
"params": {
"portfolioId": "00001",
"reportingDate": "2024-03-31",
...
} |
| Rows at the bottom of a worksheet. Value data is the same as for | As for | |
| Cells stamped when an issue is encountered (e.g. a missing mandatory pattern). Value data is the issue: data: Issue |
Common Parameters
For all supported nodes, the following parameters are considered:
Parameter | Description | Example |
|---|---|---|
| The style to apply. Used as name prefix when looking for cell patterns, see Working with Excel Templates | Style and Language Specific Cell Patterns. | |
| Whether this node should be rendered at the top of a new worksheet. | |
| The name of the worksheet if this node creates a new worksheet, i.e. it is the first node or has Note that Excel limits the length of worksheet names to 31 characters. |
Tables: VerticalTableNode
Not surprisingly, a VerticalTableNode is rendered as rows and columns in Excel. There are two ways of rendering an table in Excel: templated and generic.
Templated Table Rendering
If there is a columnHeaders pattern (maybe with matching style prefix and language suffix), the table will be rendered row by row. Each row, including the header row, corresponds to a cell pattern. Templated rendering therefore allows full control over the layout in Excel and as well as subselection and reordering of columns.
Pattern Name | Description | Optional | Example Data |
|---|---|---|---|
| Row(s) to be stamped for the column headers of the table, repeated if |
| |
| Row(s) to be stamped for each data row in the table. A different cell pattern must be specified for each row importance ( Value data are the table facts by column id:
| ||
| Row(s) to be stamped for each row group carrying a name. Value data is the group name: | ||
| Rows to be stamped before the column headers, repeated if | See | |
| Row to be stamped after the last table row. Value data is the same as for | See |
Generic Table Rendering
If there is no columnHeaders pattern, a VerticalTableNode is rendered in a generic way. This mode of rendering is similar to the Word Channel: each column is rendered individually, depending on its value type. Typical value types are text, amount, count, date. The basic idea is that the style of a column (particularly format and alignment) depends on its value type.
Pattern names containing a value type with a hyphen ('-') need to be written with a underscore ('_') instead (e.g. p-abs is written as p_abs in the template).
Column related pattern names therefore contain the value type:
Pattern Name | Description | Optional | Example Data |
|---|---|---|---|
| Row(s) and column(s) to be stamped for the column headers. A different cell pattern must be specified for each value type ( Repeated if Value data is the header text: |
| |
| Row(s) and column(s) to be to be stamped for each fact in the table. A different cell pattern must be specified for each value type ( Value data is the fact value: | ||
| Row(s) and column(s) to be stamped for the row name, if set. Value data is the row name: | ||
| Same as Working with Excel Templates | Templated Table Rendering. | See above. | |
| Same as Working with Excel Templates | Templated Table Rendering. | See | |
| Same as Working with Excel Templates | Templated Table Rendering. | See |
Generic tables can only be rendered if there is no matching columnHeaders pattern in any of the templates used (main template and configured templates). It is therefore a good idea to always prefix columnHeaders with a style if a template is also used for generic tables.
VerticalTableNode Parameters
Parameter | Description | Example |
|---|---|---|
| Mark If there are multiple | |
| The total width (in Excel units, see e.g. How column widths are determined in Excel - Microsoft 365 Apps) of all columns of the generic table. This width will be distributed among the columns according to their For templated tables, this parameter is ignored. |
Titles: SectionNode
A SectionNode renders its titles.
Pattern Name | Description | Optional | Example Data |
|---|---|---|---|
| Rows to be stamped for a section. Value data is taken from the node: |
|
Simple Text: ParagraphNode
A ParagraphNode is renders its titles and its content.
Pattern Name | Description | Optional | Example Data |
|---|---|---|---|
| Rows to be stamped for a paragraph. Value data is taken from the node: |
|
Arbitrary Data: DataNode
A DataNode renders the contents of its data object. It can be used to render arbitrary data to the Excel document, as the type of the data object itself is user defined. This allows to render relatively complex objects into a large cell pattern.
Pattern Name | Description | Optional | Example Data |
|---|---|---|---|
| Rows to be stamped for the data object. Value data is taken from the node: If the data is an array, the pattern is stamped for each array element. |
|
Production Errors: ErrorNode
An ErrorNode (typically the result of an error during report production) renders the error details.
Pattern Name | Description | Optional | Example Data |
|---|---|---|---|
| Rows to be stamped for an error. Value data is taken from the node: |
|
Debugging Excel Rendering
If the report parameter addExcelTrace is true, the rendering process is traced for each pattern, and the trace appended to the Excel document as worksheet "Pattern Usage Trace".
The trace will contain a line for each cell pattern stamped (including attempts non-existing patterns). It contains the following columns:
Column | Description |
|---|---|
| The type of the abstract report node. |
| The id of the abstract report node. |
| The id of the element when rendering table rows, columns, groups or facts. |
| The destination range being stamped. |
| The pattern used to stamp. Empty if nothing was stamped. |
| The patterns tried before |
| The data being stamped. |
Example trace
