javascript - Add dynamic columns with xlsx sheetjs - Stack Overflow

admin管理员组

文章数量:1313769

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?

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?

• javascript
• angular
• npm
• sheetjs
• js-xlsx

Share Improve this question Follow edited Apr 15, 2021 at 8:52 Mitul Panchal asked Apr 10, 2021 at 10:08 Mitul PanchalMitul Panchal 28311 gold badge44 silver badges1616 bronze badges 7

• For clarity can you align the sample data with the expected Excel output? – Robin Mackenzie Commented Apr 11, 2021 at 2:20
• Despite a bunch of edits, the data in the array still does not tally with the data in the example Excel format... – Robin Mackenzie Commented Apr 12, 2021 at 10:45
• yes, see my answer below – Robin Mackenzie Commented Apr 13, 2021 at 7:46
• @RobinMackenzie: Thank you so much for the help!!, just a one question in case if we want to format my excel updated table - 2 how can i do that?? – Mitul Panchal Commented Apr 14, 2021 at 10:34
• 1 @RobinMackenzie ,Yes truly it offered working solution and I'd upvoted and accepted the answer Thank you once again :) – Mitul Panchal Commented Apr 15, 2021 at 7:28

 |  Show 2 more ments

1 Answer 1

Sorted by: Reset to default 6

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:

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./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.

本文标签： javascriptAdd dynamic columns with xlsx sheetjsStack Overflow