I'd like to propose adding scopes to xlsx-template. This would introduce a lot more flexibility, like deeper and repeated structures. I think one way to do this is by using named ranges. Here, I'd like to discuss it a bit before taking a stab at implementing it.

I like to hear your opinions, objections, use cases and questions.

scopes by named ranges

If we have a json structure like so:

{
    scalar: 'foo',
    array: [1, 2, 3],
    table: [
        {row: 1, value: 'a'},
        {row: 2, value: 'b'},
        {row: 3, value: 'c'}
    ],
    deeper: {
        scalar: 'foo',
        array: [1, 2, 3],
        table: [
            {row: 1, value: 'a'},
            {row: 2, value: 'b'},
            {row: 3, value: 'c'}
        ],
    },
    nested: {
        deeper: {
            scalar: 'foo',
            array: [1, 2, 3],
            table: [
                {row: 1, value: 'a'},
                {row: 2, value: 'b'},
                {row: 3, value: 'c'}
            ],
        },
        table: [
            {
                name: 'lowercase',
                data: [
                    {row: 1, value: 'a'},
                    {row: 2, value: 'b'},
                    {row: 3, value: 'c'}
                ]
            }, {
                name: 'uppercase',
                data: [
                    {row: 1, value: 'A'},
                    {row: 2, value: 'B'},
                    {row: 3, value: 'C'}
                ]
            }
        ]
    }
}

We can already handle the scalar, the array and the table. Displaying the array vertically is currently not possible, but that is easily fixed by using {$table:array} and a small amendment.

The deeper.scalar value can also be used, also, I think deeper.array can be fixed, but handling deeper.table is not trivial, as we would not know where to split the path. However, this may also be fixed by descending into an object as long as possible and use the rest of the path to descend in the array of objects.

Multiple tables is not currently possible.

A named range in excel can contain dots, which is convenient. This would allow us to define a scope for a range of cells. For example, if we have a range named

\deeper

And a cell within that range with the value

${scalar}

We can piece together a working substitution. This also goes for ${array} and ${table} within \deeper. This would also work if we name the range \nested.deeper

If a scope resolves to an array, like \nested.table, we can repeat the whole range and apply the template within to every structure in the array. This makes excel templates very flexible.

Use cases

simpler tables

Although not directly needed, a table can be made simpler. Rather than having a cell with ${table:table.row} we can now use a range \table, resolving a scope to the table array. As such, the whole range structure would be repeated vertically. The cells within can now be simple scalars like ${row}. Moreover, as they are now treated as scalars, you could use them in a string: row ${row} has value ${value}

tables from deeper structures

From the above example, using deeper.table is just as easy, by renaming the range to \deeper.table

repeating tables

Suppose we have a template like this:

This would result in two tables:

Caveats

One problem is that names must be unique. This may be mitigated by smart naming in the json structure, and by avoiding single step scopes (like \data above). It is still a problem I have no solution for though

How would we prevent accidental naming clashes with user defined ranges? I suppose by prepending the \ character, chances of collision are rather small, but still...

Other thoughts

Excel also allows for an underscore at the start of a range name. We may create two variants, one that repeats vertically (\nested.table), one that repeats horizontally: _nested.table. The latter would result in this:

A way to avoid using named ranges (and the uniqueness problem) is to just put the data in a cell, for example like so:

$${B8:C11:\nested.table}

And then just delete the contents of such cells. (we do have some more flexibility for the syntax then). Also, we may have a special meta range that span some rows that contain meta info for the template like the above and have that entire set of rows removed on substitution. For example like this:

Next steps

Do you see any other problems? Or do you have interesting use cases to add that may or may not be solved with this proposal? Would there be another way in excel to define a scope on a range that does not have the uniqueness problem?

Would xlsx-template be the right codebase to put this in or should I create something else entirely? I do have a need for repeated tables and I like the way xlsx-templates are super-maintainable. I also feel I know enough of xml, js and excel to implement this.

optilude / xlsx-template

proposal: scopes by named ranges #45