# Npm xlsx how to write msg.payload to xls file

**URL:** <https://discourse.nodered.org/t/npm-xlsx-how-to-write-msg-payload-to-xls-file/83191>\
**Category:** General\
**Tags:** function-node\
**Created:** [25 November 2023 23:04 UTC](https://discourse.nodered.org/t/npm-xlsx-how-to-write-msg-payload-to-xls-file/83191 "2023-11-25T23:04:25Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Mick](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mick/32/80990_2.png) [@Mick](https://discourse.nodered.org/u/Mick)\
**Post date:** [25 November 2023 23:04 UTC](https://discourse.nodered.org/t/npm-xlsx-how-to-write-msg-payload-to-xls-file/83191/1 "2023-11-25T23:04:25Z")

</div>

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.

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/4/74af8056e81f8fd7d8591a0c2a0bb2fdb2599b8f.png)

```auto
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')

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/c/bc8cb69b8a19710e7d4d4e10869582a1873f1377.png)

see flow below. thanks

```auto
[{"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":[]}]

```

---

<div class="post-metadata">

**Author:** ![Mick](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mick/32/80990_2.png) [@Mick](https://discourse.nodered.org/u/Mick)\
**Post date:** [26 November 2023 00:14 UTC](https://discourse.nodered.org/t/npm-xlsx-how-to-write-msg-payload-to-xls-file/83191/2 "2023-11-26T00:14:16Z")

</div>

I found another example that I was able to make work...  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/7/f715adafd148abf90c47a3a6ae9f24ea7610b977.png)

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

```auto
// 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);
})();

```

```auto
[{"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":[[]]}]

```

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [26 November 2023 07:32 UTC](https://discourse.nodered.org/t/npm-xlsx-how-to-write-msg-payload-to-xls-file/83191/3 "2023-11-26T07:32:14Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Mick](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mick/32/80990_2.png) [@Mick](https://discourse.nodered.org/u/Mick)\
**Post date:** [26 November 2023 15:32 UTC](https://discourse.nodered.org/t/npm-xlsx-how-to-write-msg-payload-to-xls-file/83191/4 "2023-11-26T15:32:55Z")

</div>

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

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/3/f300b280da2b3d2175a747e2e4b22cd3fe9011b6.png)

```auto
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

```auto
[{"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"]]}]

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [10 December 2023 15:33 UTC](https://discourse.nodered.org/t/npm-xlsx-how-to-write-msg-payload-to-xls-file/83191/5 "2023-12-10T15:33:16Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.