Loops and nesting in XLSX templates

Named ranges

Excel allows you select cells and assign some name to them. You can learn about names in the Excel documentation.

The templating engine uses named ranges extensively. Here is how you can create one:

Then it will appear in the dropdown like this:

Loops and nesting example

First of all, review the loops and nesting demo. There is a template for the case that we describe here.

You can create nested constructions by putting one named range inside another or by putting a table inside a named range.

Let us create a document with following nested structure:

• Sheets

  • Regular rows

  • Named ranges

    • Regular rows

    • Tables

Let us assume we have data for a quarterly sales report. We will display the report for each quarter on a separate sheet. The resulting document will look like this:

As you can see, there are multiple sheets. Each sheet has a header with the name of a period. Then sales table with month name displayed for each month.

Before we started researching our template, review the source data in JSON format.

Here is general structure of the data from the demo. Most of the content is omitted by “...” symbols:

{
    "reports":[
        {
            "quarter": "Q1",
            "sales": [
                {
                    "month": "Jan",
                    "products": [
                        {
                            "name": "Television set",
                            "total": 63225.81
                        },
                        ...
                    ]
                },
                ...
            ]
        },
        {
            "quarter": "Q2",
            "sales": [
                {
                    "month": "Apr",
                    "products": [
                        {
                            "name": "Television set",
                            "total": 54193.55
                        },
                        ...
                    ]
                },
                ...
            ]
        }
    ]
}

Here is the template that renders reports:

You can refer a property inside collection in your template. You can even refer a property inside collection nested in another collection. The templating engine understands that it needs to render all items of a collection. It is smart enough to understand what content needs to be duplicated.

Examples:

• The {{reports.quarter}} tag lets the engine know that we want to render a separate sheet for each quarter when it is used as a sheet name. The same tag is also used at the top of the sheet. There it just displays regular bold Excel cell with larger font size.

• The {{reports.sales.month}} tag displays name for each month and lets the engine know that we want to render a list of months.

• The {{reports.sales.products.name}} tag is used inside a table and lets engine know that we want to render a list of product sales inside each month.

We also used a named range around the month title and the table with product sales. It lets the engine know that we want to repeat this particular set of cells for each month.

Named ranges is a handy way to define a set of cells that should be repeated. You can create as many named ranges as you need and they can be nested.