2

I am trying to make a transpose of JSON and save it to excel sheet:

function transpose(a) {
    return Object.keys(a[0]).map(function(c) {
        return a.map(function(r) { return r[c]; });
    });
}

//example for JSON (in the program I get a long JSON in this shape)
const sheet_arr = [{'param1': 1, 'param2': 2},{'param1': 3, 'param2': 4},{'param1': 5, 'param2': 6}] 
 
var temp_ws = xls.utils.json_to_sheet(transpose(sheet_arr));
wb.Sheets['test'] = temp_ws;

In the excel sheet I am getting:

 __________________
|  0  |  1  |  2  |
-------------------
|  1  |  3  |  5  | 
|  2  |  4  |  6  | 
-------------------

I Want to get this:

 _____________________________
|  param1  |  1  |  3  |  5  |
|  param2  |  2  |  4  |  6  |
------------------------------

how can I get it easily?

Dean Taler
  • 737
  • 1
  • 10
  • 25

1 Answers1

2

This should get you the content you want:

function transpose(a) {
    return Object.keys(a[0]).map(function(c) {
      let ret=a.map(function(r) { return r[c]; });
      ret.unshift(c); return ret;
    });
};

// or in ES6 notation:
const transp = a => Object.keys(a[0]).map(c=>{
  let ret=a.map(r=>r[c]); ret.unshift(c); 
  return ret; });

//example for JSON (in the program I get a long JSON in this shape)
const sheet_arr = [{'param1': 1, 'param2': 2},{'param1': 3, 'param2': 4},{'param1': 5, 'param2': 6}] ;

console.log(transpose(sheet_arr));
console.log(transp(sheet_arr));
.as-console-wrapper {max-height:100% !important}

I simply add the key as the first element in each row, using Array.unshift().

Carsten Massmann
  • 26,510
  • 2
  • 22
  • 43
  • thanks! it is still showing a first line of index ( 0 1 2 3), how can I remove it from the JSON/Ecxel so it like exactly as wanted? – Dean Taler Aug 03 '20 at 12:42
  • 1
    That must be happening in the `xls.utils.json_to_sheet()` call. I have not done anything with `xls.utils` yet but maybe the following will help you? https://stackoverflow.com/questions/47424755/sheetjs-do-not-include-headers-in-json-to-sheet – Carsten Massmann Aug 03 '20 at 15:03
  • thanks, the right way is `var temp_ws = xls.utils.json_to_sheet(transpose(sheet_arr), {skipHeader: 1});` – Dean Taler Aug 04 '20 at 12:16