Developer information for d3.js force diagrams

Developer information for Excel tables to d3.js Force diagram

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 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

https://gist.github.com/brucemcpherson/4684498

This generates this code from the test data in the sample workbook




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.


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. 
If you prefer Video style learning I also have two courses available. also published by O'Reilly.
 Google Apps Script for Developers and Google Apps Script for Beginners.





Comments