Generate a PowerPoint Presentation based on Site Data


Wow. Two posts in the same day. Hopefully that makes up for the long delay! If you couldn't tell I was heads down in a proof-of-concept, but I'm at least posting all the goodies 🙂


So this next piece isn't something I highly recommend. But it is great eye candy and becomes much more a possibility with Office 12 so I didn't mind putting it together. The concept is simple. A team site can be used for a group of users to come together to collaborate. As SharePoint admins, we recognize that there are specific reasons why the group comes together and this leads us to building site definitions/templates. As the users interact with the site, they are collecting data, completeing lists, filling out forms, etc. What if we could allow them to generate a briefing PowerPoint presentation based on live data collected in the site? After showing off this solution to just a few customers, I see that it resonates real well!


So the architecture here is simple. I built a PowerPoint file that is used as a template where the slides have placeholders for content that needs to be filled in. These placeholders are text strings like [@Date] and [@Title], etc. In my sample, I had two InfoPath forms. The first one had some general field data and the second one was an agenda whose items needed to be placed on a slide. To build the PowerPoint presentation, my solution automated PowerPoint on the server, opened the template, retrieved the files, filled in the data, and then saved it back to the site. There are lots of reasons why automating PowerPoint on the server is a bad idea and I'm not going to go into a lot of detail here. However, this idea is something that is going to gain momentum as Office 12 and its XML file format become a reality next year. I plan to put together an Office 12 version of this demo soon so stay tuned.


To even automate PowerPoint on the server, there are a lot of DCOM settings that you need to be concerned with. The following KB article should walk you through how you need to setup your environment: http://support.microsoft.com/kb/288367/. Also make sure that when you install PowerPoint on the server, you do a custom install to get the interop assemblies installed. You will need to reference Microsoft.Office.Interop.PowerPoint. My code assumes the following namespaces are referenced:
Imports PowerPoint = Microsoft.Office.Interop.PowerPoint
Imports Office = Microsoft.Office.Core


Once configured, my solution executed the automation in a web part. The web part was just a simple button that when clicked performed the necessary actions. Here is most of the button click event with my comments inline. I selected VB.NET since the COM interop with Office would be easier to code.


Private Sub btnGenerate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGenerate.Click
        Dim app As PowerPoint.Application
        Dim pres As PowerPoint.Presentation
        Dim slide As PowerPoint.Slide
        Dim assistantOn As Boolean
        Dim siteUrl As String = Me.Context.Request.Url.ToString()
        siteUrl = siteUrl.Substring(0, siteUrl.IndexOf("default.aspx") - 1)


        'all of these were properties that allow me to retrieve the files or build links to docs in the site
        Dim template As String = siteUrl & pptTemplateFileStr
        Dim agenda As String = siteUrl & agendaFileStr
        Dim resultFileName As String = siteUrl & resultFileNameStr
        Dim securityUrl As String = siteUrl & securityViewStr
        Dim communicationUrl As String = siteUrl & communicationsViewStr
        Dim infoFile As String = siteUrl & infoFileStr
        Dim redirect As String = siteUrl & redirectStr


        Try
            'Start Powerpoint and make its window visible but minimized.
            app = New PowerPoint.Application
            app.Visible = True
            app.WindowState = PowerPoint.PpWindowState.ppWindowMinimized


            'Here I am figuring out which XML file I need to open. I am traversing the SPS object model to find the file
            Dim parts As String() = infoFileStr.Split("/")
           
Dim web As SPWeb = SPControl.GetContextWeb(Me.Context)
            Dim sharedDocs As SPFolder = web.GetFolder(parts(0))
            Dim info As SPFile = sharedDocs.Files.Item(parts(1))
            Dim i As Byte() = info.OpenBinary()


                        'I know the file is XML so I get its string representation
            Dim iStr As String = System.Text.UTF8Encoding.UTF8.GetString(i)
            'Create a new presentation based on the specified template.
            pres = app.Presentations.Open(template, , , True)
            If Not (pres Is Nothing) Then
                'retrieve the general info xml file 
                Dim infoXml As XmlDocument = New XmlDocument
               
infoXml.LoadXml(iStr)
                Dim namespaceManager As XmlNamespaceManager = New XmlNamespaceManager(infoXml.NameTable)
                namespaceManager.AddNamespace("my", "
http://schemas.microsoft.com/office/infopath/2003/myXSD/2005-11-03T22:41:52")
                'query here for general infomation
                Dim title As String = String.Empty
                Dim name As String = String.Empty
                Dim eta As DateTime = DateTime.Today


                Dim titleNode As XmlNode = infoXml.SelectSingleNode("//my:DV", namespaceManager)
                If (Not (titleNode Is Nothing)) Then title = titleNode.InnerText
                Dim nameNode As XmlNode = infoXml.SelectSingleNode("//my:DVName", namespaceManager)
                If (Not (nameNode Is Nothing)) Then name = nameNode.InnerText
                Dim etaNode As XmlNode = infoXml.SelectSingleNode("//my:ETA", namespaceManager)
                If (Not (etaNode Is Nothing)) Then eta = etaNode.InnerText


                'cleanup
                infoXml = Nothing
                info = Nothing
                i = Nothing
                iStr = Nothing



                'work with title slide. I have some helper code at the end that helps you find the names of shapes
                slide = pres.Slides.Item(1)
                slide.Shapes.Item("Text Box 14").TextFrame.TextRange.Replace("[@Date]", eta.ToShortDateString())
                slide.Shapes.Item("Text Box 14").TextFrame.TextRange.Replace("[@Title]", title)
                slide = Nothing


                'work with second slide. This slide had an additional challenge that I wanted to check every line of a bullet list
                slide = pres.Slides.Item(2)
                slide.Shapes.Item("Rectangle 2").TextFrame.TextRange.Replace("[@Date]", eta.ToShortDateString())
                slide.Shapes.Item("Rectangle 2").TextFrame.TextRange.Replace("[@Title]", title)
                slide.Shapes.Item("Rectangle 3").TextFrame.TextRange.Replace("[@Name]", name)
                slide.Shapes.Item("Rectangle 3").TextFrame.TextRange.Replace("[@Date]", eta.ToShortDateString())
                Dim lineText As PowerPoint.TextRange
                For Each lineText In slide.Shapes.Item("Rectangle 7").TextFrame.TextRange.Lines
                    lineText.Replace("[@Name]", name)
                Next


                slide = Nothing


                'work with third slide. This was the agenda section
                Dim bodyText As String = String.Empty
                slide = pres.Slides.Item(3)
                slide.Shapes.Item("Rectangle 2").TextFrame.TextRange.Replace("[@Date]", eta.ToShortDateString())
                slide.Shapes.Item("Rectangle 2").TextFrame.TextRange.Replace("[@Title]", title)
                
parts = agendaFileStr.Split("/")
                Dim templates As SPFolder = web.GetFolder(parts(0))
                info = templates.Files.Item(parts(1))
                i = info.OpenBinary()
                iStr = System.Text.UTF8Encoding.UTF8.GetString(i)



                Dim agendaXml As XmlDocument = New XmlDocument
                agendaXml.LoadXml(iStr)
                namespaceManager = New XmlNamespaceManager(agendaXml.NameTable)
                namespaceManager.AddNamespace("mtg", "
http://schemas.microsoft.com/office/infopath/2003/sample/MeetingAgenda")
                'place each agenda item into the body text of the shape
               
Dim nodes As XmlNodeList = agendaXml.SelectNodes("//mtg:agendaItems/mtg:agenda", namespaceManager)
                Dim nodeItem As XmlNode
                For Each nodeItem In nodes
                    Dim subject As String = nodeItem("mtg:subject").InnerText
                    Dim duration As String = nodeItem("mtg:duration").InnerText
                    bodyText = bodyText & duration & Chr(9) & subject & vbCrLf
                Next
                slide.Shapes.Item("Rectangle 3").TextFrame.TextRange.Text = bodyText
                slide = Nothing
                agendaXml = Nothing
                info = Nothing
                i = Nothing
                iStr = Nothing


                'work with fourth slide. This slide had some graphics that I wanted to change the color of.
                'sort of a score card approach where I changed the color from green to yellow and hyperlinked the text

                slide = pres.Slides.Item(4)
                slide.Shapes.Item("Rectangle 2").TextFrame.TextRange.Replace("[@Date]", eta.ToShortDateString())
                slide.Shapes.Item("Rectangle 2").TextFrame.TextRange.Replace("[@Title]", title)
                slide.Shapes("AutoShape 4").Fill.ForeColor.RGB = 65535
                slide.Shapes("AutoShape 9").Fill.ForeColor.RGB = 65535
                slide.Shapes.Item("Text Box 13").TextFrame.TextRange.ActionSettings(PowerPoint.PpMouseActivation.ppMouseClick).Hyperlink.Address = securityUrl
                slide.Shapes.Item("Text Box 18").TextFrame.TextRange.ActionSettings(PowerPoint.PpMouseActivation.ppMouseClick).Hyperlink.Address = communicationUrl


                slide = Nothing


                'save to document library
                pres.SaveAs(resultFileName)
                endStr = "Completed. You presentation is located <a href='" & redirect & "'>here</a>."
                Me.btnGenerate.Visible = False



            End If


        Catch ex As Exception
            Me.errorStr = errorStr & ex.Message & ex.StackTrace
        Finally
            'Close the presentation without saving changes and quit PowerPoint.
            If Not (pres Is Nothing) Then
                pres.Saved = True
                pres.Close()
                pres = Nothing
            End If
            If Not (app Is Nothing) Then
                app.Quit()
                app = Nothing
            End If
            GC.Collect()
        End Try


    End Sub


This is by now means a production solution. But it gets a lot of jaws dropping in the audience. I used the following code during the development of the web part to find out what shapes were on a given slide:


slide = pres.Slides.Item(4)
Dim shape As PowerPoint.Shape
For Each shape In slide.Shapes
   errorStr = errorStr & shape.Name
   If (shape.HasTextFrame) Then errorStr = errorStr & " " & shape.TextFrame.TextRange.Runs(1, -1).Text
   errorStr = errorStr & "<br>"
Next


My first version of the code used an XMLUrlResolver to load the XML files from the Url. I liked this approach and it worked in my dev environment. However, the demo environment was locked down a bit more so I went with opening the file from the SharePoint object model.


'setup credentials for requesting infopath xml files
Dim resolver As XmlUrlResolver = New XmlUrlResolver
resolver.Credentials = System.Net.CredentialCache.DefaultCredentials
infoXml.XmlResolver = resolver
infoXml.Load(infoFile)

Skip to main content