DEV Community

Cover image for Generate dynamic sheets from ODS templates with CarboneJS
Steeve
Steeve

Posted on

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}
Enter fullscreen mode Exit fullscreen mode

Secondly, insert the variables on the sheet names:

{$sheet1} // for sheet number 1
{$sheet2}  // for sheet number 2
Enter fullscreen mode Exit fullscreen mode

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):

Template

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);
});

Enter fullscreen mode Exit fullscreen mode

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"
        }
      ]
    }
  ]
}
Enter fullscreen mode Exit fullscreen mode

Finally, execute the index.js:

$ node index.js
Enter fullscreen mode Exit fullscreen mode

Voila, the generated document result.ods contains sheet for every fruit πŸŽπŸŒπŸ“:
ODS Report generated

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!

Top comments (0)