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")
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
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")
Summary
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 .