# @momsfriendlydevco/spreadsheet-templater

> Simple templates markup for spreadsheets

Latest version **1.1.10** (published 2022-03-06) · MIT license · 0 weekly downloads

## Install

```sh
npm install @momsfriendlydevco/spreadsheet-templater
pnpm add @momsfriendlydevco/spreadsheet-templater
yarn add @momsfriendlydevco/spreadsheet-templater
bun add @momsfriendlydevco/spreadsheet-templater
```

## Health

**Score 15/100 (F)** — status: abandoned.

Positive: no vulnerabilities.

Warnings: low downloads; no types; no esm support.

Negative: abandoned; low maintenance score.

## Facts

| | |
|---|---|
| Version | 1.1.10 |
| Published | 2022-03-06 |
| First published | 2019-02-04 |
| Weekly downloads | 0 |
| License | MIT |
| TypeScript types | none |
| Module format | CommonJS |
| Dependencies | 4 |
| Unpacked size | 15.8 KB |
| Known vulnerabilities | 0 |
| Install scripts | no |
| GitHub stars | 0 |
| Author | Matt Carter |
| Maintainers | hash-bang, 1watt, eekthecat, melromero, mr-yellow |
| Keywords | spreadsheet, template, templaer, data, layout |

## Links

- npm: https://www.npmjs.com/package/@momsfriendlydevco/spreadsheet-templater
- Repository: https://github.com/MomsFriendlyDevCo/spreadsheet-templater
- Homepage: https://github.com/MomsFriendlyDevCo/spreadsheet-templater#readme
- Issues: https://github.com/MomsFriendlyDevCo/spreadsheet-templater/issues
- npm.io page: https://npm.io/package/@momsfriendlydevco/spreadsheet-templater

## Dependencies (4)

- [debug](https://npm.io/package/debug.md) ^4.1.1
- [lodash](https://npm.io/package/lodash.md) ^4.17.11
- [xlsx-populate](https://npm.io/package/xlsx-populate.md) ^1.19.1
- [@momsfriendlydevco/template](https://npm.io/package/@momsfriendlydevco/template.md) ^1.1.2

## Alternatives

- [csv-to-markdown-table](https://npm.io/package/csv-to-markdown-table.md) — 47.0K weekly downloads
- [@sapphire/ratelimits](https://npm.io/package/@sapphire/ratelimits.md) — 4.4K weekly downloads
- [js-csvparser](https://npm.io/package/js-csvparser.md) — 2.0K weekly downloads
- [@adadapted/js-sdk](https://npm.io/package/@adadapted/js-sdk.md) — 251 weekly downloads
- [@grapecity/spread-sheets-sparklines](https://npm.io/package/@grapecity/spread-sheets-sparklines.md) — 103 weekly downloads

## Recent versions

- 1.1.10 (latest) — 2022-03-06
- 1.1.8 — 2019-09-05
- 1.1.7 — 2019-09-04
- 1.1.6 — 2019-07-19
- 1.1.4 — 2019-07-19
- 1.1.2 — 2019-06-11
- 1.1.1 — 2019-06-11
- 1.1.0 — 2019-06-10
- 1.0.3 — 2019-05-15
- 1.0.2 — 2019-02-05
- 1.0.1 — 2019-02-04
- 1.0.0 — 2019-02-04

## README

Spreadsheet-Templater
=====================
Simple templates markup for spreadsheets (via [XLSX](https://docs.sheetjs.com)).

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.


```javascript
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](https://github.com/dtjohnson/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](https://github.com/visionmedia/debug#readme). 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](https://lodash.com/docs#get) 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](https://github.com/MomsFriendlyDevCo/template) |
| `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

---
_Source: https://npm.io/package/@momsfriendlydevco/spreadsheet-templater · Machine-readable twin of the npm.io package page. Health data is recomputed on every publish._
