Enfocus Community


https://forum.enfocus.com/

Fill excel table with data. Data not appearing

https://forum.enfocus.com/viewtopic.php?t=3908

Page 1 of 1

Fill excel table with data. Data not appearing

Posted: Thu Nov 05, 2020 3:49 pm
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();


}

Re: Fill excel table with data. Data not appearing

Posted: Mon Nov 09, 2020 10:27 am
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');

Re: Fill excel table with data. Data not appearing

Posted: Mon Nov 09, 2020 11:25 am
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

Re: Fill excel table with data. Data not appearing

Posted: Tue Nov 10, 2020 9:01 am
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();  
     


}

Re: Fill excel table with data. Data not appearing

Posted: Tue Nov 17, 2020 2:45 pm
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.

Re: Fill excel table with data. Data not appearing

Posted: Wed Nov 18, 2020 10:07 am
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.

Re: Fill excel table with data. Data not appearing

Posted: Thu Nov 19, 2020 12:20 pm
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 9571 times

Re: Fill excel table with data. Data not appearing

Posted: Thu Nov 19, 2020 12:49 pm
by jan_suhr
Try with another filepath to the local disk.

Or instead for S:/ use the //Ip.number/path/to/folder

Re: Fill excel table with data. Data not appearing

Posted: Thu Nov 19, 2020 1:01 pm
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.

Re: Fill excel table with data. Data not appearing

Posted: Thu Nov 19, 2020 2:42 pm
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.

Re: Fill excel table with data. Data not appearing

Posted: Mon Nov 23, 2020 7:21 am
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);
 }
All times are UTC+02:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited