Conditionally hide blocks in XLSX templates

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 formatter to each cell that we want to clear. In our case, we will apply formatter to contacts property like this:

As you can see, the tag {{contacts}:hide-block-if(value == null)} was used inside each cell for the conditional clearing cells. The generated document will look like this:

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:

Then it will appear in the dropdown like this:

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.

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:

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

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