3

I need to create a hierarchical tree map of energy measurement in multiple buildings.

Like this:

                 Watermeter #1
        Bldg 1 [
                 Watermeter #2
Water [
                 Watermeter #21
        Bldg 2 [
                 Watermeter #22

Electricity ....

I already have the table with the parent/children relationships recorded in MS EXCEL. The relationship is either defined :

  • With an integer field "relation to parent ID" ID is the incremental reference of my meters.

  • by the structure of the table itself : utility / Bldg / meter Initial table OS: Windows standalone or Webapp

Framework: VBA but I'd rather not code something. Or anything else, but with a very clear step by step tutorial.

The point is : I want to spend less time doing it than drawing it in draw.io. (Maybe not for that particular case, but at least for the 2 or 3 next ones)

I've already tried MS treeview instruction from VBA but :

  1. it's a pain in the ... to use. You have to do a lot of code to be able to get all meters in the tree
  2. it's ugly

I've spent literally hours on Google trying to find an online service or a software, an couldn't come close to what I'm searching. But to me, this seems a quite useful functionality. It MUST exist somewhere!

The result I want could look like : Diagram tree

I prefer Open/freeware, although my company won't hesitate to spend a couple $.

  • Also, how are the parent/children relationships defined? Probably an integer 'parent' field pointing to an integer index field? Add a screenshot of your Excel 'table'? – user416 Aug 2 '16 at 15:25
  • Could you upload a small sample of such an Excel file? Also, you say the output should be "like this", but you don't want ASCII art graphics I guess. Shall it be in the form of a mind map? The Microsoft Treeview Control may look ugly, but it's sort of "standard" which is free. If it shall not look like that, you'd need to define the desired look in more detail. – Thomas Weller Aug 2 '16 at 21:19
  • +1 thanks for updating the question – Thomas Weller Aug 3 '16 at 9:53
1

You could use a little bit of VBA, personally I would use a few lines of python, to output a dot graph visualisation format file and then run it though the Graphviz dot tool.

Graphviz is:

  • Free (Gratis)
  • Cross Platform
  • Widely Used
  • Very Flexible (see the dot language user guide)
  • Can output in a number of formats, svg, pgn, etc.

Example:

Creating a file with called bills.gv containing:

digraph g {
    rankdir="LR";
    subgraph cluster_gas{
        Gas [shape=plaintext, fontsize=16];
        B1g  [label="Building 1"; shape=box];
        B2g [label="Building 2"; shape=box];
        b1m1g [label="Meter 1"; shape=tab];
        b2m1g [label="Meter 1"; shape=tab];
        b2m2g [label="Meter 2"; shape=tab];
        b1m1gr [label="123456 | £123.44"; shape=record]; 
        b2m1gr [label="452122 | £56.44"; shape=record]; 
        b2m2gr [label="45214 | £100.01"; shape=record]; 
        Gas -> B2g ->  b2m1g -> b2m1gr;
               B2g ->  b2m2g -> b2m2gr;
        Gas -> B1g ->  b1m1g -> b1m1gr;
    }

    subgraph cluster_elect{
        Electricty [shape=plaintext, fontsize=16];
        B1e  [label="Building 1"; shape=box];
        B2e [label="Building 2"; shape=box];
        b1m1e [label="Meter 1"; shape=tab];
        b1m2e [label="Meter 2"; shape=tab];
        b2m1e [label="Meter 1"; shape=tab];
        b1m1er [label="123456 | £123.44"; shape=record]; 
        b1m2er [label="45214 | £100.01"; shape=record]; 
        b2m1er [label="452122 | £56.44"; shape=record]; 
        Electricty -> B1e ->  b1m1e -> b1m1er;
                      B1e ->  b1m2e -> b1m2er;
        Electricty -> B2e ->  b2m1e -> b2m1er;
    }
}

Then running:

c:\Program Files (x86)\Graphviz2.38\bin\dot.exe" -Tpng bills.gv > bills.png

Gives:

enter image description here

| improve this answer | |
  • 1
    Thanx for the code. The result looks like what I want, but If I have to hard code every meter in it, It would be as fast as creating a graph in draw.io – TVIatstack Aug 3 '16 at 7:10
  • @TVIatstack You should be able to generate the meter IDs from the cell row ID - mine were hand coded as I didn't have an example spreadsheet. – Steve Barnes Aug 3 '16 at 9:23
  • OK. I see. interesting. I still have to code the routine to generate code from my spreadsheet. I'll see! – TVIatstack Aug 3 '16 at 10:21
  • @TVIatstack If you are not averse to installing python on your system post a sample spreadsheet somewhere where I can get at it and I will knock up something to get you started. – Steve Barnes Aug 4 '16 at 6:27
0

If a programmatic solution will be acceptable, Essential XlsIO and Essential Diagram can be used to create these kind of treemaps from data in Excel.

Sample application with source code: link.

      employee = new ObservableCollection<Employee>();

       //Get DataSourceSettings Properties.
        settings.ParentId = "ParentId";
        settings.Id = "EmpId";
        settings.Root = "1";
        for (int i = 2; i <= worksheet.Range.Rows.Count(); i++)
        {
            Employee a = new Employee();
            a.EmpId = worksheet.Range[i, 1].DisplayText;
            a.Name = worksheet.Range[i, 3].DisplayText;
            a.ParentId = worksheet.Range[i, 2].DisplayText;
            a.Designation = worksheet.Range[i, 4].DisplayText;
            a.Color = worksheet.Range[i, 5].DisplayText;
            employee.Add(a);
        }
        settings.DataSource = employee;
        diagramControl.DataSourceSettings = settings;
        diagramControl.LayoutManager = new Syncfusion.UI.Xaml.Diagram.Layout.LayoutManager()
            {
                Layout = new DirectedTreeLayout()
                {
                    Orientation = TreeOrientation.LeftToRight,
                    VerticalSpacing=80,
                    HorizontalSpacing=80
                }
            };
            (diagramControl.LayoutManager.Layout as DirectedTreeLayout).Margin = new Thickness(350,100,0,0);

The source requires Visual Studio and Syncfusion Essential Studio to compile.

Treemap

Treemap Data

The whole suite of controls is available for free through the community license program if you qualify (less than 1 million USD in revenue). Note: I work for Syncfusion.

| improve this answer | |
  • Thanks for the reply. Unfortunately my coding skills ar rather limited. But your example is what i was looking for. – TVIatstack Sep 7 '16 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.