Npm xlsx how to write msg.payload to xls file

[Mick]

I am working npm xlsx and want to create a xlsx file on my local device from the json output. I have gotten npm xlsx to load and I am using an example I found online to test. I get nothing when I run the function.

let XLSX = global.get('xlsx')

/* original data */
var data = [
    { "name": "John", "city": "Seattle" },
    { "name": "Mike", "city": "Los Angeles" },
    { "name": "Zach", "city": "New York" }
];

/* this line is only needed if you are not adding a script tag reference */
if (typeof XLSX == 'undefined') XLSX = require('xlsx');

/* make the worksheet */
var ws = XLSX.utils.json_to_sheet(data);

/* add to workbook */
var wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "People");

/* write workbook */
XLSX.write(wb, { bookType: 'xlsx', type: 'buffer' });

Here is the output from the global.get('xlsx')

see flow below. thanks

[{"id":"c0fe6321df5e43c0","type":"inject","z":"6aa33e2ddb33f623","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":100,"y":220,"wires":[["bcba72236e7aca66"]]},{"id":"bcba72236e7aca66","type":"function","z":"6aa33e2ddb33f623","name":"function 1","func":"let XLSX = global.get('xlsx')\n\n/* original data */\nvar data = [\n    { \"name\": \"John\", \"city\": \"Seattle\" },\n    { \"name\": \"Mike\", \"city\": \"Los Angeles\" },\n    { \"name\": \"Zach\", \"city\": \"New York\" }\n];\n\n/* this line is only needed if you are not adding a script tag reference */\nif (typeof XLSX == 'undefined') XLSX = require('xlsx');\n\n/* make the worksheet */\nvar ws = XLSX.utils.json_to_sheet(data);\n\n/* add to workbook */\nvar wb = XLSX.utils.book_new();\nXLSX.utils.book_append_sheet(wb, ws, \"People\");\n\n/* write workbook */\nXLSX.write(wb, { bookType: 'xlsx', type: 'buffer' });\n\n\n\n","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[{"var":"xlsx","module":"xlsx"}],"x":260,"y":220,"wires":[["c29b1f5edf30cbb8"]]},{"id":"c29b1f5edf30cbb8","type":"debug","z":"6aa33e2ddb33f623","name":"debug 1","active":true,"tosidebar":true,"console":true,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":430,"y":220,"wires":[]}]

[Mick]

I found another example that I was able to make work...

I had to add npm path and axios for the example. Now I need to take this and make it work with json to autoformat the spreadsheet. I also tried using the Windows env %USERPROFILE% for the path, but Node-RED could not interpret the env.

Regards

// Uncomment the next line for use in NodeJS:
const XLSX = global.get("xlsx");
path = global.get('path');
axios = global.get("axios");
const dirname = '/users/w8beu/downloads';
const filePath = path.join(dirname, 'Presidents.xlsx');
//const filePath = 'Presidents.xlsx';

(async() => {
  /* fetch JSON data and parse */
  const url = "https://theunitedstates.io/congress-legislators/executive.json";
  const raw_data = (await axios(url, {responseType: "json"})).data;

  /* filter for the Presidents */
  const prez = raw_data.filter(row => row.terms.some(term => term.type === "prez"));

  /* flatten objects */
  const rows = prez.map(row => ({
    name: row.name.first + " " + row.name.last,
    birthday: row.bio.birthday
  }));

  /* generate worksheet and workbook */
  const worksheet = XLSX.utils.json_to_sheet(rows);
  const workbook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(workbook, worksheet, "Dates");

  /* fix headers */
  XLSX.utils.sheet_add_aoa(worksheet, [["Name", "Birthday"]], { origin: "A1" });

  /* calculate column width */
  const max_width = rows.reduce((w, r) => Math.max(w, r.name.length), 10);
  worksheet["!cols"] = [ { wch: max_width } ];

  /* create an XLSX file and try to save to Presidents.xlsx */
  XLSX.writeFile(workbook, filePath);
})();

[{"id":"2e057019a22821dd","type":"inject","z":"6aa33e2ddb33f623","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":180,"y":560,"wires":[["495203fdbfc3e26d"]]},{"id":"495203fdbfc3e26d","type":"function","z":"6aa33e2ddb33f623","name":"XLS-writeFile Example","func":"// Uncomment the next line for use in NodeJS:\nconst XLSX = global.get(\"xlsx\");\npath = global.get('path');\naxios = global.get(\"axios\");\nconst dirname = '/users/w8beu/downloads/';\nconst filePath = path.join(dirname, 'Presidents.xlsx');\n//const filePath = 'Presidents.xlsx';\n\n(async() => {\n  /* fetch JSON data and parse */\n  const url = \"https://theunitedstates.io/congress-legislators/executive.json\";\n  const raw_data = (await axios(url, {responseType: \"json\"})).data;\n\n  /* filter for the Presidents */\n  const prez = raw_data.filter(row => row.terms.some(term => term.type === \"prez\"));\n\n  /* flatten objects */\n  const rows = prez.map(row => ({\n    name: row.name.first + \" \" + row.name.last,\n    birthday: row.bio.birthday\n  }));\n\n  /* generate worksheet and workbook */\n  const worksheet = XLSX.utils.json_to_sheet(rows);\n  const workbook = XLSX.utils.book_new();\n  XLSX.utils.book_append_sheet(workbook, worksheet, \"Dates\");\n\n  /* fix headers */\n  XLSX.utils.sheet_add_aoa(worksheet, [[\"Name\", \"Birthday\"]], { origin: \"A1\" });\n\n  /* calculate column width */\n  const max_width = rows.reduce((w, r) => Math.max(w, r.name.length), 10);\n  worksheet[\"!cols\"] = [ { wch: max_width } ];\n\n  /* create an XLSX file and try to save to Presidents.xlsx */\n  XLSX.writeFile(workbook, filePath);\n})();","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[{"var":"xlsx","module":"xlsx"},{"var":"axios","module":"axios"},{"var":"path","module":"path"}],"x":400,"y":560,"wires":[[]]}]

[E1cid]

Would be nice to supply the example. as others may be looking at this topic as it is marked as a solution, but there is no solution shown. The forum is about sharing and helping each other after all.

[Mick]

Ok so here is the example that shows how to use npm xlsx to export json to xlsx file.

const XLSX = global.get('xlsx')
const dirname = '/users/w8beu/downloads';
const filePath = path.join(dirname, 'names.xlsx');

/* original data */
var data = [
    { "name": "John", "city": "Seattle" },
    { "name": "Mike", "city": "Los Angeles" },
    { "name": "Zach", "city": "New York" }
];

/* this line is only needed if you are not adding a script tag reference */
// if (typeof XLSX == 'undefined') XLSX = require('xlsx');

/* make the worksheet */
var ws = XLSX.utils.json_to_sheet(data);

/* add to workbook */
 var wb = XLSX.utils.book_new();
 XLSX.utils.book_append_sheet(wb, ws, 'People');

/* write workbook */
// XLSX.write(wb, { bookType: 'xlsx', type: 'buffer' });
XLSX.writeFile(wb, filePath )
return {payload: wb}

Here is the function node

[{"id":"0f03281575e68c43","type":"function","z":"6aa33e2ddb33f623","name":"function 1","func":"const XLSX = global.get('xlsx')\nconst dirname = '/users/w8beu/downloads';\nconst filePath = path.join(dirname, 'names.xlsx');\n\n\n/* original data */\nvar data = [\n    { \"name\": \"John\", \"city\": \"Seattle\" },\n    { \"name\": \"Mike\", \"city\": \"Los Angeles\" },\n    { \"name\": \"Zach\", \"city\": \"New York\" }\n];\n\n/* this line is only needed if you are not adding a script tag reference */\n// if (typeof XLSX == 'undefined') XLSX = require('xlsx');\n\n/* make the worksheet */\nvar ws = XLSX.utils.json_to_sheet(data);\n\n/* add to workbook */\n var wb = XLSX.utils.book_new();\n XLSX.utils.book_append_sheet(wb, ws, 'People');\n\n/* write workbook */\n// XLSX.write(wb, { bookType: 'xlsx', type: 'buffer' });\nXLSX.writeFile(wb, filePath )\nreturn {payload: wb}\n\n","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[{"var":"xlsx","module":"xlsx"},{"var":"path","module":"path"}],"x":320,"y":180,"wires":[["c29b1f5edf30cbb8"]]}]