In a previous post, I showed you how to create a product catalog in a Word document using the Open XML SDK. I also showed you how to make PowerPoint a reporting application based on data within a database. Today, I am going to show you how to create a product catalog in a PowerPoint deck using the Open XML SDK. One of the key things I am going to show you in this post is how to create repeating data within a table on a slide.
If you want to jump straight into the code, feel free to download this solution here.
Imagine a scenario where I'm a developer working for a fictional company called Adventure Works. In my company, we use a database to store our entire product inventory. The sales and marketing teams have asked me to build a report generation tool that is able to take the list of products and create a viewable presentation. In other words, these teams want to use a PowerPoint deck to showcase all our company's products.
Before I get into the details of my solution I want to state that I am using the freely available Adventure Works database built for SQL Server 2008.
The scenario I listed above talks about reading product data and creating a report in the form of a presentation. The products in the database are organized into categories, like clothing or bikes, and subcategories, like mountain bikes or road bikes. To make viewing the resulting presentation easier I want to make sure my slides are also organized and separated based on categories. To accomplish this task, the first thing I need to do is create a presentation template that I can use for my solution. In this case, my presentation template will contain three slides:
My presentation template will look like the following:
Given this template, here is one way to automatically generate a PowerPoint deck based off data from a database:
I should note that this solution will reuse a bit of functionality and methods from my previous post on creating a presentation report based on data. Specifically, I am going to reuse the following methods:
In addition to the three above methods, I will also reuse functionality from my previous post on pushing data from a database into a Word document. Specifically, I am going to reuse the following method:
Reusing these methods will actually make coding this solution a lot easier!
The first couple of steps require us to open the presentation template and access the two template slide parts. Here is the code snippet used to accomplish this task:
The next step is to query the database to retrieve a list of products sorted by category. Here is the code snippet used to accomplish this task:
Now we should be able to go through the list of products. As described in the solution section, for every product we encounter in our query we may need to add a new category slide or a new product table slide. We will add a new category slide if we encounter a new category (remember we are sorting our products based on category). Here is the code snippet used to accomplish this task:
We will add a new product table slide whenever we encounter the first product within a category or if adding a new row to the current slide's table would cause the table to be too high and off of the visible slide. Here is the code snippet used to accomplish this task:
Notice that I am using the Boolean value of overflow to indicate whether or not a table has too much content in it already. The next step is to add an image for every product we encounter. In addition, we need to calculate the height of the image so that we can keep track of how much content is in the current table. The height of the image will be used for the height of the added row. If the height becomes too large then we will need to make sure that overflow is set to true. Here is the code snippet used to accomplish this task:
The next step is to add a new table row that contains five cells. The first cell is going to include the product image as a background image in the cell and the other four cells will contain text. Here is the code snippet used to create the new row:
Note that variable "A" refers to
using A = DocumentFormat.OpenXml.Drawing;
I created two methods to create these two types of table cells. Here is the code snippet used to create a text cell:
Here is the code snippet used to create an image cell:
Almost done! The last step is to delete the two template slides. Here is the code snippet used to accomplish this task:
Running this code I end up with a presentation that has over one hundred slides.
Here is another view of the output:
Pretty cool stuff. The best part about this solution is that a designer can easily change the look of the template and still have this solution work as expected. For example, here is the output after the template design has been changed (same code running):
I should also note that all the screenshots above were taken with Office 2010. In other words, this solution works in Office 2007 and Office 2010.