Conditionally hide blocks in XLSX templates

For Excel templates, hiding means either clearing cells or excluding certain content from the repeatable blocks. In the first case, it is necessary to use hide-block-if or hide-block-if-empty formatter, in the second — a filter operation. You can find template and data samples in the conditionally hide blocks demo.

Clearing cells

Clear content of a single cell

Les us assume that we have data about some company, but contacts data may be missing. JSON representation of the object:

{
  "companyName": "Plumsail",
  "site": "http://plumsail.com",
  "contacts": null
}

We want to clear content of all cells with information about contacts if the contacts property is null. To do it we need to put a tag with the hide-block-if-empty formatter to each cell that we want to clear. In our case, we will apply formatter to contacts property like this:

Clear single cell template

As you can see, there are tags with hide-block-if-empty formatter inside each that we want to clear. The generated document will look like this:

Clear single cell result

Clear content of a named range

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:

Named range creation

Then it will appear in the dropdown like this:

Named range selection

Let us assume that we need to display confidentiality notice inside our document only in certain cases. Thus, sometimes we need to display it, sometimes we want to clear cells that contain it.

In our case confidentiality notice occupies two cells. We can assign a named range to them and put hide-block-if(true) formatter only inside one of them. The templating engine will see that there is a named range and clear all cells inside it. The formatter checks if a value for a current tag is equal to the value of the parameter, then clears the content of cells.

Note

This approach works only if there is a single tag inside your named range. If there are multiple tags, the templating engine will switch to logic with hiding content of a single cell

We can manipulate visibility of confidentiality notice by a single boolean property in our JSON object:

{
  "hideConfNotice": true
}

That is how our template with named range looks:

Confidentiality notice template

The result will clear content of all cells inside the named range:

Confidentiality notice result

Filtering repeatable blocks

Sample case

Let us assume we have a collection of employees. We are going to render the information about them, but we want to hide employees from a specific department (Engineering). This is a JSON representation of their data:

{
  "employees": [
    {
      "name": "Lee Gu",
      "title": "Director",
      "department": "Manufacturing",
      "manager": "Patti Fernandez",
      "dateOfHire": "2007-03-29",
      "address": "6351 Fringilla Avenue",
      "cell": "(559) 104-5475",
      "dateOfBirth": "1971-08-09",
      "SIN": "778-62-8144"
    },
    {
      "name": "Henrietta Mueller",
      "title": "Developer",
      "department": "R&D",
      "manager": "Lee Gu",
      "dateOfHire": "2017-09-19",
      "address": "574-8633 Arcu Street",
      "cell": "(861) 546-5032",
      "dateOfBirth": "1987-01-31",
      "SIN": "757-85-7495"
    },
    {
      "name": "Johanna Lorenz",
      "title": "Senior Engineer",
      "department": "Engineering",
      "manager": "Lee Gu",
      "dateOfHire": "2012-05-11",
      "address": "6818 Eget St.",
      "cell": "(425) 288-2332",
      "dateOfBirth": "1982-04-12",
      "SIN": "149-13-7317"
    }
  ]
}

Filtration

The filter operation can be put at any place, even on a separate sheet to have a convenient access to all filtration rules. Here is the operation we used in our example:

{{e = employees|filter(value.department != "Engineering")}}

The token {{e}} does not contain the object of Johanna Lorenz. She was excluded because working in the engineering department. After that, the alias token can be used for rendering the repeatable blocks.

Result

Regular rows

Template

Result

Hide regular rows template

Hide regular rows result

Table rows

Template

Result

Hide table rows template

Hide table rows result

Named range of cells

Template

Result

Hide a range of cells template

Hide a range of cells result

Sheets

Template

Result

Hide sheets template

Hide sheets result