Partager via

Importing Charts from Spreadsheets to Wordprocessing Documents

  • Article

There have been several requests made by people asking how to import a chart from one document type to another document type. In a previous post, I showed you how to push data into a spreadsheet to create a chart. Today, I am going to show you how to import a chart from a spreadsheet to a Word document.

Solution

To import a chart from a spreadsheet to a Word document we need to take the following actions:

  1. Create a template in Word that contains a content control that will be used to demarcate the region where the chart will be inserted
  2. Open up the Word document via the Open XML SDK and access its main document part
  3. Locate the content control that will contain the chart
  4. Nuke any placeholder content within the content control
  5. Create a new run and inline drawing object to be inserted in the content control. This inline drawing will contain the referencing information for the chart
  6. Open up the spreadsheet via the Open XML SDK and access all the appropriate parts (main workbook part, worksheet part, drawing part, and chart part)
  7. Clone the chart part and add it to the Word document
  8. Clone the chart graphic information (name of the chart and properties) from the spreadsheet and add it to the Word document
  9. Give the added chart a unique name and id in the Word document
  10. Add the chart data to the content control
  11. Save changes made to the Word document

My post will talk about using version 2 of the SDK.

For the sake of this post, let's say I am starting with the following spreadsheet and chart:

Also, let's say I am starting with the following Word document, which contains a placeholder content control:

If you just want to jump straight into the code, feel free to download this solution here.

The Code

As described in the solution section above, steps two through four require us to open the Word document, find the content control that will contain the imported chart, and nuke the placeholder content of the content control to get it ready to contain the chart. Below is the code snippet necessary to accomplish those tasks:

static void ImportChartFromSpreadsheet(string spreadsheetFileName, string wordFileName) { //Open Word document using (WordprocessingDocument myWordDoc = WordprocessingDocument.Open(wordFileName, true)) { //Find the content control that will contain the chart MainDocumentPart mainPart = myWordDoc.MainDocumentPart; SdtBlock sdt = mainPart.Document.Descendants<SdtBlock>() .Where(s => a.SdtProperties.GetFirstChild<Alias>().Val.Value .Equals("Chart1")).First(); //Nuke the placeholder content of the content control Paragraph p = sdt.SdtContentBlock.GetFirstChild<Paragraph>(); p.RemoveAllChildren(); ... } }

You might notice the code used to find the proper content control looks very much like the code I used in my previous post for assembling Word documents. By nuking all the children under the paragraph within the content control, we have made it ready to contain a chart.

Instead of having run and text elements, the content control needs to have a run with an inline drawing element so that we can reference the chart properly. This task is accomplished with the following code snippet:

//Create a new run that has an inline drawing object Run r = new Run(); p.Append(r); Drawing drawing = new Drawing(); r.Append(drawing); //These dimensions work perfectly for my template document wp.Inline inline = new wp.Inline( new wp.Extent() { Cx = 5486400, Cy = 3200400 });

Notice that I have hardcoded the dimensions of the chart I will be importing. The dimensions I used are based on what I thought would work best for my template. Feel free to choose any dimension that works best for your document. At this point, the Word document is ready to have the chart imported and be contained within the content control. To import the chart we need to first get the proper chart part and then add that part to my Word document. These tasks are accomplished with the following code snippet:

//Open Excel spreadsheet using (SpreadsheetDocument mySpreadsheet = SpreadsheetDocument.Open(spreadsheetFileName, true)) { //Get all the appropriate parts WorkbookPart workbookPart = mySpreadsheet.WorkbookPart; WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById("rId1"); DrawingsPart drawingPart = worksheetPart.DrawingsPart; ChartPart chartPart = (ChartPart)drawingPart.GetPartById("rId1"); //Clone the chart part and add it to my Word document ChartPart importedChartPart = mainPart.AddPart<ChartPart>(chartPart); string relId = mainPart.GetIdOfPart(importedChartPart); ... }

The chart part is now part of my Word document package. All we have to do now is reference the chart part properly from the inline drawing contained in the content control. There are two main items we need to include to get everything to work:

  1. A graphic reference to the chart part
  2. A unique id and a chart name needs to be specified for each chart

#1 is easy to accomplish since we can reuse the same graphic objects from the spreadsheet. The main difference is we need to make sure the relationship is pointing to the appropriate chart part within the Word document. Here is the code snippet to accomplish item #1:

//The frame element contains information for the chart GraphicFrame frame = drawingPart.WorksheetDrawing.Descendants<GraphicFrame>().First(); string chartName = frame.NonVisualGraphicFrameProperties.NonVisualDrawingProperties.Name; //Clone this node so we can add it to my Word document d.Graphic clonedGraphic = (d.Graphic)frame.Graphic.CloneNode(true); ChartReference c = clonedGraphic.GraphicData.GetFirstChild<ChartReference>(); c.Id = relId;

#2 requires us to calculate a unique id for the chart. Here is a method that can accomplish this task:

static uint GetMaxDocPrId(MainDocumentPart mainPart) { uint max = 1; //Get max id value of docPr elements foreach (wp.DocProperties docPr in mainPart.Document.Descendants<wp.DocProperties>()) { uint id = docPr.Id; if (id > max) max = id; } return max; }

Almost done! The last bit of work we need to do is add the unique name and id to chart drawing and then add everything to the inline drawing object. Here is the code snippet to accomplish these tasks:

//Give the chart a unique id and name wp.DocProperties docPr = new wp.DocProperties(); docPr.Name = chartName; docPr.Id = GetMaxDocPrId(mainPart) + 1; //add the chart data to the inline drawing object inline.Append(docPr, clonedGraphic); drawing.Append(inline);

End Result

Putting everything together and running this code, we end up with a Word document, which contains the chart imported from my spreadsheet.

Here is a screenshot of the final document:

Zeyad Rajabi

Comments

  • Anonymous
    March 13, 2009
    Thank you for submitting this cool story - Trackback from DotNetShoutout

  • Anonymous
    March 18, 2009
    It’s been a couple of weeks since I posted, and I’ve come across several interesting blog posts and articles

  • Anonymous
    March 25, 2009
    I tried using this code as a sample to play with Azure.   Seems like V2 of the OpenXML SDK doesn't support partial trust scenarios.     Can anyone validate this is true or let me know if/how this can be fixed?     Thanks!

  • Anonymous
    March 25, 2009
    The first CTP of version 2 does not support partial trust scenarios. The good news is we "fixed" this issue for the next CTP. Expect the next CTP to be released in the next week or so :) Zeyad Rajabi

  • Anonymous
    April 01, 2009
    In a previous post, I showed you how to import charts from spreadsheets to a Wordprocessing document

  • Anonymous
    May 19, 2009
    One of the big changes we made in the Open XML SDK v2 April 2009 CTP was improving the Low Level DOM