By Jian Sun
Introduction – Scenario
In this code sample, we use VB.NET to demonstrate creating a simple organization chart from a corporate directory (in my case Active Directory). This uses LDAP queries and some basic Open XML SDK 2.0 drawing capabilities to draw diagram inside the word document based on this organization structure.
An Organization Chart in Word generated using Open XML SDK 2.0 from Active Directory
Background Technologies - Active Directory and LDAP Query
Lightweight Directory Access Protocol (LDAP) is an Application Layer protocol on the OSI stack for querying and modifying directory services. In my example I used Microsoft’s Active Directory, but in theory any LDAP accessible Directory Service should work.
My basic LDAP query looks like; (&(objectCategory=user)(objectClass=user)(!manager=*))
This query retrieves all users that do not have an associated manager. The results represent top level managers in this organization structure. In this sample I fetch each level with a separate query. This could be improved by fetching all the people with a single query and building a graph in memory.
The manager-employee relationship is stored in the Manager property of an Active Directory user object. This can be changed by going to Windows Start -> Administrative Tools -> Active Directory Users and Computers. Right click on the subject employee then select Properties -> Organization tab -> Manager -> Change.
Organization tab under Properties dialog for MOSSService Properties
Following this approach, I quickly made myself a small organization structure that exhibits the structure generated in the Word document shown earlier.
Design Process
One of the great utility tools that ship with Open XML SDK 2.0 is Document Reflector. Think of it like the .NET reflector but for Open XML file format based documents. For a given Open XML document (pptx, docx or xslx), Document Reflector dissects the document and presents the raw xml files within the zip structure. What is even better about Document Reflector is it also generates some C# code that resembles the generation of the Open XML files.
The logic is to create a sample organization chart in Microsoft Word, open it with Document Reflector and export the code that generates this organization chart. This code can be used as a baseline then expanded to read data from Active Directory via LDAP query.
1. Create a Simple Chart in Word
In Word menu bar, go Insert | Shapes
A manually created sample organization chart in Word
2. Open this document in Document Reflector
From the source code window (bottom right), we can reasonably easily extract the code snippets for the two square and the arrowed line as following;
Code Snippet for the Arrow Line:
new Run(
new RunProperties(
new NoProof(),
new Languages(){ EastAsia = "en-NZ" }),
new Picture(
new v.Shapetype(
new v.Path(){ AllowFill = false, ShowArrowhead = true, ConnectionPointType = ovml.ConnectValues.None },
new ovml.Lock(){ Extension = v.ExtensionHandlingBehaviorValues.Edit, ShapeType = true }
){ Id = "_x0000_t32", CoordinateSize = "21600,21600", Oned = true, Filled = false, OptionalNumber = 32, EdgePath = "m,l21600,21600e" },
new v.Shape(
new v.Stroke(){ EndArrow = v.StrokeArrowValues.Block }
){ Id = "_x0000_s1031", Style = "position:absolute;margin-left:168.45pt;margin-top:42.45pt;width:0;height:32.65pt;z-index:251660288", ConnectorType = ovml.ConnectorValues.Straight, Type = "#_x0000_t32" })),
Code Snippet for Gollum:
new Run( new RunProperties(new NoProof(),new Languages(){ EastAsia = "en-NZ" }), new Picture( new v.Shapetype( new v.Stroke(){ JoinStyle = v.StrokeJoinStyleValues.Miter }, new v.Path(){ AllowGradientShape = true, ConnectionPointType = ovml.ConnectValues.Rectangle } ){ Id = "_x0000_t202", CoordinateSize = "21600,21600", OptionalNumber = 202, EdgePath = "m,l,21600r21600,l21600,xe" },
new v.Textbox(
new TextboxContent(
new Paragraph(
new ParagraphProperties(
new ParagraphMarkRunProperties(
new Languages(){ Val = "en-US" })),
new Languages(){ Val = "en-US" }),
new Text("Gollum"))
)))
){ Id = "_x0000_s1028", Style = "position:absolute;margin-left:133pt;margin-top:75.1pt;width:1in;height:32.6pt;z-index:251659264", Type = "#_x0000_t202" })),
system
Code Snippet for Yoda:
new Text("Yoda"))
){ RsidParagraphMarkRevision = "00543455", RsidParagraphAddition = "00C6075B", RsidParagraphProperties = "00543455", RsidRunAdditionDefault = "00543455" }))
){ Id = "_x0000_s1026", Style = "position:absolute;margin-left:133pt;margin-top:8.85pt;width:1in;height:33.6pt;z-index:251658240", Type = "#_x0000_t202" }))
Unfortunately, at the time of producing this article the Document Reflector did not generate VB.Net code. However, there are many free tools available that do a fantastic job at converting C# to VB.Net. Personally, I found http://www.developerfusion.com/tools/convert/csharp-to-vb/ very helpful.
This code is cleaned up and converted to VB.Net.
3. Create a Node Class
I defined a Node class to represent the rectangles in the organization chart; it has the following properties and related fields:
Property Name Type Use TopConnector System.Drawing.Point The point on the top of the rectangle to which a relationship connector is drawn. BottomConnector System.Drawing.Point The point on the bottom of the rectangle from which a relationship connector is drawn. DistinguishedName String Distinguished name of the AD user. e.g. "CN=Administrator,CN=Users,DC=sun,DC=sundium,DC=com". Name String Name of the AD user. e.g. Administrator. Top Double The Y coordinate of the top left corner in Pt. Left Double The X coordination of the top left corner in Pt. Height Double Height of the node in Pt. Width Double Width of the node in Pt. Manager Node A self referencing variable representing the Manager of the current code
Property Name
Type
Use
TopConnector
System.Drawing.Point
The point on the top of the rectangle to which a relationship connector is drawn.
BottomConnector
The point on the bottom of the rectangle from which a relationship connector is drawn.
DistinguishedName
String
Distinguished name of the AD user. e.g. "CN=Administrator,CN=Users,DC=sun,DC=sundium,DC=com".
Name
Name of the AD user. e.g. Administrator.
Top
Double
The Y coordinate of the top left corner in Pt.
Left
The X coordination of the top left corner in Pt.
Height
Height of the node in Pt.
Width
Width of the node in Pt.
Manager
Node
A self referencing variable representing the Manager of the current code
4. LDAP Queries
In my Module1.vb file, I have defined two functions that help retrieve Org Chart nodes and relationships from Active Directory, as follows:
Private Function GetDirectoryEntry() As DirectoryEntry
Dim de As New DirectoryEntry
de.Path = LDAPPATH
de.AuthenticationType = AuthenticationTypes.Secure
Return de
End Function
This method returns a DirectoryEntry for a given LDAP path. A DirectoryEntry can then be used to perform search actions to find users. A LDAP path looks like: "LDAP://CN=Users,DC=sun,DC=sundium,DC=com"
Public Function Search(ByVal filter As String) As SearchResultCollection
Dim de As DirectoryEntry = Me.GetDirectoryEntry
Dim deSearch As New DirectorySearcher
deSearch.SearchRoot = de
deSearch.Filter = filter
Return deSearch.FindAll
This method takes a filter string and performs a search on a DirectoryEntry to return all users that satisfy the filter condition.
A filter to retrieve all top level managers looks like: "(&(objectCategory=user)(objectClass=user)(!manager=*))"
A filter that retrieves all users with the Administrator as their managers looks like: "(&(objectCategory=user)(objectClass=user)(manager=" CN=Administrator,CN=Users,DC=sun,DC=sundium,DC=com"))"
5. Add Node and relationship links
The thinking here is, we add nodes one level at a time from top to bottom.
First, we retrieve the top level nodes (the ones with no managers), draw them in the Word document while noting down the node class properties.
Then for each of these top level nodes, we retrieve the nodes that are the associated managers, draw them in the Word document with appropriate spacing and insert the relationship links from the manager node to them.
This process is then repeated recursively until there are no more nodes in the organization structure.
5.1 Recursive calls
Private Sub AddLevelNodes(ByVal currentLevel As List(Of Node), ByVal rootParagraph As Paragraph, ByVal height As Integer)
Dim nextLevelNodes As New List(Of Node)
If (currentLevel Is Nothing) Then
Dim managers As SearchResultCollection = Me.Search("(&(objectCategory=user)(objectClass=user)(!manager=*))")
nextLevelNodes.AddRange(Me.AddNodes(Nothing, managers, rootParagraph, height))
Else
Dim node As Node
For Each node In currentLevel
Dim managees As SearchResultCollection = Me.Search(("(&(objectCategory=user)(objectClass=user)(manager=" & node.DistinguishedName & "))"))
nextLevelNodes.AddRange(Me.AddNodes(node, managees, rootParagraph, height))
Next
End If
If (nextLevelNodes.Count > 0) Then
height += 1
Me.AddLevelNodes(nextLevelNodes, rootParagraph, height)
End Sub
This code snippet illustrates how the top level nodes are retrieved, and then recursively retrieves nodes at subsequent levels.
5.2 Add nodes and relationship connectors
Private Function AddNodes(ByVal manager As Node, ByVal results As SearchResultCollection, ByVal rootParagraph As Paragraph, ByVal level As Integer) As List(Of Node)
Dim nodeList As New List(Of Node)
Dim i As Integer
For i = 0 To results.Count - 1
' Define current node
Dim node As New Node
node.Manager = manager
node.Name = results.Item(i).Properties.Item("name").Item(0).ToString
node.DistinguishedName = results.Item(i).Properties.Item("distinguishedname").Item(0).ToString
node.Top = (level * &H63)
node.Left = (i * &H63)
node.Height = 27
node.Width = 90
Dim topConnector As New Point
topConnector.X = (node.Left + (node.Width / 2))
topConnector.Y = node.Top
node.TopConnector = topConnector
Dim bottomConnector As New Point
bottomConnector.X = (node.Left + (node.Width / 2))
bottomConnector.Y = (node.Top + node.Height)
node.BottomConnector = bottomConnector
' Add the node
Dim text As New Text(node.Name)
text.Space = "preserve"
Dim shape As New v.Shape(New v.Textbox(New TextboxContent(New Paragraph(New Run(text)))))
shape.Style = String.Concat(New Object() {"position:absolute;margin-left:", node.Left, "pt;margin-top:", node.Top, "+pt;width:", node.Width, "pt;height:", node.Height, "pt;text-align:center"})
shape.Type = "#_x0000_t202"
rootParagraph.AppendChild(New Picture(shape))
' Draw relationship connector if node has a manager
If (Not manager Is Nothing) Then
Dim stroke As New v.Stroke
stroke.EndArrow = v.StrokeArrowValues.Block
Dim connector As New v.Shape(stroke)
connector.Style = String.Concat(New Object() {"position:absolute;margin-left:", node.Manager.BottomConnector.X, "pt;margin-top:", node.Manager.BottomConnector.Y, "pt;width:", (node.TopConnector.X - node.Manager.BottomConnector.X), "pt;height:", node.Manager.BottomConnector, (node.TopConnector.Y - node.Manager.BottomConnector.Y), "pt"})
connector.ConnectorType = ovml.ConnectorValues.Straight
connector.Type = "#_x0000_t32"
rootParagraph.AppendChild(New Picture(connector))
nodeList.Add(node)
Next i
Return nodeList
This is essentially what the Document Reflector generated, but converted to VB.Net. At the current level we first add the nodes at this level then we add a relationship connector from the manager to the current node if the current nodes has a manager.
5.3 Run up the application
If your Active Directory resembles a simple Organization Chart, you should be able to generate a Word document that contents a simple Org Chart that looks much like the one below:
Conclusion
This sample has been an interesting discovery into how to generate drawings in Open XML. When I set out I had planned to use DrawingML but realised only at the end the approach that some drawings in Word 2007 still use the older VML standard. Because I used Word to create a prototype document in Word and then generated code, the sample uses VML not the newer DrawingML. This should be addressed in Word 2010.
Document Reflector is a very handy tool for Open XML developers as it can dissect a prebuilt document and generate C# code. I wanted so use VB.Net so had to port the C# code to VB.NET myself. It would be great if the Document Reflector outputs VB.NET as well, but even without that it still a lot less work to generate then port the code than it would be to figure out all the method calls and parameters yourself.
The sample code attached contains the source for my sample, but because it needs an LDAP data source I have not included a sample that you can just run up. However if you have Visual Studio 2008 you can open the source and set it to connect to your LDAP and it should run for you.
With a little investigation it should be possible to automate the creation of all kinds of drawings from an application.
References:
LDAP Query Basics http://technet.microsoft.com/en-us/library/aa996205(EXCHG.65).aspx
What is Active Directory http://msdn.microsoft.com/en-us/library/aa746492(VS.85).aspx
Open XML Developer Centre http://msdn.microsoft.com/en-us/office/bb265236.aspx