0
406
VistasHow to Convert nested JSON into excel in nodejs
I am trying to convert the below JSON into excel, I am using XLSX for it, it is converting my JSON to excel but, the nested array of dailyPointsArray is blank after converting into excel.
Tried code
const XLSX = require("xlsx");
const workSheet = XLSX.utils.json_to_sheet(attendanceData);
const workBook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workBook, workSheet, "attendance");
XLSX.write(workBook, { bookType: "xlsx", type: "buffer" });
XLSX.write(workBook, { bookType: "xlsx", type: "binary" });
XLSX.writeFile(workBook,"newExcel.xlsx");
attendanceData:[
{
workerId: '1230',
workerFullName: 'A',
workerDepartment: 'INFORMATION TECHNOLOGY',
workerDesignation: 'ASSISTANT MANAGER',
Location: 'locationA',
dailyPointsArray: [
{
inTime: '-',
Date: '23/03/2022',
outTime: '-',
Points: null,
createdAs: 'ABSENT'
},
{
inTime: '-',
Date: '24/03/2022',
outTime: '-',
Points: null,
createdAs: 'ABSENT'
}
],
total_duration: 0,
total_shift_points: 0
},
{
workerId: '1128',
workerFullName: 'B',
workerDepartment: 'INFORMATION TECHNOLOGY',
workerDesignation: 'MANAGER',
Location: 'LocationA',
dailyPointsArray: [
{
inTime: '-',
Date: '23/03/2022',
outTime: '-',
Points: null,
createdAs: 'ABSENT'
},
{
inTime: '-',
Date: '24/03/2022',
outTime: '-',
Points: null,
createdAs: 'ABSENT'
}
],
total_duration: 17,
total_shift_points: 2
},
]
Below is the excel file output
As you can see the column of dailyPointsArray is empty.
I want to my excel file should be like the below image
·
Juan Pablo Isaza
1 Respuestas
Responde la pregunta0
try flattening the array: filter nested array, get the keys you want, in order you want
try this:
const filtered = attendanceData.map(obj => {
// get totals to add them later to keep column order (or use `header` param for columns order)
const {
dailyPointsArray,
total_duration,
total_shift_points,
...rest
} = obj;
// flatten..
dailyPointsArray.map(el => {
rest[el['Date']] = el.createdAs;
});
return {...rest,
total_duration,
total_shift_points
};
});
const XLSX = require("xlsx");
const workSheet = XLSX.utils.json_to_sheet(filtered);
const workBook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workBook, workSheet, "attendance");
XLSX.write(workBook, { bookType: "xlsx", type: "buffer" });
XLSX.write(workBook, { bookType: "xlsx", type: "binary" });
XLSX.writeFile(workBook,"newExcel.xlsx");
·
Juan Pablo Isaza
Denunciar
Responde la pregunta