JSON to Excel in JavaScript

Question

I am trying to save data in json to excel .xlsx file. Json looks like this(with changing value names, this is just an example):

{"hum_in":[{"ts":1646034284215,"value":"22"},{"ts":1646033983313,"value":"22"}]}

I tried converting and downloading using this code:

const EXCEL_TYPE = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8';
const EXCEL_EXTENSION = '.xlsx';

function downloadAsExcel(data) {
    const worksheet = XLSX.utils.json_to_sheet(data);
    const workbook = {
        Sheets: {
            'data': worksheet
        },
        SheetNames: ['data']
    };
    const excelBuffer = XLSX.write(workbook, { bookType: 'xlsx', type: 'array' });
    console.log(excelBuffer);
    saveAsExcel(excelBuffer);
}

function saveAsExcel(buffer) {
    const data = new Blob([buffer], { type: EXCEL_TYPE });
    saveAs(data, "Export_" + new Date().getTime() + EXCEL_EXTENSION);
}

and then calling it like this:

downloadAsExcel(json);

It returned an error:

TypeError: r.forEach is not a function
    at rb (xlsx.full.min.js:23:18346)
    at Object.tb [as json_to_sheet] (xlsx.full.min.js:23:19000)
    at downloadAsExcel (app.js:233:34)
    at app.js:112:25

Does anyone have any idea what's gone wrong?

Mike D Sutton · Accepted Answer · 2022-03-22 14:02:10Z

1

The json_to_sheet function takes in an Array, whereas your data argument is an Object. Instead pass it the hum_in property to target the internal data array:

const worksheet = XLSX.utils.json_to_sheet(data.hum_in);

Here's a more complete example including support for multiple keys in the data object:

const data = {"hum_in":[
  {"ts":1646034284215,"value":"22"},
  {"ts":1646033983313,"value":"22"}
]};

function generateAsExcel(data) {
  try {
    const workbook = XLSX.utils.book_new();
    
    for (let key in data) {
      const worksheet = XLSX.utils.json_to_sheet(data[key]);
      XLSX.utils.book_append_sheet(workbook, worksheet, key);
    }
    
    let res = XLSX.write(workbook, { type: "array" });
    console.log(`${res.byteLength} bytes generated`);
  } catch (err) {
    console.log("Error:", err);
  }
}

document.getElementById("gen").addEventListener("click",
  () => generateAsExcel(data));

<script type="text/javascript" src="//cdn.jsdelivr.net/npm/xlsx/dist/xlsx.full.min.js"></script>
<button id="gen">Generate</button>

To combine all the data keys into a dataset to generate a single worksheet from, you can use something like this:

const data = {
  "hum_in":[ {"ts":1646034284215,"value":"22"}, {"ts":1646033983313,"value":"22"} ],
  "wind_dir":[ {"ts":1646034284215,"value":"123"}, {"ts":1646033983313,"value":"125"} ]
};

let merged = Object.keys(data).reduce((merged, key) => {
  for (record of data[key]) { merged.push(Object.assign({ key }, record)); }
  return merged;
}, []);

console.log(merged);

edited Mar 22, 2022 at 14:02

answered Mar 22, 2022 at 11:40

Mike D Sutton

8165 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Deesak Over a year ago

I cannot add the hum_in property, because this property always changes depending on what the customer chooses to export(one time it is hum_in, next time it is wind_dir, and so on). Is there a way to do this without exact property?

Mike D Sutton Over a year ago

Example with multi-key support added, each key in the data object will be exported to a new sheet

Deesak Over a year ago

Thank you, works well! Would it be possible to place all keys on only one sheet?

Mike D Sutton Over a year ago

Added an additional example of merging the datasets.

Mike D Sutton Over a year ago

The SheetJS docs are available here, there are lots of examples in there

|

Collectives™ on Stack Overflow

JSON to Excel in JavaScript

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related