Creating a Presentation Report Based on Data

  • Article

In my last two posts, I showed you how to create a spreadsheet based on data and how to read data from spreadsheet. Today, I am going to move away from SpreadsheetML to talk about PresentationML. Specifically, I am going to show you how to create a rich presentation report based on data from a database.

Scenario – Document Assembly

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 all data pertaining to our sales force. For example, we keep track of our entire sales team in terms of contact information, territories they own, their total sales, bonus, etc. I have been asked to build a report generation tool that is able to take this data and create a viewable presentation. The sales team would like to have this solution run on the server, so there is a strict requirement that we cannot use the PowerPoint client.

Solution

Before I get into the details of my solution I want to call out a couple of things:

  1. My solution is based on the freely available Adventure Works database built for SQL Server 2005
  2. I updated the adventure works database to include contact photos (source code included in the link below)

Okay, we're ready to get started. If you just want to jump straight into the code, feel free to download this solution here.

Step 1 – Create a Template

The scenario I listed above talks about reading sales person data and creating a report in the form of a presentation. 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 a slide template that has placeholder regions for the following information:

  • Sales person contact information, such as name, email address and photo
  • Sales summary information, such as territory owned by sales person, total sales made by sales person, sales quota
  • Extra information, such as bonus amount and commission %

My slide template will look like the following:

Step 2 – Clone the Slide Template

For each sales person in Adventure Works, I will need to clone the slide template, shown above, and fill in the necessary information based on data from my database. Below is some code that does the following:

  • Create a brand new slide for each sales person
  • Copy over the content from my slide template into my new slide
  • Make sure that my new slide references the slide template's slide layout (since my new slide has the same layout of my slide template)
    • Add my new slide to the end of my presentation (think adding a node to the end of a linked list)
SlidePart CloneSlidePart(PresentationPart presentationPart, SlidePart slideTemplate) { //Create a new slide part in the presentation SlidePart newSlidePart = presentationPart.AddNewPart<SlidePart>("newSlide" + i); i++; //Add the slide template content into the new slide newSlidePart.FeedData(slideTemplate.GetStream(FileMode.Open)); //make sure the new slide references the proper slide layout newSlidePart.AddPart(slideTemplate.SlideLayoutPart); //Get the list of slide ids SlideIdList slideIdList = presentationPart.Presentation.SlideIdList; //Figure out where to add the next slide (find max slide) uint maxSlideId = 1; SlideId prevSlideId = null; foreach (SlideId slideId in slideIdList.ChildElements) { if (slideId.Id > maxSlideId) { maxSlideId = slideId.Id; prevSlideId = slideId; } } maxSlideId++; //Add new slide at the end of the deck SlideId newSlideId = slideIdList.InsertAfter(new SlideId(), prevSlideId); //Make sure id and relid is set appropriately newSlideId.Id = maxSlideId; newSlideId.RelationshipId = presentationPart.GetIdOfPart(newSlidePart); return newSlidePart; }

Step 3 – Swap out Placeholder Text

At this point I have just cloned my slide template and added my new slide, which at the moment is an exact copy of the slide template, to my presentation. My next step is to swap out the placeholder text in my new slide with the appropriate data. I created a method that finds all placeholder text locations and swaps out the placeholder text with another string given a slide part. Here is the code:

void SwapPlaceholderText(SlidePart slidePart, string placeholder, string value) { //Find and get all the placeholder text locations List<Drawing.Text> textList = slidePart.Slide.Descendants<Drawing.Text>() .Where(t => t.Text.Equals(placeholder)).ToList(); //Swap the placeholder text with the text from DB foreach (Drawing.Text text in textList) text.Text = value; }

Step 4 – Swap out Placeholder Photo

My slide template includes one placeholder picture that is intended to represent the image of a sales person. In order for me to swap out this photo, I am going to need to do the following:

  • Add an image part to my new slide, which is based on my slide template
  • Insert my image data into my newly added image part
  • Swap the photo reference from my placeholder image to my new image

The following code accomplishes the above tasks:

//Add an image to the new slide ImagePart imagePart = newSlide.AddImagePart(ImagePartType.Gif, imgId); //Add image data to new image part imagePart.FeedData(new MemoryStream(item.Employee.Contact.Photo.ToArray())); ...     //Swap photo reference and save SwapPhoto(newSlide, imgId);

Swapping out the photo is pretty easy since images are simply referenced via ids. In my case, all I need to do is find the image reference and swap it out with the image reference of the new image I added to the slide. The following code accomplishes these tasks:

void SwapPhoto(SlidePart slidePart, string imgId) { //Find the placeholder image Drawing.Blip blip = slidePart.Slide.Descendants<Drawing.Blip>().First(); //Swap out the placeholder image with the image from DB blip.Embed = imgId; //Save the part slidePart.Slide.Save(); }

Step 5 – Delete Template Slide

Before we can call this solution complete we need to make sure to delete the slide template. Since I already know the relationship id of my slide template I can delete the slide with the following code:

void DeleteTemplateSlide(PresentationPart presentationPart, SlidePart slideTemplate) { //Get the list of slide ids SlideIdList slideIdList = presentationPart.Presentation.SlideIdList; //Delete the template slide reference foreach (SlideId slideId in slideIdList.ChildElements) { if (slideId.RelationshipId.Value.Equals("rId3")) slideIdList.RemoveChild(slideId); } //Delete the template slide presentationPart.DeletePart(slideTemplate); }

End Result

Putting everything together and running my code, I will end up with the following output:

Zeyad Rajabi