Needing to copy a chart from a spreadsheet into a presentation is probably a common scenario for many of us. Luckily the Open XML SDK makes this remarkably easy. Let me show you how.
Note: For this code to work you need the Open XML Format SDK 2.0 – April CTP which you can get on MSDN.(
http://www.microsoft.com/downloads/details.aspx?FamilyId=C6E744E5-36E9-45F5-8D8C-331DF206E0D0)
There are three steps involved in moving a chart from a spreadsheet into a presentation.
- Opening the source worksheet and target slide
- Copying the chart ( a separate part in the package)
- Creating a shape that references the chart
Let's go ahead and implement this feature. You can get the code for this sample here (http://blogs.code-counsel.net/Wouter/Downloads/CopyChart.zip)
First you will need to create spreadsheet similar to the following sample. I am using a simple table and a bar-chart.
Step 1 – Opening the spreadsheet and the presentation
The first step is to open both the spreadsheet and the presentation. After opening these two files the source and target parts need to be retrieved. We will copy the chart from a worksheet into a slide. This sample code assumes that the first worksheet contains the chart, and that the chart is copied to the first slide in the presentation. After retrieving the source and target parts the chart is copied and then a shape is added to the presentation that shows the chart.
using (SpreadsheetDocument spreadsheet =
SpreadsheetDocument.Open("Spreadsheet.xlsx", false))
using(PresentationDocument presentation =
PresentationDocument.Open("Presentation.pptx", true))
{
// Gets the from / to items
WorksheetPart worksheet = spreadsheet.WorkbookPart.WorksheetParts.First();
SlidePart slide = presentation.PresentationPart.SlideParts.First();
// Copies the chart from the spreadsheet into the presentation
string relationshipID = ImportChartIntoPresentation(worksheet, slide);
// Adds a shape to the slide that shows the chart
ReferenceChartFromSlide(slide, relationshipID);
}
Step 2 – Copying the chart data
Both SpreadsheetML and PresentationML use the same format for defining charts. The chart format is also fully standardized as part of DrawingML (the same DrawingML that allows you to do cool things such as SmartArt graphics). Reusing a chart from a spreadsheet in a presentation is therefore very easy. You only need to copy the full XML definition of the chart into the presentation. The key difference between SpreadsheetML and PresentationML when it comes to charting is how the chart is embedded into the document. SpreadsheetML uses cell-based anchors, something which you can obviously not use in a presentation. PresentationML uses a special shape to reference the chart.
The following code opens the chart referenced by the worksheet. It then creates a new chart attached to the slide and copies over the XML. Note that the XML definition of the chart, which includes the plotted data, is not changed. This makes it easy for us since we only need to worry about how the slide will display the chart.
static string ImportChartIntoPresentation(WorksheetPart worksheet, SlidePart slide)
{
// Assume chart is the only drawing on the first worksheet
DrawingsPart worksheetDrawings = worksheet.DrawingsPart;
ChartPart spreadsheetChart = worksheetDrawings.ChartParts.First();
// Copy the chart over to the first slide of the presentation
ChartPart presentationChart = slide.AddNewPart<ChartPart>();
using (Stream stream = spreadsheetChart.GetStream())
{
presentationChart.FeedData(stream);
}
// Return the relationship ID so the chart shape can
// reference the chart
return slide.GetIdOfPart(presentationChart);
}
Step 3 – Creating a chart shape
The last step is to create a shape in the presentation that points to the newly added chart part. Presentations use the p:graphicFrame shape to hold pointers to special shapes like a chart. The graphic frame is a normal shape with the same unique identifiers as other shapes and with information on size and positioning. The XML structure that you need to create is not very complex. The easiest way to find out what to create is to use an Open XML enabled office suite such as Lotus Symphony (didn't some company deem Open XML too complex to implement?) to create a chart and then look at the XML that is being generated.
The following sample shows how to define a shape on a slide that points to a chart. A few things of interest are the unique identifiers, the positioning of the chart, the fact that the graphic is pointing to a chart and the relationship ID of the chart.
First let's discuss the unique identifiers stored in the p:cNvPr node. Well, they need to be unique... Insightful isn't it? You can use X-Path to ensure the ID and name are unique, or just hope for the best like I am doing in the code sample accompanying this blog post.
The size and position stored in the p:xfrm element are also important if you want people to actually see the chart. Shapes of 0-width are supported. Both the position and size are specified in EMUs (English Metric Units). Think of an EMU as the least common denominator between various measurement systems such as meters, inches and pixels. This ensures you do not need floats for calculating positions. Computers do not like floats, they like integers much better.
The a:graphicData indicates that it contains a reference to a chart by using the uri attribute. You can store a variety of graphics in a graphic frame and this attribute indicates exactly what can be expected. The chart itself is not stored inline but it is referenced using a relationship ID. The XML structure is similar to the following example.
<p:graphicFrame>
<p:nvGraphicFramePr>
<p:cNvPr id="2" name="Chart 1" />
<p:cNvGraphicFramePr />
<p:nvPr />
</p:nvGraphicFramePr>
<p:xfrm>
<a:off x="1524000" y="1397000" />
<a:ext cx="6096000" cy="4064000" />
</p:xfrm>
<a:graphic>
<a:graphicData uri="http://schemas.openxmlformats.org/drawingml/2006/chart">
<c:chart r:id="rId2" />
</a:graphicData>
</a:graphic>
</p:graphicFrame>
The code to generate this markup is very similar to the markup itself. The Open XML SDK provides us with classes corresponding to each XML element. The classes can be composed together by using the constructor. The first few bits are displayed below. As you can see it follows the exact same XML structure. Later versions of the SKD might provide an even higher level of abstraction.
GraphicFrame graphicFrame = new GraphicFrame(
new NonVisualGraphicFrameProperties(
new NonVisualDrawingProperties()
{
Id = 2, // should calculate this to ensure it is unique
Name = "Chart 1" // should also be unique
},
new NonVisualGraphicFrameDrawingProperties(),
new AppNonVisualDrawingProperties()),
That's it. Now if you open the presentation you should see something that looks like the following picture. Outside of improving our sizing and positioning code you should be good to go!
Hope it helps! And have fun developing with the Open XML SDK!