Embed Charts, not Images into Slides

Introduction

Typically, when you have a need to generate slides programmatically (in BI, reporting applications), programmers tend to generate the image (of say a chart) and insert the same into a slide. Where it is required for the user to access the underlying data behind the image (chart), one should consider embedding OLE objects in slides. There are several benefits with this.

Background

In our tool, the manual process of exporting the data, converting it to charts and integrating with slides prove to be ineffective. We needed:

• Push button slide creation
• Ability to change look & feel of the charts generated by program

Approaches

We investigated several approaches of solution implementation, including make-vs-buy options. The first option was to try and build the needed libraries ourselves. This approach meant an Open XML based implementation. This approach was abandoned quite quickly due to the following:

• Coding effort needed was huge
• Maintenance would have required resource planning to change

We needed off the shelf tools and after in-depth research, we chose the library sets for “Cells” and “Slides” on .NET platform from Aspose.

Using the Code – Steps to Implement

Even though the stated requirement was to generate slides with charts, embedding the OLE object of a chart into a slide would help the user to access the Excel data behind the chart. This exactly was the need.

Implementation is quite straight forward. The steps are:

1. Create the Excel workbook with the needed chart:
   C++

   Workbook[] Cm_Wrkbk;
   Cm_Wrkbk = new Workbook[1];
   ChartSheetIndex = AddExcelChartInWorkbook(Cm_Wrkbk, InputChartType); 

2. Open the presentation, and for each slide:
   C++

   //Create a presentation
   Presentation Cm_Pptx = new Presentation();
    
   //Set the Slide Size
   int IntSldHt = 10*576 ;
   int IntSldWd = 7* 576 ;
    
   for (int Count = 0; Count < NoOfSlides; Count++)
   {

3. Convert the Excel Workbook to a MemoryStream:
   C++

   //Get the image of the chart
   Bitmap ImgChart = Cm_Wrkbk[Count].Worksheets[ChartSheetIndex].Charts[0].ToImage();
   //Save the Workbook to stream
   MemoryStream WbStream = Cm_Wrkbk[Count].SaveToStream(); 

4. Set size and select needed type of slide (e.g.: body and not title!)
   C++

   Size Sz = new Size(IntSldWd, IntSldHt);
   Slide Cm_Sld = Cm_Pptx.GetSlideByPosition(Count + 1);
    
   if (null == Cm_Sld)
   Cm_Sld = Cm_Pptx.AddBodySlide(); 

5. Add the MemoryStream(Workbook) to Slide:
   C++

   //Add the workbook on slide
   AddExcelChartInPresentation(Cm_Pptxx, Cm_Sld, WbStream, ImgChart);
    
   …..and code that handles other properties of the slide e.g., title of slide, border
   }
   return Cm_Pptx; 

With this, we now have slides with embedded OLE objects. The users of the slides are happy that they can access the reference data and also importantly (as they discovered), can change the type of chart after the slide is made – not stuck to what the program provides.

Summary

• It’s OK to use commercial SDK for internal projects. Having first toyed with the idea of doing all with OpenXML and knowing what it would have meant in terms of the effort and schedule needed, we can confidently state this.
• Choosing the right APIs with library set is important. While user requirement was generation of slides at click of mouse, the implicit expectation could only be met with OLE embedding. We were almost going to show output of just embedding images (not OLE).
• Choosing right licensing while purchasing the SDK is important too – Developer license would have been fine for our requirement. But with OEM Developer license, we can address a few external projects too.