What are tree diagrams
These are useful for showing hierarchical relationships. Here’s an example and belowAnd 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
- a Label – this will be shown on the chart
- a Key – this is a unique key that identifies each row.
- a Parent Key – this refers to the unique key that is the parent of this row.
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.
Tweaking the format
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.
All code is implemented in the cDataSet.xlsm download. 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
Public Sub testD3Tree() makeD3Tree "d3treeparameters", "d3tree" End Sub Private Sub makeD3Tree(params As String, data As String) Dim ds As New cDataSet, cj As New cJobject, dsOptions As New cDataSet Dim cs As New cSankey, dsParam As New cDataSet, _ js As String, content As String ' generate a d3.js Tree chart from excel data ' get parameters dsParam.populateData wholeSheet(params), , , True, "item" dsOptions.populateData wholeSheet(params), , , True, "options" ' get data values as json js = cj _ .init(Nothing) _ .makeD3Tree(ds.populateData(wholeSheet(data), , , , , , True), _ dsOptions) _ .serialize ' construct the executable file and call the browser content = "" With dsParam content = content & _ .cell("titles", "value").toString content = content & _ .cell("styles", "value").toString content = content & _ .cell("code", "value").toString content = content & _ "</pre> " content = content & _ .cell("banner", "value").toString content = content & _ .cell("body", "value").toString With .cell("htmlname", "value") openNewHtml .toString, content If Not OpenUrl(.toString) Then MsgBox ("could not open " & .toString & " using default browser") End If End With End With End Sub
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)) End If End If If cj Is Nothing Then MsgBox ("could not find " & key & " " & parentkey) Else With cj.add(key) .add "label", label End With End If Set addD3TreeItem = cj End Function 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 Exit Function End If Next dr End Function Private Function cleanDot(s As String) As String '. has special meaning for cJobject so if present in key, then remove cleanDot = makekey(Replace(s, ".", "_ _")) End Function 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, _ cleanDot(dr.cell("label").toString), _ cleanDot(dr.cell("key").toString), _ cleanDot(dr.cell("Parent key").toString) Next dr ' now lets tweak that to a d3 format Set c3 = New cJobject With c3.init(Nothing) ' add an options branch With .add("options") For Each dr In dsOptions.rows If dr.cell("value").toString <> vbNullString Then .add dr.cell("options").toString, _ dr.cell("value").toString End If Next dr End With ' add a branch for data With .add("data") .add "label", dsOptions.cell("root", "value").toString .makeD3 cj.Children(1) End With End With Set makeD3Tree = c3 End Function Public Function makeD3(cj As cJobject) As cJobject Dim cjc As cJobject If cj.hasChildren Then With add("children").addArray.add For Each cjc In cj.Children .makeD3 cjc Next cjc End With Else add cj.key, cj.Value End If Set makeD3 = Me End Function
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.