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.

See https://bmpi.atlassian.net/wiki/spaces/CRDOC/pages/3252125697/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:

• 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.

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 https://bmpi.atlassian.net/wiki/spaces/CRDOC/pages/3252125697/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:

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

data: Issue

"data": {
  "id": "rep-2024-03-31-15-6",
  "description": "Cell pattern 'data' to stamp DataNode 'returns' not found",
  "severity": "error",
  "source": "rendering"
}

Common Parameters

For all supported nodes, the following parameters are considered:

"parameters": {
  "style": "ReclWhTax"
}

"parameters": {
  "pageBreakBefore": true
}

"parameters": {
  "renderingOptions": {
    "excel": {
      "worksheetName":  {
        "resolved": "Rückforderbare QST"
      }
    }
  }
}

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.

data: Record<string,string>

"data": {
  "ValueDate": "Valutadatum",
  "TradeDate": "Ex-Datum",
  "TransactionId": "Trx-Id",
  "Amount": "Betrag"
}

data: Record<string,number | string | boolean | Date | null>

"data": {
  "ValueDate": "2024-02-14T00:00:00Z", // as Date instance
  "TradeDate": "2024-02-12T00:00:00Z", // as Date instance
  "TransactionId": "41567",
  "Amount": 11424.3
}

data: { groupName: string }

"data": {
  "groupName": "Current Account EUR"
}

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.

Column related pattern names therefore contain the value type:

data: { header: string }

"data": {
  "header": "Betrag"
}

data: { value: number | string | boolean | Date | null }

"data": {
  "value": 11424.3
}

data: { rowName: string }

"data": {
  "rowName": "Gebühren- und Zinsabrechnung"
}

VerticalTableNode Parameters

"parameters": {
  "renderingOptions": {
    "excel": {
      "repeatHeaders": true
    }
  }
}

"parameters": {
  "renderingOptions": {
    "excel": {
      "tableWidth": 115
    }
  }
}

Titles: SectionNode

A SectionNode renders its titles.

data: {
  mainTitle?: string;
  subTitle?: string;
}

"data": {
  "mainTitle": "Renditeattribution",
  "subTitle": "Vom 01.01.2024 bis 31.03.2024, nach Anlagekategorie"
}

Simple Text: ParagraphNode

A ParagraphNode is renders its titles and its content.

data: {
  mainTitle?: string;
  subTitle?: string;
  content?: string;
}

"data": {
  "content": "Disclaimer: the information in this document is..."
}

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.

data: object | object[]

"data": [
  { "id": "A", "name": "Letter A", "order": 1 },
  { "id": "B", "name": "Letter B", "order": 2 }
]

Production Errors: ErrorNode

An ErrorNode (typically the result of an error during report production) renders the error details.

data: {
  error: string;
  message?: string;
  code?: string;
}

"data": {
  "error": "Error: No building block with name 'BalanceBB' registered.",
  "code": "production-rep-20231213-170410-1",
  "message": "Fehler bei der Reportproduktion"
}

Debugging Excel Rendering

If the report parameter addExcelTrace to 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:

Example trace