How to Convert nested JSON into excel in nodejs

0 votes

I'm trying to convert the JSON in the following into an excel document using XLSX, but the nested array of dailyPointsArray is empty after conversion.

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

Dec 28, 2022 in Others by Kithuzzz
• 38,010 points
2,070 views

1 answer to this question.

0 votes

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");
answered Dec 28, 2022 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

How to change two different date format into single date format in excel

With data in A2, in B2 enter: =IF(ISNUMBER(A2),A2,DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2))) and apply your format ...READ MORE

answered Dec 24, 2022 in Others by narikkadan
• 63,420 points
786 views
0 votes
1 answer

How to unmerge multiple cells and transpose each value into a new column in Pandas dataframe from excel file

Try this: df = pd.read_excel("Sample_File.xlsx", header=[0,1,2,3,4,5], index_col = ...READ MORE

answered Jan 8, 2023 in Others by narikkadan
• 63,420 points
1,714 views
0 votes
1 answer

How to divide data in excel into 4 columns whose sum is almost equal to 1/4 of the sum of all values/

5049 is the sum of all numbers, ...READ MORE

answered Feb 7, 2023 in Others by narikkadan
• 63,420 points
372 views
0 votes
1 answer

How to split columns into two sub columns under their parent column using python in excel

Use str.split: df[['pre', 'post']] = df['Column A'].str.split(r'\s*-->\s*', expand=True) print(df) # Output ...READ MORE

answered Apr 7, 2023 in Others by Kithuzzz
• 38,010 points
1,056 views
0 votes
1 answer

AWS Lambda : JSON object undefined

Change event.TagData.Time; To event.TagData[0].Time; This sh ...READ MORE

answered Jan 25, 2019 in IoT (Internet of Things) by Shubham
• 13,490 points
1,979 views
0 votes
1 answer

How to send Bitcoins with node.js?

This website https://blockr.io/tx/push will successfully do the ...READ MORE

answered Jul 20, 2018 in Blockchain by Christine
• 15,790 points
3,091 views
0 votes
1 answer

Running a childProcess as shell script with node.js server

Here's what I think, you could pass ...READ MORE

answered Aug 14, 2018 in IoT (Internet of Things) by DataKing99
• 8,240 points
489 views
0 votes
1 answer

How to convert PDF to Excel in C#?

Solutions a) Cometdocs makes a free online conversion from PDF ...READ MORE

answered Oct 1, 2022 in Others by narikkadan
• 63,420 points
608 views
0 votes
1 answer

How to Convert Excel Cell Values Into Individual PNG Files?

This will: create a chart add an image to ...READ MORE

answered Dec 19, 2022 in Others by narikkadan
• 63,420 points
241 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP