Spreadsheet Template

Spreadsheet template is a file in "Microsoft Excel 2007-2013 XML" format.

Placeholders

To populate the file with data, placeholders are used. Each spreadsheet cell can contain only one placeholder and the placeholder cannot share the cell with any other content. 

Each placeholder starts and ends with opening { and closing } brace correspondingly and refer data field by field id. For example:

{2500000024117643203}

It also is possible to refer sub fields of any level by adding corresponding id(s) separated by dot. For example:

{2500000024117643929.2500000024117643847}

Tables

If some referred field is identified as a collection then the system will build a table for it. The whole row where the reference occurred, and all following rows as well until special marker {table_end}, will be considered as a template and will be copied for each collection item. Here is an example:

Note that 222 here is the identifier of the collection field.

Formats

There is only one special format supported by the system. It is so called wide-field format. It enables to spread field value widely, one character per a cell. To achieve it, the requirements is to place the corresponding reference in 2 cells in a row or column marking the area where the value should be spread. The references should be appended with "~". For example:

According to the above markers the value of the field with id=333 will be spread among columns 2, 3, 4 and 5 one character per a cell.

Sometimes the value is shorter than the given area. For example, in the above case the field value could be 3 characters wide. As a result column 5 would be left blank. However it can be necessary to align the value to the right, so that the column 2 would become blank instead. It can be done by adding a function to the reference descriptor - "shift()". Here is the example:

If you need any other format besides the mentioned wide-field format then consider the native Spreadsheet functionality. Just select and format the corresponding cell in the template. Later when data will be inserted into the cell it will be displayed according to the defined format.

Functions

Besides the "shift()" function, which has been already mentioned above, there are 2 more functions - "default()" and "select(,)".

The "default()" function can be used to specify default value to be applied in case the actual value is null or blank. Here is an example:

{2500000024117642399:default(There is no data)}

If the field with id = 2500000024117642399 is blank then value "There is no data" will be used.