Excel Templates

XLSX templates share the same placeholder syntax as DOCX and PPTX, but the Excel format introduces capabilities that the other formats do not have — typed cell values, live formulas, multi-sheet workbooks, and additional data sources.

These features make XLSX templates suitable for two distinct use cases:

  • Batch reports — periodic exports that pull data from multiple entity types, run calculations in formulas, and produce a workbook ready for analysis or distribution.

  • Single-record document export — a contract schedule, price appendix, or reconciliation sheet generated from one record, where the output must be a real spreadsheet with working formulas rather than a static printout.

This page covers behavior that is specific to the XLSX engine. Features common to all three formats — placeholders, repeating sections, expressions, and images — are described in their own pages and referenced here where XLSX handling differs.

Values land as real Excel types

When the engine fills a placeholder, it does not paste every value as text. It inspects the resolved value and writes it as the appropriate Excel cell type:

Number

If the value can be parsed as a number, the cell receives a numeric value. Excel treats it as a number for formulas, sorting, and formatting.

Boolean

If the value is the string true or false (case-insensitive), the cell receives a boolean value.

Date

If the value matches a recognized date pattern, the cell receives a date serial number — the way Excel stores dates internally. A date format is applied automatically (see Date auto-formatting below).

Text

If none of the above applies, the cell receives a text string.

Empty

If the value is an empty string, the cell is left blank — no value, no formula, no empty-string constant.

This type awareness means that a column of prices filled by {{unitPrice}} — where unitPrice is the machine name of a Decimal field on the entity type — will sum correctly in an Excel formula without conversion.

Date auto-formatting

When a date value is written to a cell whose format is General (the Excel default), the engine automatically applies the format dd.MM.yyyy — for example, 15.03.2025.

If the template designer has already set a custom number format on the cell — for example, MM/dd/yyyy or yyyy-MM-dd HH:mm — the engine preserves that format and does not override it.

This means you can control date display in two ways:

  • Accept the default — leave the cell format as General and the output will use dd.MM.yyyy.

  • Choose your own format — set a custom date format on the cell in the template file, and the engine will respect it.

Auto-formatting applies only when the engine inserts a date value. Cells that receive text or numbers are not affected.

Live Excel formulas

A formula typed into the template the ordinary way — entered in Excel as =A2*B2 — is left alone. It arrives in the output as a live formula and recalculates as usual.

That is enough when the formula is known in advance. When it is not — when the range depends on how many rows the data produced — write the formula as text and mark the cell with the formula prefix.

The formula prefix

A cell whose text begins with {{=}} is treated as a formula. The prefix is stripped, whatever follows is filled in as a normal template, and the finished text is written into the cell as a live Excel formula.

The point of the prefix is that the formula itself is templated. Here lastRow is the machine name of a field on the entity type that holds the last row of the data block:

{{=}}SUM(D2:{{lastRow}})

If lastRow resolves to D41, the cell receives =SUM(D2:D41). Next month, with more rows, the same template produces a different range.

Several placeholders can appear in one formula. Both ends of a range can come from the data:

{{=}}SUM({{rangeStart}}:{{rangeEnd}})

With rangeStart as C2 and rangeEnd as C41, the cell receives =SUM(C2:C41).

The prefix must be at the very start of the cell.

Type a formula with a fixed range into the template cell in Excel as =SUM(D2:D100). The engine leaves ordinary formulas alone and the formula is easier to read. Use the prefix when part of the formula has to come from the data.
Do not put an expression placeholder inside a formula-prefix cell. {{=}}SUM(D2:D{{=1 + itemCount}}) does not produce a formula: the engine finds the inner {{=...}}, evaluates it, and replaces the whole cell with the result — the cell ends up holding a bare number. Use a plain placeholder that already carries the value you need, and compute it in an expression field on the record if it has to be derived.
The same prefix works on the _data sheet, where it is used to compute the filter expressions that select the records a report pulls in.

Formula or computed value

The formula prefix is easy to confuse with an expression placeholder, because both start with {{=. They do opposite things.

{{=}}SUM(D2:{{lastRow}})

Formula prefix — note the closing braces immediately after the =, and that the rest of the cell is still a template. Writes a live formula into the cell. Excel owns the calculation; the reader can change an input and watch the total follow.

{{=quantity * unitPrice}}

Expression placeholder — the expression sits inside the braces. quantity and unitPrice are machine names of fields on the entity type; the expression is evaluated as JavaScript against the record’s data and written as a static value. The output holds a number; there is no formula behind it.

Use the prefix when the recipient should be able to re-model the sheet. Use the expression when they only need the answer.

Server-side formula evaluation

After all sheets are processed, the engine evaluates every formula in the workbook on the server. This means the file opens with values already in place.

When a workbook is too large to evaluate in reasonable time, the engine stops and marks the workbook for recalculation on open instead. The file is still delivered and still correct — Excel simply does the arithmetic when the reader opens it. The visible difference is a brief recalculation on open, and stale-looking values until it finishes.

DATEDIF function

DATEDIF is evaluated on the server like every other formula.

DATEDIF calculates the difference between two dates in whole units:

Unit Meaning

"Y"

Whole years between the two dates.

"M"

Whole months between the two dates.

"D"

Days between the two dates.

Example:

=DATEDIF(A2, B2, "M")

If A2 is 2025-01-15 and B2 is 2025-09-20, the result is 8 (eight whole months).

If the start date is after the end date, DATEDIF returns a #NUM! error, matching Excel’s behavior.

Cell splitting with the pipe character

A single template cell can produce multiple output cells by using the pipe character (|) as a delimiter.

The split is done on the template text, before any placeholder is resolved. The engine cuts the cell at the first pipe:

  • the text before the pipe stays in the current cell,

  • the text after the pipe goes into a new cell inserted to the right, shifting the existing cells along,

  • both halves are then processed again, so a cell with several pipes keeps splitting until none are left.

Because the cut happens before resolution, a pipe that arrives inside a value does not split anything — only a pipe typed into the template does.

This is useful in repeating sections where one data row needs to populate cells across several columns but the template structure makes it inconvenient to place each placeholder in its own cell.

Example:

Suppose the template is attached to an entity type whose fields include displayName, address, and city — each is the machine name of a field on the entity type. One cell in the template becomes three in the output:

Template
A

1

{{displayName}}|{{address}}|{{city}}
Result
A B C

1

Tverskaya 12

1 Tverskaya St

Moscow

Cells already sitting to the right are shifted along, not overwritten.

Formatting and formulas survive row repetition

When a repeating section duplicates rows (see repeating sections), the duplicated rows preserve:

  • Cell styles — fonts, colors, borders, number formats, and conditional formatting survive intact.

  • Cell formulas — formulas in the template row are copied to each duplicated row as-is.

This means a template row with a price column formatted as #,##0.00 and a total column containing =C5*D5 will produce repeated rows where every price is formatted the same way and every total formula is preserved.

Excel cell references in copied formulas are not automatically adjusted. A formula that says =C5*D5 in the template row will say =C5*D5 in every repeated row — it does not shift to =C6*D6, =C7*D7, and so on.
Template row 5
C D E

5

{{quantity}}

{{unitPrice}}

=C5*D5
What repetition produces
C D E

5

10

4 500

=C5*D5

6

4

7 200

=C5*D5

7

25

1 100

=C5*D5

Every row now reports the first row’s total. For a calculation that has to follow each row, use an expression placeholder instead — it is evaluated per item and therefore correct on every row:

{{=quantity * unitPrice}}

Error resilience

The XLSX engine isolates errors at two levels so that a single problem does not break the entire workbook.

Per-cell

If a placeholder or expression in one cell fails, the engine writes an error marker into that cell — ERR: followed by the error type and message — and continues processing the remaining cells. All other cells on the sheet render normally.

Per-sheet

If an entire sheet fails to render — for example, due to a structural problem in the template — the engine writes RENDER ERROR followed by the sheet name and error detail into cell A1 of that sheet and continues with the remaining sheets. All other sheets in the workbook render normally.

In both cases, the workbook is still produced and delivered. The error markers make problems visible in the output.

Multi-sheet workbooks

An XLSX template can contain any number of sheets. Each sheet is processed independently:

  • Sheets that contain placeholders are rendered through the template engine.

  • Sheets that contain no placeholders are passed through unchanged — they appear in the output exactly as they appear in the template.

This means a template can combine dynamic sheets (filled from data) with static sheets (reference tables, instructions, or formatting guides) in the same workbook.

The reserved _data sheet is processed separately and removed from the output. See the _data sheet for details on additional data sources.

Root and parent context in nested sections

Inside a repeating section, placeholders resolve against the current item. To reach fields from the enclosing record, XLSX templates support the same context syntax as DOCX and PPTX:

  • {{$.fieldName}} — the root context (the top-level record), regardless of nesting depth.

  • {{../fieldName}} — the parent context (one level up).

Nested collections in XLSX

XLSX templates support nested repeating sections — a section inside another section. The engine pre-calculates the total number of rows needed for the expansion so that content below the outermost section shifts to the correct position regardless of how many items the collections contain.

Example — campaigns with placements:

In this example, the entity type has a collection field campaigns (machine name). Each campaign item has a field campaignName and its own nested collection placements. Each placement item has fields displayName, address, and price — all machine names defined on the respective entity types (see nested entities):

Template
A B C

3

{{#campaigns}}

4

Campaign: {{campaignName}}

5

{{#placements}}

6

{{displayName}}

{{address}}

{{price}}

7

{{/placements}}

8

{{/campaigns}}

The tag rows (3, 5, 7, 8) are removed. Row 4 is repeated for each campaign; rows 5—​7 expand the placements within each campaign. Rows below row 8 shift down to accommodate the inserted content.

See repeating sections for the full syntax and additional patterns.

Long text truncation

Excel cells have a maximum length of 32,767 characters. If a placeholder resolves to a value longer than that limit, the engine truncates the text to 32,767 characters.

The output file is produced normally. This limit comes from the Excel file format itself and cannot be raised.

Images in sheets

Image placeholders work in XLSX templates the same way as in DOCX and PPTX. A shape on the sheet whose entire text content is a placeholder tag — for example, {{displayPhoto}}, where displayPhoto is the machine name of a File/Image field on the entity type — is replaced with the image if the field value is an image reference.

The shape’s position and size define the bounding box. The image is scaled to fit within the box while preserving its aspect ratio.

See image placeholders for setup instructions, supported formats, and troubleshooting.

Summary of XLSX-specific behavior

Feature Behavior

Cell types

Values are written as numbers, booleans, dates, or text.

Date auto-format

dd.MM.yyyy applied to General-format cells; custom formats preserved.

Live formulas

Regular Excel formulas are preserved; the {{=}} prefix builds a formula from the data.

Formula evaluation

Formulas are evaluated on the server; falls back to recalculation on open.

DATEDIF

DATEDIF(start, end, unit) with "Y", "M", "D" units; evaluated on the server.

Cell splitting

Pipe character (|) splits one template cell into multiple output cells.

Row repetition

Cell styles and formulas are copied to each repeated row.

Error resilience

Per-cell and per-sheet isolation; error markers in the output.

Multi-sheet

Each sheet processed independently; static sheets passed through; temp sheets cleaned up.

Context access

$. for root, ../ for parent; nested collections supported with pre-calculated row shifts.

Text truncation

Values longer than 32,767 characters are truncated.

Images

Shape-based image replacement, same as DOCX and PPTX.

Related pages