How to Convert nested JSON into excel in nodejs

Question

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

traynor · Accepted Answer · 2022-03-25 16:53:25Z

2

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

edited Mar 25, 2022 at 16:53

answered Mar 25, 2022 at 16:44

traynor

10.4k3 gold badges15 silver badges30 bronze badges

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

Comments

Tushar Panja · Accepted Answer · 2022-08-11 12:10:00Z

2

You can also use typeof operator

const mappedArr = attendanceData.map(item => {
   
    if (item.dailyPointsArray == null) {
        item.dailyPointsArray = "";
    } else if (typeof item.dailyPointsArray == "object") {
        item.dailyPointsArray = JSON.stringify(item.dailyPointsArray);
    }
    return {
    ...item,
    item.dailyPointsArray
    }
}

answered Aug 11, 2022 at 12:10

Tushar Panja

971 silver badge7 bronze badges

Collectives™ on Stack Overflow

How to Convert nested JSON into excel in nodejs

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related