javascript - 使用 xlsx/sheetjs 添加动态列

Question

我有一个带有 id 和数据的多个标签数组:

[
  {
    "id": "tagID1", 
    "error": { "code": 0, "success": true }, 
    "data": [
      [1604395417575, 108, 3], 
      [1604395421453, 879, 3]
    ]
  },
  {
    "id": "tagID2", 
    "error": {"code": 0, "success": true}, 
    "data": [
      [1604395417575, 508, 3], 
      [1604395421453, 179, 3]
    ]
  }
]

我想用 xlsx 将此数据转换为 Excel 电子表格来自 NPM 的软件包。
注意:1604395417575 是时间戳，608 是值，3 是质量。
我想在 Excel 表中显示如下格式

| Timestamp    |  tagID1 value  | tagID1 quality | tagID2 value | tagID2 quality|
| --------     | -------------- | -------- ------| -------------| ------------- |
| 1604395417575| 108            |   3            |     508      |   3           |
| 1604395421453| 879            |   3            |     179      |   3           |
    

更新图纸格式

|----------------------------------------------------------
| Timestamp    |     TagID-1          |   TagID-2         | 
|              | ----------------------------------------
|              | value   | quality    | value   | quality |  
|----------------------------------------------------------
| 1604395417575|  108    |     3     |  508     |   3     |
| 1604395421453|  879    |     3     |  179     |   3     |

我是 XLSX(又名 SheetJS)的新手——我该怎么做？

Answer 1

以下代码中遵循的过程是:

通过排列 id 转换数据和 data从每个对象的属性到一个长列表

添加 order属性，它是 id 末尾的数字例如1对于 tagID1

按 Timestamp 对新数组进行排序然后 order - 如果您的数据已经在该顺序中，这可能是不必要的

解析标题并创建 tagIDN quality 对和 tagIDN value

通过获取唯一的时间戳并为每个时间戳创建 1 行，使用与标签数量一样多的列对将数据转换为宽格式

第 4 步和第 5 步是创建一个数组数组，可以将其传递给 XLSX 方法 XLSX.utils.aoa_to_sheet

因为这些长时间戳会被 Excel 转换为科学计数法，所以将它们设置为 0 的数字格式。

创建工作簿，使用步骤 6 中的方法插入工作表并保存

工作代码:

const XLSX = require("xlsx");

// input data
const input_data = [
  {
    "id": "tagID1", 
    "error": { "code": 0, "success": true }, 
    "data": [
      [1604395417575, 108, 3], 
      [1604395421453, 879, 3]
    ]
  },
  {
    "id": "tagID2", 
    "error": {"code": 0, "success": true}, 
    "data": [
      [1604395417575, 508, 3], 
      [1604395421453, 179, 3]
    ]
  }
];

// data transforms
// 1st transform - get long array of objects
const prep = input_data.map(obj => {
  return obj.data.map(arr => {
    return {
      "TimeStamp": arr[0],
      "id": obj.id,
      "order": +obj.id.substr(5, obj.id.length - 5),
      "quality": arr[1],
      "value": arr[2]
    }
  });
}).flat();

// sort by timestamp asc, order asc
prep.sort((a, b) => a.TimeStamp - b.TimeStamp || a.order - b.order);

// headers
const headers = ["Timestamp"].concat(
  [...new Set(prep.map(obj => obj.id))]
    .map(id => [`${id} quality`, `${id} value`])
    .flat()
);

// organise the data - in wide format
const ws_data = [...new Set(prep.map(obj => obj.TimeStamp))]
  .map(ts => {
    const objByTimestamp = prep.filter(obj => obj.TimeStamp === ts);
    let arr = [ts];
    objByTimestamp.forEach(obj => arr = arr.concat([obj.quality, obj.value]));
    return arr;
  });

// prepend the headers
ws_data.unshift(headers);

// to Excel
// new workbook
const wb = XLSX.utils.book_new();

// create sheet with array-of-arrays to sheet method
const ws = XLSX.utils.aoa_to_sheet(ws_data);

// assign sheet to workbook
XLSX.utils.book_append_sheet(wb, ws, "Sheet1");

// set column A as text
const range = XLSX.utils.decode_range(ws['!ref']);
console.log(range);
for (let i = range.s.r; i <= range.e.r; i++) {
  const ref = XLSX.utils.encode_cell({r: i , c: 0});
  console.log(ref);
  ws[ref].z = "0";
}

// save workbook
XLSX.writeFile(wb, "C:\\Users\\Robin\\Desktop\\so.xlsx", {});

Excel输出:

编辑
要在第一行有合并单元格的双标题(用于标签 ID) - 请参阅更新:

const XLSX = require("xlsx");

// input data
const input_data = [
  {
    "id": "tagID1", 
    "error": { "code": 0, "success": true }, 
    "data": [
      [1604395417575, 108, 3], 
      [1604395421453, 879, 3]
    ]
  },
  {
    "id": "tagID2", 
    "error": {"code": 0, "success": true}, 
    "data": [
      [1604395417575, 508, 3], 
      [1604395421453, 179, 3]
    ]
  }
];

// data transforms
// 1st transform - get long array of objects
const prep = input_data.map(obj => {
  return obj.data.map(arr => {
    return {
      "TimeStamp": arr[0],
      "id": obj.id,
      "order": +obj.id.substr(5, obj.id.length - 5),
      "quality": arr[1],
      "value": arr[2]
    }
  });
}).flat();

// sort by timestamp asc, order asc
prep.sort((a, b) => a.TimeStamp - b.TimeStamp || a.order - b.order);

// headers
// const headers = ["Timestamp"].concat(
//   [...new Set(prep.map(obj => obj.id))]
//     .map(id => [`${id} quality`, `${id} value`])
//     .flat()
// );
const ids = [...new Set(prep.map(obj => obj.id))];
const headers1 = [""].concat(ids.map(id => Array(2).fill(id)).flat());
const headers2 = ["Timestamp"].concat(ids.map(id => Array(["quality", "value"])).flat()).flat();

// organise the data - in wide format
const ws_data = [...new Set(prep.map(obj => obj.TimeStamp))]
  .map(ts => {
    const objByTimestamp = prep.filter(obj => obj.TimeStamp === ts);
    let arr = [ts];
    objByTimestamp.forEach(obj => arr = arr.concat([obj.quality, obj.value]));
    return arr;
  });

// prepend the headers
ws_data.unshift(headers2);
ws_data.unshift(headers1);

// to Excel
// new workbook
const wb = XLSX.utils.book_new();

// create sheet with array-of-arrays to sheet method
const ws = XLSX.utils.aoa_to_sheet(ws_data);

// assign sheet to workbook
XLSX.utils.book_append_sheet(wb, ws, "Sheet1");

// set column A as text
const range = XLSX.utils.decode_range(ws['!ref']);
for (let i = range.s.r; i <= range.e.r; i++) {
  const ref = XLSX.utils.encode_cell({r: i , c: 0});
  ws[ref].z = "0";
}

// assign merges to sheet
// https://stackoverflow.com/questions/53516403/sheetjs-xlsx-how-to-write-merged-cells
const merges = ids.reduce((acc, curr, idx) => {
  acc.push({
    s: {r: 0, c: 1 + (2 *idx)},
    e: {r: 0, c: 1 + (2 *idx) + 1}
  });
  return acc;
}, []);
ws["!merges"] = merges;

// save workbook
XLSX.writeFile(wb, "C:\\Users\\Robin\\Desktop\\so.xlsx", {});

Excel输出:

方法是按这个 post .