如何在Node.js中将嵌套的JSON转换为Excel

Question

我正在尝试将以下JSON转换为Excel，我正在使用XLSX进行转换，它将我的JSON转换为Excel，但是在转换为Excel后，dailyPointsArray的嵌套数组为空。

尝试过的代码：

 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
  },
]

以下是Excel文件输出 正如您所看到的，dailyPointsArray列为空。 我希望我的Excel文件应该像下面的图片一样

- chirag prajapati

Answer 1

尝试将数组压平：筛选嵌套的数组，按照您想要的顺序获取所需的键。请尝试以下操作：

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

- traynor

Answer 2

你也可以使用 typeof 运算符。

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
    }
}

- Tushar Panja