11/13/2023 0 Comments Sankey diagram excel downloadPrivate Function getUniqueIndex(s As String) As Long add "target", getUniqueIndex(dr.cell("TargetID").toString) add "source", getUniqueIndex(dr.cell("SourceID").toString) If (dr.cell("Value").Value 0 Or Not pIgnoreZero) Then Job As cJobject, nodes As cJobject, links As cJobject 'return a jobject version for input to D3.js sankey diagramĭim cs As cJobject, dr As cDataRow, cc As cCell, _ Public Property Get jObject() As cJobject ' special tweak for json format needed- get rid of extra Public Property Get jSon(Optional optBeautify As Boolean = False) As String ' this is used to create sankey diagram input jSon data to D3.js plugin ![]() I have introduced a new class – cSankey – which is reponsible for organizing the Excel data, and creating a suitable jSon representation as expected by the d3.js sankey plugin. Js = cs.init(ds.populateData(wholeSheet("sankey"),, ,, ,, True)).jSonĬontent =. ' generate a sankey chart from excel dataĭsParam.populateData wholeSheet("sankeyparameters"),, , True, "Item",, True Here is the calling procedure to generate and execute the chart code.ĭim ds As New cDataSet, cs As New cSankey, dsParam As New cDataSet, _ Chrome and Firefox are good.Īll code is implemented in the cDataSet.xlsm download. 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. You should set this to a directory you have permission to create files in. Some directories on your machine may be read only 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. The generated filename is set with the htmlName parameter. It is also possible to change the operation of the chart if you are comfortable with d3.js – you can find this in the chartCode parameter. Most of this you won’t 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. Tweaking the formatĪs in other projects on this site, the javaScript, css and html that does all this is stored as a series of parameter values in a parameter sheet- in this case the sankeyParameters sheet. To create a Sankey diagram, you simply need to construct a table as above. Here is a section of the input data that created the above, which you can find in the Sankey tab of the cDataSet.xlsm download Now it may be that you want to give two columns the same name (let’s say this represented some kind of transformation where the labels before and after were the same), so it means that I needed to allow for an ID (to represent the source and target) and a label in the dataįormat. Labels (what to call each column section).A value (which values transfer between each column section).A source and target (which column sections are connected).That means that each data item needs to describe This kind of visualization can be thought of as being a column chart that also shows the connections between sections of each column. Here’s a screenshot for those with older browsers. You can try the interactive version here (note that this doesn’t work properly with IE8 and below) This supposed to represent the breakdown of effort that a fictional company spends in various functions and roles. Here is a screenshot. ![]() In this example we will take some Excel data and directly create an interactive Sankey diagram. They are designed to show flows through a network, and are sometimes called flow diagrams. If you are Google Sheets user, here’s an Add-on to create these directly in Sheets. These are implemented in the cDataSet workbook, downloadable from here. See these blog posts – adding urls to Sankey links and nodes, inheriting data cell colors for Sankey links, and another Sankey example There have been a few features added to this since this entry. If you use any of this material, please be sure to maintain the acknowledgement for his work. Much of the JavaScript and d3.js came from Mike Bostok’s site.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |