Get Excel data in and out of your workbook with jSon

A common theme in many of the articles I have written for this excellent site, is the capability to release data from Excel and Outlook for visualization and other uses. Continuing with that theme, here is how to get data in and out of your workbook by converting to and from jSon. As usual there are downloable examples and VBA classes you can use in your own workbooks.

Further abstraction of layout

In a previous article, I covered the topic of data abstraction, and how to separate the physical workbook location from processing the data it contains. Taking it one step further, let's look at converting that data to and from other formats, so that the abstraction is also one of encoding structure as well as location. Whereas Excel does like XML, (with an implementation that is wearisomely complex in my view), it does not have a jSon capability, which is fundamental in other more capable languages. In this article I will demonstrate the use of jSon serialization classes for the Excel workbook object, all of which you can download for your own use.

Serialing the abstracted data

Just as in the data abstraction article we will be using the cDataSet object to interact with our excel worksheet. However we will also provide a capability to translate this cDataSet to and from a cJobject object. This provides jSon serialization and deserialization methods, with which we can take jSon feeds or provide jSon strings for use with javaScript or or other languages. It also gives the building blocks to be able to take feeds directly from Google Docs, which will be the subject of a subsequent article.  However, jSon is a data representation framework, not a structure, so we still have to decide how to layout (and expect to receive) the Excel data.

Consider the following table:

It will be represented as an array of key/value pair rows in jSon as follows. The cDataSet class will generate and expect this kind of structure.

{"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"} ]

Implementatation details
Using the Data Abstraction tools and the cJobject class, this is a very straightforward implementation. Here is the test module you will find 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")
            ' 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

This test was just to excercise the various new capabilities. Let's take a look at what's happening.

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 to see what it looks like
            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.
       Set jo = New cJobject
          Range("text!$b$1").value = jo.deSerialize(dsClone.jObject.Serialize).Serialize

The .jObject property

Now the cDataSet object has a new property, .jObject, which is a heirarchical representation of its data returned as a cJobject. Although we are using it to represent a simple table here, this object can hold an arbitrary structure of any depth of heirarchy.

Public Property Get jObject(Optional jSonConv As eJsonConv = eJsonConvPropertyNames) as cJobject

    Dim dr As cDataRow, dh As cCell, dc As cCell, cr As cJobject, ca As cJobject
    ' create serializable 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")


You can download these examples and other stuff here. All that is needed for these examples are in the cDataSet download. My next article on this topic will cover how to use these techniques and classes to directly take a feed into Excel from a googledocs spreadsheet using the google wire protocol. As always I appreciate your comments, questions or anything else on this forum or here .

By bruce mcpherson   Popularity  (5344 Views)