Excel-jSon-Excel in one line of code
On the ramblings site, I set myself the challenge to write some classes that could be invoked to convert an entire Worksheet to jSon, or alternatively, to consume a webservice and populate and entire worksheet in one line of code.
Here we go
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)
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
Hopefully you will see how that works from some of the other jSon examples on the ramblings site, or you can ask me on the forum
