DEV Community

karthik22061993
karthik22061993

Posted on

Cannot read back correct JSON from excel workbook

I am using npm module xlsx to write and read JSON data.

I want to write this JSON to excel
{ "name": "John", "class": 1, "address" : [ { "street": "12th Cross" , "city": "London" }, { "street": "22nd Cross" , "city": "Cade" } ] }

Later when I read back I want to get same JSON from excel file

If you already solved, any suggestion or help will be of great help :)

Here is what I have tried

var XLSX = require("xlsx");
console.log("Node Version: " + process.versions.node);
console.log("XLSX Version: " + XLSX.version);

/* GENERATE TEST FILE */
(function() {
  // create workbook
  var wb = XLSX.utils.book_new();
  var ws = XLSX.utils.json_to_sheet([
    { "name": "John", "class": 1, "address" : [ { "street": "12th Cross" , "city": "London" }, { "street": "22nd Cross" , "city": "Cade" } ] }
    ], {header:["name","class","address","street","city"]});

XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
XLSX.writeFile(wb, "testfile.xlsx");
let worksheet = wb.Sheets['Sheet1'];
let jsonArray= XLSX.utils.sheet_to_json(worksheet);
console.log(JSON.stringify(jsonArray));
})();

This returns

Node Version: 8.12.0
XLSX Version: 0.16.2
[{"name":"John","class":1}]

But I was expecting 
 { "name": "John", "class": 1, "address" : [ { "street": "12th Cross" , "city": "London" }, { "street": "22nd Cross" , "city": "Cade" } ] }

Any help or suggestion will be of great help :)

Top comments (0)