@momsfriendlydevco/spreadsheet-templater

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