These are useful for showing hierarchical relationships. Here's an example
And for those with capability challenged browsers (IE mainly), here's a screenshot.
It's rather straightforward to set up the data structure for this chart in excel. Here is some sample data - you'll find this in the d3Tree tab of cDataSet.xlsm , downloadable from Project Downloads. All you need is
The depth and complexity of the tree is up to you - as long as you define the keys uniquely.
To create a d3 Tree diagram, you simply need to construct a table as above. If your want to automate creating data in this format, take a look at an easy way to transform it in this post.
Most of this you wont need to touch, but you can tweak the style (these are css style definitions) and header parameters to affect the layout and content of the final web page. The options tweak the tree layout, and I will add some more at some point. It is also possible to change the operation of the chart if you are comfortable with d3.js - you can find this in the code parameter. The generated filename is set with the htmlName parameter. Some directories on your machine may be readonly to you - for example if you have downloaded this and run it from the download directory on windows 8 you may not be able to create a file in that directory because of local permission settings. You should set this to a directory you have permission to create files in. This is a single file with no dependencies (except to the d3.js library) and you can put it on a web site or mail it to someone as you wish. Note that IE8 and below may not work with d3.js. Chrome is best and Firefox works too.
Here is the calling procedure to generate and execute the chart code.The cJobject class has been extended to be able to transform the hierarchical data with parent/child keys into jSon format suitable for consumption by d3.js
Here are the additional cJobject methods
Public Function addD3TreeItem(ds As cDataSet, label As String, key As String, parentkey As String) As cJobject
Dim cj As cJobject, dr As cDataRow
' does parent key exist?
Set cj = Find(parentkey)
If (cj Is Nothing) Then
Set dr = findD3Parent(ds, parentkey)
If Not dr Is Nothing Then
Set cj = addD3TreeItem(ds, label, parentkey, cleanDot(dr.cell("Parent key").toString))
If cj Is Nothing Then
MsgBox ("could not find " & key & " " & parentkey)
.add "label", label
Set addD3TreeItem = cj
Private Function findD3Parent(ds As cDataSet, parentkey) As cDataRow
Dim dr As cDataRow
For Each dr In ds.rows
If cleanDot(dr.cell("key").toString) = parentkey Then
Set findD3Parent = dr
Private Function cleanDot(s As String) As String
'. has special meaning for cJobject so if present in key, then remove
cleanDot = makekey(Replace(s, ".", "_ _"))
Public Function makeD3Tree(ds As cDataSet, dsOptions As cDataSet) As cJobject
' this one will take a list of Name/Parents and make a structured cJobject out of it
Dim dr As cDataRow, cj As cJobject, parent As String, name As String, c3 As cJobject
Const container = "contents"
If Not ds.headingRow.validate(True, "Label", "Parent Key", "Key") Then Exit Function
Set cj = add("D3Root")
For Each dr In ds.rows
cj.addD3TreeItem ds, _
' now lets tweak that to a d3 format
Set c3 = New cJobject
' add an options branch
For Each dr In dsOptions.rows
If dr.cell("value").toString <> vbNullString Then
.add dr.cell("options").toString, _
' add a branch for data
.add "label", dsOptions.cell("root", "value").toString
Set makeD3Tree = c3
Public Function makeD3(cj As cJobject) As cJobject
Dim cjc As cJobject
If cj.hasChildren Then
For Each cjc In cj.Children
add cj.key, cj.Value
Set makeD3 = Me
Why not join our forum,follow the blog or follow me on twitter to ensure you get updates when they are available. You can find out more about other d3.js visualizations from Excel here.
You want to learn Google Apps Script?
Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly.
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > d3.js >