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

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 namesPublic Sub d3forceHere()    d3ForceDoEnd Sub

d3ForceDo

This can be called with a specific worksheet name, and will run the e2e process on the data in the given worksheetPublic 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, fieldNameEnd 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 cNodesLinksnodesLink.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