Sheetjs: xlsx to nested JSON

[nicolasgirado]

Is it possible to read a xlsx file and convert it to a nested JSON? (i'm using node.js)

For example, this table:

Name | City | Country
Jon | New York | USA
Jon | Kansas | USA
Peter | Nevada | USA

to this JSON:

{
   "Name": "Jon",
   "Addresses": [
              {"City": "New York", "Country": "USA"},
              {"City": "Kansas", "Country": "USA"}
           ]
},
{
   "Name": "Peter",
   "Addresses": [
              {"City": "Nevada", "Country": "USA"}
      ]
}

[RandomFractals]

how would xlsx or any other library know how you'd like to nest your flat md data in exported json properties?

You can try to mock it up with Data Preview 🈸 vscode extension that supports xlsx files loading, md tables parsing and save as json + grouping :)

https://github.com/RandomFractals/vscode-data-preview

[RandomFractals]

otherwise, just transform the json you get from xlsx sheet to json to the structure you prefer looping over json array records and adding/modifying/deleting flat data object properties. should be aesy custom forEach loop :)

[RandomFractals]

@nicolasgirado see how I handle it in my excel.data.provider for example and just add your json transform structure as needed :)

https://github.com/RandomFractals/vscode-data-preview/blob/master/src/data.providers/excel.data.provider.ts

[ahmad-reza619]

well it's pretty good example, but i can't really understand this (sorry, just a beginner programmer lol) its just look the same as in docs @RandomFractals , i have similar problem, but i want to write an xlsx for it instead of reading it

[RandomFractals]

@ahmad-reza619 see saveData() to convert json to one of the supported Excel data formats with js-xlsx library and write it to disk with nodejs fs (file system api): https://github.com/RandomFractals/vscode-data-preview/blob/master/src/data.providers/excel.data.provider.ts#L108

[ahmad-reza619]

OK thanks for explanation 👍 @RandomFractals