Freeing Excel Data through JSON

There have been a number of topics on this site dealing with the serialization of JSON, for example to create input to google visualizations,  Hiding data in Excel Objects , Integrating Excel with Maps and Earth and a whole bunch of other Web Services. Whereas XML is built into Excel (although way too complicated in my opinion), JSON is not, so I keep having to write various encoding code. Since I already have abstracted the data away from physical Excel location through the use of data abstraction classes, the next step is to provide a way to convert between cDataSet and cJobject – in other words to be able to serialize and deserialize worksheets and JSON data.  The  cJobject class has now been implemented as a class of the cDataSet family and can be downloaded in CDataSet.xlsm.  

Are you in the right place ?


If you are looking to populate Excel from general Rest queries (the most likely reason for needing to use jSon with Excel), then visit the Rest to Excel library. Although it uses the same classes as are described here, it might be a better place for you to start. See this blog entry for why.

Excel-jSon-Excel in one line of code

Before we get started though, I set myself the challenge that these classes should be able to populate an entire worksheet from a web service, or to convert an entire worksheet into jSon in one line of code. Why ? because it will ensure that every data structure and object is compatible and can work together.

Here we go.. (both these examples are in the cDataSet.xlsm download in the popWebServces module)


Get a jSon response from a web service and populate a worksheet with the result.

      Dim dSet As New cDataSet, cb As New cBrowser, jo As New cJobject       dSet.populateJSON jo.deSerialize( _         cb.httpGET( _             "" _             ) _         ).Child("cDataSet"), Range("json1!$a$1")  

create and print a jSon string from a worksheet.

    Dim dSet As New cDataSet

    Debug.Print dSet.populateData( _
        Range("jSon2!$a$1"), , , , , , True _


The jSon string both created and consumed looks like this:

    {    "cDataSet":[      {          "Customer":"Acme",          "Contact":"john",          "Total":"100",          "Country":"US"       },       {          "Customer":"Smiths",          "Contact":"fred",          "Total":"460",          "Country":"UK"       },       {          "Customer":"Jones",          "Contact":"joe",          "Total":"24",          "Country":"US"       },       {          "Customer":"Renault",          "Contact":"Marie",          "Total":"536",          "Country":"FRANCE"       },       {          "Customer":"Schneider",          "Contact":"Hans",          "Total":"1334",          "Country":"Germany"       }     ]  }


The Excel sheet that gets created looks like this


  Hopefully you will see how that works from some of the other examples in this section, or you can ask me on the forum  

Format of JSON


The serializer and deserializer provided can deal with most JSON structures, but here we are going to look at specifically how a rowwise Excel sheet might be represented as JSON. Consider this table  


There are a number of ways to represent this, but I’m choosing row -wise with an array of a list of key/value pairs. The resultant serialization, then, looks like this.     {"cDataSet":[{"Customer":"Acme","Price":"100", "Quantity":"1","Country":"US","Contact":"john", "Total":"100"},{"Customer":"Smiths","Price":"20","Quantity":"23", "Country":"UK","Contact" :"fred","Total":"460"},{"Customer":"Jones","Price":"12","Quantity":"2", "Country":"US","Contact":"joe","Total":"24"},{"Customer":"Renault","Price":"67","Quantity":"8", "Country":"FRANCE","Contact":"Marie","Total":"536"},{"Customer":"Schneider","Price":"23","Quantity":"58", "Country":"Germany","Contact":"Hans","Total":"1334"} ] }  

How to create


Using the Data Abstraction tools and the cJobject class, this is a very straightforward problem to solve. Here is the test module in the cDataSet downloadable example to exercise these new capabilities.     Option Explicit Public Sub jobjectExample()     Dim dSet As cDataSet, dsClone As cDataSet, jo As cJobject          Set dSet = New cDataSet          With dSet         .populateData Range("orders!$a$1"), , , , , , True                  If .Where Is Nothing Then             MsgBox ("No data to process")         Else             ' make a json object of one sheet, and the deJSon it in a clone             Set dsClone = New cDataSet             dsClone.populateJSON .jObject, Range("Clone!$A$1")                          ' show the result of a serialization             Range("text!$a$1").value = .jObject.Serialize                          ' excercise the whole thing - take the clone dataset, serialize it, desrialize it and serialize it again             Set jo = New cJobject             Range("text!$b$1").value = jo.deSerialize(dsClone.jObject.Serialize).Serialize                      End If     End With   End Sub




  • Load the worksheet into the data abstraction class, and populate a cDataSet
          .populateData Range("orders!$a$1"), , , , , , True  
  • Create another cDataSet and this time populate it by using the cJobject representation of the original, and output that to another worksheet
              Set dsClone = New cDataSet

            dsClone.populateJSON .jObject, Range("Clone!$A$1")

  • Serialize the original object into Json text
              ' show the result of a serialization             Range("text!$a$1").value = .jObject.Serialize  
  • Take the cJobject representation of the cloned sheet, serialize it, deserialize the result, and serialize again. Hopefully, the final result should be exactly the same as in the previous step.
              ' excercise the whole thing - take the clone dataset, serialize it, desrialize it and serialize it again      Set jo = New cJobject      Range("text!$b$1").value = jo.deSerialize(dsClone.jObject.Serialize).Serialize  

The .Jobject property


This returns a representation of the cDataSet as cJobJect.     Public Property Get jObject(Optional jSonConv As eJsonConv = eJsonConvPropertyNames)       ' convert dataset to a JSON string     Dim dr As cDataRow, dh As cCell, dc As cCell, cr As cJobject, ca As cJobject          ' create serialization object     Dim cj As cJobject     Set cj = New cJobject       ' so far only implemented the property names conversion     Debug.Assert jSonConv = eJsonConvPropertyNames     cj.init Nothing, pName          Set cr = cj.add("cDataSet").AddArray     For Each dr In Rows         With cr.add             For Each dc In dr.Columns               Set dh = HeadingRow.Collect(dc.Column)               .add dh.toString, dc.toString             Next dc         End With     Next dr     ' return from branch where data starts     Set jObject = cj.Child("cDataSet")      End Property  



Together, this data abstraction coupled with JSON conversion capability frees up spreadsheet data. The next step will be to collect JSON feeds to populate excel tables. A properly formatted JSON data feed will easily be converted to a dataset and worksheet as follows.   Set dsClone = New cDataSet

Set jo = new cJobject

dsClone.populateJSON jo.deSerialize(datafeedString), Range("Clone!$A$1")

  See Create a treeview from json to take it one step further and visualize your jSon string or worksheet in just one line of code.