Conditionally clear cells in XLSX templates¶
You can use hide-block-if formatter or hide-block-if-empty 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 equal to a value of the parameter, then clears the content of cells.
Two cases when you can use it:
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 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:
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 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 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 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.
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:
The result will clear content of all cells inside the named range:
