Insert Data using Javascript inside Excel

[A-Pradeep]

Hi , how to attach Javascript code to the Xlsx template ,
i have a data (example {{name}} ) , where i'm going trim the data in half and want to insert to the cell .

var data="{{name}}";
var res= data.slice(0,10);

Now how can i insert this res value inside the cell.
I tried in online playground and could find a correct way of doing it.

[bjrmatos]

hi! i think you have two options:

• use a beforeRender script in which you can do your calculation and save your new value by putting it into req.data.newCalculatedField = <your new value here>, then you can use that variable newCalculatedField inside the rendering logic of your xlsx template and print it normally

• define a helper that does this calculation in your template and call this helper inside your xlsx template to print the calculated value

if you still have problems about this, you can create a playground example that contains some entities that you are using, and then share the link here, with that i can show you what parts you need to modify to get what you need.

[A-Pradeep]

@bjrmatos Hey ! thanks a lot ! it works perfectly now , but out of curiosity , what is the use of the panel below the template ( sorry but i'm not able to add the Image don't know why )
but here is the link https://playground.jsreport.net/w/anon/tQRHnoXD , in xlsx template will it work ? , the reason i'm asking because , in the invoice template the JS code written and it works . but in the xlsx template it won't and i have to create a separate script file and link the it will work.

And just like as you said , i hope even the email function works same as this , beforeRender afterRender , https://jsreport.net/learn/periodical-report-sending-through-email , but didn't understand the following line :

var smtpTransport = mailer.createTransport('smtps://user%40gmail.com:pass@smtp.gmail.com');

smtps://user_email:password); am i correct ?

[bjrmatos]

what is the use of the panel below the template

you mean this panel, right?

it lets you define template helpers, both template helpers and scripts lets you define javascript code, but they have differences, scripts usually have more power because it lets you define some lifecycle functions (beforeRender, afterRender functions), access some data about the request and even modify it.

template helpers are designed to be just simple function, functions that take values and return something, these functions can be used inside your template using specific syntax provided by the template engine that you are using for your report, in the cases of the xlsx recipe that template engine used is handlebars, so template helpers in this case are just handlebars helpers, the panel you see bellow the template definition is where you can put the functions that are designed to be those helpers, so putting beforeRender or afterRender in that panel will not work because it has different meaning. you will notice for example that the normal way to define a helper in handlebars is to do something like this:

Handlebars.registerHelper('fullName', function(person) {
  return person.firstName + " " + person.lastName;
})

however in the helpers panel you can just do

function fullName(person) {
  return person.firstName + " " + person.lastName;
})

and it will register the helper normally

And just like as you said , i hope even the email function works same as this , beforeRender afterRender , https://jsreport.net/learn/periodical-report-sending-through-email , but didn't understand the following line :

var smtpTransport = mailer.createTransport('smtps://user%40gmail.com:pass@smtp.gmail.com');
smtps://user_email:password); am i correct ?

i just have an answer for that here in your github issue. TL;DR the best will be to check the nodemailer docs to see if that is the updated way to do it, but so far this smtps://user_email:password) looks good to me.

[A-Pradeep]

@bjrmatos Thank you so much ! i'll work on the information you provided ( in github also) , and thanks for the amazing report server , where i prefer to use for all my reporting purpose using JSReport only ! ;)

[A-Pradeep]

@bjrmatos Hi , quick doubt , how can use the {{data}} inside the beforeRender function , example

function beforeRender(req, res, done) {
   var today="{{created}}";
   var trim= today.slice(0,10);
   req.data.newtrim= trim;
   
   var startdate="{{data.startdate}}";
   var trimstart= startdate.slice(0,10);
   req.data.newtrimstart= trimstart;
   
   var period="{{data.period}}";
   var indays=period*30;
   req.data.newindays= indays;
  
  done();
}

but instead of data ( i mean the value ) , it's printing like this inside the cell {{created}

[bjrmatos]

instead of var today="{{created}}" write var today=req.data.created and so on for the rest of code where you have {{}} inside the function. note that the {{data}} syntax only works on the template part, as you noticed this does not work inside script because that syntax is specific to the template engine execution.

[bjrmatos]

This post is deleted!

[A-Pradeep]

@bjrmatos i'm getting this error !

Error while executing user script. Cannot read property 'slice' of undefined. Error on line 3:20.

  1 | function afterRender(req, res, done) {
  2 |    var today=req.data.created;
> 3 |    var trim= today.slice(0,10);
    |                    ^
  4 |    req.data.newtrim= trim;
  5 |    
  6 |    var startdate=req.data.startdate;


TypeError: Cannot read property 'slice' of undefined
    at Object.afterRender (evaluate-user-script.js:3:20)
    at __runAfter (/home/dist/jsreportapp/node_modules/jsreport-scripts/lib/scriptEvalChild.js:90:26)
    at Object.apply (/home/dist/jsreportapp/node_modules/vm2/lib/contextify.js:288:34)
    at evaluate-user-script.js:16:1
    at ContextifyScript.Script.runInContext (vm.js:59:29)
    at VM.run (/home/dist/jsreportapp/node_modules/vm2/lib/main.js:212:72)
    at run (/home/dist/jsreportapp/node_modules/jsreport-core/lib/render/safeSandbox.js:171:19)
    at module.exports (/home/dist/jsreportapp/node_modules/jsreport-scripts/lib/scriptEvalChild.js:134:5)
    at IncomingMessage.<anonymous> (/home/dist/jsreportapp/node_modules/script-manager/lib/worker-servers.js:239:47)
    at emitNone (events.js:106:13)

Any thing i'm missing ?

[bjrmatos]

are you passing the data? note that if you are rendering this from studio, then you need to create some data entity

and then assign it to the template, with that you can run your template from studio and it will load into req.data what you put in the data entity (json).

according to the error it looks like you are not passing any data to your report so it ends failing because there is no value from which you can continue with the calculations.

[A-Pradeep]

Hi , the workflow which is happening is like this ,

jsreport.serverUrl = 'http://loaclhost:5488';
            var request = {
                template: {
                    shortid: "XXXXXX"
                },
                data: data
            };
            jsreport.render(request);      
 };

when this triggers in the HTML i'll get the data , so how can i use it in the beforeRender function

[bjrmatos]

what is the object passed here data: data? what you pass there is what you will have available as req.data in your beforeRender function, so you need to have all the data that you need, available before calling jsreport.render(request), if you are sure you have the data already there, i will add a console.log(JSON.stringify(data)) before jsreport.render(request) and then check your console to inspect the variables, maybe you are not sending the values that you think

[A-Pradeep]

HI the data is the collection of all the form (HTML) data and i have checked it in the console and it's having all the data needed that used for the report.
The main thing is , the values in the data generated only when the user fills the form and clicks submit button.
so before that basically the value of the data is empty.

Only at the time of rendering the report , i was mapping the user filled data to the corresponding excel cell . Now , since i have to slice it up before the render , it's getting complicated

[A-Pradeep]

Is JSReport has option to connect to mongoDB and build report based on the data present inside it ?

[bjrmatos]

Is JSReport has option to connect to mongoDB and build report based on the data present inside it ?

yes, just like you can use nodemailer inside an script then you can use another library, like a database connector and use it. with that you can query your database normally inside the script and fill the data of your template by putting it into req.data.yourData = <your value from query here>

[A-Pradeep]

thanks
other than this ( using beforeRender ) , any other way i can cut / slice the data ?

[bjrmatos]

sorry for late reply.. yes, you can define a helper and then call it directly in the template, so you won't need an script for that. here is the example (note that an script is still used but just for adding some data to the template)

[A-Pradeep]

@bjrmatos Hi, is there a way possible that i can use {{name}} (JSON object name) inside the javascript ( like we do in the HTML) inside the EXCEL , rather then the afterRender or beforeRender .
The afterRender & beforeRender are very straightforward & best solution to many problems , but since i don't get the data , unless someone post the data` so this is the main problem.
Any solution for this issue ?

[A-Pradeep]

This post is deleted!

[bjrmatos]

Hi, is there a way possible that i can use {{name}} (JSON object name) inside the javascript ( like we do in the HTML) inside the EXCEL , rather then the afterRender or beforeRender .

do you mean to access name inside the helpers of template? if yes, then you can pass it explicetly to the helper (as a parameter) when using it from handlebars, like {{trimField name date}}. unlike scripts, helpers can not access all the data in single variable, you need to pass what you need as parameters if you want to do something with it.

The afterRender & beforeRender are very straightforward & best solution to many problems , but since i don't get the data , unless someone post the data` so this is the main problem.

sorry i'm getting confused for what you want..

since i don't get the data , unless someone post the data

will your report only be called on form submit? if yes, then i don't see the problem, you will be able to get all data present during submit and send it to jsreport, in which it will process your data with the helpers that you defined. or are you trying to describe that you can not test the report easily without going to the flow of opening your form and then clicking submit? if that is the issue, you can attach some sample data to your report (there are some actions for that in the jsreport studio UI), so it allows you to test it right in jsreport studio without sending data