Quick Links

Other stuff

Ask questions, get help, comment, collaborate

Site owners

  • Bruce Mcpherson
Services‎ > ‎Excel Liberation‎ > ‎jSon‎ > ‎

Excel JSON conversion

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, gadgets,  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( _
            "http://dl.dropbox.com/u/14196218/files/jSon1.html" _
            ) _
        ).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 _
        ).jObject.Serialize(True)

The jSon string both created and consumed looks like this - or see it online
  {
   "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

Walkthrough

  • 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

Summary

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.





Comments