Create a treeview from json

What can you learn here?

• Populate a treeview
• Use a cjobject
• Serialize anything to a treeview

How to turn a cJobject into a treeview get it now

Since the cJobject is just a tree type structure, it is very simple to turn in into a treeview to display it on an Excel User Form. This means that anything on this site that has been represented as a cJobject can be turned into a treeview in one line of code.

 

Try it out first ?

These are enhancements to Data Manipulation Classes so they have been included in cDataSet.xlsm, which is downloadable from Download Complete Projects  

The Userform

A treeview user control is normally on a form. Here is one that takes the Rest to Excel library and shows it on a form.

 

 

 

 

How it’s done

 

The cRest library nowadays is itself kept as a cJobject, and can be retrived with the createRestLibrary() function. To create the form above, you simply have to create a user form, add a treeView control and execute this (where the name of your control is trcJobject.

 

Private Sub UserForm_Initialize()
    createRestLibrary().toTreeView trcJobject
End Sub  

cJobject, cDataset and treeview

 

In Excel JSON conversion I covered how to convert a worksheet to a cJobject and serializing it in jSon. There are many examples of this kind of conversion on this site, and with the use Data Manipulation Classes a cJobject is simply a property of abstracted data. This means that anything that you can put in a cDataSet can be converted to a treeview in the same one line of code.

Execute SQL from Excel covered how to use SQL to get data from multiple places such as an external database, a closed worksheet and so on. Since the data is processed as a cDataSet, we can easily show it on a form as above. This simple code below will pick up data using SQL, do a filter on it, convert it to a cJobject and populate a treeview

 

Private Sub UserForm_Initialize()
    Dim ado As New cADO
    ado.init(Range("testAdo!a1")).execute _
        ("tweetsentiments", , "where value=1").dSet.jObject.toTreeView trcJobject
End Sub  

 

Code for the toTreeView method

 

This has been implemented in the cJobject class that can be found in the cDataSet.xlsm file from Download Complete Projects. Because the cJobject class is itself a tree, the conversion to a treeview control is beautifully simple and recursive as below.

 

Public Function toTreeView(tr As TreeView) As TreeView
    ' this populates a treeview with a cJobject
    Set toTreeView = treeViewPopulate(tr, Me)
End Function

Private Function treeViewPopulate(tr As TreeView, cj As cJobject, Optional parent As cJobject = Nothing)
    Dim c As cJobject, s As String
    s = vbNullString
    If cj.hasChildren Then
        s = cj.key
    Else
        s = cj.key + " : " & cj.toString
    End If
    If (Not parent Is Nothing) Then
        tr.Nodes.add parent.fullKey, tvwChild, cj.fullKey, s
    Else
        tr.Nodes.add(, , cj.fullKey, cj.key).Expanded = True
    End If
    For Each c In cj.Children
        treeViewPopulate tr, c, cj
    Next c
    Set treeViewPopulate = tr
End Function

  Go back to Excel to JSON topics