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?
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);