How to render xlsx report data from api instead of using dummy data?

[reshmajacob93]

Hi sir,

• How to bind xlsx report data from api .Can you please show me one example?

• Also can you please tell me the xlsx report rendering flow?
  What i understood is that first create sample excel template and upload it as asset then add script and attach script with xlsx handlebar (script which contain both beforeRender and afterRender hook).Then write the xlsx code in handlebar file ?

Is the flow is correct ?

• Also i have attached url which i have got from one example in forum .In that data is given which contain user array and excel template also uploaded but in xlsx handlebar no code is written inside it ie, #each for looping user but still looping is happening in report while running the report?How does it works?

url - https://playground.jsreport.net/w/bjrmatos/FzfaQhJ~

• Do you have any document to understand xlsx report working to understand its code for binding data?

[bjrmatos]

How to bind xlsx report data from api .Can you please show me one example?

You just need to send data in the payload of the http request you made, an specific example depends on your favorite http client, but to make things simple here is example code using the jsreport node.js client

const client = require('@jsreport/nodejs-client')('http://localhost:5488')

async function render () {
  const res = await client.render({
    template: {
      name: '<name of your template here>'
    },
    data: { /* here you can define your custom data, if will replace the dummy data attached to your template */ }
  })

  console.log(res.headers)
  const bodyBuffer = await res.body()
  // you have the buffer here, so you can just use standard fs module to save the buffer on your computer
  // fs.writeFileSync('output.xlsx', bodyBuffer) 
}

render().catch(console.error)

Also can you please tell xlsx report render flow?
What i understood is that first create sample excel template and upload it as asset then add script and attach script with xlsx handlebar (script which contain both beforeRender and afterRender hook).Then write the xlsx code in handlebar file ?

i think this is best described in the xlsx docs

you are correct about the steps, except that it is not needed to use a jsreport script if you don't need.

right now we have two ways/phases to be able generate the xlsx, one is called generation phase, and the other transformation phase, each phase can be used individually or together at the same time.

the generation phase take a xlsx template you provide and check in the content if there are helper calls (like each, xlsxChart), if there are helper calls we proceed to evaluate them and transform the xlsx template based on the functionality of the helper call, in the case of the each we just produce more rows in the exact place the each was used, in the case of the xlsxChart we fill a chart with data based on template chart, etc.
this phase was created to make things simpler, that user just describe (with the helper calls) what parts of the xlsx template should be dynamic and based on data and we will make sure to transform the xlsx template to produce the desired output, this phase does not require any knowledge about how the xlsx format (xml files) internally works, because you won't be modifying these files directly. Also this phase is relative new, so there is still room for improvement there and support for more things (like making a each loop to work across multiple rows, right now the each only works if you have the start and end each call in the same row). the helper calls in this phase are expected to be inside the xlsx cells

the transformation phase works by making the user to provide code that modifies xml files (the internal representation of the xlsx), there are helpers available to make things easier (to add, remove, update xml nodes), however this phase requires the user to have the knowledge about how the xlsx format (xml files) works, the knowledge is required because it is expected user knows what and how to update different xml nodes to produce its desired output. this is the phase that have been there for years. the helper calls in this phase are expected to be in the template (that code button that appears in this screenshot)

you can use both phases, if you provide template with helper calls (each, etc) in cells, then the generation will be activated, then if you have code in the template content with {{#xlsxAdd call then the transformation phase will be activated, so basically you can generate a document with simple each calls in the cells, and then provide another code to transform the output.

the order of execution of these phases is basically like this:

1. we check if there is xlsx template provided, if yes we check if there are helper calls in the xlsx cells, if there are helpers calls we execute them and transform the xlsx based on those.
2. we check if there transformation code defined, if yes, we take the previous output (from the generation phase) and run that code with it.

this is basically how it works, of course there are different combinations like i've mentioned

• you don't provide any xlsx template and you just use transformation phase and generate a full xlsx document with it, if you know how the xml format of the xlsx works.
• you provide xlsx template and you use the transformation phase to modify it to your liking
• you provide xlsx template and just use the generation phase
• etc

[reshmajacob93]

Thank you so much sir for the detailed explanation. Also our report is async because we were calling multiple microservices in order to get whole data for report.So is the jsreport script will work in the same way as that of other reports like chrome-pdf,html recipes?

[bjrmatos]

@reshmajacob93 yes, it should not be different behaviour regarding the scripts, it should be work the same as with other recipes.

[reshmajacob93]

Thank you sir