Xlsx module | docxtemplater

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 :

Numbers, 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
Currencies, eg $15.00 for a value of 15

It also supports adding dynamic images to your Spreadsheets if you also have access to the image module.

Usage (nodejs)

const XlsxModule = require("docxtemplater-xlsx-module");

const fs = require("fs");
const content = fs.readFileSync("demo_template.xlsx");
const zip = new PizZip(content);
const doc = new Docxtemplater(zip, {
    modules: [new XlsxModule()],
});
doc.render({
    name: "John Doe",
    totalPrice: {
        type: "currency",
        value: 100,
    },
    discount: {
        type: "percent",
        value: 0.195,
    },
});

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

fs.writeFile("output.xlsx", buffer);

Usage (browser)

<html>
    <script src="node_modules/docxtemplater/build/docxtemplater.js"></script>
    <script src="node_modules/pizzip/dist/pizzip.js"></script>
    <script src="node_modules/pizzip/vendor/FileSaver.js"></script>
    <script src="node_modules/pizzip/dist/pizzip-utils.js"></script>
    <script src="build/xlsxmodule.js"></script>
    <script>
        const xlsxMime =
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

        PizZipUtils.getBinaryContent(
            "examples/simple.xlsx",
            function (error, content) {
                if (error) {
                    console.error(error);
                    return;
                }

                const zip = new PizZip(content);
                const doc = new docxtemplater(zip, {
                    modules: [new XlsxModule()],
                });

                doc.render({
                    name: "John Doe",
                });
                const out = doc.getZip().generate({
                    type: "blob",
                    mimeType: xlsxMime,
                });
                saveAs(out, "generated.xlsx");
            }
        );
    </script>
</html>

Features

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.render({average: 10.5})
For string, you can use Javascript strings, eg doc.render({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).

Percent type

For Percents, the type is "percent" and the value is a number where :

1 maps to 100%
0.5 maps to 50%

For example : doc.render({discount: {type: "percent", value: 0.10}}) represents 10%.

Currency type

For Currencies, the type is "currency" and the value a number. For example : doc.render({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.render({price: {type: "currency", value: 200, fmt: "#,##0.00 [$€-407];[RED]-#,##0.00 [$€-407]"}}) to represent the value 200 €

Date type

For dates, you can use the date object in javascript, For example : doc.render({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.render({date: {type: "date", value: new Date(1561799335622), /*29th June 2019*/ fmt: "DD/MM/YYYY"}}). It is possible to use the following parameters for the date format :

m:Months as 1–12
mm:Months as 01–12
mmm:Months as Jan–Dec
mmmm:Months as January–December
mmmmm:Months as the first letter of the month
d:Days as 1–31
dd:Days as 01–31
ddd:Days as Sun–Sat
dddd:Days as Sunday–Saturday
yy:Years as 00–99
yyyy:Years as 1900–9999
h:Hours as 0–23
hh:Hours as 00–23
m:Minutes as 0–59
mm:Minutes as 00–59
s:Seconds as 0–59
ss:Seconds as 00–59
h AM/PM:Hours as 4 AM
h:mm AM/PM:Time as 4:36 PM
h:mm:ss A/P:Time as 4:36:03 P
[h]:mm:Elapsed time in hours; for example, 25.02
[mm]:ss:Elapsed time in minutes; for example, 63:46
[ss]:Elapsed time in seconds
h:mm:ss.00:Fractions of a second

Formula type

For formulas, with following template :

{myVal}

You can use this code :

doc.render({ myVal: { type: "formula", value: "A1+B1" } });

If you are using document previewers, the computed result of this calculation may not be shown. Since version 3.14.0, you can use the following to make it work :

doc.render({
    myVal: { type: "formula", value: "A1+B1", result: 42 },
});

Note that this will not apply to formulas that are not generated by docxtemplater, for example existing formulas won't be updated.

Since version 3.24.0, it is possible to add a format to formulas using an array for the type, like this :

doc.render({
    result: {
        type: ["formula", "currency"],
        value: "A1+B1+C1",
        result: 42,
    },
});

This will show the cell as "$42.00"

Loops

It is possible to use loops with the syntax :

{#items}

{name}

{quantity}

{price}

{/items}

With following code :

// doc = new Docxtemplater()
// ...
doc.render({
    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 this data :

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

will render as :

AcmeSoft

fun
awesome

$100

The innerLoopNewLine option can be used to disable insertion of newlines inside innerloops.

Set this option to false to disable the insertion of newlines.

const doc = new Docxtemplater(zip, {
    modules: [
        new XlsxModule({
            innerLoopNewLine: false,
        }),
    ],
});
doc.render({
    users: [
        { name: "John" },
        { name: "Mary" },
        { name: "Franck" },
    ],
});

With the following template :

{#users}{name}{/}

xxx

Will render

JohnMaryFranck

xxx

Horizontal loop (or expansion)

You can, since version 3.5.0, expand an array horizontally :

Users are	{>users}{name}
Ages are	{>users}{age}

with this data :

{
    "users": [
        { "name": "John", "age": 22 },
        { "name": "Mary", "age": 29 },
        { "name": "Dave", "age": 41 }
    ]
}

will render as :

Users are	John	Mary	Dave
Ages are	22	29	41

Note that the cells on the right of the horizontal loop will be pushed (but only those that are on the same line of course).

Table insertion

By using standard loops (to create multiple rows), together with horizontal loops (to create multiple columns), one can create a table which has dynamic number of rows and dynamic number of columns.

Write the following in your template :

{#elements}{>.}{.}

And following data :

{
    "elements": [
        ["Name", "Age", "Hobby"],
        ["John", 12, "Football"],
        ["Stan", 6, "Music"],
        ["Kate", 9, "Shopping"]
    ]
}

It will generate the following xlsx :

Name	Age	Hobby
John	12	Football
Stan	6	Music
Kate	9	Shopping

If your data is shaped like this :

{
    "elements": [
        {
            "name": "John",
            "age": 12,
            "hobby": "Football"
        },
        {
            "name": "Stan",
            "age": 6,
            "hobby": "Music"
        }
    ]
}

You can use this template :

{#elements | to2d}{>.}{.}

Together with following angular parser filter :

const expressionParser = require("docxtemplater/expressions.js");
expressionParser.filters.to2d = function (elements) {
    if (!(elements instanceof Array)) {
        return expressionParser.filters.to2d([elements]);
    }
    const keys = Object.keys(elements[0]);
    const result = [keys];
    elements.forEach(function (element) {
        const line = [];
        keys.forEach(function (key) {
            line.push(element[key]);
        });
        result.push(line);
    });
    return result;
};

Default Formats

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

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

For example :

const xlsxOpts = {
    fmts: {
        float: "0.0",
        currency: "#,##0.00 [$€-407];[RED]-#,##0.00 [$€-407]",
        date: "DD/MM/YYYY",
        percent: "0.0%",
    },
};
const doc = new Docxtemplater(zip, {
    modules: [new XlsxModule(xlsxOpts)],
});
doc.render(data);

preferTemplateFormat

It is possible to configure the module to use the format ("fmt" property) from the cell in the template if that format matches the type of the value.

This can be used like this (locally for some data points) :

doc.render({
    date: {
        type: "date",
        value: new Date(1561795023000), // 29th June 2019
        fmt: "DD/MM/YYYY",
        preferTemplateFormat: true,
    },
});

In that case, if the {date} tag is in a cell formatted as a Date, it will use the existing cell format.

If the {date} tag is formatted as General or something that is not a date, it will use the format specified in "fmt".

This option can also be set globally like this :

const doc = new Docxtemplater(zip, {
    modules: [
        new XlsxModule({
            preferTemplateFormat: true,
        }),
    ],
});
doc.render(data);

Customize how objects are stringified

With following template :

Date	{#loop}{date}{/}

With following data :

doc.render({
    loop: [{ date: new Date() }, { date: new Date() }],
});

In versions 3.14.4 and prior, the output would contain some [object Object].

It will now instead show Mon Nov 20 2023 13:16:03 GMT+0100

It is possible to configure how those parts will be shown using the stringify option, like this :

const doc = new Docxtemplater(zip, {
    modules: [
        new XlsxModule({
            stringify: (value) => {
                if (value.fmt === "DD/MM/YYYY") {
                    return value.value.toLocaleDateString(
                        "uk-GB"
                    );
                }
                if (value instanceof Date) {
                    return value.toLocaleDateString("uk-GB");
                }
                return value.toString();
            },
        }),
    ],
});
doc.render(data);

Nullgetter configuration

It is possible to change the nullGetter to avoid showing "undefined" for xlsx files.

Use the following :

const doc = new Docxtemplater(zip, {
    modules: [new XlsxModule()],
    nullGetter: (part) => {
        // (The part.square value is only set on xlsx files)
        if (part.square || part.module) {
            return "";
        }
        return "undefined";
    },
});
doc.render();

Nested subloops on same line

It is possible to loop over multiple layers of your data structure and still have exactly one line for each recursive loop.

For example, with following data structure :

{
    {
      "name": "Plan 1",
      "price": "800 $",
      "product": [
        {
          "name": "Awesome table",
          "quality": [
            {
              "name": "Blueish"
            },
            {
              "name": "Gray"
            }
          ]
        },
        {
          "name": "Fun Jigsaw",
          "quality": [
            {
                "name": "Easy",
            },
            {
                "name": "Difficult",
            }
          ]
        }
      ]
    }
  ]
}

You can generate following file :

Plan 1	800 $	Awesome table	Blueish
Plan 1	800 $	Awesome table	Gray
Plan 1	800 $	Fun Jigsaw	Easy
Plan 1	800 $	Fun Jigsaw	Difficult

By using following template :

{#plans | subloop:'product.quality'}{name}

{price}

{product ? product.name : '' }

{quality ? quality.name : ''}{/}

and following code :

const XlsxModule = require("docxtemplater-xlsx-module");
const expressionParser = require("docxtemplater/expressions.js");

expressionParser.filters.subloop = function (input, filters) {
    function recur(fullLine, targetedLine, properties, output) {
        if (properties.length === 0) {
            return fullLine;
        }
        const property = properties[0];
        if (!targetedLine[property]) {
            output.push(fullLine);
        }
        if (targetedLine[property].length === 0) {
            output.push(fullLine);
        }
        if (targetedLine[property].length > 0) {
            targetedLine[property].forEach(function (subtarget) {
                fullLine = {
                    ...fullLine,
                    [property]: subtarget,
                };
                const newLines = recur(
                    fullLine,
                    subtarget,
                    properties.slice(1),
                    []
                );
                output = output.concat(newLines);
            });
        }
        return output;
    }

    return input.reduce(function (output, line) {
        return recur(line, line, filters.split("."), output);
    }, []);
};

const doc = new Docxtemplater(zip, {
    modules: [new XlsxModule()],
    parser: expressionParser,
});
doc.render(data);

Automerge loops

With the following option :

const doc = new Docxtemplater(zip, {
    modules: [new XlsxModule({ autoMergeLoops: true })],
});
doc.render({
    companies: [
        {
            name: "Acme",
            users: [
                {
                    name: "John",
                },
                {
                    name: "Mary",
                },
            ],
        },
        {
            name: "Doc",
            users: [
                {
                    name: "John",
                },
                {
                    name: "Mary",
                },
            ],
        },
    ],
});

{#companies}{name}

{#users}{name} {/}{/}

Will render into :

Acme	John
	Mary
Doc	John
	Mary

Height of rows

The height of rows is updated by the module and tries to use an algorithm optimized for Microsoft Excel.

It is possible that if you open generated files using other software, such as libreoffice or WPS, the rendering of the text inside the rows will make the row not high enough to contain the whole text.

This is because the margin between text in a row is not specified in the document itself and is dependent on the software used for rendering the sheet.

For this reason, it is possible to change the algorithm used for calculating the height of the row.

const doc = new Docxtemplater(zip, {
    modules: [
        new XlsxModule({
            getRowHeight({ lines, fontSize }) {
                return Math.round(lines * (fontSize + 3) + 1.4);
            },
        }),
    ],
});
doc.render({
    users: [
        { name: "John" },
        { name: "Mary" },
        { name: "Franck" },
    ],
});

The lines is the number of lines in that cell, and fontSize is the fontsize of the text in that cell.

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 are not rewritten, as this would require a much more complex algorithm.

It is possible to work around this limitation by using following trick :

In formulas, if you are on row number 8 for example, and your formula looks like this :

=2*C8/D8

You can instead reference the current row number from the formula, by writing :

=2*INDIRECT("C"&ROW())/INDIRECT("D"&ROW())

The ROW() call will return the number of the row that a given cell is in, and INDIRECT("C8") will return the value of the cell in C8.

This way, loops with formulas will work well.

Inserting images

In order to add images to the xlsx, you will also need to have access to the image module.

You can add an image simply by using the following template :

Text

{%logo}

The "%" is the prefix for image tags, thus the data will be retrieved from the "logo" key.

Use following code to add the image :

const fs = require("fs");
const XlsxModule = require("docxtemplater-xlsx-module");
const ImageModule = require("docxtemplater-image-module");

const imageOptions = {
    getImage(tagValue) {
        return fs.readFileSync(tagValue, "binary");
    },
    getSize(img, value, tagName, context) {
        const width = context.part.containerWidth;
        const height = context.part.containerHeight;
        return [width, height];
    },
};

const content = fs.readFileSync("demo_template.xlsx");
const zip = new PizZip(content);
const doc = new Docxtemplater(zip, {
    modules: [new XlsxModule(), new ImageModule()],
});
doc.render({
    image: "image.png",
});

The getSize function configured above will not keep the aspect ratio of your source image, it will simply scale the image to take the full width and full height of the cell it is in.

If you'd like to contain the image inside the cell, without distorting the image, use the following for your getSize function :

const sizeOf = require("image-size");

const imageOptions = {
    // getImage, // DON'T FORGET TO DECLARE IT
    getSize(img, value, tagName, context) {
        const sizeObj = sizeOf(img);
        const maxWidth = context.part.containerWidth;
        const maxHeight =
            context.part.containerHeight ||
            context.part.containerWidth;

        const widthRatio = sizeObj.width / maxWidth;
        const heightRatio = sizeObj.height / maxHeight;
        if (widthRatio < 1 && heightRatio < 1) {
            /*
             * Do not scale up images that are
             * smaller than maxWidth,maxHeight
             */
            return [sizeObj.width, sizeObj.height];
        }
        let finalWidth, finalHeight;
        if (widthRatio > heightRatio) {
            /*
             * Width will be equal to maxWidth
             * because width is the most "limiting"
             */
            finalWidth = maxWidth;
            finalHeight = sizeObj.height / widthRatio;
        } else {
            /*
             * Height will be equal to maxHeight
             * because height is the most "limiting"
             */
            finalHeight = maxHeight;
            finalWidth = sizeObj.width / heightRatio;
        }

        return [Math.round(finalWidth), Math.round(finalHeight)];
    },
};

See the documentation of getImage for more information about how to configure your image module.

CHANGELOG

3.26.0

Add feature to be able to automatically merge together cells in loops : new XlsxModule({autoMergeLoops: true})
Fix bug with resolving of tags that are the same on multiple levels.
When the following template is used :
{#companies}{name} {#users}{name} {/}{/}
Both name properties would resolve to the same {name} value even though one is inside the {#companies} loop, and the other in the {#users} loop.
Now the correct value is resolved depending on the position of the tag.

3.25.3

Improve fix of 3.25.2 (to work on all cases).

3.25.2

Bugfix following stacktrace on some xltm/xltx files that have a calcChain.xml file :

Cannot read properties of undefined (reading 'A1')

3.25.1

Bugfix following stacktrace on some xltm/xltx files :

Cannot read properties of undefined (reading 'sheetId')

3.25.0

Add support for xltx and xltm files, those are template xlsx files.

3.24.1

Bugfix to take into account merged cells when updating cell height when text is wrapped.

If a merged cell has some wrapped text, in previous versions the first row of that cell would increase if the text was higher than the first row.

This behavior does not happen anymore if the text is smaller than the merged cell.

3.24.0

Add support to set "formula" type + "currency" type.

Simply use an array for the type property with the two types (the main type formula should be placed first).

doc.render({
    result: {
        type: ["formula", "currency"],
        value: "A1+B1+C1",
        result: 42,
    },
});

3.23.4

Update to throw error if a corrupt character is present inside a loop.

Previously, the error would be ignored and no content would be shown in that cell.

3.23.3

Fix following stacktrace :

Cannot read properties of undefined (reading 'getAttribute')
at XlsxModule.addXmlDocuments
at XlsxModule.set
at node_modules/docxtemplater/js/docxtemplater.js:124:16
at Array.forEach (<anonymous>)
at Docxtemplater.setModules (node_modules/docxtemplater/js/docxtemplater.js:123:20)
at Docxtemplater.compile (node_modules/docxtemplater/js/docxtemplater.js:307:12)

This happened when using an XLSX file that had no style named "Normal".

3.23.2

Bugfix for chart module, to correctly move the charts down if there is a loop above the chart.

(This fix also needs chart module version 3.13.6 or higher)

3.23.1

Update to avoid stacktrace if passing an odt file (it should instead correctly show the error message The filetype "odt" is not handled by docxtemplater).

3.23.0

Improve calculation of cell width by following the official algorithm :

From OpenXML Spec point 18.3.1.13 (Column width && Formatting)

Column width measured as the number of characters of the maximum digit width of the
numbers 0, 1, 2, …, 9 as rendered in the normal style's font.
There are 4 pixels of margin
padding (two on each side), plus 1 pixel padding for the gridlines.

width = Truncate(
        [{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}] /
        {Maximum Digit Width}*256
) / 256

[Example: Using the Calibri font as an example, the maximum digit width of 11 point font
size is 7 pixels (at 96 dpi). In fact, each digit is the same width for this font. Therefore, if
the cell width is 8 characters wide, the value of this attribute must be

Truncate( (8*7+5)/7*256 )/256 = 8.7109375

This means that together with the image-module version 3.29.0, image centering with the %% tag should work better when using merge cell tags.

3.22.1

Bugfix regression when font name does not exist, the error was :

Cannot read properties of undefined (reading 'getAttribute')
at XlsxModule.addXmlDocuments
at XlsxModule.set
at node_modules/docxtemplater/js/docxtemplater.js:124:16

3.22.0

Add support for offsetting the images, the hyperlinks, and conditionalFormatting when those are placed right after loops.

In previous versions, images, links, and some conditionalFormatting would not move in that case.

3.21.1

Add support for centering of images when using {%%image} in xlsx files.

(This needs version 3.28.6 of the image module or higher).

3.21.0

Add support for moving conditional formatting when having loops

When loops are present in the document, the conditional formatting formulas will correctly update to take into account the loops present in the spreadsheet.

3.20.7

Bugfix for merged cell issue when multiple merge cells need to be updated because of a loop.

With the following template :

The output was invalid (note the A3,B3,A4,B4 cells which are not part of the merge cell).

And the correct output is now since v3.20.7 :

3.20.6

Make it possible to use prefix from the constructor

3.20.5

Bugfix when saving cell containing a tag such as {name} without wrapText, it would sometimes wrap the text.

3.20.4

Add typescript typings to be able to change the module prefix

3.20.3

In the continuation of 3.20.2, fix another similar infinite loop happening when having a very small cell with some bigger content.

3.20.2

Bugfix of infinite loop that occured since version 3.20.0

The issue was happening when a cell containing a placeholder like {name} was very small (less than 1 or 2 characters wide).

3.20.1

Bugfix to make it possible to include multiple newlines, like this :

doc.render({
    message: "Title \n\nContent\n\nBaz",
});

The line height of the generated cell will be correctly calculated in this case.

3.20.0

In this version, we fixed multiple issues related to cell height :

First of all, a bit of context: the height of a line is something that has to be written in the xlsx file, and Excel will not automatically change the height of a line if the text exceeds the height.

Excel does this at runtime when you write text from the keyboard, but it doesn't do it when you open an Excel workbook.

We therefore need to simulate the algorithm for changing the cell height.

We use the following input : the width of the cell, the new text in the cell, and the font-size and font-family of that text.

The bugs were the following :

The size of the columns was calculated and instead of storing the size for each Excel sheet, there was only one data structure. data structure. Logically, this had serious consequences in the case of multiple sheets. We now fixed this
There was also a bug with the loops. cell created by a loop, the height of the cell was miscalculated calculated incorrectly when there were "merged cells" (mergeCell)
The last and most complex improvement was that in previous versions, all characters were treated as having the same width. This may be true for some fixed-width font sizes such as Courier, but for Arial for example, the letter "W" and the symbol "!" are obviously not of the same width. The XLSX module now comes uses tables of characters for different font-families to calculate the width of a text to know how many lines of text should be created.

3.19.2

Bugfix browser test to work correctly when the template contains the "&" character, such as :

Hello && Goodbye

The following error was thrown :

TypeError: siTag is null

3.19.1

Add support for preferTemplateFormat.

3.19.0

Improve date support :

If passing the following as the data :

doc.render({
    date1: {
        type: "date",
        value: new Date(1561795023000),
        fmt: "mm/dd/yy",
    },
});

The mm/dd/yy format will be used for that cell, regardless of the previous format of that cell.

If the current cell has a date format, such as "mmmm/dd/yyyy", then that format will be kept, for example using this code :
```
doc.render({
    date1: new Date(1561795023000),
});
```

3.18.0

Add support for keepWrapping: true to keep existing wrapping even when the text will overflow.

3.17.5

Upgrade module to use NodeNext moduleResolution setting. See explanation here

3.17.4

Bugfix issue when having multiple sheets that contain tags.

Use nullGetter correctly. This could result in having "undefined" show up on your output document.

See nullGetter configuration section.

3.17.3

Update to bugfix an issue in ErrorLocationModule (version 3.9.2 of the ErrorLocationModule).

3.17.2

Use new resolvedId API => requires docxtemplater@3.43.0

3.17.1

Throw specific error during compilation if the parser cannot parse a given tag.

For example, the following tag :

{name //// foo}

Will now throw an error when running :

const doc = new Docxtemplater(zip, {
    modules: [new XlsxModule()],
});
doc.render(data);

This makes that error show with the error-location module as a comment.

3.17.0

Add possibility to use error-location module together with the XLSX module.

3.16.0

If text wrapping is enabled, and you replace a tag by a long text, the cell height will now be increased accordingly.

For example :

{text}

Foo

With following code :

doc.render({
    text: "Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet.",
});

Will now render :

Lorem ipsum dolor sit amet,
consetetur sadipscing elitr,
sed diam nonumy eirmod
tempor invidunt ut labore et
dolore magna aliquyam erat,
sed diam voluptua. At vero
eos et accusam et justo duo
dolores et ea rebum. Stet
clita kasd gubergren, no sea
takimata sanctus est Lorem
ipsum dolor sit amet.

foo

3.15.0

Allow to template {placeholder} inside sheetname, like in this example :

3.14.5

Add "stringify" function in constructor of the xlsx module.

This allows to render objects the way you want.

3.14.4

Add support for =HYPERLINK("https://ddg.gg", "DDG") in formulas, it will also automatically show up as underline with a blue link color.

3.14.3

When trying to include a document that has no styles.xml or no settings.xml file, the module would throw an error.

Now, the document is included correctly.

3.14.2

When using the inspectModule to getAllTags, avoid showing "undefined" key because the printArea tag or printTitle tag

Requires docxtemplater 3.42.1 or higher

3.14.1

Fix bug when using renderAsync and parser compilation fails : the error should now be shown inside a multierror. To to this, we know use sm.getValueAsync in resolve instead of sm.getValue

3.14.0

Update to allow to set "result" property when having formulas, such as :

{myVal}

doc.render({
    myVal: { type: "formula", value: "A1+B1", result: 42 },
});

3.13.13

Bugfix to set or get xml attributes correctly, even when using tabs instead of spaces in tags attributes

3.13.12

Set module.priority in order to have no more issues related to module ordering

3.13.11

Bugfix when using raw xml without any data value, like this :

{@raw}

With following code :

doc.render({
    // no data
});

The following error was thrown previously :

Cannot read properties of undefined (reading 'childNodes')

3.13.10

Upgrade to correctly pass in fileType, contentType, … to resolve

Makes it compatible with docxtemplater 3.40.1

3.13.9

Make xlsx module compatible with docxtemplater@3.40.0

3.13.8

Add module.clone() internal

3.13.7

Bugfix to work correctly (resize height of cell correctly) with html-xlsx module when the HTML output contains bulleted lists.

3.13.6

Bugfix to correctly update print area when the print area contains a "quoted" sheet, like this :

<definedName name="_xlnm.Print_Area" localSheetId="0">'Sheet1'!$B$1:$D$3</definedName>

Previously, such a printarea would not get updated.

3.13.5

Add support for bigints, to allow to render for example 10000000000000000033.

Previously, this value would be internally converted into 10000000000000000000 because of floating arithmetic.

3.13.4

Bugfix to allow for dates, fmt with hours, minutes, and seconds :

doc.render({
    date: {
        type: "date",
        value: new Date(1561799335622) /* 29th June 2019*/,
        fmt: "DD/MM/YYYY hh:mm:ss",
    },
});

3.13.3

Bugfix to correctly handle dates. Previously, dates were handled only with following code :

doc.render({
    date: {
        type: "date",
        value: new Date(1561799335622) /* 29th June 2019*/,
        fmt: "DD/MM/YYYY",
    },
});

Now it is also possible to write the following :

doc.render({
    date: {
        type: "date",
        value: "2019-06-29T07:57:03.000Z",
    },
});

This means that you can also use dates when you're using a JSON api (or when you are using the docker version).

3.13.2

Update to allow creation of HTML-xlsx module

3.13.1

Performance bufix when using XLSX module with many cells in async mode.

In prior versions, the async version of docxtemplater would be twice slower than the sync version if all data getters resolved immediately.

Now, the async version should be faster than before.

3.13.0

Performance bugfix :

In prior versions, the xlsx module was taking a lot of time when generating over 50000 cells.

Previously the generation of 50000 cells could take up to 7 minutes, now it should normally take less than 5 seconds.

3.12.1

Bugfix to correctly apply the alignment property when having data with new lines.

In previous versions, if a cell was left aligned, if the data contained a new line, the rendered cell would be wrongfully left aligned.

3.12.0

Bugfix when used together with the image module :

If the image is an image that uses the "description" tag, and is present inside a loop, the image is now correctly replaced.

This module requires version 3.19.0 of the xlsx module or higher

3.11.7

Update to work well with new angular parser (from docxtemplater/expressions.js), when using {this} as a numeric value.

In previous versions, this value would be converted to a string and formatted as such.

3.11.6

If using a corrupt character in some value, throw an error during generation.

For example,

doc.render({
    name: `John${String.fromCharCode(0x02)}Doe`,
});

Will now fail, in the same way that it fails on docx or pptx documents.

3.11.5

Avoid possible issue of "Maximum call stack size exceeded"

3.11.4

Bugfix : Previously, every unclosed loop would show the error twice.

The template :

{#loop}

{name}

(with no closing loop tag) would show two errors.

The error is no more duplicated.

3.11.3

Calculate correct image size even when having merged cell

3.11.2

Remove the single quotes in sheetnames when parsing a range.

3.11.1

Update to add forceFullCalc="1" and calcCompleted="0" after generation

3.11.0

Add containerWidth and containerHeight attributes to image tags.

3.10.3

Bugfix following regression :

Could not parse position for square ''

When having a "#REF" in the printarea or printtitle

3.10.2

Update to make module compatible with docxtemplater@3.30.0

Fixes following error :

TypeError: Cannot read properties of undefined (reading 'droppedTagsInsidePlaceholder')
at parse (js/parser.js:170:46)

3.10.1

Bugfix corruption when having calcChain and some loops will "move" formulas.

3.10.0

Enhance height calculation of rows for loops + inner loops.

Add getRowHeight function to customize the height calculation

3.9.3

Bugfix corruption when using horizontal loop expand module with cells after Z. The previous version was generating a cell named "A@5", which is disallowed (the cell name after Z should be AA).

3.9.2

Bugfix corruption when using styles inside cells with placeholders.

Bugfix error when having an "infinite" range, like :

"$A:$K" or "$1:$10"

It gave the following error :

TypeError: Cannot read properties of null (reading '4')
at getPosition (docxtemplater-xlsx-module/js/xl-utils.js:41:23)

3.9.1

Bugfix corruption when using a cell that has multiple styles (for example partially bold), and that has a {placeholder} in it.

3.9.0

When used together with the image module 3.13.0 or higher, Add possibility to replace images in Excel documents (using the description field).

3.8.6

Update typing files to add new option innerLoopNewLine.

3.8.5

Add option innerLoopNewLine to disable insertion of newlines inside innerloops.

3.8.4

Bugfix escaping of special characters such as <, >, & in async mode (with renderAsync)

3.8.3

Improve rendering speed a lot (and minimize output size), when using loops that contain a lot of empty cells.

Sometimes, rows can contain up to 16000 cells :

<c r="O2"/>
<c r="P2"/>
....
<c r="EEA2"/>
<c r="EEB2"/>
<c r="EEC2"/>
<c r="EED2"/>
<c r="EEE2"/>

3.8.2

Bugfix to avoid this error when generating a document :

RangeError: Maximum call stack size exceeded

3.8.1

Bugfix related to 3.8.0

Whitespace in

doc.render({ name: "      John" });

will now be correctly rendered.

This will work both with spacing and newlines

3.8.0

Always render spaces in xlsx files. Previously, spaces such as the following would not be rendered (sometimes) :

doc.render({ name: "     John" });

By always ensuring the use of <t xml:space="preserve">, the spaces are now shown correctly.

Make module compatible with docxtemplater@3.28.0. Please make sure to update docxtemplater to 3.28.0 at the same time you update this module. The internal change made is the use of the new matchers API which fixes bugs that were triggered depending on the order of the modules that are attached to the instance. Now the order of the modules should not matter as expected.

3.7.3

Update to make module work well together with docxtemplater 3.27

3.7.2

Update to make module work well together with docxtemplater 3.26.3

3.7.1

Return errors properly when there is an unopened tag or an unclosed tag.

Previously, the error "Error !!" was raised, without any better explanation.

3.7.0

Add support for {@rawXML} tag to be able to add formatted text to a given cell.

3.6.4

When using a loop containing a mergecell, with no data, that would put the number of mergecells of the document to 0 (no more mergecells in that sheet), the resulting document would be corrupted.

Now, the document is no more corrupted in this case.

3.6.3

Use @xmldom/xmldom instead of xmldom, see this github issue

3.6.2

Generate files in built with correct filename In previous versions, the filename was always build/docxtemplater.js. Now the filename is build/xlsxmodule.js The .min.js file is also created now.

3.6.1

Avoid corruption of document when using {>user} tag followed by an empty cell (that is formatted for example but has no value inside it)

Fixes this github issue

3.6.0

Make it possible to insert XLSX formulas, by using the library like this :

doc.render({
    computation: { type: "formula", value: "A1+B1" },
});

3.5.3

Make module work correctly when using docxtemplater >= 3.22.1 with >, <, …

Add typescript definitions for public API

3.5.2

Bugfix when having multiple merge cells inside a loop, fixes this bug report.

3.5.1

Bugfix when using multiple loops on same line, for example on :

{#loop}{#cond}

{name}

{/cond}{/loop}

Previously, this would generate the message : The tag "loop" is closed by the tag "cond"

3.5.0

Add horizontal loop feature with ">" prefix.

Fixes this github issue

3.4.3

Bugfix printarea when using async mode (with resolveData)

The stacktrace was :

TypeError: Cannot read property 'toString' of undefined
at getResolvedId (docxtemplater-xlsx-module/js/index.js:100:47)

3.4.2

Automatically update printarea when having loops

3.4.1

Bugfix to make Image replacement work in async mode (you need access to both modules image and xlsx)

3.4.0

Add support for ImageModule together with XLSXModule to insert images with {%image} (you need access to both modules)

3.3.7

Bugfix to allow to have loop at the right of a merged cell, and have the merged-cell auto expand.

3.3.6

Bugfix when having table with filters with loops.

In previous versions, the filter would not apply to the newly created rows.

Now, it does apply to all rows, ie the table is expanded when adding data.

3.3.5

Performance improvements for big loops.

Loops of more than 5000 elements will now be significantly faster.

3.3.4

Fix xlsx module to work with docxtemplater 3.18.2.

If you use docxtemplater 3.19.0 or higher, you have to use docxtemplater-xlsx module 3.3.4 or higher or you will see some rendered values duplicated at multiple points in the generated spreadsheet.

3.3.3

Bugfix issue "Cannot read property 'type' of null" when using null value in a cell

3.3.2

Add support for templating headers and footers.

3.3.1

Bugfix corruption when using xlsx table with multiline header

3.3.0

Add support for inverted loops (to show a cell if a value is falsy), for example :

{^show_vat} VAT DOES NOT APPLY {/}

3.2.9

Avoid corruption when using array formulas (For example <f t="array" ref="C3"/>)

3.2.8

Add fullCalcOnLoad="1" to force recalc when opening file on Excel. (This does not work on Libreoffice)

3.2.7

Preserve height of rows if there is no placeholder within the cell

This avoids the following error that would occur previously:

TypeError: Cannot read property 'setAttribute' of undefined
   at XlsxModule.createFormat (docxtemplater-xlsx-module/js/index.js:513:18)

3.2.6

Declare supportedFileTypes, which allows to use this module with the new docxtemplater constructor which was introduced in docxtemplater 3.17.

3.2.5

Bugfix to use format specified in cell when converting string cell to int, instead of taking the "General" format, even when cell contains specific styles such as borders

3.2.4

Bugfix to use format specified in cell when converting string cell to int, instead of taking the "General" format

3.2.3

Bugfix when having <, >, or & in a tablecell

3.2.2

Reuse same style of existing cell as much as possible (keep borders, font-sizes, colors, …)
Handle the case of implicit numFmtId

3.2.1

Avoid corruption for multiline content in a single cell

3.2.0

Add support for having multiple conditions inside a single cell, for example :
{#cond1}foo{/cond1}{#cond2}bar{/cond2}
Keep font-style (bold, italic, …) from cell when converting to a numeric cell.
Allow nested inner loops, for example :
{#cond1}{#cond2}{text}{/}{/}

3.1.6

Bugfix when having merged cells beyond AA, the end of the merge cell was reversed (for example "AC1:AG2" would be transformed into "AC1:GA2"

3.1.5

Bugfix when having merged cells beyond AA , for example between Z1 and AA5, the document was corrupted

Bugfix when having excel file which has only formulas (no static content), the module was throwing this error

Cannot read property 'getElementsByTagName' of undefined
at XlsxModule.addXmlDocuments

Bugfix when having excel file which had a dragged formula, the module was throwing an error :

Cannot read property 'value' of undefined
at XlsxModule.postparseSimple

3.1.4

Bugfix when having multiple merge cells within multiple sheets, the renderer would produce corrupt spreadsheets.

Bugfix when having a formula which value computes to "" (an empty string), the following error was shown :

Cannot read property 'value' of undefined
at XlsxModule.postparseSimple

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