Updating Charts in Word Document using OpenXML

• Download demo project - 45.16 KB
• Download source - 79.81 KB

Introduction

This article contains information on how to update a Chart in a Word Document using Open XML 2.0 programmatically.

Background

Let’s say you have a Word document where you would like to update a chart with some new values. I have seen some examples when Goggling on how to do so, but these articles always left out the fact that you needed to open the Word document again, right-click on the chart, open “Edit data” and then close the spreadsheet in order for the values to be updated. This application shows how to do it all automatically.

Using the code

Adding a Chart in a Word Document

1. Start Microsoft Word 2007 (or later).
2. Navigate to the ‘Insert’ tab.
3. Click on the Chart icon in the illustrations box.
4. For this example, add a Column 3d chart by selecting it from the column templates.
5. This will now open up a spreadsheet with some default values. Close this window.
6. The Chart will now appear in the Word document
7. Save the document and close it.

Implementation Details

To update a chart in the Word document, you will need to:

1. Update the spreadsheet cells
2. Get the Chart part within the docx file
3. Find the element that corresponds to the spreadsheet cell
4. Update the element
5. Save the document

The code in the UpdateChart method and InsertCellInWorksheet I just found somewhere and used it. These are the methods that can be found just by searching for Chart update in Google. I then created the ModifyChartSimplified and ModifyChartDetailed methods to actually update the Word Document. The ModifyChartSimplified and ModifyChartDetailed methods do the exact same thing, but they just illustrate the way that you can navigate through the XML elements in the Word document file.
If you open the Word Document in the Deflector tool and click on the Chart element under /word/document.xml, you can drill further down and see the XML data where the Chart data is stored.
Below I have just shown the “Series 1” XML data stored in the document file:

<c:chartSpace xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart"  
xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"  
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
  <c:date1904 val="1" />
  <c:lang val="en-US" />
  <c:chart>
    <c:view3D>
      <c:perspective val="30"  />
    </c:view3D>
    <c:plotArea>
      <c:layout />
      <c:bar3DChart>
        <c:barDir val="col" />
        <c:grouping  val="clustered" />
        <c:ser>
           <c:idx val="0" />
           <c:order val="0" />
           <c:tx>
            <c:strRef>
              <c:f>Sheet1!$B$1</c:f>
              <c:strCache>
                 <c:ptCount val="1"  />
                 <c:pt idx="0">
                    <c:v>Series  1</c:v>
                 </c:pt>
              </c:strCache>
            </c:strRef>
           </c:tx>
           <c:cat>
             <c:strRef>
               <c:f>Sheet1!$A$2:$A$5</c:f>
               <c:strCache>
                 <c:ptCount val="4"  />
                 <c:pt idx="0">
                    <c:v>Category  1</c:v>
                 </c:pt>
                 <c:pt idx="1">
                     <c:v>Category  2</c:v>
                 </c:pt>
                 <c:pt idx="2">
                    <c:v>Category  3</c:v>
                 </c:pt>
                 <c:pt idx="3">
                    <c:v>Category  4</c:v>
                 </c:pt>
               </c:strCache>
             </c:strRef>
            </c:cat>
            <c:val>
              <c:numRef>
                <c:f>Sheet1!$B$2:$B$5</c:f>
                <c:numCache>
                   <c:formatCode>General</c:formatCode>
                   <c:ptCount val="4"  />
                   <c:pt idx="0">
                     <c:v>4.3</c:v>
                   </c:pt>
                   <c:pt idx="1">
                      <c:v>2.5</c:v>
                   </c:pt>
                   <c:pt idx="2">
                      <c:v>3.5</c:v>
                   </c:pt>
                   <c:pt idx="3">
                      <c:v>4.5</c:v>
                   </c:pt>
                </c:numCache>
              </c:numRef>
            </c:val>
        </c:ser>

If you look at my ModifiedDetailed method, you can see that I basically drill down each element and get the next element. This was a great exercise in learning the OpenXML format.

private void ModifyChartDetailed
(string  cellColumn, uint intRow, string cellValue, bool  axisValue)
{
  try
  {
    ChartPart  c_p = this.mainDocPart.ChartParts.FirstOrDefault();
    Chart  chart = c_p.ChartSpace.Descendants<Chart>().FirstOrDefault();
    PlotArea  p_c = chart.PlotArea;
    Bar3DChart  b3d = p_c.Descendants<Bar3DChart>().FirstOrDefault();
    BarChartSeries  bs1 = b3d.Descendants<BarChartSeries>().Where(s  => 
    string.Compare(s.InnerText, "Sheet1!$" + cellColumn + "$1", true)  > 0).First();
    if  (axisValue)
    {
      CategoryAxisData  v1 = bs1.Descendants<CategoryAxisData>().FirstOrDefault();
      StringReference  sr = v1.Descendants<StringReference>().First();
      StringCache  sc = sr.Descendants<StringCache>().First();
      StringPoint  sp = sc.Descendants<StringPoint>().First();
      NumericValue  nv = sp.Descendants<NumericValue>().First();
      nv.Text = cellValue;
    }
    else
    {
      DocumentFormat.OpenXml.Drawing.Charts.Values v1 =  bs1.Descendants;
      DocumentFormat.OpenXml.Drawing.Charts.Values>().FirstOrDefault();
      NumberReference  nr = v1.Descendants<NumberReference>().First();
      NumberingCache  nc = nr.Descendants<NumberingCache>().First();
      NumericPoint  np = nc.Descendants<NumericPoint>().ElementAt((int)intRow - 2);
      NumericValue  nv = np.Descendants<NumericValue>().First();
      nv.Text = cellValue;
     }
  }
  catch
  {
    // Chart Element is not in a recognizable format. 
    // Most likely the defined Chart is  incorrect. Ignore the chart creation.
    return;
   }
  }

In ModifyChartSimplified, I just go to the elements I know that I will need and update them directly.

private void ModifyChartSimplified
(string  cellColumn, uint intRow, string cellValue, bool  axisValue)
{
  try
  {
    ChartPart  c_p = this.mainDocPart.ChartParts.FirstOrDefault();
    BarChartSeries  bs1 = c_p.ChartSpace.Descendants<BarChartSeries>().Where
    (s  => string.Compare(s.InnerText, "Sheet1!$" + 
		cellColumn + "$1", true)  > 0).First();
    if  (axisValue)
    {
       NumericValue  nv1 = bs1.Descendants<NumericValue>().First();
       nv1.Text = cellValue;
     }
     else
     {
       DocumentFormat.OpenXml.Drawing.Charts.Values  v1 = 
       bs1.Descendants<DocumentFormat.OpenXml.Drawing.Charts.Values>().FirstOrDefault();
       NumericPoint  np = v1.Descendants<NumericPoint>().ElementAt((int)intRow - 2);
       NumericValue  nv = np.Descendants<NumericValue>().First();
       nv.Text = cellValue;
     }
  }
  catch
  {
     // Chart Element is not in a recognizable format. 
     // Most likely the defined Chart is  incorrect. Ignore the chart creation.
     return;
  }
}

Points of Interest

Using the Deflector tool has really helped me a lot in understanding how the OpenXML SDK works. It may look overwhelming to begin with, but it is worth looking into.

Please feel free to comment on this article for improvements and criticism. When I was Googling on how to insert pictures in a Image placeholder, I found a number of articles using direct XML to find the place to update. I figured there must be a simpler way just by using the OpenXML itself, and to my relief there was.

History

• V1 - April 28, 2011 - Application created and posted