Excel to JSON

What is it?

Data stored as tables in Excel is converted by a VBA macro to JSON format.

How to use it?

• The data on Excel worksheets must be stored in accordance with the rules
• Specify 2 options on the Instruction worksheet:
  • name of the file eg. data.json
  • path to save the file eg. C:\data\
• Press EXCEL → JSON button and that's it!

Demo

Data storage rules

1. ROOT worksheet is the place where JSON creation starts
2. Every worksheet contains a table (set of rows), 1 row = 1 object:
   • the 1st row of the table (headers) contains keys of objects
   • the other rows in the table contains values assosiated with the header (key) from the same column
3. 1 worksheet (table) = 1 object / array of objects
   • if there is only 1 row it's an object
   • if there are more than 1 row it's an array of objects
4. Every cells in the tables could contain one of the following:
   • Any text value (the JSON format stores all values as strings)
   • Array of text values:
     • use the square brackets to identify an array → [ ... ]
     • inside the brackets the array items goes separated by comma → [ item1 , item2 , item3 ]
   • Another object / array of objects:
     • use the curly brackets to identify an object → { ... }
     • in fact, this is a link to another worksheet (see section 3)
     • inside the brackets put worksheet's name → { worksheet name }
     • every row from specified worksheet will be converted to an object
5. Limitations:
   • directly nested arrays currently is not supported → [ item1 , [ ... ] , item3 ]
   • it's OK to put an object inside an array → [ { ... } ]