Scenario

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.

Solution

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:

Title slide – This slide represents the title of the presentation deck

Template category section slide – This slide represents the divider that will separate different sections of my deck based on categories

Template product table slide – This slide represents the table I will extend based on the products in my database

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:

Open up the template presentation via the Open XML SDK and access its main presentation part

From the presentation part, access the two template slides (category section slide and product table slide)

Connect to the Adventure Works database and query for the list of products sorted by category

Go through every product returned in the query

For every new category encountered, clone the template category section slide and change the placeholder text to be the actual category name

Clone the template product table slide at the start of every new section or if the product table becomes too large and needs to be extended to the next slide

For every product, add an image part to the slide and feed the image part with data from the database

For every product, add a new row to the appropriate cloned table slide. These rows will contain five cells, where the first cell will contain a background image of the product, and the other four cells will contain text

This method will be used to delete the template slides. I extended this method to have an additional parameter in order to specify the relationship id of the part to be deleted. It seems that my previous post actually hardcoded the value in the method

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:

bool overflow = false;

int totalHeight = 0;

SlidePart current = null;

foreach (var product in productQuery)

{

…

if (overflow)

{

SlidePart newTablePart = CloneSlidePart(presPart, tableSlidePart);

SwapPlaceholderText(newTablePart, “Section”, category);

current = newTablePart;

overflow = false;

totalHeight = 0;

}

…

}

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:

A.Table tbl = current.Slide.Descendants<A.Table>().First();

A.TableRow tr = new A.TableRow();

tr.Height = heightInEmu;

tr.Append(CreateDrawingCell(imageRel + imageRelId));

tr.Append(CreateTextCell(category));

tr.Append(CreateTextCell(subcategory));

tr.Append(CreateTextCell(model));

tr.Append(CreateTextCell(price.ToString()));

tbl.Append(tr);

imageRelId++;

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:

static A.TableCell CreateTextCell(string text)

{

A.TableCell tc = new A.TableCell(

new A.TextBody(

new A.BodyProperties(),

new A.Paragraph(

new A.Run(

new A.Text(text)))),

new A.TableCellProperties());

return tc;

}

Here is the code snippet used to create an image cell:

static A.TableCell CreateDrawingCell(string relId)

{

A.TableCell tc = new A.TableCell(

new A.TextBody(

new A.BodyProperties(),

new A.Paragraph()),

new A.TableCellProperties(

new A.BlipFill(

new A.Blip() { Embed = relId },

new A.Stretch(

new A.FillRectangle()))));

return tc;

}

Almost done! The last step is to delete the two template slides. Here is the code snippet used to accomplish this task:

DeleteTemplateSlide(presPart, sectionSlidePart, “rId3”);

DeleteTemplateSlide(presPart, tableSlidePart, “rId4”);

End Result

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.

I have a problem with clonning template slide with the chart. I have a template slide to fill only the data of the chart in it. All seems to be OK, but when I open the resulting pptx file, I get the error message and new cloned slides are empty. Can somebody advise me? Thanks a lot.