如何在nodejs中将嵌套的JSON转换为Excel

Question

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

尝试过的代码

 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 文件应该如下图所示

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

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