Sheetjs: Get JSON Object which has first column as Headers

Created on 28 Sep 2017 · 5Comments · Source: SheetJS/sheetjs

Hi All,

The current sheet_to_json function is only considers rows as headers.

var jsonObj = XLSX.utils.sheet_to_json(worksheet, {raw: true,header:0});

Is it possible to get the JSON object for the sheet having first column as header?

Attached the sample excel below.

Regards,
Sathish.
Col_Headers.xlsx

Source

sathish804

Most helpful comment

If you convert with header:1, the output format will be an array of arrays, like

data = [ 
  ["a", 1, 2],
  ["b", 3, 4],
  ["c", 5, 6]
]

That can be manually converted to an array of objects based on columns:

var max = d.reduce((x,y) => Math.max(x,y.length), 0)
var o = new Array(max-1);
for(var i = 0; i < max-1; ++i) o[i] = {};
data.forEach(row => { row.slice(1).forEach((elt, i) => { o[i][row[0]] = elt }); });

// now o will be [ { a: 1, b: 3, c: 5 }, { a: 2, b: 4, c: 6 } ]

SheetJSDev on 28 Sep 2017

🚀2 🎉2 😄1

All 5 comments

If you convert with header:1, the output format will be an array of arrays, like

data = [ 
  ["a", 1, 2],
  ["b", 3, 4],
  ["c", 5, 6]
]

That can be manually converted to an array of objects based on columns:

var max = d.reduce((x,y) => Math.max(x,y.length), 0)
var o = new Array(max-1);
for(var i = 0; i < max-1; ++i) o[i] = {};
data.forEach(row => { row.slice(1).forEach((elt, i) => { o[i][row[0]] = elt }); });

// now o will be [ { a: 1, b: 3, c: 5 }, { a: 2, b: 4, c: 6 } ]

SheetJSDev on 28 Sep 2017

🚀2 🎉2 😄1

Hi SheetJSDev, Thank you for the quick answer.

I modified the sheet_to_json with extra parameter to create the json object on column headers.

if(isColHeader){
        var max = out.reduce((x,y) => Math.max(x,y.length), 0)
        var o = new Array(max-1);
        for(var i = 0; i < max-1; ++i) o[i] = {};
        out.forEach(row => { row.slice(1).forEach((elt, i) => { o[i][row[0]] = elt }); });
        return o;
    }else{

    return out;

    }

But the output is not similar as you mentioned above.

[["a",1,2],["b",3,4],["c",5,6]]

Please advise what I am doing wrong here.

Regards,
Sathish.

sathish804 on 29 Sep 2017

https://jsfiddle.net/1jwkyx13/ is a complete example starting from a workbook object generated from a CSV:

/*
  External Resources:
  -  https://unpkg.com/xlsx/dist/xlsx.full.min.js
*/

/* start with a worksheet */
var original_csv = "a,1,2\nb,3,4\nc,5,6";
var wb = XLSX.read(original_csv, {type:'string'});
var ws = wb.Sheets[wb.SheetNames[0]];

/* convert to array-of-arrays */
var data = XLSX.utils.sheet_to_json(ws, {raw:true, header:1});

/* convert to column-major objects */
var max = data.reduce(function (x, y) { return Math.max(x, y.length); }, 0);
var o = new Array(max-1);
for(var i = 0; i < max-1; ++i) o[i] = {};
data.forEach(function (row) { row.slice(1).forEach(function (elt, i) { o[i][row[0]] = elt; }); });

/* display the result */
console.log(o);

SheetJSDev on 30 Sep 2017

Thank You!!

sathish804 on 3 Oct 2017

Hi,
I have XL sheet where some column names are like(Area,Comarch),I want to convert them into (area,comarch).
I want to do this for particular column names not for all.
any suggestion how should I do it..?? (I am new in Angular)

Thanks