The process being followed in the code below is:
- Transform the data by arranging the
id and data properties from each object into a long list
- Add an
order property which is the number at the end of the id e.g. 1 for tagID1
- Sort that new array by
Timestamp then order - this may be unnecessary if your data is already in that order
- Parse out the headers and create pairs of
tagIDN quality and tagIDN value
- Cast the data into a wide format by taking unique timestamps and creating 1 row per timestamp with as many column pairs as there are tags
- Steps 4 and 5 are creating an array of arrays which can be passed to the XLSX method
XLSX.utils.aoa_to_sheet
- Because those long timestamps will be converted to scientific notation by Excel, set them to a number format of
0
- Create a workbook, insert a sheet with the method from step 6 and save
Working code:
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 output:
Edit
To have double headers with merged cells on first row (for tag ids) - see the update:
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 output:
The method is per this post.
