Page Comparison

Patterns

Patterns are cell ranges, defined in the excel template that are used for creating a stamp. Patterns are set in the following way:

Mark the cell range for a new pattern
Head to Formulas → Define Name and set a new name for the cell range. Note: This name is static, and needs to be referenced identically in the implementation

...

Stamp

From a pattern the implementation creates stamps. When a pattern is loaded it is assigned an object with all relevant keys and values, i.e. data.<key> references this object. The pattern can be overloaded but when it is stamped, it will only insert the values from the pattern as defined in the excel template in the same format (i.e., same number formatting, same font style, font type etc.)

Defined Patterns

...

Excel Pattern Name

...

Usage

...

Available Keys

...

headerRow, footerRow

...

Used as a header, displaying basic report information

...

reportLanguage portfolioId periodLength reportName reportTypeId logo reportingDate reportTitle

...

columnHeaders

...

Column headers of the table

...

Dependent on the sources of the columnsConfiguration of the report type

...

posRow.<Importance>.<Indent>

...

Rows with explicit indent value for individual positions

...

Dependent on the sources of the columnsConfiguration of the report type. Note: Indents are normally not set and only occur for some building blocks.

...

posRow.<Importance>

...

Rows used for the individual positions

...

Dependent on the sources of the columnsConfiguration of the report type

Template

The default template for assets list follows the design principle of the NGIS FO-Report.

View file

name	SampleExcel.xlsx

...

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:

Cell style is taken from the template.
Cell context 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 [Supported Node Types] for the names to be assigned.

The style of the cells is copied to the stamped cells in the Excel report. This includes format, font, row height and column width, wrapping, colors, conditional formatting, merging, etc.
The content of the cells is evaluated, then copied. All ${...} expressions in the content will be treated as JavaScript expressions on report specific data.

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 [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)}`

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 is obtained by looking for MyStyle.header.de, MyStyle.header,header.de, header.

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.

Info
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 [common cell patterns].

Supported Node Types

The Excel Channel supports a limited set of report nodes. All other node types in an abstract report are ignored, but not their children.

Common Cell Patterns

For all supported nodes, the following cell patterns are rendered:

Pattern Name

Description

Optional

Content Data

header

Rows at the top of a worksheet. Content data is taken from the node creating the new worksheet and the report.

✔️

Code Block

language	json

"data": {
  "mainTitle": <node mainTitle>,
  "subTitle": <node subTitle>,
  "reportName": <report name>,
  "params": <report execution parameters>
}

footer

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

✔️

As for header.

issue

Cell created when an issue is encountered (e.g. a missing mandatory pattern). Content data is the issue.

❌

Code Block

language	json

"data": {
  "id": <issue id>,
  "description": <issue description>,
  "severity": "error",
  "source": "rendering"
}

Common Parameters

For all supported nodes, the following parameters are considered:

Parameter

Description

Example

style

The style to apply. Used as name prefix when looking for cell patterns.

Code Block

language	json

"parameters": {
  "style": "ReclWhTax",
  ...
}

renderingOptions.excel

The Excel Channel rendering options. The supported options are:

worksheetName: Name of the worksheet if this node creates a new worksheet. Note that Excel limits the length of worksheet names to 31 characters.
repeatHeaders: Mark tableHeader and columnHeaders to be repeated on each page (VerticalTableNode only). If there are multiple VerticalTableNode rendered into a single worksheet, only the headers of the last one with this option will be repeated.
tableWidth: The total width of all columns of a generically rendered table, see [Generic Table Rendering].

Code Block

language	json

"parameters": {
  "renderingOptions": {
    "excel": {
      "worksheetName":  {
        "resolved": "Rückforderbare QST"
      },
      "repeatHeaders": true
    }
  },
  ...
}

Tables: `VerticalTableNode`

Not surprisingly, a VerticalTableNode is rendered as rows and columns in Excel. There are two ways of rendering an table in Excel: styled and generic.

Styled Table Rendering

By specifying a style on the VerticalTableNode, the table will be rendered row by row. Each row, including the header row, corresponds to a cell pattern. Styled rendering therefore allows full control over the layout in Excel and as well as subselection and reordering of columns.

Generic Table Rendering

VerticalTableNodes without a style are 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.

Worksheets: `SectionNode`

A SectionNode is rendered with its titles.

Simple Text: `ParagraphNode`

A ParagraphNode is rendered as single line of text.

Versions Compared

Old Version 3

New Version 4

Key

Patterns

Stamp

Defined Patterns

Template

Principles of Operation

Cell Patterns

Style and Language Specific Cell Patterns

Templates

Language Specific Templates

Page headers and footers

Supported Node Types

Common Cell Patterns

Common Parameters

Tables: `VerticalTableNode`

Styled Table Rendering

Generic Table Rendering

Worksheets: `SectionNode`

Simple Text: `ParagraphNode`

`DataNode`

Errors: `ErrorNode`

Debugging Excel Rendering

Page Comparison

Versions Compared

Old Version 3

New Version 4

Key

Patterns

Stamp

Defined Patterns

Template

Principles of Operation

Cell Patterns

Style and Language Specific Cell Patterns

Templates

Language Specific Templates

Page headers and footers

Supported Node Types

Common Cell Patterns

Common Parameters

Tables: VerticalTableNode

Styled Table Rendering

Generic Table Rendering

Worksheets: SectionNode

Simple Text: ParagraphNode

DataNode

Errors: ErrorNode

Debugging Excel Rendering

Tables: `VerticalTableNode`

Worksheets: `SectionNode`

Simple Text: `ParagraphNode`

`DataNode`

Errors: `ErrorNode`