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( _
"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:
{
"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
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
{"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.