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.

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:

  1. Title slide – This slide represents the title of the presentation deck
  2. Template category section slide – This slide represents the divider that will separate different sections of my deck based on categories
  3. 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:

  1. Open up the template presentation via the Open XML SDK and access its main presentation part
  2. From the presentation part, access the two template slides (category section slide and product table slide)
  3. Connect to the Adventure Works database and query for the list of products sorted by category
  4. Go through every product returned in the query
  5. For every new category encountered, clone the template category section slide and change the placeholder text to be the actual category name
  6. 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
  7. For every product, add an image part to the slide and feed the image part with data from the database
  8. 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
  9. Delete the two template slides
  10. Save and close the presentation

The Code

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:

  1. SlidePart CloneSlidePart(PresentationPart presentationPart, SlidePart slideTemplate)
    1. This method will be used to actually clone slide parts
  2. void SwapPlaceholderText(SlidePart slidePart, string placeholder, string value)
    1. This method will be used to swap placeholder text for a new value
  3. void DeleteTemplateSlide(PresentationPart presentationPart, SlidePart slideTemplate)
    1. 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

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:

  1. void CalculateImageEmus(Bitmap bitmap, out int widthInEmu, out int heightInEmu)
    1. This method will be used to calculate image widths and heights in EMUs. The image height will be used to calculate the row height

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:

using (PresentationDocument myPres = PresentationDocument.Open("output.pptx", true))
{
PresentationPart presPart = myPres.PresentationPart;
SlidePart sectionSlidePart = (SlidePart)presPart.GetPartById("rId3");
SlidePart tableSlidePart = (SlidePart)presPart.GetPartById("rId4");
...
}

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:

AdventureWorksDataContext db = new AdventureWorksDataContext();
var productQuery =
from p in db.Products
join PM in db.ProductModels on p.ProductModelID equals PM.ProductModelID
join PSC in db.ProductSubcategories on p.ProductSubcategoryID equals PSC.ProductSubcategoryID
join PC in db.ProductCategories on PSC.ProductCategoryID equals PC.ProductCategoryID
orderby PC.Name, PSC.Name
select p;

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:

string section = "";
foreach (var product in productQuery)
{
string category = product.ProductSubcategory.ProductCategory.Name;
string subcategory = product.ProductSubcategory.Name;
string model = product.ProductModel.Name;
string productName = product.Name;
decimal price = Math.Round(product.ListPrice, 2);
if (section != category)
{
SlidePart newSectionPart = CloneSlidePart(presPart, sectionSlidePart);
SwapPlaceholderText(newSectionPart, "Section Title", category);
section = category;
overflow = true;
}
...
}

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:

string imageRel = "imageRelId";
int imageRelId = 1;
...
ImagePart imagePart = current.AddImagePart(ImagePartType.Gif, imageRel + imageRelId);
imagePart.FeedData(new
MemoryStream(product.ProductProductPhotos.First().ProductPhoto.LargePhoto.ToArray()));
// We need to know the proper dimensions of the image in Emus
Bitmap bitmap = new Bitmap(new MemoryStream(product.ProductProductPhotos.First().ProductPhoto.LargePhoto.ToArray()));
CalculateImageEmus(bitmap, out widthInEmu, out heightInEmu);
totalHeight += heightInEmu;
if (totalHeight > 4000000)
overflow = true;

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.

Zeyad Rajabi