Love the idea and implementation, one thing I ran into is that the columns widths need to be adjusted to match the strings inserted in a table or array. I have not yet found a proper way to do that.

The only thing that I came up with is that in the worksheets xml, there can be cols and col elements that define a custom with for the column. e.g:

<cols>
    <col min="6" max="6" width="27.7109375" bestFit="1" customWidth="1"/>
</cols>

The value is a number of "characters", which is basically the with of a number. For non-propertional fonts, this may be something else than the real with of a string, but it's a first approximation. I could live with counting the number of characters and adding a few for safety and hoping that it's not all W's

Does anyone know of an "auto width" setting that would remove the need to recalculate the column width needed? I'd rather just set this in the template than having to adjust it in code.

Note that I am not really asking for a solution in xlsx-template, just a way to get it working, as I can imagine more people will have to deal with this problem here. I am ok with having to write some code to further manipulate the worksheet xml

When I tried this at first, I always received corrupt files. I opened it with a text editor, and saw a lot more XML than I'd expect in a zip.

The reason was that I used fs.writeFile(fileName, data) instead of fs.writeFile(fileName, data, 'binary').

It might be worth mentioning this in the docs

I'd like to propose adding scopes to xlsx-template. This would introduce a lot more flexibility, like deeper and repeated structures. I think one way to do this is by using named ranges. Here, I'd like to discuss it a bit before taking a stab at implementing it.

I like to hear your opinions, objections, use cases and questions.

scopes by named ranges

If we have a json structure like so:

{
    scalar: 'foo',
    array: [1, 2, 3],
    table: [
        {row: 1, value: 'a'},
        {row: 2, value: 'b'},
        {row: 3, value: 'c'}
    ],
    deeper: {
        scalar: 'foo',
        array: [1, 2, 3],
        table: [
            {row: 1, value: 'a'},
            {row: 2, value: 'b'},
            {row: 3, value: 'c'}
        ],
    },
    nested: {
        deeper: {
            scalar: 'foo',
            array: [1, 2, 3],
            table: [
                {row: 1, value: 'a'},
                {row: 2, value: 'b'},
                {row: 3, value: 'c'}
            ],
        },
        table: [
            {
                name: 'lowercase',
                data: [
                    {row: 1, value: 'a'},
                    {row: 2, value: 'b'},
                    {row: 3, value: 'c'}
                ]
            }, {
                name: 'uppercase',
                data: [
                    {row: 1, value: 'A'},
                    {row: 2, value: 'B'},
                    {row: 3, value: 'C'}
                ]
            }
        ]
    }
}

We can already handle the scalar, the array and the table. Displaying the array vertically is currently not possible, but that is easily fixed by using {$table:array} and a small amendment.

The deeper.scalar value can also be used, also, I think deeper.array can be fixed, but handling deeper.table is not trivial, as we would not know where to split the path. However, this may also be fixed by descending into an object as long as possible and use the rest of the path to descend in the array of objects.

Multiple tables is not currently possible.

A named range in excel can contain dots, which is convenient. This would allow us to define a scope for a range of cells. For example, if we have a range named

\deeper

And a cell within that range with the value

${scalar}

We can piece together a working substitution. This also goes for ${array} and ${table} within \deeper. This would also work if we name the range \nested.deeper

If a scope resolves to an array, like \nested.table, we can repeat the whole range and apply the template within to every structure in the array. This makes excel templates very flexible.

Use cases

simpler tables

Although not directly needed, a table can be made simpler. Rather than having a cell with ${table:table.row} we can now use a range \table, resolving a scope to the table array. As such, the whole range structure would be repeated vertically. The cells within can now be simple scalars like ${row}. Moreover, as they are now treated as scalars, you could use them in a string: row ${row} has value ${value}

tables from deeper structures

From the above example, using deeper.table is just as easy, by renaming the range to \deeper.table

repeating tables

Suppose we have a template like this:

This would result in two tables:

Caveats

One problem is that names must be unique. This may be mitigated by smart naming in the json structure, and by avoiding single step scopes (like \data above). It is still a problem I have no solution for though

How would we prevent accidental naming clashes with user defined ranges? I suppose by prepending the \ character, chances of collision are rather small, but still...

Other thoughts

Excel also allows for an underscore at the start of a range name. We may create two variants, one that repeats vertically (\nested.table), one that repeats horizontally: _nested.table. The latter would result in this:

A way to avoid using named ranges (and the uniqueness problem) is to just put the data in a cell, for example like so:

$${B8:C11:\nested.table}

And then just delete the contents of such cells. (we do have some more flexibility for the syntax then). Also, we may have a special meta range that span some rows that contain meta info for the template like the above and have that entire set of rows removed on substitution. For example like this:

Next steps

Do you see any other problems? Or do you have interesting use cases to add that may or may not be solved with this proposal? Would there be another way in excel to define a scope on a range that does not have the uniqueness problem?

Would xlsx-template be the right codebase to put this in or should I create something else entirely? I do have a need for repeated tables and I like the way xlsx-templates are super-maintainable. I also feel I know enough of xml, js and excel to implement this.

Is there a way to specify a formula for a cell explicitly? I have a value of something like this in my data:
"formula": "=SUM(B12:B26)"

It correctly puts it in the generated spreadsheet as a formula value, but it doesn't actually trigger calculating the value until I go into the cell and hit the enter key.

Hello!
I use v0.4.0
I tried to CopySheet
In particular:

JSZipUtils.getBinaryContent('/template.xlsx', (error: Error | undefined, content: string) => {
        if (error) {
                throw error;
        }
        const template = new XlsxTemplate(content);
        template.copySheet('BaseSheet', 'LookAtMe1');
        template.substitute('BaseSheet', params);
        template.copySheet('BaseSheet', 'LookAtMe2');
        const data = template.generate({ type: 'arraybuffer' });
        ...
});

However, result is sad

1. The copied sheets ('LookAtMe1', 'LookAtMe2') are both blank
2. First sheet ('BaseSheet') loses its design (column widths change, cell borders become visible, background color is lost, and so on)

Please tell me how to insert columns (month_1, ..., month_N) from an array if their number is initially unknown?
The number of columns I can have from 1 to N, as well as rows in each column
My array looks like this:

marray: [
		0: {
			month_1: {
					name: [
						count: 1,
						price: 10,
						sum: 10
					],
					name_1: [
						count: 3,
						price: 12,
						sum: 36
					],
					name_2: [
						count: 2,
						price: 8,
						sum: 16
					],
					...: [
					],
					name_N: [
						count: 1,
						price: 15,
						sum: 15
					]
			}
		}
		1: {
			month_2: {
					name: [
						count: 2,
						price: 6,
						sum: 12
					],
					name_1: [
						count: 1,
						price: 13,
						sum: 13
					],
					name_2: [
						count: 4,
						price: 10,
						sum: 40
					],
					...: [
					],
					name_N: [
						count: 3,
						price: 11,
						sum: 33
					]
			}
		},
		2: {
			...: {
					...
			}
		},
		...: {
			...: {
			}
		},
		N: {
			month_N: {
					name: [
						count: 1,
						price: 7,
						sum: 7
					],
					name_1: [
						count: 2,
						price: 15,
						sum: 30
					],
					name_2: [
						count: 2,
						price: 8,
						sum: 16
					],
					...: [
					],
					name_N: [
						count: 5,
						price: 6,
						sum: 30
					]
			}
		}
			
		]

template.xlsx

as a result, the data file should be obtained by example 'template.xlsx'

It would be nice if I could pass in an array and simply do ${foo[0]} in the template. Right now, for my use case, I have to do this in my code:

const templateData = {
   foo0: foo[0],
   foo1: foo[1],
   ...
}

Hi,

How to download file from server.....and.....how to retrieve in the frontend....

  // Get binary data backend
      var data = template.generate();
      res.setHeader('Content-Type', 'application/vnd.openxmlformats');
        res.setHeader("Content-Disposition", "attachment; filename=" + "Report.xlsx");
        res.end(data, 'binary');

//Frontend...
Get ajax?

hi.

it would be nice have also the typing definitions of this library in order to use it seamlessy within a typescript project.

I love this project! Is there a way to prevent having "undefined" substituted for a missing value? I would like to generate a report where some of the columns may have data, and others it may not apply so they should be empty.

I have just tested this module and it seems to be working great. The only issue I have found that is not working is when I use ${someVariable} in the header or footer of an XLSX file, all that appears is is the variable name ... ${someVariable}. should the substitution be working in headers and footers? If not, I would like to request that as a feature / enhancement if that is possible.

 var values = {
                     people: [
                          {
                             vendorType: 'Decorator', name: "", age: 0, city: '',
                             data: [{ vendorType: '', name: "Bob Johnson", age: 22, city: 'mumbai' },
                             { vendorType: '', name: "Bob Johnson", age: 22, city: 'mumbai' },
                             { vendorType: '', name: "Bob Johnson", age: 22, city: 'mumbai' },
                             ] },
                         {
                             vendorType: 'Decorator', name: "", age: 0, city: '',
                             data: [{ vendorType: '', name: "Bob Johnson", age: 22, city: 'mumbai' },
                             { vendorType: '', name: "Bob Johnson", age: 22, city: 'mumbai' },
                             { vendorType: '', name: "Bob Johnson", age: 22, city: 'mumbai' },
                             { vendorType: '', name: "Bob Johnson", age: 22, city: 'mumbai' },
                             ]},
                         {
                             vendorType: 'Decorator', name: "", age: 0, city: '',
                             data: [{ vendorType: '', name: "Bob Johnson", age: 22, city: 'mumbai' },
                             { vendorType: '', name: "Bob Johnson", age: 22, city: 'mumbai' },
                             { vendorType: '', name: "Bob Johnson", age: 22, city: 'mumbai' },
                             ]},
                         { 
                            vendorType:'Decorator',name: "", age:0,city:'',
                     data: [{vendorType:'',name: "Bob Johnson", age: 22,city:'mumbai'},
                           {vendorType:'',name: "Bob Johnson", age: 22,city:'mumbai'},
                           {vendorType:'',name: "Bob Johnson", age: 22,city:'mumbai'},
                    
                     
                           ]}
                            ],
                 };

please help me

Can i write image in xlsx template?

how to export the generated blob data using express?

Hi,
as per issues #1 and #2, this library had some problems in the past when used in Windows. My question is: were those problems solved? and, if they were, does the last version on npm, 0.0.3, contain this correction?

I'm doing a very simple sample but with no success. Excel always refuses to open the resulting file saying that the file is damaged and can't be opened.

I really need this library and it will help me a lot in developing my project; it is just I haven't been able to have it working (in Windows). Many thanks and bye ...

Looks like a good idea especially if template preserves formatting so Pivots etc still work when data swapped out.

have any example repo?

Dear Team,

I am populating xlsx from an array of objects, it works perfect. And I also define a cell validation drop down list too as the last column of the XSLS template.
After the generation, only the 1st row has the drop down list in place, but not the subsequent row.... any hack I can try?

Thank you very much.

Is it possible to merge cells?

I was trying to create a file only in memory and download it, and it took me hours to figure it out, i couldn't find anything even in Stack Overflow and lots of programming blogs elsewhere, but when "give up" is not an option... you get there.
i'm suggesting to you guys to just improve a little more the documentation
and thanks a lot for your work, it saved my day.

the following lines of code worked fine on the framework i'm using for nodejs

var fs = require("fs");
var path = require("path");
var XlsxTemplate = require('xlsx-template');

//...

try{
/* use only fs.readFileSync */
let xlsxTemplate = new XlsxTemplate(fs.readFileSync(path.resolve(__dirname, file_path))) // <-
let sheetNumber = 1;
let values = {
data: some_data,
arr: some_array
}
xlsxTemplate.substitute(sheetNumber, values);
// use only "nodebuffer"
let encode = { type: 'nodebuffer' };
let buffer = xlsxTemplate.generate(encode)

//expected return in the framework
return  [
    buffer,
   "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
];

}catch(error){
let e = {
message: error.message,
name: error.name,
stack: error.stack,
properties: error.properties
};
console.log(JSON.stringify({ error: e }));
throw error;
}

Hi, I just made some tests with this library, and it seems pretty good, but I'm having issues generating excel files that are huge in rows (500k to 1m rows).

I've tried enabling compression, but Excel does not open the resulting file, saying it has an invalid extension.

The lines I've used:

    var newData = t.generate({
        compression: "DEFLATE",
        compressionOptions: {
            level: 3
        }
    });

I've tried to just let compression: "DEFLATE" as well, no sucess.

hi,

if you print a string containing an escaped unicode character (e.g. \u0010), the generated excel is corrupted. atm i will strip any unicode character in order to make it working but it would be nice have utf-8 support or automatic stripping of unicode chars.

t.substitute(1, {
    titles: ['title1', 'title2'],
    planData: [
        { name: 'A', role: ['role1'] },
        { name: 'B', role: ['role2'] }
    ]
});

With a input like this

Would result a file which excel would complain upon opening.
This is due to duplicate element inside sheet1.xml.

    <row ht="14" r="2" spans="1:7" x14ac:dyDescent="0.2">
      <c r="A2" t="s">
        <v>6</v>
      </c>
      <c r="B2" s="6" t="s">
        <v>8</v>
      </c>
      <c r="B2" s="6" t="s">
        <v>3</v>
      </c>
    </row>

Using an object to substitute placeholders in table header names results in an Excel file that needs to be repaired while the replacement is done. Templates are joint.

This works fine:

// Load an XLSX file into memory
fs.readFile('./template2.xlsx', function (err, data) {

    // Create a template
    var template = new XlsxTemplate(data);

    // Replacements take place on first sheet
    var sheetNumber = 1;

    // Set up some placeholder values matching the placeholders in the template
    var values = {
        columnName: 'Will have to be repaired'
    };

    // Perform substitution
    template.substitute(sheetNumber, values);

    // Get binary data
    var fileData = template.generate();

    fs.writeFileSync('./output2.xlsx', fileData, 'binary');
});

This doesn't:

// Load an XLSX file into memory
fs.readFile('./template1.xlsx', function (err, data) {

    // Create a template
    var template = new XlsxTemplate(data);

    // Replacements take place on first sheet
    var sheetNumber = 1;

    // Set up some placeholder values matching the placeholders in the template
    var values = {
        column: { name: 'Will have to be repaired'}
    };

    // Perform substitution
    template.substitute(sheetNumber, values);

    // Get binary data
    var fileData = template.generate();

    fs.writeFileSync('./output1.xlsx', fileData, 'binary');
});

template1.xlsx
template2.xlsx

my english no good,so pls try understand my word.

I learn a nodejs , build a project,I need a module export xlsx file, and I find xlsx-template ,
but something dont work and I dont work , and I cant fix,
so I find your contact , write a email , hope you can help me, thx!

the error msg:

.join(self.prefix, '_rels', path.basename(workbookPath) + '.rels')).asText()).
                                                                    ^
TypeError: Cannot call method 'asText' of null
    at module.exports.Workbook.loadTemplate (E:\WEB\model-chooese\node_modules\x
lsx-template\lib\index.js:50:131)
    at new module.exports.Workbook (E:\WEB\model-chooese\node_modules\xlsx-templ
ate\lib\index.js:27:18)
    at E:\WEB\model-chooese\app.js:56:20
    at fs.js:266:14
    at Object.oncomplete (fs.js:107:15)

and my code like this:

app.get('/api/save/:mark',function(req,res){
  var mark = req.params.mark;
  var file = __dirname+'\\public\\'+mark+'.xlsx';
  console.log(file);
  fs.readFile(file, function(err, data) {

    // Create a template
    console.log(data);
    var template = new XlsxTemplate(data);          //here is line 56
    console.log(template);

    // Replacements take place on first sheet
    var sheetNumber = 1;

    // Set up some placeholder values matching the placeholders in the template
    var values = {
      extractDate: new Date(),
      dates: [new Date("2013-06-01"),
            new Date("2013-06-02"),
            new Date("2013-06-03")
      ],
      people: [{
        name: "John Smith",
        age: 20
      }, {
        name: "Bob Johnson",
        age: 22
      }]
    };

    // Perform substitution
    template.substitute(sheetNumber, values);

    // Get binary data
    var data = template.generate();
    console.log(data);
    // ...

    // here , I wanna download this file , how can I do? what is  template.generate() return object?
  });


  //res.download(__dirname+'/public/'+mark+'.xls',mark+'.xls');
})

@optilude if you don't have time anymore to keep an eye on this project, perhaps you could appoint some collaborers to keep the project alive.

I believe it's awesome and solves a functionality that every other XLSX manipulation library is missing right now.

Currently, I've had to resort to @woodenfish fork to adress the issue of appending merged cells to a table, which is the only showstopper for me to use the main branch.

It would be neat if that and the other PRs could be merged sometime soon.

Is it possible to define the scalar as an Excel formula? For example, I defined the JSON { a: '=concatenate("A1")' } and put the placeholder "${a}" in Excel template.

I have a ${page} which outputs page number for every sheet.
Problem is every sheet ${page} would be the same.

In the subsititueScalar, It replace String for sharedStrings.

// Perform substitution of a single value
    Workbook.prototype.substituteScalar = function(cell, string, placeholder, substitution) {
        var self = this;

        if(placeholder.full && typeof(substitution) === "string") {
            self.replaceString(string, substitution);
        }

        ...

    };

So it would not substitute again because the string in sharedStrings is already replaced at first time.

When I comment replaceString in function substituteScalar, this problem is fixed.

Any Idea?

Thanks.

Hello,
I'm using the v0.4.0
The copySheet did not copy the charts!

It would be great also to specify some basic styling for a content.

There similar project tha run with angular or with browser js?

Thank you

Situation: I need to show one or multiple data entries in an xls.
When I have 2 or more entries, everything is perfect, xls gets rendered with the correct headers in the columns. When I have 1 entry, only the headers are generated in the xls.

Current workarround: When the array.length == 1, add an empty entry to the array. In the xls only one entry is shown because the second entry is empty and we can't really check if it's there.
if (filteredEvents.length == 1) { this.addEmptyEvent(filteredEvents); }

Please help.

Can i set the Column Header to be dynamic?

not sure if this is a restriction of both excel and google sheets needing to have a special event action to mark a cell as being a formulae, but I hit this snag:

In my sheet template, I have a cell with this value: ${table:fills.usdChange}

in the values I pass in to substitute, I have an array of fills with an object keypair like this:

...
fills: [{
...
usdChange: '=(E4*D4)+F4'
}]
...

when I execute and open the sheet in excel or google spreadsheets, the value of the repeated columns is '=(E4*D4)+F4' instead of a formulae executing that function.

Is there another way I should be tagging the spreadsheet or the fill value in order to id this as a formulae?

Hi,

Is posible write the values into multiples sheets of a workbook........?

Thanks.

When trying to add a table with multiple rows I get this error:
TypeError: Cannot read property 'attrib' of null

traced down to this:

dimension = sheet.root.find("dimension")

dimension turns out to be null

TypeError: Cannot read property 'attrib' of null
    at Workbook.module.exports.Workbook.substitute (.../node_modules/xlsx-template/lib/index.js:248:59)

Hi

When I use template.copySheet or template.deleteSheet and open generated xlsx file with MS Excel, it opens with all-selected sheets:

That means that if you don't choose another sheet, all sheets will change with current (if you in Excel set cell A1 = asd in current sheet, all A1 cells in another sheets well be set "asd")

Can you please fix it? I think this is because of template._rebuild method, which is calling after these methods.

Thx

We set up a simple XLSX with two variables and then a cell that adds those two cells together. When we substitute the two variables and save the new file, the 3 third that has the formula didn't re-calculate.

Hi,

First, thanks for the project, it's great :)

I'm trying to substitute a placeholder to put an array, but i've been in trouble when inserting a 1 cell array. Here is an exemple :
My excel template is just : | ${table:sales.payments} |
My code :

  var tpl = fs.readFileSync('tpl.xlsx');
  tpl = new xls(tpl);
  tpl.substitute(1, data);
  const result = tpl.generate();

The strange thing is, with data = { "sales": [ { "payments": [123,456], } ] };, it works perfectly, but with data = { "sales": [ { "payments": [666], } ] };, it does not replace the placeholder (${table:sales.payments} is written in the output file).

It also works for data = { "sales": [ { "payments": 123, } ] };

Is this a normal behaviour ? It's pretty annoying to have to check the length of my arrays and convert them when their length is 1...

Thanks !

In excel templates I define some formulas =sum(C10) but on the final generated file the formulas are equal to zero. After clicking the cell where formula was defined the value appears.
I have checked 'on' the option formula auto-calculation in my Excel and still it is not helping. I have to click all cells manually to trigger the calculations.

I'm creating a table template with date column. After substitution is done and I open result in Excel I get the message saying that the file is corrupt and Excel proposes to try repairing this file.

When I investigated this issue for a while i noticed that Excel does not add "t" attribute for non-text cells (tested with numbers, dates, currency, percentage and general format) at all. I have a fix for this and am going to sign off a PR soon. Would be great if someone could test it in different version of Excel and also on Windows.

When I write tables, if ${table:people.name} is put in a merged cell, after calling template.substitute(), people.name is correctly placed in the merged cell only for the first row. For the following rows, people.name is placed in the 1st cell of the merged cell.

Any hint to solve that?

Hello,

First of all, this is super helpful library. Thank you for your support. Anyway, I am wondering that how can I do dynamic headers(nested dynamic headers)?

For instance,

var values = {
    items: [{
    name: 'Drink',
    count: 3,
    brand: 'Pepsi'
  }, {
    name: 'Food',
    count: 2,
    brand: 'KFC'
  }, {
    name: 'Desert',
    count: 5,
    brand: 'CheeseCakeFactory'
 }]
}

and I want to print this array in this format:

Drink
count 3
brand Pepsi

Food
count 2
brand KFC

Desert
count 5,
brand CheesecakeFactory

I thought this way, but it did not work out:

${table:items.name}
${table:items.count}
${table:items.brand}

but it prints

Drink
Food
Desert
3
2
5
Pepsi
KFC
CheesecakeFactory

if this lib supports this feature, please let me know how can I do that? Thanks a lot for any kind of help!

I am using using the mssql connector in node and have the stream option enabled. The reason being the record set getting returned is big (more than 60K) and I want the whole process of generating the excel consume less memory on the server and create the excel a little faster.

Now when calling the template.substitute function it basically removes the placeholders and then all the rows that are returned from the stream are not populated in the final excel output. I was wondering if there is a function which will allow to do so.
Until then the workaround that I have is keep the adding the placeholder back in the excel until the last row is retrieved.

lmk if you need more info.

Hi @optilude ,
This is a useful package.
I'm very happy to use it, but I want to add an image into the template, so I have to use another package so this does not interest me, so I hope to support more images.
Please help me!
Thanks for the support!

In template cells is combine, but when report is generate only first row has adjusted styles. see screenshot.

Thanks!

    var values = {
        people: [
            {
                name: 'John Smith',
                age: 20,
                test: { name: 'Smith' },
            },
            {
                name: 'Bob Johnson',
                age: 22,
                test: { name: 'Johnson' },
            },
        ],
        people: {
            users: [
                {
                    name: 'John Smith User',
                    age: 20,
                    test: { name: 'Smith' },
                },
                {
                    name: 'Bob Johnson User',
                    age: 22,
                    test: { name: 'Johnson' },
                },
            ],
            admins: [
                {
                    name: 'John Smith Admin',
                    age: 20,
                    test: { name: 'Smith' },
                },
                {
                    name: 'Bob Johnson Admin',
                    age: 22,
                    test: { name: 'Johnson' },
                },
            ],
        },
    };

So for example I have this object values that I want to put into template.

${table:people.name} - this placeholder works
${table:peopleGroup.users.name} - this does not work, in result value is blank

I tried to download from an angularjs application an excel file (xslx) generated thorough a nodejs application with this library. The data is sent in binary form; I don't save the file on the server, but directly send the stream of data in the express response.

This is the server-side snippet code

var template = new XlsxTemplate(data);
var sheetNumber = 1;
var values = {};
template.substitute(sheetNumber, values);
var blobExport = template.generate();
return res.send(new Buffer(blobExport, 'binary'));

and this is the client-side snippet code

var contentType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
var blob = new Blob([blobData], {type: contentType});
var urlObj = (window.URL || window.webkitURL);
var url = urlObj.createObjectURL(blob);
var link = document.createElement('a');
link.href = url;
link.download = filename;
link.click();

the file is getting downloaded but it seems to be corrupted. Is this a bug or is there a workaround to it?

I put an empty string, but Excel does not treat empty string as null value. Any possible way to put exact null value in placeholder?

That would be awesome!