Fill excel table with data. Data not appearing

Post Reply
PdFUser5000
Member
Posts: 120
Joined: Fri Jun 12, 2020 11:23 am

Fill excel table with data. Data not appearing

Post by PdFUser5000 »

I am trying to log data to a external excel file. Everything works, the script saves the xslx, but no data is added. I get no errors either. is the data not picked up from the job ?

Code: Select all

const fs = require("fs");
const ExcelJS = require('exceljs');
const path = require('path');

async function jobArrived(s, flowElement, job) {

    //get data from job
    let jobName = await job.getName(includeExtension=false);
    let PDFStamp = await job.getPrivateData("PDFTime"); 
    let NESTStamp = await job.getPrivateData("NESTTime"); 

//convert seconds to HH:MM:SS for excel
     function secondsToTimePDF(e) {
        h = Math.floor(e / 3600).toString().padStart(2, '0'),
            m = Math.floor(e % 3600 / 60).toString().padStart(2, '0'),
            s = Math.floor(e % 60).toString().padStart(2, '0');

        return h + ':' + m + ':' + s;
    }

      function secondsToTimeNEST(e) {
        h = Math.floor(e / 3600).toString().padStart(2, '0'),
            m = Math.floor(e % 3600 / 60).toString().padStart(2, '0'),
            s = Math.floor(e % 60).toString().padStart(2, '0');

        return h + ':' + m + ':' + s;
    }


let asd = secondsToTimePDF(PDFStamp) ;
let xyz = secondsToTimeNEST(NESTStamp);

//location of excel file

let filePath ="S:/1-AUTO PP/ERROR/Data.xlsx"

  // excel stuff
  const workbook = new ExcelJS.Workbook()

 

  workbook.xlsx.readFile(filePath).then(async function () {
      let worksheet = workbook.getWorksheet('Time counter')
     
      worksheet.addRow([jobName, asd, xyz]);
      await workbook.xlsx.writeFile(filePath)
  })
  

await job.sendToNull();


}
PdFUser5000
Member
Posts: 120
Joined: Fri Jun 12, 2020 11:23 am

Re: Fill excel table with data. Data not appearing

Post by PdFUser5000 »

When trying to debug, i get the following messages.

Code: Select all

Could not read source map for file:///C:/Program%20Files/Enfocus/Enfocus%20Switch/ScriptExecutor/NodeScriptExecutor.js: ENOENT: no such file or directory, open 'c:\Program Files\Enfocus\Enfocus Switch\ScriptExecutor\NodeScriptExecutor.js.map'
debug stops at:

Code: Select all

const {execJobArrived: entrypoint, PropertyType, DatasetModel, Connection, AccessLevel, LogLevel, Scope} = require("switch-scripting");debugger;const fs = require('fs');
freddyp
Advanced member
Posts: 1008
Joined: Thu Feb 09, 2012 3:53 pm

Re: Fill excel table with data. Data not appearing

Post by freddyp »

You can ignore the map message.

Debugging always stops there. Note that the script that is running is a cached version of your original. This implies that any breakpoints you may have set on the original main.js are not recognized. Stopping here means you can set your breakpoints on that cached version and continue.

In next week's webinar on advanced scripting topics this is dealt with in more detail: https://www.enfocus.com/en/scripting-wi ... and-nodejs
PdFUser5000
Member
Posts: 120
Joined: Fri Jun 12, 2020 11:23 am

Re: Fill excel table with data. Data not appearing

Post by PdFUser5000 »

From debugger the excel function skips the last three rows and goes directly to job.sendToNull. is this a sync function error? I changed the function a bit.

Code: Select all

//get variables
//excel stuff:
async function excelOp() {
        let filePath = "S:/1-AUTO PP/ERROR/Data.xlsx";
        let workbook = new ExcelJS.Workbook();
        workbook = await workbook.xlsx.readFile(filePath); // script jumps from this line to await job.sendToNull();
        let worksheet = workbook.getWorksheet(1); // these lines are not activated
        worksheet.addRow([jobName, asd, xyz]);
       workbook.xlsx.writeFile(filePath);
        
    }

     excelOp();


await job.sendToNull();
//fixed the script to write data, but the data still does not appear. Completely clueless over here why is this not happening. debugger shows everything is correct, but the data just does not appear into the table.

Code: Select all

const fs = require('fs');
const path = require('path');
const ExcelJS = require('exceljs');

async function jobArrived(s, flowElement, job) {

    //get data from job
    let jobName = await job.getName(includeExtension=false);
    const PDFStamp = await job.getPrivateData("PDFTime"); 
    const NESTStamp = await job.getPrivateData("NESTTime"); 

//convert seconds to HH:MM:SS for excel
     function secondsToTimePDF(e) {
        h = Math.floor(e / 3600).toString().padStart(2, '0'),
            m = Math.floor(e % 3600 / 60).toString().padStart(2, '0'),
            n = Math.floor(e % 60).toString().padStart(2, '0');

        return h + ':' + m + ':' + n;
    }

      function secondsToTimeNEST(e) {
        h = Math.floor(e / 3600).toString().padStart(2, '0'),
            m = Math.floor(e % 3600 / 60).toString().padStart(2, '0'),
            n = Math.floor(e % 60).toString().padStart(2, '0');

        return h + ':' + m + ':' + n;
    }


let asd = secondsToTimePDF(PDFStamp) ;
let xyz = secondsToTimeNEST(NESTStamp);


//location of excel file


let filePath = "S:/1-AUTO PP/ERROR/Data.xlsx";
let workbook = new ExcelJS.Workbook();

await workbook.xlsx.readFile(filePath)
.then(()=> {
    workbook.getWorksheet('Time counter').addRow([jobName, asd, xyz]);
    return workbook.xlsx.writeFile(filePath);
        
});

await job.sendToNull();  
     


}

PdFUser5000
Member
Posts: 120
Joined: Fri Jun 12, 2020 11:23 am

Re: Fill excel table with data. Data not appearing

Post by PdFUser5000 »

The problem seems to be that the data picked up from switch ( jobname, privatedata) is not written into the excel file.
Creating a new excel table from scratch works, but even then the selected data does not appear in the table. Been pulling out my hairs for over a week and i still can't figure out why it wont write the data.
freddyp
Advanced member
Posts: 1008
Joined: Thu Feb 09, 2012 3:53 pm

Re: Fill excel table with data. Data not appearing

Post by freddyp »

I have not tested this but there is something that is not OK in these three lines of code:

Code: Select all

let workbook = new ExcelJS.Workbook();
workbook = await workbook.xlsx.readFile(filePath); // script jumps from this line to await job.sendToNull();
let worksheet = workbook.getWorksheet(1); // these lines are not activated
on the first line workbook is an object instantiated from the ExcelJS.Workbook class and on the second line this variable is replaced by the result of workbook.xlsx.readFile. I do not know what workbook is at that point in time (the debugger will tell you) but if it is not a workbook object then workbook.getWorksheet will obviously fail. Just write:

Code: Select all

await workbook.xlsx.readFile(filePath);
Put a breakpoint on the second line and on the third line. When the debugger hits the second line, check what the value of workbook is. Continue to the third line and check the value of workbook again. I am pretty sure it will have changed and then there is your problem.

General tip: place such code inside a try-catch blok so you can log the error message. General warning: beware of the variable scope! Variables declared inside a try-catch block only "live" inside that block.
PdFUser5000
Member
Posts: 120
Joined: Fri Jun 12, 2020 11:23 am

Re: Fill excel table with data. Data not appearing

Post by PdFUser5000 »

Changed my code, but data is still not appearing. In debug, all variables seem to be correct?

Code: Select all

const fs = require('fs');
const path = require('path');
const ExcelJS = require('exceljs');

async function jobArrived(s, flowElement, job) {


    try {
        //get data from job
    let workName = await job.getName();
    let PDFStamp = await job.getPrivateData("PDFTime"); 
    let NESTStamp = await job.getPrivateData("NESTTime"); 

//convert seconds to HH:MM:SS for excel
function secondsToTimePDF(e) {
    h = Math.floor(e / 3600).toString().padStart(2, '0'),
        m = Math.floor(e % 3600 / 60).toString().padStart(2, '0'),
        n = Math.floor(e % 60).toString().padStart(2, '0');

    return h + ':' + m + ':' + n;
}

  function secondsToTimeNEST(e) {
    h = Math.floor(e / 3600).toString().padStart(2, '0'),
        m = Math.floor(e % 3600 / 60).toString().padStart(2, '0'),
        n = Math.floor(e % 60).toString().padStart(2, '0');

    return h + ':' + m + ':' + n;
}


let asd = secondsToTimePDF(PDFStamp) ;
let xyz = secondsToTimeNEST(NESTStamp);

//location of excel file
    let filePath = "S:/1-AUTO PP/ERROR/Data.xlsx";


        let workbook = new ExcelJS.Workbook();
        await workbook.xlsx.readFile(filePath);
        let worksheet = workbook.getWorksheet('Time counter');
        worksheet.addRow([workName,asd,xyz]);
        
        await workbook.xlsx.writeFile(filePath);
       
        await job.sendToNull();  
     }
     catch(e) {
         console.log(e);
 }

  };
Capture.PNG
Capture.PNG (194.51 KiB) Viewed 9495 times
jan_suhr
Advanced member
Posts: 586
Joined: Fri Nov 04, 2011 1:12 pm
Location: Nyköping, Sweden

Re: Fill excel table with data. Data not appearing

Post by jan_suhr »

Try with another filepath to the local disk.

Or instead for S:/ use the //Ip.number/path/to/folder
Jan Suhr
Color Consult AB
Sweden
=============
Check out my apps
freddyp
Advanced member
Posts: 1008
Joined: Thu Feb 09, 2012 3:53 pm

Re: Fill excel table with data. Data not appearing

Post by freddyp »

I tried it out with some fixed values for asd and xyz to reduce setup time and it worked.

console.log is not going to show anything in the Switch messages! Make that:

Code: Select all

await job.log(LogLevel.Error, e.message);
and you will get a (hopefully) clear error message. The first time I ran it I got the error:

Code: Select all

Cannot read property 'addRow' of undefined
which immediately lead me to the fact that in my Excel there was no sheet with the name "Time counter". When I added that, all was well.

Side note: do not hardcode paths. Use a property with the editor "Choose file" and get the property value.
PdFUser5000
Member
Posts: 120
Joined: Fri Jun 12, 2020 11:23 am

Re: Fill excel table with data. Data not appearing

Post by PdFUser5000 »

Still not working :( Updated code:

Code: Select all

const fs = require('fs');
const path = require('path');
const ExcelJS = require('exceljs');

async function jobArrived(s, flowElement, job) {


    try {
        //get data from job
    let workName = await job.getName();
    let PDFStamp = await job.getPrivateData("PDFTime"); 
    let NESTStamp = await job.getPrivateData("NESTTime");
    let str = await flowElement.getPropertyStringValue("ChooseXLSX");  //location of excel file

//convert seconds to HH:MM:SS for excel
function secondsToTimePDF(e) {
    h = Math.floor(e / 3600).toString().padStart(2, '0'),
        m = Math.floor(e % 3600 / 60).toString().padStart(2, '0'),
        n = Math.floor(e % 60).toString().padStart(2, '0');

    return h + ':' + m + ':' + n;
}

  function secondsToTimeNEST(e) {
    h = Math.floor(e / 3600).toString().padStart(2, '0'),
        m = Math.floor(e % 3600 / 60).toString().padStart(2, '0'),
        n = Math.floor(e % 60).toString().padStart(2, '0');

    return h + ':' + m + ':' + n;
}


let asd = secondsToTimePDF(PDFStamp) ;
let xyz = secondsToTimeNEST(NESTStamp);




        let workbook = new ExcelJS.Workbook();
        await workbook.xlsx.readFile(str);
        let worksheet = workbook.getWorksheet('Time counter');
        worksheet.addRow([workName,asd,xyz]);
        
        await workbook.xlsx.writeFile(str);
       
        await job.sendToNull();  
     }
     catch(e) {
        await job.log(LogLevel.Error, e.message);
 }

  };
No errors are logged into Switch.

Running the script in switch takes about 15 seconds from script activation until receiving processing finished, is this normal? Tried using another excel file, then it went down to 1 second.

Tried with a table from the server and also on the local disk, neither did work. I also tried to use sheet id, instead of name which also did not work. It does save the excel file, i can see that by the timestamp, but the data is not inserted.

When i run the same script in node, without Switch functions, everything works just fine.
PdFUser5000
Member
Posts: 120
Joined: Fri Jun 12, 2020 11:23 am

Re: Fill excel table with data. Data not appearing

Post by PdFUser5000 »

Finally got it working somehow. Had to add column keys and values to the creation part of the excel, tried this before too, but without property value. Thanks to everybody for helping!
Working exceljs part:

Code: Select all

        let workbook = new ExcelJS.Workbook()
        await workbook.xlsx.readFile(str);

 let worksheet = workbook.getWorksheet('Time counter')
 
 worksheet.columns = [
     {header: 'Job Name', key: 'jobName', width: 32}, //Column A
     {header: 'PDF Time', key: 'PDFKey'}, //Column B
     {header: 'Nesting Time', key: 'NestKey'}, // Column C
     
   ]
  
 worksheet.addRow({jobName:workName,PDFKey: asd,NestKey: xyz});
 
        await workbook.xlsx.writeFile(str);
       
        await job.sendToNull();  
     }
     catch(e) {
        await job.log(LogLevel.Error, e.message);
 }
Post Reply