xlsx recipe generates excel files from plain Office Open XML SpreadsheetML File Format. The source xml is assembled using templating engines and helpers provided itself. See the example in playground:
Excel files are defined by several xml files zipped into one with xlsx extension. This recipe employs templating engine to directly modify these source files and produces excel files.
The excel source xml follows up quite verbose and complicated Office Open XML SpreadsheetML File Format specification and it would be difficult to create one from the scratch. That's why the recipe rather replaces or adds just particular pieces to particular places.
The modification of source XML is used by several predefined helpers the recipe provides. There is for example helper xlsxAdd which adds piece of xml into particular file and its particular path. The following code is the most simple example using handlebars for creating excel with one row and cell.
{{#xlsxAdd "xl/worksheets/sheet1.xml" "worksheet.sheetData[0].row"}}
<row>
<c t="inlineStr"><is><t>Hello world</t></is></c>
</row>
{{/xlsxAdd}}
{{{xlsxPrint}}}
The parameter xl/worksheets/sheet1.xml is the physical path to the file representing the sheet. You can always verify the path by unzipping the xlsx file and observing its content. The parameter worksheet.sheetData[0].row is then javascript based path into the particular xml. We use here javascript instead of XPath because we anyway keep in memory javascript representation of xml because it is more practical. Check this helper if you are not sure how is the xml path constructed. Finally the value inside the xlsxAdd helper is xml which is added.
At the end of the template, there always must be called helper xlsxPrint which prints the internal json representation into xml which is by recipe zipped into xlsx.
Using other provided helper you should be able to manipulate the source xml into every desired shape.
Unfortunately Office Open XML SpreadsheetML File Format is not very well documented and quite hard to handle, but you can look here for a general overview of what tags and attributes are supported. When you are lost, it is always good idea to create a test xlsx file in excel, unzip it and analyze its content. Another approach we recommend is to create the whole excel the first, upload it into jsreport and then only replace its data, which is simple.
The must be the call to xlsxPrint at the end of every template.
{{#xlsxReplace filePath xmlPath}}...{{/xlsxReplace}}
Replace the whole xml in filePath and xmlPath with the xml produced by the block helper.
{{#xlsxMerge filePath xmlPath}}...{{/xlsxMerge}}
Merges the xml in filePath at xmlPath with content generated by block helper.
Hint -> you can calculate the xmlPath string dynamically using handlebars subexpressions.
{{#each items}} {{#xlsxMerge "xl/worksheets/sheet1.xml" (replace "worksheet.sheetData[0].row[@value].c[0]" "@value" @index)}} <c t="inlineStr"><is><t>{{this}}</t></is></c> {{/xlsxMerge}} {{/each}}
function replace(str, pattern, value) {
return str.replace(pattern, value)
}
{{#xlsxAdd filePath xmlPath}}...{{/xlsxAdd }}
Add xml provided by block helper into filePath and collection found at xmlPath.
{{#xlsxRemove filePath xmlPath index}}...{{/xlsxRemove }}
Remove element from collection in filePath at xmlPath at index.
{{#xlsxAddImage "test" "sheet1.xml" 0 0 1 1}}
{#asset myImage.png @encoding=base64}
{{/xlsxAddImage}}
Add an base64 encoded image provided by the block helper content into the sheet cell. Arguments are imageName, sheet id, column from, row from, column to, row to
You can always write your custom helpers. The best is to get started by checking the source of the standard ones
See general documentation for office preview in studio here.
You can specifty template the standard way using name/shortid or you can also send it fully in the API call:
{
"template": {
"recipe": "xlsx",
"engine": "handlebars",
"content": "{{{xlsxPrint}}}",
"xlsx": {
"templateAssetShortid": "xxxx"
}
},
"data": {}
}
In case you don't have the xlsx template stored as an asset you can send it directly in the API call.
{
"template": {
"recipe": "xlsx",
"engine": "handlebars",
"content": "{{{xlsxPrint}}}",
"xlsx": {
"templateAsset": {
"content": "base64 encoded word file",
"encoding":"base64"
}
}
},
"data": {}
}
"extensions": {
"xlsx": {
// default is to escape & into &amp;
"escapeAmp": true,
// recipe stops parsing xlsxAdd output into json after the following is reached
"numberOfParsedAddIterations": 50,
// huge reports are flushed into buffered files with the following size
"addBufferSize": 50000000,
}
}