CODEWITHSUNDEEP
javascriptnode.jsjsonexcelnpm
karansys
karansys

Reputation: 2719

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 :)

Upvotes: 1

Views: 69

Answers (1)

bsaverino
bsaverino

Reputation: 1285

Your json given to XLSX.utils.json_to_sheet() seems incorrect:

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

Will fixing this address your conversion issue?

Suggestion:

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

Note: first and last brackets may be optional.

Keep me posted!

Upvotes: 1

Related Questions