Security News
The Unpaid Backbone of Open Source: Solo Maintainers Face Increasing Security Demands
Solo open source maintainers face burnout and security challenges, with 60% unpaid and 60% considering quitting.
xlsx-template-matriz
Advanced tools
based on https://www.npmjs.com/package/xlsx-template
This module provides a means of generating "real" Excel reports (i.e. not CSV files) in NodeJS applications.
The basic principle is this: You create a template in Excel. This can be formatted as you wish, contain formulae etc. In this file, you put placeholders using a specific syntax (see below). In code, you build a map of placeholders to values and then load the template, substitute the placeholders for the relevant values, and generate a new .xlsx file that you can then serve to the user.
Placeholders are inserted in cells in a spreadsheet. It does not matter how those cells are formatted, so e.g. it is OK to insert a placeholder (which is text content) into a cell formatted as a number or currecy or date, if you expect the placeholder to resolve to a number or currency or date.
Simple placholders take the format ${name}
. Here, name
is the name of a
key in the placeholders map. The value of this placholder here should be a
scalar, i.e. not an array or object. The placeholder may appear on its own in a
cell, or as part of a text string. For example:
| Extracted on: | ${extractDate} |
might result in (depending on date formatting in the second cell):
| Extracted on: | Jun-01-2013 |
Here, extractDate
may be a date and the second cell may be formatted as a
number.
Inside scalars there possibility to use array indexers. For example:
Given data
var template = { extractDates: ["Jun-01-2113", "Jun-01-2013" ]}
which will be applied to following template
| Extracted on: | ${extractDates[0]} |
will results in the
| Extracted on: | Jun-01-2113 |
You can use arrays as placeholder values to indicate that the placeholder cell
is to be replicated across columns. In this case, the placeholder cannot appear
inside a text string - it must be the only thing in its cell. For example,
if the placehodler value dates
is an array of dates:
| ${dates} |
might result in:
| Jun-01-2013 | Jun-02-2013 | Jun-03-2013 |
Finally, you can build tables made up of multiple rows. In this case, each
placeholder should be prefixed by table:
and contain both the name of the
placeholder variable (a list of objects) and a key (in each object in the list).
For example:
| Name | Age |
| ${table:people.name} | ${table:people.age} |
If the replacement value under people
is an array of objects, and each of
those objects have keys name
and age
, you may end up with something like:
| Name | Age |
| John Smith | 20 |
| Bob Johnson | 22 |
If a particular value is an array, then it will be repeated across columns as above.
To make this magic happen, you need some code like this:
var XlsxTemplate = require('xlsx-template-matriz');
// Load an XLSX file into memory
fs.readFile(path.join(__dirname, 'templates', '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 = {
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();
// ...
});
At this stage, data
is a string blob representing the compressed archive that
is the .xlsx
file (that's right, a .xlsx
file is a zip file of XML files,
if you didn't know). You can send this back to a client, store it to disk,
attach it to an email or do whatever you want with it.
You can pass options to generate()
to set a different return type. use
{type: 'uint8array'}
to generate a Uint8Array
, arraybuffer
, blob
,
nodebuffer
to generate an ArrayBuffer
, Blob
or nodebuffer
, or
base64
to generate a base64-encoded string.
Also, you can build composite tables of multiple rows. In this case, each the placeholder
must have the prefix matriz:
and contain only the name of Placeholder
variable (a list of objects).
For example:
| ${matriz:headers} | |
| ${matriz:people} | |
If the replacement value under people
is an array of arrays, and each of
those arrays have the information the a person, may end up with something like:
| Name | Age |
| John Smith | 20 |
| Bob Johnson | 22 |
If a particular value is an array, then it will be repeated across columns as above.
To make this magic happen, you need some code like this:
var XlsxTemplate = require('xlsx-template-matriz');
// Load an XLSX file into memory
fs.readFile(path.join(__dirname, 'templates', '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 = {
extractDate: new Date(),
headers: [ "Name", "Age" ],
people: [
["John Smith", 20],
["Bob Johnson", 22]
]
};
// Perform substitution
template.substitute(sheetNumber, values);
// Get binary data
var data = template.generate();
// ...
});
At this stage, data
is a string blob representing the compressed archive that
is the .xlsx
file (that's right, a .xlsx
file is a zip file of XML files,
if you didn't know). You can send this back to a client, store it to disk,
attach it to an email or do whatever you want with it.
You can pass options to generate()
to set a different return type. use
{type: 'uint8array'}
to generate a Uint8Array
, arraybuffer
, blob
,
nodebuffer
to generate an ArrayBuffer
, Blob
or nodebuffer
, or
base64
to generate a base64-encoded string.
.xlsx
format. .xls
, .xlsb
or .xlsm
won't work.TableName[ColumnName]
in your formula to refer to all values in a given
column in the table as a logical range.options
to generate()
, which are passed to JSZipMerged pending pull requests
Merged a number of overdue pull requests, including:
FAQs
Generate .xlsx (Excel) files from templates built in Excel
The npm package xlsx-template-matriz receives a total of 0 weekly downloads. As such, xlsx-template-matriz popularity was classified as not popular.
We found that xlsx-template-matriz demonstrated a not healthy version release cadence and project activity because the last version was released a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
Solo open source maintainers face burnout and security challenges, with 60% unpaid and 60% considering quitting.
Security News
License exceptions modify the terms of open source licenses, impacting how software can be used, modified, and distributed. Developers should be aware of the legal implications of these exceptions.
Security News
A developer is accusing Tencent of violating the GPL by modifying a Python utility and changing its license to BSD, highlighting the importance of copyleft compliance.