CODEWITHSUNDEEP
c#.netexceloffice-interopexcel-interop
Michael Kirkegaard
Michael Kirkegaard

Reputation: 379

Make Org Chart with interop excel

I want to create an excel worksheet with an organizational chart with data from a database.

I have played around with the interop excel library in a c# console application, but i am stuck.

I can make a worksheet containing a smartart object with the hierachy chart layout(Org chart), but i dont know how to add my own data to it. When you create the chart it comes with the nodes shown in the picture below.

Is it even possible to add you own data to the chart through the interop excel library?

This is my code so far:

    private static Excel.Workbook Wb = null;
    private static Excel.Application Xl = null;
    private static Excel.Worksheet Sheet = null;

    static void Main(string[] args)
    {
        Xl = new Excel.Application();
        Xl.Visible = true;
        Wb = Xl.Workbooks.Add();
        Sheet = Wb.Worksheets[1];

        var myLayout = Xl.SmartArtLayouts[88];

        var smartArtShape = Sheet.Shapes.AddSmartArt(myLayout, 50, 50, 200, 200);

        smartArtShape.AlternativeText = "Test";

    }

This is the result: enter image description here

Upvotes: 1

Views: 412

Answers (1)

Cindy Meister
Cindy Meister

Reputation: 25673

Yes, the functionality is accessible through the "interop" :-) It's just a bit... arcane, especially for those who don't come from PowerPoint.

Since a SmartArt is "wrapped" in a Shape object, it's first necessary to get the Shape, then through it the SmartArt - the Shape object has HasSmartArt (test) and SmartArt (use) properties.

(Note that I find it very useful to explicitly declare the object type, rather than rely on var to "guess". That helps immensely when trouble-shooting.)

The actual content (the "boxes") are Nodes. For some reason, the PIAs don't provide Node objects (but they are present in the COM object model), so accessing them is a bit round-about in C#. To get all the nodes, use the AllNodes property to return the collection.

The Node objects can then be addressed collection indexing nds[i]. An individual node can be assigned to a SmartArtNode or to an Office Shape object. For the purposes of this question - adding text - the next step is the same...

The "boxes" are, themselves, Office Drawing Text Boxes (Shapes) and have the TextFrame2.TextRange.Text property for accessing their content. (Note that the default content is actually a placeholder, so this property will not return anything for a newly created SmartArt.)

Note: The code in the question attempts to use AlternativeText - this is like "AltText" in HTML objects, it provides accessibilty information and is not relevant for what you're trying to do.

static void Main(string[] args)
{
    excelApp = new Excel.Application();
    excelApp.Visible = true;
    Excel.Workbook wb = excelApp.Workbooks.Add();
    Excel.Worksheet ws = (Excel.Worksheet) wb.Worksheets[1];

    var myLayout = excelApp.SmartArtLayouts[88];

    var smartArtShape = ws.Shapes.AddSmartArt(myLayout, 50, 50, 200, 200);

    if (smartArtShape.HasSmartArt == Office.MsoTriState.msoTrue)
    {
      Office.SmartArt smartArt = smartArtShape.SmartArt;
      Office.SmartArtNodes nds = smartArt.AllNodes;

      for (int i = 1; i <= nds.Count; i++)
      {
          Office.SmartArtNode nd = nds[i]; //both work
          var shpNode = nd.Shapes.Item(1);
          nd.TextFrame2.TextRange.Text = "Testing Node " + i;
          shpNode.TextFrame2.TextRange.Text = "Testing Shape " + i;
      }
    }
}

Upvotes: 1

Related Questions