Mitosis - Excel Templater

Presentation

Mitosis - Excel Templater is a templater engine to create Excel files, inspiring from {Mustache} syntax. It enables to generate complex formatted Excel files without code, just by feeding an Excel template and data to it. It is a npm package for node or browser.

Visit the website for a live Demo!

How it works

Use Excel to create a template with the formatting and formulae you want and insert placeholders where your data must be mapped.

{tagName} is a simple placeholder. It will be replaced by the value mapping to tagName.
{=tagName} is a numeric placeholder. It will enforce the type of the value under tagName to be numeric.
{?tagName} is a conditional placeholder. It will display the line where it stands only if it is truthy. (Note that the placeholder gets removed, it is not replaced by its value.)
{#tagName} and {/tagName} are contextual and iterative placeholders.
- If the value under tagName is an object, the placeholders within the lines between both bounds will refer to the fields of this object.
- If the value under tagName is an array of objects, the section between both bounds will be repeated for each element of the array, with the placeholders referring to the fields of the current iteration's object.

Example:

Excel Template:

  |     A     |     B      |      C     |
1 | {name}    | {surName}  |            |
2 | Job: {job}             | {?job}     |
3 | Sport: {sport}         | {?sport}   |
4 | Cars of {name} {surName}:           |
5 | > {brand} | {model}    | {#carList} |
6 |   {color} | {=year}    | {/carList} |
7 |           |            |            |
8 | Oldest car's year:     | =MIN(B6)   |

Data:

{
 "name": "Louis",
 "surName": "Durand",
 "job": "Software Engineer",
 "carList": [
   { "model": "C4", "color": "Blue", "brand": "Citroën", "year": "2011" },
   { "model": "3008", "color": "Brown", "brand": "Peugeot", "year": 2012 }
 ]
}

Result:

  |     A     |    B    |     C    |
1 | Louis     | Durand  |          |
2 | Job: Software Engineer         |
3 | Cars of Louis Durand:          |
4 | > Citroën | C4      |          |
5 |   Blue    |    2011 |          |
6 | > Peugeot | 3008    |          |
7 |   Brown   |    2012 |          |
8 |           |         |          |
9 | Oldest car's year:  | 2011*    |   *result of formula: =MIN(B5,B7)

The values inserted are formatted according to the format of the cell it belongs to. Values can be anything supported by ExcelJS CellValue type: strings, numbers, dates, or specific structures for formulae, links, styles, ...

Features

Separate Style from content

Supports:
- nested iterations and conditionals
- named tables
- merged cells
- images
- multiple worksheets
- conditional styles
Updates references in formulae
Runs on browser or node
Supports ExcelJs CellValue to set style programmatically

Designed for Typescript

In-code types and documentation
Provides utility to generate the type structure corresponding to the Excel template. See below.
Possibility to integrate type generation in the build process: this ensures template and data are aligned!

Get started easily!

Try it yourself with the Demo!
The Demo website is open-source! Look at the code to see how to integrate ExcelTemplater in a project!

Integration

Buy Mitosis - Excel Templater on PrivJs
As per the instructions in the mail you will receive, run the following commands:
- npm config set @privjs:registry https://r.privjs.com
- npm login --registry https://r.privjs.com
- npm install mitosis-excel-templater
Use the class ExcelTemplater in your project as follows:

import { ExcelTemplater, TemplateData } from 'mitosis-excel-templater';

// Specify the path where to fetch the template from, or a Buffer/ArrayBuffer of the xlsx template.
const excelTemplater = new ExcelTemplater('templates/cars.xlsx');

// The data mapping.
const templateData: TemplateData = {
 name: "Louis",
 surName: "Durand",
 job: "Software Engineer",
 carList: [
   { model: "C4", color: "Blue", brand: "Citroën", year: "2011" },
   { model: "3008", color: "Brown", brand: "Peugeot", year: 2012 }
 ]
};

// Generate the output xlsx file: it will be saved as a file in the file system for NodeJS, or as a download in the browser.
await excelTemplater.saveAsExcel(templateData, 'generated/my-cars.xlsx');

Type generation

If you want to enforce alignment between the placeholders in the Excel template and the data you feed into it, Mitosis - Excel Templater offers a type generation functionality that you can integrate into your build process!

Create the file scripts/gen-types.js with the following content:

const { ExcelTemplater } = require('mitosis-excel-templater');

// Specify the path where to fetch the template from, or a Buffer/ArrayBuffer of the xlsx template.
const excelTemplater = new ExcelTemplater('templates/cars.xlsx');
// Specify the path in your project where to save the type definition file.
excelTemplater.generateTemplateDataTypeFile('src/types/generated/CarsData');

Add the following script to your package.json. You can also add it to your build command!

{
  ...
  "scripts": {
    ...
    "gen-types": "node scripts/gen-types.js",
    "build:prod": "node scripts/gen-types.js && webpack --mode production"
    ...
  },
  ...
}

Run type generation with npm run gen-types. This will create a typescript file CarsData.d.ts with the type structure inferred from the Excel template!
Use this type in your code!

import { CarsData } from "src/types/generated/CarsData";
...
// The data mapping.
const templateData: CarsData = {
 name: "Louis",
 ...
};
...

You're done! This will enable type checking at compile time and a convenient auto-completion of the fields in your IDE!

Known caveats

Graphs and plots are sadly not supported because of a restriction from ExcelJS. They will just disappear in your generated file.
There is currently no iteration over columns because it wasn't needed in my different use cases. Please ping me if you want this supported in the future! This could be my next feature!
If you get errors when opening your generated Excel file, or a recovery message, try creating a new template file from scratch and copy the content of each worksheet from the original template to the new file.
If you run into any other restriction or bug, please reach out by e-mail!

Contact

You can contact me at the e-mail below. We can schedule a videocall to discuss your specific needs.

I'm actively maintaining Mitosis - Excel Templater, so do not hesitate to send me your questions, requests or suggestions.

I'm an experienced Software Engineer, from France, and have worked at various companies including Amazon, Safran or my own startup. I am committed to delivering the best software for my customers.

louisworkplace.net

contact@louisworkplace.net