Generate dynamic sheets from ODS templates with CarboneJS

Creating dynamic sheets is possible but it is not documented on the CarboneJS website and github.
The use of variables is the only way to generate new sheets on ODS files. Unfortunately, it does not work with Excel templates. before taking this forward, make sure to understand Carbone basis and here is a quick reminder about Carbone variables.

As an example, let’s generate an ODS report with multiple sheets containing fruit nutritional values.
First, let’s define 2 variables on the template:

{#sheet1 = d.fruits[i].name}
{#sheet2 = d.fruits[i+1].name}

Secondly, insert the variables on the sheet names:

{$sheet1} // for sheet number 1
{$sheet2}  // for sheet number 2

During the rendering, CarboneJS replace the variables with the repetition markers {d.fruits[i].name} and {d.fruits[i+1].name} to generate new sheets that inherit content from the first sheet.

The example below comprises of an ODS template, a JSON data, the Javascript and a screenshot of the generated document.

The following shows a template example (filename: template.ods):

The index.js to generate the report is as follows:

// Imports 
const fs = require("fs");
const carbone = require("carbone");

// Files input/ouput
const FILE_INPUT = "./template.ods";
const FILE_OUTPUT = "./result.ods";

// Data to inject
const DATA = require("./data.json");

// Report rendering
carbone.render(FILE_INPUT, DATA,  async (err, result) => {
  if (err) {
    return console.error(err);
  }
  await fs.writeFileSync(FILE_OUTPUT, result);
});

The JSON data that contain the fruit list (filename: data.json):

{
  "fruits": [
    {
      "name": "apple",
      "nutrients": [
        {
          "type": "energy",
          "value": 52,
          "unit": "Kcal"
        },
        {
          "type": "Carbohydrates",
          "value": 13.81,
          "unit": "g"
        },
        {
          "type": "Protein",
          "value": 0.26,
          "unit": "g"
        },
        {
          "type": "Fats",
          "value": 0.17,
          "unit": "g"
        },
        {
          "type": "Cholesterol",
          "value": 0,
          "unit": "g"
        },
        {
          "type": "Dietary Fiber",
          "value": 2.4,
          "unit": "g"
        }
      ]
    },
    {
      "name": "banana",
      "nutrients": [
        {
          "type": "energy",
          "value": 89,
          "unit": "Kcal"
        },
        {
          "type": "Carbohydrates",
          "value": 22.84,
          "unit": "g"
        },
        {
          "type": "Protein",
          "value": 1.09,
          "unit": "g"
        },
        {
          "type": "Fats",
          "value": 0.33,
          "unit": "g"
        },
        {
          "type": "Cholesterol",
          "value": 0,
          "unit": "g"
        },
        {
          "type": "Dietary Fiber",
          "value": 2.6,
          "unit": "g"
        }
      ]
    },
    {
      "name": "strawberry",
      "nutrients": [
        {
          "type": "energy",
          "value": 53,
          "unit": "Kcal"
        },
        {
          "type": "Carbohydrates",
          "value": 12.75,
          "unit": "g"
        },
        {
          "type": "Protein",
          "value": 1.11,
          "unit": "g"
        },
        {
          "type": "Fats",
          "value": 0.33,
          "unit": "g"
        },
        {
          "type": "Cholesterol",
          "value": 0,
          "unit": "g"
        },
        {
          "type": "Dietary Fiber",
          "value": 3.3,
          "unit": "g"
        }
      ]
    }
  ]
}

Finally, execute the index.js:

$ node index.js

Voila, the generated document result.ods contains sheet for every fruit 🍎🍌🍓:

Conclusion

We have covered how to generate multiple sheets from an ODS template. Carbone can be used to render complex (and awesome) reports, do not hesitate to contact me for any help.

If you like the Carbone project, feel free to fork and contribute: Github.

Leave a like/comment or follow me to support this article 🍻

Thanks for reading!