This guide outlines how I built a dynamic floor plan that gets details out of AD and displays them in SharePoint 2010.
- SharePoint 2010 w/ Enterprise Features (Although it may work with earlier versions)
- Visio 2010 (Although it may work with earlier versions)
- Active Directory
- Some knowledge of VB.net, SharePoint, Active Directory, Java script and CSS
Firstly, this is what it looks like for me, but bear in mind you will be supplying the background image, and choosing the icons you want to use for the ‘people’ entities yourself. I chose one named ‘torso’
Pretty basic looking, Although if you hover over someone you also get their details which are updated from Active Directory
There is two systems working together to bring this up.
Firstly a Visio Web Drawing, that is linked to a spreadsheet in SharePoint, stores the floor plan image and icon locations and popup content. Its displayed on a web part page with a little bit of hidden JS on the side to display the popups.
Behind the scenes I also have an application that periodically polls Active Directory for user information and updates the spreadsheet.
How to do it.
Firstly you need to create some data and put it into a SharePoint document library. if you want you can skip this step and come back to it later, just create a dummy XLSX file with data in it like so. Call it OfficePlanData.XLSX
|a1026||user A||demo title||2349|
|a1020||User B||demo title||7151|
|a1031||User C||demo title||8004|
|a1028||user A||demo title||3429|
|a1027||User B||demo title||5339|
|a1025||User C||demo title||3188|
You may want to do this another way, perhaps maintain this file manually, or export data from AD manually etc. I didn’t so I wrote a tool that would take details out of AD, store them in a xlsx file, then upload that to the document library every 60 minutes
Here is the source code to the vb.net project I built (VS2010) that takes user name, title, phone number and extensionattribute1 from AD. (Extension Attribute 1 is storing a unique ‘location’ ID that I call ‘SpaceID’) and upload it to the document Library
It is a console application, requires office 2010 to be added as a service reference. You could easily re-write it to use a different office version I am sure.
Imports System.DirectoryServices Imports System.Web Imports System.Configuration Imports System.Xml Imports System.Net Imports Microsoft.Office.Interop Module Module1 Public sSPURL As String = "http://yoursharepointsite.com" Public sDocLib As String = "Floorplan" 'the name of the document library Public sUser As String = "sharepointhelper" 'a user with access to upload files to that document library and do AD lookups Public sPwd As String = "password123" 'users password Public sDomain As String = "domainname.local" 'the local domain Public localfile As String = "C:\Program Files\ADtoFloorplan\OfficePlanData.xlsx" 'the temporary location of the file before upload Public destinationfile As String = "OfficePlanData.xlsx" 'the filename thats going into the document library Dim userarray(1000, 6) Sub Main() Call getusers() UploadDocument(localfile, destinationfile) End Sub Public Sub getusers() ' Bind to the users container. Dim ldapServerName = "domainname" Dim oRoot As DirectoryEntry = New DirectoryEntry("LDAP://" & ldapServerName & "/OU=Users,DC=DOMAINNAMECHANGEME,DC=local") Dim oSearcher As DirectorySearcher = New DirectorySearcher(oRoot) Dim oResults As SearchResultCollection Dim oResult As SearchResult Dim name, homedrive, extat1, title, phone Dim iplacer As DirectoryEntry = New DirectoryEntry userarray(0, 0) = "SpaceID" userarray(0, 1) = "Name" userarray(0, 2) = "Title" userarray(0, 3) = "Phone" Try oResults = oSearcher.FindAll Dim i As Integer = 1 For Each oResult In oResults name = oResult.GetDirectoryEntry().Properties("name").Value title = oResult.GetDirectoryEntry().Properties("title").Value phone = oResult.GetDirectoryEntry().Properties("telephoneNumber").Value homedrive = oResult.GetDirectoryEntry().Properties("homeDrive").Value extat1 = oResult.GetDirectoryEntry().Properties("extensionattribute1").Value If extat1 <> "" Then 'only populate the file with people who have extension attribute 1 set userarray(i, 0) = extat1 userarray(i, 1) = name userarray(i, 2) = title userarray(i, 3) = phone i = i + 1 End If Next WriteSpreadsheetFromArray(userarray, localfile) Catch e As Exception Console.WriteLine("Error is " & e.Message) End Try End Sub Public Sub WriteSpreadsheetFromArray(ByVal strOutputArray As Array, Optional ByVal strExcelFileOutPath As String = "") Dim objxlOutApp As Excel.Application Dim objxlOutWBook As Excel.Workbook Dim objxlOutSheet As Excel.Worksheet Dim objxlRange As Excel.Range 'Try to Open Excel, Add a workbook and worksheet Try 'Try to Open Excel, Add a workbook and worksheet objxlOutApp = New Excel.Application objxlOutWBook = objxlOutApp.Workbooks.Add '.Add.Sheets objxlOutSheet = DirectCast(objxlOutWBook.Sheets.Item(1), Excel.Worksheet) Catch ex As Exception MsgBox("While trying to Open Excel recieved error:" & ex.Message, , "Export to Excel Error") Try If Not IsNothing(objxlOutWBook) Then objxlOutWBook.Close() 'If an error occured we want to close the workbook End If If Not IsNothing(objxlOutApp) Then objxlOutApp.Quit() 'If an error occured we want to close Excel End If Catch End Try objxlOutSheet = Nothing objxlOutWBook = Nothing 'If Not IsNothing(objxlOutApp) Then 'System.Runtime.InteropServices.Marshal.ReleaseComObject(objxlOutApp) 'This will release the object reference 'End If objxlOutApp = Nothing Exit Sub 'An error occured so we don't want to continue End Try Try objxlOutApp.DisplayAlerts = False 'This will prevent any message prompts from Excel (IE.."Do you want to save before closing?") objxlOutApp.Visible = False 'We don't want the app visible while we are populating it. 'This is the easiest way I have found to populate a spreadsheet 'First we get the range based on the size of our array objxlRange = objxlOutSheet.Range(Chr(strOutputArray.GetLowerBound(1) + 1 + 64) & (strOutputArray.GetLowerBound(0) + 1) & ":" & Chr(strOutputArray.GetUpperBound(1) + 1 + 64) & (strOutputArray.GetUpperBound(0) + 1)) 'Next we set the value of that range to our array objxlRange.Value = strOutputArray 'This final part is optional, but we Auto Fit the columns of the spreadsheet. objxlRange.Columns.AutoFit() If strExcelFileOutPath.Length > 0 Then 'If a file name is passed Dim objFileInfo As New IO.FileInfo(strExcelFileOutPath) If Not objFileInfo.Directory.Exists Then 'Check if folder exists objFileInfo.Directory.Create() 'If not we create it End If objFileInfo = Nothing objxlOutWBook.SaveAs(strExcelFileOutPath) 'Then we save our file. End If objxlOutApp.Visible = True 'Make excel visible Catch ex As Exception MsgBox("While trying to Export to Excel recieved error:" & ex.Message, , "Export to Excel Error") Try objxlOutWBook.Close() 'If an error occured we want to close the workbook objxlOutApp.Quit() 'If an error occured we want to close Excel Catch Console.WriteLine(ex.Message) End Try Finally objxlOutWBook.Close() 'If an error occured we want to close the workbook objxlOutApp.Quit() objxlOutSheet = Nothing objxlOutWBook = Nothing 'If Not IsNothing(objxlOutApp) Then ' System.Runtime.InteropServices.Marshal.ReleaseComObject(objxlOutApp) 'This will release the object reference 'End If objxlOutApp = Nothing End Try End Sub Public Function UploadDocument(ByVal localFile As String, _ ByVal remoteFile As String) As String '// Read in the local file On Error GoTo handler Dim r As Byte() Dim Strm As System.IO.FileStream = New System.IO.FileStream(localFile, _ System.IO.FileMode.Open, System.IO.FileAccess.Read) Dim reader As System.IO.BinaryReader = New System.IO.BinaryReader(Strm) Dim filecontents As Byte() = reader.ReadBytes(CInt(Strm.Length)) reader.Close() Strm.Close() Dim sRemoteFileURL As String Dim NC As System.Net.NetworkCredential = _ New System.Net.NetworkCredential(sUser, sPwd, sDomain) sRemoteFileURL = sSPURL & "/" & sDocLib & _ "/" & Trim(LTrim(RTrim(remoteFile))) sRemoteFileURL = Replace(sRemoteFileURL, " ", "%20") sRemoteFileURL = Replace(sRemoteFileURL, "\", "/") Dim m_WC As WebClient = New WebClient m_WC.Credentials = NC r = m_WC.UploadData(sRemoteFileURL, "PUT", filecontents) Return "TRUE" Exit Function handler: Return Err.Description End Function End Module
I used the following references to build this app
Creating a XLSX file from vb.net: http://www.vbforums.com/showthread.php?t=455162
Uploading a file to a SharepPoint document library: http://www.codeproject.com/KB/sharepoint/File_Shunter.aspx
Getting AD details : http://www.codeproject.com/KB/system/active_directory_in_vbnet.aspx (Started with this, its a bit different now)
You will need to change a few things in there obviously, only as far as
Dim oRoot As DirectoryEntry = New DirectoryEntry("LDAP://" & ldapServerName & "/OU=Users,DC=DOMAINNAMECHANGEME,DC=local")
I have compiled this as a console application then used task scheduler to run it every hour. you could quite easily also create a service and user your own system.timer if you prefered.
So I am going to assume you now have a xlsx file called officeplandata sitting in your document library which is in the root of your site, and is called ‘floorplan’
open up Visio 2010, first thing we need is the background image, personally I found it was easiest to set the floor plan image as a jpg in the background instead of an image on the page as it means it won’t get in the way when placing other objects on top of it. To do this, add a generic background, then overlay your image on the second tab of the document like so.
You will want your image to be the same aspect ratio as the document (eg letter/a4) so it consumes the entire page.
Now link your data to the xlsx spreadsheet we created earlier. Go to the Data tab, click on Link Data to Shapes, and select Excel Workbook, and link it to the URL of the excel file
Click Next, choose ‘SpaceID’ as the unique Identifier, and finish.
Flick back to Page-1 if you have not already, search an appropriate icon for your people, as you can see I am using ‘torso’
select the icon, so that its highlighted as you can see above, then drag someone from the right, (the linked data) onto the plan. This will create the shape that is highlighted and link it to the shapeID with all the users details. I have remove the Data Graphics as I found they were too much. to do this, select all icons, then click ‘Data’ from the Ribbon, Data Graphics, then ‘No Data Graphic’
Save the document as a Visio Web Diagram and upload it to the document library.
Assuming you have SharePoint set up correctly you could view the VDW as is and it would show you all the people, but nothing happens when you hover over anyone. Almost there!
Now create a new web part page in the document library, I suggest a Full Page, Vertical
Add a Visio Web Access web part (Under Business Data),
Now create a text file somewhere and paste this into it, save it as floorplan.js or hover.js or whatever you please.
Goto Layout, and select ‘hidden’
Save the page and cross your fingers; View it.
With any luck your page will now show the floorplan, hovering over the people will show relevant details. and everything will be done.
You can edit what the box looks like by editing the JS file, line 130 is what you want.
Version 1.0 created 22/09/11