Document toolboxDocument toolbox

Working with Excel Templates

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.

image-20240326-145513.png

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:

  1. <style>.<pattern>.<reportLanguage>

  2. <style>.<pattern>

  3. <pattern>.<reportLanguage>

  4. <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 specific data. The expression can access the following identifiers:

Identifier

Description

Example

Identifier

Description

Example

data

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.

${data.mainTitle}

translate

Render inline language dependent content. Takes an ITranslations dictionary object mapping languages to translations.

${translate({ de: "Konto", en: "Account", fr: "Compte" })}

format

Formatter for dates and date periods, where the Excel formats cannot be used.

${translate({ de: "Seit", en: "Since" })} ${format(data.startDate)}

  • 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 styleSheet property of the report type’s "excel" outputFormat. If not specified, the default excel_standard.xlsx is 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

Pattern Name

Description

Optional

Example Data

header

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", ... }

footer

Rows at the bottom of a worksheet. Value data is the same as for header.

As for header.

issue

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

Parameter

Description

Example

style

The style to apply. Used as name prefix when looking for cell patterns, see Working with Excel Templates | Style and Language Specific Cell Patterns.

pageBreakBefore

Whether this node should be rendered at the top of a new worksheet.

renderingOptions.excel.worksheetName

The name of the worksheet if this node creates a new worksheet, i.e. it is the first node or has pageBreakBefore: true.

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

Pattern Name

Description

Optional

Example Data

columnHeaders

Row(s) to be stamped for the column headers of the table, repeated if renderingOptions.excel.repeatHeaders: true. Value data are the header texts by column id:

row.<importance>
row.<importance>.<indent>

Row(s) to be stamped for each data row in the table. A different cell pattern must be specified for each row importance (row.1, row.2 etc.). If a row specifies an indent, there must be a cell pattern for the importance and indent (row.1.1, row.1.2 etc.).

Value data are the table facts by column id:

data.rowName contains the row name, if set.

rowGroup.<importance>

Row(s) to be stamped for each row group carrying a name. Value data is the group name:

tableHeader

Rows to be stamped before the column headers, repeated if renderingOptions.excel.repeatHeaders: true. Value data is the same as for header.

See header.

tableFooter

Row to be stamped after the last table row. Value data is the same as for header.

See header.

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

Pattern Name

Description

Optional

Example Data

columnHeader.<valueType>
columnHeader

Row(s) and column(s) to be stamped for the column headers. A different cell pattern must be specified for each value type (columnHeader.text, columnHeader.amount etc.) used.

Repeated if renderingOptions.excel.repeatHeaders: true.

Value data is the header text:

fact.<valueType>.importance
fact.<importance>

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 (fact.text.1, fact.amount.1 etc.) and for each row importance (fact.text.1, fact.text.2 etc.) used.

Value data is the fact value:

rowName.<importance>

Row(s) and column(s) to be stamped for the row name, if set.

Value data is the row name:

rowGroup.<importance>

Same as Working with Excel Templates | Templated Table Rendering.

See above.

tableHeader

Same as Working with Excel Templates | Templated Table Rendering.

See header.

tableFooter

Same as Working with Excel Templates | Templated Table Rendering.

See header.

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

Parameter

Description

Example

renderingOptions.excel.repeatHeaders

Mark tableHeader and columnHeaders to be repeated on each page (VerticalTableNode only).

If there are multiple VerticalTableNodes rendered into a single worksheet, only the headers of the last one with this option will be repeated.

renderingOptions.excel.tableWidth

The total width (in Excel units, see e.g. https://learn.microsoft.com/en-us/office/troubleshoot/excel/determine-column-widths) of all columns of the generic table. This width will be distributed among the columns according to their columnWeight. If not specified, column widths are taken from the cell patterns.

For templated tables, this parameter is ignored.

Titles: SectionNode

A SectionNode renders its titles.

Pattern Name

Description

Optional

Example Data

Pattern Name

Description

Optional

Example Data

section

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

Pattern Name

Description

Optional

Example Data

paragraph

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

Pattern Name

Description

Optional

Example Data

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

Pattern Name

Description

Optional

Example Data

error

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

Column

Description

nodeType

The type of the abstract report node.

nodeId

The id of the abstract report node.

elementId

The id of the element when rendering table rows, columns, groups or facts.

range

The destination range being stamped. missing if a mandatory pattern did not exist, optional if an optional pattern did not exist, missing: generic if a missing columnHeaders pattern triggered generic table rendering.

usedPattern

The pattern used to stamp. Empty if nothing was stamped.

triedPatterns

The patterns tried before usedPattern.

data

The data being stamped.

Example trace

image-20240327-145855.png