javascriptangularnpmsheetjsjs-xlsx

Add dynamic columns with xlsx/ sheetjs

I have an array of multiple tags with ids and 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]
    ]
  }
]

I want to transform this data into an Excel spreadsheet with the xlsx package from NPM.

Note: 1604395417575 is timestamp, 608 is value, 3 is quality.

I want to show in Excel sheet as in the below format

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

Update Sheet Format

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

I'm new to XLSX (aka SheetJS) - how can I do it?

Solution

  • The process being followed in the code below is:

    1. Transform the data by arranging the id and data properties from each object into a long list
    2. Add an order property which is the number at the end of the id e.g. 1 for tagID1
    3. Sort that new array by Timestamp then order - this may be unnecessary if your data is already in that order
    4. Parse out the headers and create pairs of tagIDN quality and tagIDN value
    5. 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
    6. Steps 4 and 5 are creating an array of arrays which can be passed to the XLSX method XLSX.utils.aoa_to_sheet
    7. Because those long timestamps will be converted to scientific notation by Excel, set them to a number format of 0
    8. 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:

    enter image description here

    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:

    enter image description here

    The method is per this post.