Conditionally hide blocks in XLSX templates

Clear content of a single cell with hide-block-if

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 single cell with “map” formatter

Let us assume that we would like to inform a customer about the fulfillment of their order and optionally include a coupon.

This is JSON representation of order data:

{
    "orderNumber": "NL-3752",
    "includeCoupon": "Yes"
}

The map formatter can be used as follows:

{{includeCoupon}:map("Yes" = "As a token of our appreciation, we'd like to offer you 10% off your next order. Just use the code DISCOUNT10 at checkout.", "No" = "")}

This way, the coupon information will be displayed if the token’s value is set to “Yes”, and hidden if the value is set to “No”.

The map formatter can accept an unlimited number of values. We can use this to provide different discount tiers:

{{includeCoupon}:map("10%" = "We'd like to offer you 10% off your next order. Use the code DISCOUNT10 at checkout.", "20%" = "We'd like to offer you 20% off your next order. Use the code DISCOUNT20 at checkout.", "No" = "")}

The result for the “20%” value will look like this:

Clear content of a single cell with “if” formatter

Let us assume that we would like to inform a customer about the fulfillment of their order and optionally include a coupon.

This is JSON representation of order data:

The if formatter can be used as follows:

{{includeCoupon}:if(value == "Yes", "As a token of our appreciation, we would like to offer you 10% off your next order. Just use the code DISCOUNT10 at checkout.", "")}

This way, the coupon information will be displayed if the token’s value is set to “Yes” and hidden in case of any other value.

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