Xlsx smart templates DataFill

The library takes an existing .xlsx file and _populates_ the extracted data from provided JSON, into the designated cells, which we call _templates_. If the extracted data is an array - it is expanded and occupies as many cells, as the size of the data. In both dimensions.

Each template follows a specific format and structure, and it defines these crucial aspects:

* Where is the data coming from?
* How is the data extracted?
* How are the values for the cells extracted from the data?
* How are the cell styles tweaked?

All these in the context of the provided JavaScript object. If you've alrady know the story - check the API.

Few examples that explain it all

Consider the following JSON:

``json
{
"title": "An exemplary title",
"rows": [
{
"header": "Row 1",
"data": [11, 12, 13, 14, 15]
}, {
"header": "Row 2",
"data": [21, 22, 23, 24, 25]
}, {
"header": "Row 3",
"data": [31, 32, 33, 34, 35]
}
]
}
`

A cell with the following content: {{ | | title }} will be expanded into An exemplary title after the data is filled. More information on the template format will be given in a second.

Consider this cell content: {{ | rows | header }}. It’ll be expanded into three cells, in a column. Like this:

| | A |
|--------|--------|
| 1 | Row 1 |
| 2 | Row 2 |
| 3 | Row 3 |

The template defines that the _data_ should be extracted from rows - which results in an array of 3 objects, and the _values_ for the target cells - 3, of course - should be extracted using the header _path_ inside each of the entries in the extracted _data_.

So far, the data was extracted from the root of the initially provided JavaScript object. However, each template can refer another one, taking the data extracted from it, as a basis for its own processing.

Nested blocks. Consider this spreadsheet:

| | A | B | C | D | E | F |
| ---- | ----------------------- | ----------------------- | ---- | ---- | ---- | ---- |
| 1 | {{ \| rows \| header }} | {{ A1 \| 1 * data \| }} | | | | |
| 2 | | | | | | |
| 3 | | | | | | |

The template in A1 is clear - it expands into the range A1:A3 as expected. The template in B1, however, introduces two new, interesting aspects. First, it _refers_ another template - the one in A1, and second - it gives strange notion of how the data is extracted - 1 * data, instead of just data.

The second one is simple - since the general data extraction form is and, for example rows (in A1) is a shorthand for rows 1, so the 1 * data instructs the engine to expand the retrieved data horizontally (i.e. in many columns, but a single row), rather than vertically.

The first one - the reference - means that the B1 will _not_ extract the data directly from the provided JS object, but rather - _from the data already extracted from the referred template_. And this happens for _each data entry_ extracted from the referred template.

Replaying in "slow-mo", the whole data extract & placement process will look like:

1. The engine processes A1 template:
1. Extracts the data, resulting in an array of 3 _objects_ (exactly the one referred by rows property).
2. From each of these object, a value is extracted using the header property, resulting in the following array: [“Row 1”, “Row 2”, “Row 3”].
3. The values are placed from the template’s cell (A1) downwards.
2. For each of the objects in the array, extracted in [1.a], all dependent templates - in this case B1 are processed. We’ll show the processing for only the { “header”: “Row 1”, “data”: [...]} object:
1. Data is extracted using the data property _from the provided reference object_, resulting in an array of 5 numbers.
2. Since there is no additional _value extractor_ - the data is used as is, for filling the cells - in this case in the range B1:F1.
3. The same process is repeated for _all three_ of the objects extracted in step [1.b].

The resulting table will look like:

| | A | B | C | D | E | F |
| ---- | ----- | ---- | ---- | ---- | ---- | ---- |
| 1 | Row 1 | 11 | 12 | 13 | 14 | 15 |
| 2 | Row 2 | 21 | 22 | 23 | 24 | 25 |
| 3 | Row 3 | 31 | 32 | 33 | 34 | 35 |

If you want to get a real feel about the power of the engine — check the multi-dimensional example, showing how an automatically generated 5D data can get expanded throughout the sheet, by a simple template.

Great! That’s it!

_One more thing..._ As the general syntax of the data extraction suggests - there is another, more elegant way to achieve the same result. The template in B1 could have be written in the following form: {{ | rows * data | }}. Quite natural to write, and should be clear, by now, why it leads to the same result.

There is one more heavy lifting task that the engine does - it automatically _merges cells_, if the referring template turns to occupy more than one cell in the same dimension. In other words, if the template in B1 was written as {{ A1 | data | }}, (i.e. without the 1* part), this would instruct the engine to grow the data vertically. But, the data from A1 template, already grows vertically, so the engine will have to make the A1 cells “bigger”, i.e. occupying more rows. The result will look like this:

| | A | B |
| ---- | ----- | ---- |
| 1 | Row 1 | 11 |
| 2 | | 12 |
| 3 | | 13 |
| 4 | | 14 |
| 5 | | 15 |
| 6 | Row 2 | 21 |
| 7 | | 22 |

... at least, these are the first 7 rows of it. Cells A1:A5 will be merged, just like A6:A10, and A11:A15.

Hope it is clear by now. Check this and the other examples.

How to use it

The actual access to a XLSX notebook is delegated to an external library, through a so-called _accessor_, and there is currently one implementation, based on xlsx-populate library. Check the API to see how a custom one can be implemented.

Considering the existing accessor implementation, the use of xlsx-datafill is quite simple:

`javascript
// Open the notebook and create the accessor for it
const wb = await XlsxPopulate.fromFileAsync(path);
const xlsxAccess = new XlsxPopulateAccess(wb, XlsxPopulate);

// Create an instance of XlsxDataFill and provide custom
// options.
const dataFill = new XlsxDataFill(xlsxAccess, {
callbacksMap: ... // Some custom handlers.
});

// Make the actual processing of data.
dataFill.fillData(data);

// The data in populated inside the wb, so it can be used.
wb.workbook().toFileAsync(...);

`

> Note: The template definitions are overwritten by the actual data, so don’t expect to be able to run fillData() with different data.

Check the template options section for more information on how to configure the XlsxDataFill instance.

Refer to the examples folder, as well as to the API documentation for more and deeper documentation.

Template format

The general format of each template is like follows:

`
{{
|
|
|
| }}
`

Both the surrounding {{mustache}} brackets, and the | separator symbol are configurable, via XlsxDataFill constructor’s options.

The meaning of each field is:

| Field | Meaning |
| ----------- | ------------------------------------------------------------ |
| reference | The address of a cell, to be used as a reference for data extraction. If empty - the provided object’s root is considered. In both cases this is referred as _template data root_ in the rest of the description. |
| iterators | JSON paths, determining how the data should be extracted from the _template data root_, and it follows the form
.
The is applied on the _template data root_, while the works on the result of extraction. If one needs the data to grow vertically (i.e. only as a column), the form 1 is allowed, in which case works directly on the _template data root_.
Can be empty, if the _template data root_ itself should be used. |
| extractor | A JSON path, determining how the value that needs to be written in the cell(s) should be extracted from the data, provided by the iterators.
Can be omitted, in which case the iterators’ provided data is taken as a whole. |
| padding | A : delimited pair specifying how many cells on each direction row:column need to be _added_ for each new entry from the extracted data. Can be omitted. |
| styling | A comma-delimited styling pairs of the format