Demo
Readme
Changelog
Releases RSS Feed
Compatibility : nodejs and browser

Summary (version 3.0.2) Buy xlsx module

This module makes it possible to do templating on xlsx. It allows to do simple replacements and also loops.

README

Installation:

You will need docxtemplater npm install docxtemplater

Install this module with npm install --save "$url"

Usage

By using this module, you add support for the xlsx format (Excel format) that allows you to use placeholders in xlsx documents.

It supports rendering values as :

  • Floats/Integers, eg 10 or 10.5
  • Strings, eg Category
  • Percents, eg 33.5% for a value of 0.335
  • Dates, eg 06/29/19 when using a javascript Date object
  • Currency, eg $15.00 for a value of 15
var XlsxModule = require("docxtemplater-xlsx-module");

var xlsxOpts = {};
var xlsxModule = new XlsxModule(xlsxOpts);

var zip = new PizZip(content);
var docx = new Docxtemplater()
  .attachModule(xlsxModule)
  .loadZip(zip)
  .setData({
    name: "John Doe",
    totalPrice: {
      type: "currency",
      value: 100
    },
    discount: {
      type: "percent",
      value: 0.195
    }
  })
  .render();

var buffer = docx
  .getZip()
  .generate({ type: "nodebuffer", compression: "DEFLATE" });

fs.writeFile("test.docx", buffer);

Types

To work with "typed" data, (dates, percents, etc, you will have to use the following :

Basic types

  • For floats and integers, you can use Javascript floats or integers, eg doc.setData({average: 10.5})
  • For string, you can use Javascript strings, eg doc.setData({customerName: "John Doe"})

Complex types

For all complex types, you should use an object for representing them.

The object should contain a key type (string) representing the name of the type. The object should contain a key value representing the value of the type. The object can contain a key fmt representing how to represent that type (for example, how many leading zeroes, how many numbers after the dot).

  • For Percents, the type is "percent" and the value a number where 1 maps to 100% and 0.5 maps to 50%. For example : doc.setData({discount: {type: "percent", value: 0.10}}) to represent the number 10%

  • For Currencies, the type is "currency" and the value a number. For example : doc.setData({price: {type: "currency", value: 200}}) to represent the value $200. It is possible to change the currency by using a custom format, for example : doc.setData({price: {type: "currency", value: 200, fmt: "#,##0.00 [$€-407];[RED]-#,##0.00 [$€-407]"}}) to represent the value 200 €

  • For dates, you can use the date object in javascript, For example : doc.setData({today: new Date()}) will show you the current date formatted as "MM/DD/YY". You can also use the "object notation" to specify a custom format : doc.setData({date: {type: "date", value: new Date(1561799335622), /*29th June 2019*/ fmt: "DD/MM/YYYY"}})

Loops

It is possible to use loops with the syntax :

{#items} | {name} | {quantity} | {price} | {/items}

With following code :

// doc = new Docxtemplater()
// ...
doc.setData({
  items: [
    {
      name: "Product A",
      quantity: 5,
      price: {
        type: "currency",
        value: 10
      }
    },
    {
      name: "Product B",
      quantity: 1,
      price: {
        type: "currency",
        value: 20
      }
    }
  ]
});

The beginning and the end of the loop must be on the same row, but not on the same cell (if the loop start is on the same cell as the loop end, docxtemplater will compile it to a "innerloop", see below).

InnerLoops

If the beginning and the end of the loop are on the same cell, the row is not repeated but rather just the content inside that cell.

For example :

{#items} {name} | {#tags}{value} |  {quantity} | {price} | {/items}
                | {/}            |             |         |

with the data

{
  "items": [
    {
      "name": "AcmeSoft",
      "tags": [{ "value": "fun" }, { "value": "awesome" }],
      "quantity": 10,
      "price": {
        "type": "currency",
        "value": 100
      }
    }
  ]
}

will render as :

AcmeSoft | fun      | 10 | $100 |
         | awesome  |    |      |

Default Formats

It is possible to change the default formats for currencies, percents, dates.

To do it, use the fmts option in the opts of the module

For example :

const xlsxOpts = {
  fmts: {
    general: "General",
    int: "General",
    float: "0.0",
    currency: "#,##0.00 [$€-407];[RED]-#,##0.00 [$€-407]",
    date: "DD/MM/YYYY",
    percent: "0.0%"
  }
};
const xlsxModule = new XlsxModule(xlsxOpts);
doc.attachModule(xlsxModule);

Limitations

With the xlsx module, when using loops, the cells under them will be moved down so that do not get "overwritten" by the loop. However the formulas in the cells do not get overwritten, as this would require a much more complex algorithm.

CHANGELOG

3.0.2

Add support for xlsm filetype

3.0.1

  • Bugfix when using this module on a docx document, this no more fails.

  • Bugfix when creating XLSXModule without any option (a stacktrace was shown in 3.0.0)

3.0.0

  • Initial release

Edgar Hipp

I'm the creator of docxtemplater. I work on making docxtemplater great since 2013.