CODEWITHSUNDEEP
javascriptjsonsheetjs
yeqiuuu
yeqiuuu

Reputation: 147

return JSON object for each row of data in excel

I'm working in webpack for SheetJS. I am relatively new to webpack and a total newbee with SheetJS. Instead of returning one JSON object of the Excel data, I want to return one JSON object for each row of data in the Excel.

Example input excel layout:

Col1     Col2      Col3
A2       B2        C2
A3       B3        C3

Ideal output in this example would be 2 JSON objects:

JSON 1:

{   
 "A2": [
        {
           "Col1": "A2"
           "Col2": "B2"
           "Col3": "C2"
        }
    ]
}

JSON 2:

 {   
    "A3": [
        {
           "Col1": "A3"
           "Col2": "B3"
           "Col3": "C3"
        }
    ]
 }

Attempted solution:

var to_json_linebyline = function to_json_linebyline(wb){
    var sheet = wb.Sheets['Sheet1'];
    var result = {};
    var row, rowNum, colNum;
    var range = XLSX.utils.decode_range(sheet['!ref']);
    for(rowNum = range.s.r; rowNum <= range.e.r-2; rowNum++){
    row = [];
       for(colNum=range.s.c; colNum<=range.e.c; colNum++){
          var nextCell = sheet[
          XLSX.utils.encode_cell({r: rowNum, c: colNum})
       ];
       if( typeof nextCell === 'undefined' ){
          row.push(void 0);
       } else row.push(nextCell.w);
       }
       result[nextCell.v] = row;
    }
    return JSON.stringify(result, 2, 2);
}

Current result:

{
  "Col3": [
    "Col1",
    "Col2",
    "Col3"
  ],
  "C2": [
    "A2",
    "B2",
    "C2"
  ],
  "C3": [
    "A3",
    "B3",
    "C3"
  ]
}

Anything in the right direction'd be awesome. If it helps here's the github repo .. thank you!

Upvotes: 0

Views: 1818

Answers (1)

HymnZzy
HymnZzy

Reputation: 2925

You're intent is right the code is wrong. You were defining and pushing data into arrays instead of creating a JSON object. Try this.

var to_json_linebyline = function to_json_linebyline(wb){
    var sheet = wb.Sheets['Sheet1'];
    var results = [];
    var range = XLSX.utils.decode_range(sheet['!ref']);
    for(let rowNum = (range.s.r+1); rowNum <= range.e.r; rowNum++){
       let thisRow = {},
           thisNode = '';
       
       for(let colNum=range.s.c; colNum<=range.e.c; colNum++){
          var thisHeader = sheet[XLSX.utils.encode_cell({r: 0, c: colNum})].w
          var thisCell = sheet[XLSX.utils.encode_cell({r: rowNum, c: colNum})].w
          if(colNum === 0){ 
            thisNode = thisCell;
          }
          thisRow[thisHeader] = thisCell;
       }
       thisResult = {};
       thisResult[thisNode] = [thisRow]
       results.push(thisResult)
    }
    return JSON.stringify(results);
}

Upvotes: 2

Related Questions