Generate a PowerPoint Presentation based on Site Data

  • Article

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: https://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", "https://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", "https://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)