Excel Template Syntax

Converts an Excel document (.xls, .xlsx) to another format, returning the resulting file.

Excel templates can use a simple templating language and data sources to generate documents. Excel templates look like regular documents, with embedded template expressions.

The example below shows the generated output from an Excel template and a data source:

Template

	A	B
1	Name:	<<[Person.FirstName]>> <<[Person.LastName]>>
2	Address:
3		<<[Person.Address.Number]>>, <<[Person.Address.Street]>>
4		<<[Person.Address.City]>>
5		<<[Person.Address.Postcode]>>

Data source

{
  "Person": {
    "Title": "Mr",
    "FirstName": "John",
    "LastName": "Smith",
    "Address": {
      "Number": "5",
      "Street": "Saffron Road",
      "City": "Leicester",
      "Postcode": "LE18"
    }
  }
}

Output

	A	B
1	Name:	John Smith
2	Address:
3		5, Saffron Road
4		Leicester
5		LE18

How to insert data into an Excel Spreadsheet

Step 1: Create an Excel File:

Open Excel and save a new workbook. This is where you will write your template expressions.

Step 2: Define your template region:

In order to insert data from a data source into a spreadsheet, regions must be defined in order for the templating syntax to know where to insert the values.

This can be done either by Creating A Named Range or Formatting as a table, and prefixing its name with a _ (underscore) to mark as a template region, for example _shoppingList.

Step 2a: Creating a Named Range

Creating a Named Range

1. On the Formulas tab, in the Defined Names group, click Define Name.

   Note

   Names can be up to 255 characters in length.

2. In the New Name dialog box, in the Name box, type in the name that you want to use for your reference.

3. To specify the scope of the named range, in the Scope drop-down list box, select Workbook or the name of a worksheet in the workbook.

   A named range set to a Workbook scope will be available for use throughout the workbook, whereas a range set to a particular sheet’s scope can be used within the sheet only.

4. Optionally, in the Comment box, enter a descriptive comment up to 255 characters.

5. In the Refers to box, do one of the following:

   • Click Collapse Dialog (which temporarily shrinks the dialog box), select the cells on the worksheet, and then click Expand Dialog
   • To enter a constant, type = (equals sign) and then the constant value.
   • To enter a formula, type = and then type the formula.

6. Click OK.

Step 2b: Formatting as a table

Formatting as a Table

Windows

Excel provides numerous predefined table styles that you can use to quickly format a table. If the predefined table styles don’t meet your needs, you can create and apply a custom table style. Although you can delete only custom table styles, you can remove any predefined table style so that it is no longer applied to a table.

You can further adjust the table formatting by choosing Quick Styles options for table elements, such as Header and Total Rows, First and Last Columns, Banded Rows and Columns, as well as Auto Filtering.

Note

The screen shots in this article were taken in Excel 2016. If you have a different version your view might be slightly different, but unless otherwise noted, the functionality is the same.

Choose a table style

When you have a data range that is not formatted as a table, Excel will automatically convert it to a table when you select a table style. You can also change the format for an existing table by selecting a different format.

1. Select any cell within the table, or range of cells you want to format as a table.

2. On the Home tab, select Format as Table.

3. Select the table style that you want to use.

Note

• Auto Preview - Excel will automatically format your data range or table with a preview of any style you select but will only apply that style if you press Enter or select with the mouse to confirm it. You can scroll through the table formats with the mouse or your keyboard’s arrow keys.

• When you use Format as Table, Excel automatically converts your data range to a table. If you don’t want to work with your data in a table, you can convert the table back to a regular range while keeping the table style formatting that you applied. For more information, see Convert an Excel table to a range of data.

Web

In Excel for the web, you can apply table style options to format the table elements.

Choose table style options to format the table elements:

There are several table style options that can be toggled on and off. To apply any of these options:

1. Select any cell in the table.

2. On the Table Design tab, under Table Style Options, check or uncheck any of the following:

• Header Row – Apply or remove formatting from the first row in the table.
• Total Row – Quickly add SUBTOTAL functions like SUM, AVERAGE, COUNT, MIN/MAX to your table from a drop-down selection.
• Banded Rows – Display odd and even rows with alternating shading.
• First Column – Apply or remove formatting from the first column in the table.
• Last Column – Apply or remove formatting from the last column in the table.
• Banded Columns – Display odd and even columns with alternating shading.
• Filter Button – Toggle AutoFilter on and off.

Step 3: Inserting Templating Syntax

Within your defined template region you can now use templating syntax and data sources according to the target output format.

For example, within a _shoppingList formatted table region, you could:

Template

Item Name	Price	Quantity	Use By Date
<<foreach [item in shoppingList]>> <<[item.name]>>	<<[item.price]>>	<<[item.quantity]>>	<<[item.useByDate]>> <</foreach>>

Data source

Name: shoppingList

[
  { "name": "Milk",  "price": 1.99, "quantity": 2,  "useByDate": "2025-07-28" },
  { "name": "Eggs",  "price": 2.50, "quantity": 12, "useByDate": "2025-08-05" },
  { "name": "Bread", "price": 1.20, "quantity": 1,  "useByDate": "2025-07-25" }
]

Output

Item Name	Price	Quantity	Use By Date
Milk	1.99	2	2025-07-28
Eggs	2.50	12	2025-08-05
Bread	1.20	1	2025-07-25

Language features

Simple expressions

The following template defines two template expressions. If applied to the input object, the expressions will be replaced by the corresponding properties. An expression is a <<, some contents, followed by a >>. When the template is executed, these expressions are replaced with values from a data source.

Template

	A
1	<<[FirstName]>> <<[LastName]>>

Data source

{
  "FirstName": "Yehuda",
  "LastName": "Katz"
}

Output

	A
1	Yehuda Katz

Nested input objects

Sometimes, the input objects contain other objects or arrays. In such a case, you can use a dot-notation to gain access to the nested properties. For example:

Template

	A
1	<<[Person.FirstName]>> <<[Person.LastName]>>

Data source

{
  "Person": {
    "FirstName": "Yehuda",
    "LastName": "Katz"
  }
}

Output

	A
1	Yehuda Katz

Some of the built-in helpers allow you to change the current context to a nested object. You can then access this object as if it were the root object.

Conditionals

You can use the if helper to conditionally render a block. If its argument returns false, undefined, null, "", 0, or [], the block will not be rendered.

A conditional expression must return a Boolean value (‘true’ or ‘false’). If none of the conditional expressions equate to true, and no fallback template is provided, the entire conditional block is removed.

The first expression that returns true will trigger the corresponding template option to be used.

<<if [conditional_expression1]>>

template_option1

<<elseif [conditional_expression2]>>

template_option2

<<else>>

default_template_option

<</if>>

For example:

Template

	A
1	<<if [Author]>>
2	<<[FirstName]>> <<[LastName]>>
3	<<else>>
4	Unknown author
5	<</if>>

Data source

{
  "Author": true,
  "FirstName": "Yehuda",
  "LastName": "Katz"
}

Output

	A
1	Yehuda Katz

If the input is an empty JSON object {}, then Author will become undefined and if condition fails. This will result in the output being:

Unknown author

Operators

Build a Doc supports a number of operators to assist with template creation.

Operators are special symbols or keywords that tell the template engine how to manipulate values: whether it’s accessing data, performing mathematical operations, making decisions, or combining pieces of text. They are the “verbs” of your template expressions: they act on your data to produce the final output.

Primary Operators

Operators that let you access data and call functions. The expressions should be written using this syntax template: <<[x]>>

Operator	Description	Example
x.y	Access member/property y on object x.	person.Name → “Alice”
x?.y	Safely access y on x; returns null if x is null.	user?.Email → null
x.f()	Call helper or method f on variable x.	name.ToUpper() → “ALICE”
a[x]	Retrieve element at index position x from array or list a.	colors[0] → “Red”
a?[x]	Safely access item at index positon x in a; returns null if a is null.	items?[2] → null
new T(...)	Create a new instance of type T with the given constructor arguments.	new DateTime(2025,1,1) → date obj

Unary Operators

Operators that work on a single value. The expressions should be written using this syntax template: <<[-x]>>

Operator	Description	Example
-x	Negate a number: if amount is 5, then -amount yields -5; if total is -3, then -total yields 3	-amount → -5
!x	Logical NOT: flips a boolean (true → false)	!isActive
~x	Bitwise NOT: flips each single bit in the number - swaps all the 0s for 1s and all the 1s for 0s. Applies it to 0 (which is all zeros in binary) turns every bit into a 1, producing -1	~0 → -1
(T)x	Cast value x to type T	(int)3.14 → 3

Binary Operators

Arithmetic

Operators that perform basic mathematical calculations (addition, subtraction, multiplication, division, remainder). The expressions should be written using this syntax template: <<[x * y]>>

Operator	Description	Example
x * y	Multiply	2 * 3 → 6
x / y	Divide	6 / 2 → 3
x % y	Remainder	7 % 4 → 3
x + y	Add numbers or concatenate strings	"A" + "B" → “AB”
x - y	Subtract	5 - 2 → 3

Bitwise

Operators that manipulate individual bits of integer values (shift, AND, OR, XOR, NOT). The expressions should be written using this syntax template: <<[x << y]>>

Operator	Description	Example
x << y	Shift bits of x left by y positions	1 << 2 → 4
x >> y	Shift bits of x right by y positions	4 >> 1 → 2
x & y	Bitwise AND	3 & 1 → 1
x ^ y	Bitwise XOR	5 ^ 3 → 6
x | y	Bitwise OR	5 | 2 → 7

Relational

Operators that compare two values and return a boolean (true/false) result. The expressions should be written using this syntax template: <<[x < y]>>

Operator	Description	Example
x < y	True if x is less than y.	2 < 3 → true
x > y	True if x is greater than y.	3 > 2 → true
x <= y	True if x is ≤ y.	2 <= 2 → true
x >= y	True if x is ≥ y.	3 >= 4 → false
x == y	True if x equals y.	5 == 5 → true
x != y	True if x does not equal y.	5 != 4 → true

Logical

Operators that combine boolean values to form more complex conditions. The expressions should be written using this syntax template: <<[(x && y)]>>

Operator	Description	Example
x && y	True only if both x and y are true.	true && false → false
x || y	True if either x or y is true.	true || false → true

Null-Coalescing

Operators that provides a default when a value is null. The expressions should be written using this syntax template: <<[(x ?? y)]>>

Operator	Description	Example
x ?? y	Return x if not null; otherwise return y.	name ?? "Unknown"

Loops

You can iterate over a list using the built-in foreach helper. Inside the block, you can use variable name to reference the element being iterated over.

<<foreach [variable_name in sequence_expression]>>

<<[data_band_body]>>

<</foreach>>

For example:

Template

	A
1	<<foreach [person in people]>>
2	<<[person]>>
3	<</foreach>>

Data source

Name: people

[
  "Yehuda Katz",
  "Alan Johnson",
  "Charles Jolley"
]

Output

	A
1	Yehuda Katz
2	Alan Johnson
3	Charles Jolley

Reference item position

When looping through items in a foreach statement, you can reference the position of each item using the IndexOf() and NumberOf() extension methods.

• IndexOf() returns a zero‑based index (0 for the first item, 1 for the second, etc.).

• NumberOf() returns a one‑based index (1 for the first item, 2 for the second, etc.).

IndexOf() Example: Comma-Separated List

To manipulate the data source to turn it into a comma-separated list, the below method can be applied, utilising the IndexOf() method:

Template

	A
1	The fruits are:
2	<<foreach [fruit in items]>>
3	<<[ IndexOf() > 0 ? ”, ” : "" ]>><<[fruit]>>
4	<</foreach>>.

Data source

Name: items

[
  "Apple",
  "Banana",
  "Cherry"
]

Output

	A
1	The fruits are: Apple, Banana, Cherry.

The example uses the foreach loop, applying the ternary rule ‘if the item’s index is greater than 0, add a comma before the item, else do nothing’ to each item from the data source.

• First pass: IndexOf() = 0 → no comma

• Later passes: IndexOf() > 0 → insert ", "

This results in a comma-separated list, with a comma after each item except the first.

NumberOf() Example: Simple Numbered List

When looping through items in foreach, you can optionally reference the current loop index via the IndexOf() function.

You can use this function to distinguish sequence items with different indexes and then handle them in different ways. For example, given that items is a list of the strings “apples”, “bananas”, and “oranges”, you can use the following template to enumerate them, prefixing all but the first with commas. This is useful when producing lists.

Template

	A
1	No. - Item
2	<<foreach [item in items]>>
3	<<[ NumberOf() ]>> - <<[item]>>
4	<</foreach>>

Data source

Name: items

[
  "Apple",
  "Banana",
  "Cherry"
]

Output

	A
1	No. - Item
2	1 - Apple
3	2 - Banana
4	3 - Cherry

Force move to next Item

You can instruct the engine to force movement to the next item within a loop using a next tag.

This feature is useful in label-print-like scenarios when you need to output data about a fixed number of items in a single table row, like in the following example.

Given that Clients is a list having a field named “Name”, you can use the following template to output three client names per row while outputting names of all clients. The next tag forces an increment in the loop, ‘jumping’ to the next item and allowing access to it before the next pass of the loop. As this moves the count of the loop on one place, the next pass of the loop will continue from the next sequential position.

In this case, the engine produces a document as follows:

Template

	A
1	<<foreach [c in Clients]>>
2	<<[c.Name]>><<next>><<[c.Name]>><<next>><<[c.Name]>>
3	<</foreach>>

Data source

Name: Clients

[
  { "Name": "A Company" },
  { "Name": "B Ltd." },
  { "Name": "C & D" },
  { "Name": "E Corp." },
  { "Name": "F & Partners" },
  { "Name": "G & Co." },
  { "Name": "H Group" },
  { "Name": "I & Sons" },
  { "Name": "J Ent." }
]

Output

	A	B	C
1	A Company	B Ltd.	C & D
2	E Corp.	F & Partners	G & Co.
3	H Group	I & Sons	J Ent.

Formatting values

Expression tags let you control how raw data appears in your document - whether as dates, numbers, or styled text. You insert a format directive directly into your placeholder, which is applied at run time.

You can format values using expression tags. An expression tag serves as a placeholder for an expression result within a template, and allows you to specify a format for the output.

An expression tag has no name and consists of the following elements:

• An expression enclosed by brackets
• An optional format string enclosed by double quotes and preceded by the ”:” character
• An optional html switch

<<[expression]:"format">>

Format string

The format string can be used to format numeric values or strings, and must correspond to the expected format described in the context provided: a string method can only be applied to a string, and a dateTime method can only be applied to a dateTime etc.

If you have a string or numeric value and you want to format it into a specific pattern, you can do so by using the format string within the expression tag. In the below example, the string is formatted to upper case.

Template

	A
1	<<[string]:upper>>

Data source

Name: string

{
  "string": "Hello, World!"
}

Output

	A
1	HELLO, WORLD!

Dates and Times

Dates from a data source property can be formatted using the following syntax.

<<[expression]:"pattern">>

For example:

Template

	A
1	<<[person.DateOfBirth]:“dd/MM/yyyy”>>

Data source

{
  "Person": {
    "DateOfBirth": "1981-09-24"
  }
}

Output

	A
1	24/09/1981

Numbers

Provides several additional number formats that can not be specified using format strings. The following table describes these formats.

Number Format	Description
alphabetic	Formats an integer number as an upper-case letter (A, B, C, …)
roman	Formats an integer number as an upper-case Roman numeral (I, II, III, …)
ordinal	Appends an ordinal suffix to an integer number (1st, 2nd, 3rd, …)
ordinalText	Converts an integer number to its ordinal text representation (First, Second, Third, …)
cardinal	Converts an integer number to its text representation (One, Two, Three, …)
hex	Formats an integer number as hexadecimal (8, 9, A, B, C, D, E, F, 10, 11, …)
arabicDash	Encloses an integer number with dashes (- 1 -, - 2 -, - 3 -, …)

The following is an example of how you can use one of these additional number formats, instead of a format string. Given that i is an integer number, you can format i as an upper-case letter (1 = A, 2 = B, 3 = C, …).

Strings

String Format	Description
lower	Converts a string to lower case (“the string”)
upper	Converts a string to upper case (“THE STRING”)
caps	Capitalises a first letter of every word in a string (“The String”)
firstCap	Capitalises the first letter of the first word in a string (“The string”)

The following is an example of how you can specify an additional string format. Given that s is a string, you can capitalise the first letter of every word in s using the following template.

Template

	A
1	<<[s]:caps>>

Data source

{
  "s": "hello, world!"
}

Output

	A
1	Hello, World!

Combine formatters

You can also combine expression formatters like in the following examples. Given that d is a DateTime value, you can convert its textual month representation to upper case using the following template.

Template

	A
1	<<[d]:“MMMM”:upper>>

Data source

{
  "d": "2021-09-24T00:00:00"
}

Output

	A
1	SEPTEMBER

Template

	A
1	<<[i]:roman:lower>>

Data source

{
  "i": 7
}

Output

	A
1	vii

Note

In contrast to format strings, additional number and string formats must not be enclosed with double quotes.

Using Variables

Templates enable you to use variables in template documents. Use variables to store expensive calculations once and reuse the result throughout your template. This is particularly useful for complex calculations, such as running totals.

You can declare a variable in a template using a var tag:

<<var [variable_type variable_name = variable_value]>>

If you do not specify the type explicitly, it is determined implicitly from the specified variable value.

Each new variable must have a unique identifier. After a variable is declared in a template, its value can be accessed using its name, just like any other variable.

Source

	A
1	<<var [s = “Hello!”]>><<[s]>>

Output

	A
1	Hello!

You can redefine the value of a variable using a var tag against the name of this variable. For example, the following template outputs string “Hello, World!”

Source

	A
1	<<var [s = “Hello, ”]>><<[s]>><<var [s = “World!”]>><<[s]>>

Output

	A
1	Hello, World!

Note

In contrast to format strings, additional number and string formats must not be enclosed with double quotes.

Variables come with the following restrictions:

• You can not redefine the type of a variable.
• Using a var tag, you can not redefine the value of an iteration variable or a data source.

Tables

You can dynamically populate tables using Build a Doc template expressions.

A table‑row body can span one or more rows of a document table. Its band begins at the start of the first occupied row and ends at the close of the last occupied row:

<<foreach ...>>…	…	…
…	…	…
…	…	… <</foreach>>

For example, to populate a document table given the data source.

Template

	A	B	C
1	<<foreach [c in Contracts]>> <<[c.Clients.Name]>>	<<[c.Managers.Name]>>	<<[c.Price]>> <</foreach>>
2	Total:		<<[Contracts.Sum(c=>c.Price)]>>

Data source

Name: Contracts

[
  {
    "Clients": { "Name": "A Company" },
    "Managers": { "Name": "John Smith" },
    "Price": 1200000
  },
  {
    "Clients": { "Name": "B Ltd." },
    "Managers": { "Name": "John Smith" },
    "Price": 750000
  },
  {
    "Clients": { "Name": "C & D" },
    "Managers": { "Name": "John Smith" },
    "Price": 350000
  },
  {
    "Clients": { "Name": "E Corp." },
    "Managers": { "Name": "Tony Anderson" },
    "Price": 650000
  }
]

Output

	A	B	C
1	A Company	John Smith	1200000
2	B Ltd.	John Smith	750000
3	C & D	John Smith	350000
4	E Corp.	Tony Anderson	650000
5	Total:		2950000

---

Template

	A	B
1	<<foreach [m in Managers]>><<[m.Name]>>	<<[Contracts.Where(c => c.ManagerID==m.ManagerID).Sum(c => c.Price)]>>
2	<<foreach [c in Contracts.Where(c => c.ManagerID==m.ManagerID)]>><<[c.Clients.Name]>>	<<[c.Price]>><</foreach>><</foreach>>
3	Total:	<<[Contracts.Sum(c => c.Price)]>>

Data source

Name: Contracts

[
  {
    "Clients": { "Name": "A Company" },
    "ManagerID": 1,
    "Price": 1200000
  },
  {
    "Clients": { "Name": "B Ltd." },
    "ManagerID": 1,
    "Price": 750000
  },
  {
    "Clients": { "Name": "C & D" },
    "ManagerID": 1,
    "Price": 350000
  },
  {
    "Clients": { "Name": "E Corp." },
    "ManagerID": 2,
    "Price": 650000
  }
]

Name: Managers

[
  {
    "ManagerID": 1,
    "Name": "John Smith"
  },
  {
    "ManagerID": 2,
    "Name": "Tony Anderson"
  }
]

Output

	A	B
1	John Smith	2300000
2	A Company	1200000
3	B Ltd.	750000
4	C & D	350000
5	Tony Anderson	650000
6	E Corp.	650000
7	Total:	2950000

---

Template

	A	B
1	<<foreach [m in Managers]>> <<[m.Name]>>	<<foreach [c in m.Contracts]>> <<[c.Clients.Name]>> <</foreach>> <</foreach>>

Data source

Name: Managers

[
  {
    "ManagerID": 1,
    "Name": "John Smith",
    "Contracts": [
      {
        "Clients": { "Name": "A Company" },
        "Price": 1200000
      },
      {
        "Clients": { "Name": "B Ltd." },
        "Price": 750000
      },
      {
        "Clients": { "Name": "C & D" },
        "Price": 350000
      }
    ]
  },
  {
    "ManagerID": 2,
    "Name": "Tony Anderson",
    "Contracts": [
      {
        "Clients": { "Name": "E Corp." },
        "Price": 650000
      }
    ]
  }
]

Output

	A	B
1	John Smith	A Company B Ltd. C & D
2	Tony Anderson	E Corp.

Single Column Tables

Single‑column tables are treated differently: when the opening and closing foreach tags are both placed in the same cell, the engine treats that band as a standard data band rather than a table‑row band by default. This displays the content as a basic block of repeated content, similar to a single line of text, rather than as a table row band, which spreads the content across multiple rows. The examples below demonstrate this behaviour.

Example 1: Table-Row Band

Template

Managers Table (Table-Row Band)

Name
<<foreach [m in Managers]>>
<<[m.Name]>>
<</foreach>>

Data Source

{
  "Managers": [
    { "Name": "John Smith" },
    { "Name": "Tony Anderson" },
    { "Name": "July James" }
  ]
}

Output

Managers Table (Table-Row Band)

Name
John Smith
Tony Anderson
July James

Example 2: Standard Data Band:

Template

Managers List (Standard Data Band)

Managers
<<foreach [m in Managers]>><<[m.Name]>> <</foreach>>

Data Source

{
  "Managers": [
    { "Name": "John Smith" },
    { "Name": "Tony Anderson" },
    { "Name": "July James" }
  ]
}

Output

Managers List (Standard Data Band)

Managers
John Smith Tony Anderson July James

-greedy Switch

The -greedy switch is used to influence how tags are processed, particularly when using nested tags.

When the -greedy switch is applied to a tag, the engine captures as much content as possible for that tag, including any nested tags. This can be particularly useful if you need to ensure that all relevant data is included, even if there are multiple nested instances of the same tag type; the -greedy switch will help ensure that all iterations of the inner tag are captured effectively without being prematurely terminated by the outer tag.

Template

	A	B
1	<<foreach [items] -greedy>>	Item: <<[Name]>> <<foreach [subItems]>> Sub-item: <<[SubItemName]>> <</foreach>> <</foreach>>

Data source

Name: items

{
  "items": [
    {
      "Name": "Fruit",
      "subItems": [
        { "SubItemName": "Apple" },
        { "SubItemName": "Banana" }
      ]
    },
    {
      "Name": "Vegetable",
      "subItems": [
        { "SubItemName": "Carrot" },
        { "SubItemName": "Lettuce" }
      ]
    }
  ]
}

Output

	A	B
1	Fruit	Apple Banana
2	Vegetable	Carrot Lettuce

Merging Cells

You can dynamically merge table cells with the same textual contents using cellMerge tags.

<<cellMerge -horz>>

A horizontal switch is optional. If the switch is present, it denotes a cell merging operation in a horizontal direction. If the switch is missing, it means that a cell merging operation is to be performed in a vertical direction (the default).

For two or more successive table cells to be merged, in either direction, the following requirements must be met:

• Each of the cells must contain a cellMerge tag, indicating a cell merging operation in the same direction.
• Each of the cells must not be already merged in another direction.
• The cells must have equal textual contents (ignoring leading and trailing whitespaces).

Note

In the context of merging cells, “Same textual content” means the cell texts are identical, ignoring any extra spaces at the start or end.

Consider the following template:

...
<<cellMerge>><<[value1]>>
...
<<cellMerge>><<[value2]>>

If value1 and value2 have the same value, say “Hello”, table cells containing cellMerge tags are successfully merged during runtime:

...
Hello
...

If value1 and value2 have different values, say “Hello” and “World”, table cells containing cellMerge tags are not merged during runtime:

...
Hello
...
World

Note

A cellMerge tag can be normally used within a table data band.