Search code examples
javascriptnode.jsexcelexpressxlsx

How 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 enter image description here

As you can see the column of dailyPointsArray is empty. I want to my excel file should be like the below image enter image description here

Solution

  • 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");