Developer information for d3.js force diagrams

To develop Excel applications that generate web pages incorporating d3.js force diagrams from Excel data, first read this Excel tables to d3.js Force diagram. You will also need to be familiar with How to use cDataSet and How to use cJobject This section will walk through the coding of a sample application.
 
All the code for this can be found in the cDataSet.xlsm, downloadable from Downloads
Page Content hide
1 D3 Module
2 d3ForceHere
3 d3ForceDo
4 mashupGeneral
5 mashD3Force
6 Here is the whole thing
7 Summary

D3 Module

This contains examples of applications. 

d3ForceHere

This would normally be assigned to a button on the worksheet containing the table you want to chart.  It creates a web using the data on this page, using the default parameter block names
Public Sub d3forceHere()
    d3ForceDo
End Sub

d3ForceDo

This can be called with a specific worksheet name, and will run the e2e process on the data in the given worksheet
Public Sub d3ForceDo(Optional wn As String = vbNullString, _
            Optional optionName As String = "force options", Optional fieldName As String = "force fields")
    Dim w As String
    w = wn
    If w = vbNullString Then
        w = ActiveSheet.name
    End If
    mashupGeneral "d3allparameters", w, "force", optionName, fieldName
End Sub

mashupGeneral

Given a data worksheet name and parameter sheet name, it will run the end to end process and create and display a web page. Here are the main activities you can use a template
  • create a collection of dsets and add each of the parameter blocks and data.
 Set dsets = New cDataSets
   ' get all parameters and data
    With dsets.create()
  • parse out the parameters to discover which columns are needed for what purpose
With .init(wholeSheet(params), , "fields", True, fieldName)
            ' validate stuff
            links = Split(.cell("links", "value").toString, ",")
            groups = Split(.cell("groups", "value").toString, ",")
            names = Split(.cell("names", "value").toString, ",")
            count = .cell("count", "value").toString
            styleColumn = .cell("styleColumn", "value").toString
            linkName = .cell("linkName", "value").toString
            labels = Split(.cell("count", "value").toString, ",")
            If arrayLength(labels) < 1 Then
                labels = names
            End If
        End With
  • Initialize a nodesLink class with the options parameter block
Set nodesLink = New cNodesLinks
nodesLink.init .init( _
            wholeSheet(params), , "options", True, optionName)
  • Get the item parameter block

.init wholeSheet(params), , item, True, item

  • get the data and check we have all the mentioned fields
 With .init(wholeSheet(data), , "data", , , True)
            ' check everything exists
            Debug.Assert dsValidateHeadings(.headingRow, links)
            Debug.Assert dsValidateHeadings(.headingRow, groups)
            Debug.Assert dsValidateHeadings(.headingRow, labels)
            If count <> vbNullString Then Debug.Assert _
                .headingRow.validate(True, count)
            If styleColumn <> vbNullString Then Debug.Assert _
                .headingRow.validate(True, styleColumn)
            If linkName <> vbNullString Then Debug.Assert _
                .headingRow.validate(True, linkName)
  • run through each row of the data and add all the nodes and all the links
For Each dr In .rows
                ' add the nodes
                For i = LBound(links) To UBound(links)
                    nodesLink.addNode _
                        fixup(dr, links(i)), _
                        fixup(dr, groups(i)), _
                        fixup(dr, labels(i)), _
                        fixup(dr, count)
 
                Next i
                ' make the links
                For i = LBound(links) To UBound(links) - 1
                     nodesLink.addLink _
                        dr.cell(links(i)).toString, _
                        dr.cell(links(i + 1)).toString, _
                        fixup(dr, count), _
                        fixup(dr, styleColumn), _
                        fixup(dr, linkName)
                        
                Next i
            Next dr
  • generate the chart
 ' generate a d3.js force chart
   mashD3Force dsets.dataSet(item), nodesLink.jObject, dsets.dataSet("fields")

mashD3Force

Now that we have the nodeLinks structure, and all the options we can serialize the data and options, and create the web page

  • serialize the data

js = job.serialize

  • put together all the html components in one string
With dsParam
        content = content & _
            .cell("titles", "value").toString
        content = content & _
            .cell("styles", "value").toString
        If Not dsFields Is Nothing Then
            With dsFields.cell("linkStyles", "value")
                If .toString <> vbNullString Then
                    content = content & "<style>" & _
                        .toString & _
                        "</style>"
                End If
            End With
        End If
        content = content & _
            .cell("code", "value").toString
        content = content & _
            "<script> var mcpherTreeData = " & js & ";</script></head><body><div>"
        content = content & _
            .cell("banner", "value").toString
        If Not dsFields Is Nothing Then
            With dsFields.cell("banner", "value")
                If .toString <> vbNullString Then
                    content = content & .toString
                End If
            End With
        End If
        content = content & _
            .cell("body", "value").toString
  • write to an html file and open in a browser
With .cell("htmlname", "value")
            openNewHtml .toString, content
            If Not OpenUrl(.toString) Then
                MsgBox ("could not open " & .toString & " using default browser")
            End If
        End With

Here is the whole thing

For the cNodesLinks object see the cDataSet.xlsm workbook.

Summary

Why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available. in the meantime, see what else you can do with d3.js straight from Excel.

Subpages