Loop horizontally when generating an XLSX file

[vdechef]

Environment

• Version of docxtemplater : 3.21.1
• Used docxtemplater-modules :
  • docxtemplater-footnote-module: 3.1.4
  • docxtemplater-html-module: 3.25.0
  • docxtemplater-html-pptxmodule: 3.2.0
  • docxtemplater-image-module: 3.8.3
  • docxtemplater-slides-module: 3.2.9
  • docxtemplater-subtemplate-module: 3.6.1
  • docxtemplater-wrun-module: 3.1.1
  • docxtemplater-xlsx-module: 3.4.1
• Runner : Browser with Vue.js

How to reproduce my problem :

I need to generate an excel file containing the list of children per school.
I want to obtain something like this in Excel:

id	Dpt	Firstname	Lastname	age	level	Firstname	Lastname	age	level
School A	31	Henri	IV	12	6ème	Louis	Le Gros	6	CP
School B	64	Marie-Antoinette	d'Autriche	10	CM2	Hubert	de Montmirail	17	Terminale

Each school has to be on 1 line, with the kids firstname, lastname, age, and level one after the other.
This file will then be imported automatically on an other application, so I cannot modify its format (I need to stick to the application import format).

My data is something like this:

const schools = [
            {
                id: "School A",
                department: "31",
                students: [
                    {
                        firstname: "Henri",
                        lastname: "IV",
                        age: 12,
                        level: "6ème"
                    },
                    {
                        firstname: "Louis",
                        lastname: "Le Gros",
                        age: 6,
                        level: "CP"
                    }
                ]
            },
            {
                id: "School B",
                department: "64",
                students: [
                    {
                        firstname: "Marie-Antoinette",
                        lastname: "d'Autriche",
                        age: 10,
                        level: "CM2"
                    },
                    {
                        firstname: "Hubert",
                        lastname: "de Monmtirail",
                        age: 17,
                        level: "Terminale"
                    }
                ]
            }
        ]

I know I can make a template like this:

{#schools}{id} | {department} | {students[0].firstname} | {students[1].firstname} | {students[2].firstname} | {/schools}

But I may have thousands of students, and some complex conditions for each cell, so it is very tedious.

Would i be possible to have a dedicated tag or pipe in XLSX module to fill cells on a row ? This would not insert columns, but fill cells, overwriting existing content if any.

[edi9999]

Hello Vincent !

I have good news, I was able to produce something that will solve your usecase.
I call it the "horizontal loop expand" feature. It has been released in version 3.5.0 of the xlsx module.

You can use it like this :

{#schools}{id} | {department} | {>students}{firstname} | {/schools}

The {>students} tag will be repeated horizontally for each student in the array, thus resulting in exactly the output that you would expect.

I set the prefix to > because it gave the idea of shifting, a bit like the bitwise shifting operators >>.

Let me know if it works well for you, I hope you will find it useful !

Best,

Edgar

[vdechef]

Hello Edgard,
Thanks for the feature. It works fine for a single field, but I still can't obtain the desired output, because I need to write multiple fields for a single student (see the table in my first comment).

I need to do something like this:
{#schools}{id} | {department} | {>students}{firstname} | {lastname} | {age} | {/schools}

[edi9999]

Hello @vdechef , I did'nt get this correctly indeed.

I would suggest to do the following :

{>students | cells:'firstname':'lastname':'age'}{.}

And add in your angular filter :

expressions.filters.cells = function cells(input, ...keys) {
	return input.reduce(function (result, element) {
		keys.forEach(function (key) {
			result.push(element[key]);
		});
		return result;
	}, [])
}

[edi9999]

You can change the name of the filter, I used the word "cells" as I thought it would be a good fit.

Maybe there is something better, maybe "multi", "multiplier", "star", ...

[vdechef]

Thanks, it worked with the filter you suggested.

{>students | explode:'firstname':'lastname':'age'}{.}

[edi9999]

I really like explode, thanks for sharing !

[vdechef]

Do you have a suggestion about how to add a condition inside the horizontal loop ?
I have a list of 10 students on which I want to iterate, but 2 of them must not be displayed in the resulting excel (based on their 'age' field). I tried with something like {>students}{#age > 3}{name}{/} but I get empty cells for the 2 student that are under 3.

[edi9999]

You could do :

{>students | where:'age>3'}{name}

And define the where filter like this :

const expressions = require("angular-expressions");
expressions.filters.where = function (input, query) {
	return input.filter(function (item) {
		return expressions.compile(query)(item);
	})
}

I never had this idea before of reusing angular-expressions inside a where statement, it can be very powerful.

[vdechef]

Thanks for the tip : this where filter is very useful in many places ! (though I wonder if it is really injection-safe ... I guess we need to trust Google engineers on this one).

BTW, I stumbled upon a strange corner-case when using horizontal loops :

• if you add borders on the cell containing the horizontal loop, the xlsx is generated correctly
• if you add borders on the cell containing the horizontal loop and on the following cell, the generated xlsx is corrupted

[edi9999]

Hello @vdechef , could you send me a document with this issue ?
I could probably reproduce this issue myself, but I like to have the real document causing the issue, sometimes there are multiple things that together will trigger the bug, and I sometimes do not fix the bug completely without the real document.

[vdechef]

The problem happens only when opening with Microsoft Word (not with LibreOffice).

My configuration:

"docxtemplater": "^3.22.1",
"docxtemplater-footnote-module": "footnote-module-3.2.2.tar.gz",
"docxtemplater-html-module": " html-module-3.26.0.tar.gz",
"docxtemplater-html-pptx-module": " html-pptx-module-3.2.1.tar.gz",
"docxtemplater-image-module": " image-module-3.8.8.tar.gz",
"docxtemplater-slides-module": " slides-module-3.3.2.tar.gz",
"docxtemplater-subtemplate-module": " subtemplate-module-3.6.3.tar.gz",
"docxtemplater-wrun-module": " word-run-module-3.1.1.tar.gz",
"docxtemplater-xlsx-module": " xlsx-module-3.5.2.tar.gz",

I changed nothing to the sample.js in XlsxModule, and used the following templates :

• demo_template_OK.xlsx
• demo_template_KO.xlsx

Here are the resulting files :

• demo_generated_OK.xlsx
• demo_generated_KO.xlsx

[edi9999]

Hello @vdechef ,

This is now fixed in version 3.6.1 of the XLSX module !