Code does not work in function node but works fine when run separately

General
1

Hi friends, I am using ExcelJS to write array to excel. and it's working fine when i run it using node exceltest.js

const ExcelJS = require('exceljs');

exceltest()

async function exceltest(){
try{
const workbook = new ExcelJS.Workbook();
const sheet = workbook.addWorksheet('contacts_company');

  	sheet.columns = [{ header: 'company', key: 'company', width: 10}, 
  	{ header: 'contact', key: 'contact', width: 10}, { header: 'country', key: 'country', width: 10}]
        
       
        sheet.addRows([[ 'Alfreds Futterkiste', 'Maria Anders', 'Germany' ],[ 'Centro comercial Moctezuma', 'Francisco Chang', 'Mexico' ], [ 'Ernst Handel', 'Roland Mendel', 'Austria' ], [ 'Island Trading', 'Helen Bennett', 'UK' ], [ 'Laughing Bacchus Winecellars', 'Yoshi Tannamuri', 'Canada' ], [ 'Magazzini Alimentari Riuniti', 'Giovanni Rovelli', 'Italy' ]]);
       
        await workbook.xlsx.writeFile("./writtenbyexceljs.xlsx")
      
    }catch(err){
        console.log("Error is ------------ ", err)
    }

}

Output:

image

But, when I put the same code in a function node (after adding the require in settings.js file and using it as global.get), It doesn't add the rows anymore

Output:

image

Any help on why it doesn't work inside function node is appreciated

2

Are you saying it builds an Excel spreadsheet and adds the top row?

Perhaps there is already an existing (possibly locked) xls file and it's not overwriting ?

Instead of console.log("Error...") put node.error(err,msg) in the catch block - and check see if you get any errors in node-red debug window?

3

Thanks for the reply

I checked it and deleted the file to make sure and also changed the name of the file in the script

No errors. Flow executes without any error... This makes me break my head :crazy_face:

4

↑ what about this question? ↑

5

In addition to Steve's question. Replace the relative file path with an absolute file path because that is a very common error where you aren't looking at the file you think you are.

6

Sorry, missed it. Yes, Excel spreadsheet is written with the top header

7

Have you tried singular addRow

Have you tried with named props in an object (instead of arrays)?

Have you tried the non async way?

8

I tried this now, but with the same observation.

9

Yes, again it's not added.

Yes, this works fine. For example If I use

worksheet.addRow({company: 'Alfreds Futterkiste', contact: 'Maria Anders', country: 'Germany'});

Output:
image

Am not sure if that means, removing the function and awaits and adding the commands one after another. I did try that, but same issue.

10

Then go with that :slight_smile:

The examples in the readme of exceljs has a few examples NOT using async await


Out of interest, was node-red-contrib-excelsheets no good for you or do you need to use exceljs?

11

I tried with addRows and it doesn't work. I tried with addTable and it does work. Tables are better anyway when working with Excel.

1 Like
12

Yes, that's what I ended up doing, but I just wanted to know if I was doing anything wrong.

I will try this for sure and update. Thanks for the same.

Not like that.., am just trying things out. and learning ....Will try with node-red-contrib-excelsheets also.

1 Like
13

It works well if you define the rows by using the keys from header.

sheet.columns = [
  	    { header: 'company', key: 'company', width: 10},
  	    { header: 'contact', key: 'contact', width: 10}, 
  	    { header: 'country', key: 'country', width: 10}
  	]
        
   let rows = [
       {company:'The company',contact:'200-300',country:'foo'},
       {company:'Other company',contact:'234-300',country:'fii'}
   ]
    const newrows = sheet.addRows(rows);
1 Like
14

Still a bug I would guess

15

Well, this worked like a charm, and without much changes to code and looks better too

image

1 Like
16

Probably. Maybe depends on something like OS or ... who knows
addRow by array don't work? · Issue #530 · exceljs/exceljs · GitHub

17

Yes, but as per the document, array should work too (without the keys)

18

Probably the biggest mistake people make with Excel is not using tables.

19

Might be. But it I faced the issue only when I run it from inside node-red :grinning_face_with_smiling_eyes:

20

Well I will use them from now, for sure. Thanks again :grinning:

1 Like