@momsfriendlydevco/spreadsheet-templater v1.1.10
Spreadsheet-Templater
Simple templates markup for spreadsheets (via XLSX).
This plugin allows a spreadsheet to use handlebars-like notation to replace cell contents which enables an input spreadsheet to act as a template for incoming data.
var SpreadsheetTemplater = require('@momsfriendlydevco/spreadsheet-templater');
new SpreadsheetTemplater()
.read('input.xlsx')
.data({...})
.apply()
.write('output.xlsx')See the test directory for some example spreadsheets.
Limitations
There are a few restrictions with this module, mainly due to time and technical limitations:
- Nested
{{#each}}+{{/each}}statements are not supported - Due to no support for dynamic row adding, at the time of writing, in the upstream xlsx-populate library this module will overwrite all rows below the
{{each}}blocks with however many rows of data need placing - the spreadsheet contents below the{{each}}blocks will not be moved down
Debugging
This module uses the Debug NPM. To enable simply set the DEBUG environment variable to include spreadsheet-templater
Markup
This module reads all cells in all sheets and applies simple substitutions based on a Handlebars like template based on an input data set.
Simple substitution
Simple substitution is performed by putting a lodash compatible dotted notation path inside double braces.
For example {{people.0.name}} - extracts from the data object the key people, the first element of the array and the subkey name.
Repeaters
Basic support is provided for single level repeaters. Repeaters start ({{#each ITERABLE}}) in the first cell and are read horizontally until the end is encountered ({{/each}}).
For example assuming the following CSV spreadsheet layout:
Name,Email,Phone,Address
{{#each people}}{{name}},{{email}},{{phone}},"{{address.street}}, {{address.city}}, {{address.zipcode}}{{/each}}"The spreadsheet would be populated with all items in the people collection until exhausted.
If no specific data path is specified for each (i.e. {{#each}}) the main data object is assumed to be an array and it is used instead.
API
The module exposes a single object.
This module supports the following options:
| Option | Type | Default | Description |
|---|---|---|---|
re | Object | The regular expressions used when detecting markup | |
re.expression | RegExp | /{{(.+?)}}/ | RegExp to detect a single expression replacement |
re.repeatStart | RegExp | /{{#?\s*each\s+(.+?)}}/g | How to identify the start of a repeater |
re.repeatEnd | RegExp | /{{\/each.*?}}/ | How to identify the end of a repeater |
repeaterSilentOnError | Boolean | false | Whether the module should throw when a non-array path is provided to a repeater |
template | Object | Options to control templates | |
template.path | String | The source file to process the template from | |
data | Object | {} | The data object used when marking up the template output |
defaultValue | Any | '' | The value used when no corresponding simple dotted path can be located |
templateDetect | Function | See code | How to determine if a cell needs to be templated |
templatePreProcess | Array | [] | Array of functions that can mutate a cell template before processing |
templateSettings | Object | See code | Settings passed to the templating NPM |
dateDetect | RegEx | See code | RegEx for detecting date output before formatting |
dateFormat | String | "dd/mm/yyyy" | The date format to use when dateDetect succeeds |
Constructor(options)
Setup the initial object with options.
set(key, val)
Set a single or multiple options (if key is an object). Lodash array and dotted notation is supported for the key.
read(path)
Parse the input template file. This function is automatically called if constructor is given a filename when initialized.
apply(data)
Apply the given data (or the data specified in options.data) to the loaded template.
data(data)
Alternate way to set template data.
json()
Convenience function to return the workbook as a JSON object This will return an object with each key as the sheet ID and a 2D array of cells