const data = [{ deviceId: 1, name: "B相电压", signalTypeId: 1, ts: "2024-05-13 12:10:06", unit: "kV", 1: 39.37936, value:39.37936, },{ deviceId: 1, name: "A相电压", signalTypeId: 2, ts: "2024-05-13 12:11:06", unit: "kV", 1: 39.37936, value:39.37936, },{ deviceId: 1, name: "A相电压", signalTypeId: 2, ts: "2024-05-13 12:11:06", unit: "kV", 2: 39.37936, value:39.37936, },{ deviceId: 1, name: "A相电压", signalTypeId: 3, ts: "2024-05-13 12:12:06", unit: "kV", 3: 39.37936, value:39.37936, },] sheet1.columns = [ { header: '日期', key: 'ts', width: 10 }, { header: "A相", key: '1', width: 32 }, { header: 'B相', key: '2', width: 10 }, { header: 'B相', key: '3', width: 10 } ]; columns中的key值是data对象中的1:39.37936中的key,如何利用exceljs将data数据导出表格实现2024-05-13 12:10:06这一行有A相的数据,2024-05-13 12:11:06这一行有A相和B相两列的值,且日期不能重复
如果要将数据导出到表格中,确保每个日期只显示一次,并根据数据中的列值将其填充到对应的列中,你可以使用以下代码:
const ExcelJS = require('exceljs');
// 示例数据
const data = [{
deviceId: 1,
name: "B相电压",
signalTypeId: 1,
ts: "2024-05-13 12:10:06",
unit: "kV",
1: 39.37936,
value: 39.37936,
}, {
deviceId: 1,
name: "A相电压",
signalTypeId: 2,
ts: "2024-05-13 12:11:06",
unit: "kV",
1: 39.37936,
value: 39.37936,
}, {
deviceId: 1,
name: "A相电压",
signalTypeId: 2,
ts: "2024-05-13 12:11:06",
unit: "kV",
2: 39.37936,
value: 39.37936,
}, {
deviceId: 1,
name: "A相电压",
signalTypeId: 3,
ts: "2024-05-13 12:12:06",
unit: "kV",
3: 39.37936,
value: 39.37936,
}];
// 创建一个工作簿
const workbook = new ExcelJS.Workbook();
// 添加一个工作表
const sheet1 = workbook.addWorksheet('Sheet1');
// 设置列
sheet1.columns = [
{ header: '日期', key: 'ts', width: 20 },
{ header: "A相", key: 'A相', width: 20 },
{ header: 'B相', key: 'B相', width: 20 },
{ header: 'C相', key: 'C相', width: 20 }
];
// 使用对象存储每个日期的数据
const rowDataMap = {};
data.forEach(rowData => {
const ts = rowData.ts;
if (!rowDataMap[ts]) {
rowDataMap[ts] = { ts };
}
const columnKey = rowData.signalTypeId.toString();
rowDataMap[ts][rowData.name] = rowData[columnKey] || '';
});
// 填充数据
Object.values(rowDataMap).forEach(rowData => {
sheet1.addRow(rowData);
});
// 保存工作簿到文件
workbook.xlsx.writeFile('data.xlsx')
.then(() => {
console.log('Excel 文件已生成');
})
.catch(err => {
console.error('保存 Excel 文件时出错:', err);
});