Integrating Excel (Services) and PowerPoint with the Microsoft SDK for Open XML Formats
Today's author: Danny Khen, a program manager who works on both the Excel and Excel Services teams.
What is this about?
Office 2007 introduced new default file formats – Open XML. Excel, Word and PowerPoint take advantage of them. One of the nicest things about those file formats is that they enable easy manipulation of Office documents without needing the Office applications up and running – by simply modifying XML “parts” within the files. In June 2007 we published an SDK that facilitates this process.
On an entirely unrelated subject (not really… wait for it…), Microsoft Office SharePoint Server 2007 introduced Excel Services – a true server-side, enterprise-grade version of Excel, that enables robust and scalable calculation of Excel workbooks on the server, displaying interactive, data-connected Excel reports in a browser, and controlling proprietary information while still sharing Excel-based results to a wide audience. Excel Services expose a SOAP-based web service, which lets developers create custom solutions that use Excel models as part of their logic – helping cut IT costs by letting the subject-matter experts maintain those models on an ongoing basis, without needing a developer to change code.
All of this lends itself to a variety of interesting integrations between Office applications and server-side Excel models. I’d like to show you how you could create a useful solution this way.
The “UpdateChart” application
Imagine that you regularly create and maintain Word documents or PowerPoint slide decks, which incorporate charts with data that needs to be updated. This is common, for example, in the investment industry, where portfolio managers and analysts present their results, proposals or analyses this way to their customers. The data charts would often be created in Excel. The analyst would have an Excel workbook, with all sorts of data aggregations and calculations, most of which are not to be exposed to customers – they represent the investment house’s intellectual property. So the analyst updates and refreshes the Excel workbook, then copies the charts and pastes them into the final pitch book, which is consequently handed to the customer.
Another common case is budgeting or other what-if scenarios. For example, you prepare a sales projection, and you use an Excel model to calculate your projected sales. You then want the results in the form of a chart in a PowerPoint presentation.
How cool would it be if all of this could be automated? Furthermore, imagine that the person preparing the end document did not even have to be the person maintaining the Excel model behind it – or even have access to open the Excel file! Responsibilities could be split this way, and the document creator could easily use the Excel results – even though s/he has no open rights to the Excel model. The benefits of this are great from a compliance standpoint.
The “UpdateChart” demo application (posted here) shows a simple case of doing exactly that. In this application, a server-side Excel workbook has a model that creates a chart. Conceptually (although it is not part of the demo), this workbook could be connected to data sources, and could calculate a different, up-to-date chart each time it is run. A client-side program uses this workbook to update a chart in a PowerPoint deck. The program connects to the server using a web service, gets the up-to-date calculated workbook, extracts the updated chart from it, and uses it to replace the chart within the PowerPoint deck – all by using the Open XML SDK, and requiring neither Excel nor PowerPoint to run.
The building blocks
- Projected Sales.xlsx: An Excel workbook that contains a chart with the title "Projected Sales".
- Projected Sales.pptx: A PowerPoint presentation that contains an identical chart on one of the sheets, with an identical title.
- UpdateChart.exe: The program. This is a console (command line) application; running it with no arguments displays its usage help. In the demo package, the compiled program is located under UpdateChart\bin\Debug.
- Microsoft Office SharePoint Server 2007: Not included with this demo, but also not entirely required in order to try out some of the demo program’s capabilities.
Running “UpdateChart”
The Basics: Simple Excel and PowerPoint file integration
The simplest thing that this program can do is open the Excel file directly (not using Excel Services or Excel), grab the chart from it, and replace the chart in the PowePoint file. This is interesting from the standpoint of understanding how Open XML file manipulation works. It also highlights the fact that in Office 2007, the format of a chart in Excel and PowerPoint (and Word) is identical – they can be exchanged by mere file manipulations, without needing any of the Office applications.
To see how this works, start by opening the Excel file in Excel, setting some values to the two parameter cells (G3 and H3), and saving the file. Then open a command window, and run the following in the folder where you have the demo Excel and PowerPoint files:
UpdateChart.exe "Projected Sales.xlsx" "Projected Sales.pptx" "Projected Sales"
(The third argument is the chart title that the program looks for in both files.) If you now open the PowerPoint file, you’ll see that the chart changed to reflect the new values from the Excel file.
Moving to the server
Using UpdateChart as shown in the previous section, while demonstrating Open XML SDK usage, is not very useful from the perspective of updating a live chart. The program does not know how to connect the Excel file to external data sources, refresh the data, and re-calculate the resulting chart. It just grabs the chart in whatever state it was when it was saved.
Here’s where the server comes into play. If you have access to a MOSS 2007 server, publish the demo Excel file to the server.
Then run this in a folder containing the demo PowerPoint file:
UpdateChart.exe -s <server_name> "<full path to server Excel file>" "Projected Sales.pptx" "Projected Sales"
You can now open your PowerPoint file and see that the chart is updated. Realize that this represents a much broader and stronger class of solutions than is actually implemented here; the Excel file on the server could be connected to data sources, and could generate a different updated file each time it is run, or potentially even different for each user who runs it.
Parameterization
What if your Excel model relies on parameter values within the workbook, instead of (or in addition to) external data sources? You’d want to let your users change some values in the workbook, and have those values affect the resulting chart. Well, this is possible, even if the users have no open access to the Excel file on the server – just the right to run it!
To demonstrate this, I’ve designated the two parameter cells (G3 and H3) as named parameters that the server recognizes (you’ll need to select them as server parameters in Excel’s “Publish” dialog when you publish the workbook to the server). Now, a user – or a program – can easily set values to those parameters, causing Excel Services to recalculate the Excel models with the new values and affecting the resulting chart. Our program does exactly that.
To see this, add an extra argument to the command line, that sets the parameter “Growth2007” to 70%:
UpdateChart.exe -s <server_name> -p Growth2007 "70%" "<full path to server Excel file>" "Projected Sales.pptx" "Projected Sales"
Your PowerPoint file should show a chart that reflects this growth in 2007.
What the code does
Note: The code sample, UpdateChart, was created using Microsoft Visual Studio 2008, however you do not need this particular version of Visual Studio to use the code. You can use the main source file, Program.cs, in a project of a previous VS version, or just open the file in a text editor and grab the pieces of the code that you find useful.
UpdateChart’s code does the following:
- Parses arguments.
- In the case of server usage (-s), uses the Excel Web Service to open a server workbook, optionally set a parameter value (when -p is used), and get the resulting workbook. The workbook is returned into the program as a bite array; this is a snapshot of the entire server-side Excel workbook, in the calculated state that it was when it was retrieved from the server.
- In all cases, the code uses the Open XML SDK to:
- Look for and extract the chart part within the Excel file.
- Look for the chart part in the PowerPoint file, then override it with the new Excel chart.
One thing to notice is that in the case of the server-side Excel file, no client-side temporary file is used. The code receives the server result directly into memory as a byte array, then uses the option to initialize an Open XML file package from a stream – feeding the byte array directly into the package.
In the client-side Excel file case, the code simply initializes the Open XML package from a file.
Useful links
- Code sample for this post: http://officeblogs.net/excel/updatechart.zip
- Open XML SDK download: http://www.microsoft.com/downloads/details.aspx?FamilyID=AD0B72FB-4A1D-4C52-BDB5-7DD7E816D046&displaylang=en
- MOSS 2007 SDK – Creating Custom Solutions with Excel Services: http://msdn2.microsoft.com/en-us/library/ms517343.aspx
- MOSS 2007 SDK – Excel Web Service reference: http://msdn2.microsoft.com/en-us/library/microsoft.office.excel.server.webservices.aspx
