Conditionally clear cells in XLSX templates

You can use collapse formatter to clear cells of an Excel document.

First of all, review the conditionally clear cells demo. There is a template for the case that we describe here.

The formatter checks if a value for current tag is empty or true, then clears the content of 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 collapse 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 collapse formatter inside each that we want to clear. You may notice that we also use hide formatter with collapse formatter together. It hides the value current tag.

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 sometimes we need to display confidentiality notice inside our document. 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 collapse formatter only inside one of them. The templating engine will see that there is a named range and clear all cells inside it.

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