Click here to Skip to main content
11,645,489 members (71,128 online)
Click here to Skip to main content

Updating Charts in Word Document using OpenXML

, 29 Apr 2011 CPOL 20.4K 1.5K 10
Rate this:
Please Sign up or sign in to vote.
This article contains information on how to update a chart in a Word document.

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Imran S Mohamed
United States United States
No Biography provided

You may also be interested in...

Comments and Discussions

 
QuestionUpdate data content is not working Pin
Laura Gabriela10-Jun-15 6:59
memberLaura Gabriela10-Jun-15 6:59 
QuestionChart is not refreshing Pin
mariavillalonga25-Aug-14 7:13
membermariavillalonga25-Aug-14 7:13 
GeneralBeautiful Article Man....Thank you. Pin
Member 431555726-Jul-14 22:34
memberMember 431555726-Jul-14 22:34 
GeneralMy vote of 2 Pin
netizenk5-Sep-13 9:00
membernetizenk5-Sep-13 9:00 
Generaldo you know how to increase the chart area according to the data rows i hav??? Pin
Hasi12345612-Jun-11 19:23
memberHasi12345612-Jun-11 19:23 
GeneralDeflector? Pin
jspaey7-Jun-11 2:27
memberjspaey7-Jun-11 2:27 
GeneralRe: Deflector? Pin
Imran S Mohamed7-Jun-11 3:07
memberImran S Mohamed7-Jun-11 3:07 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150731.1 | Last Updated 29 Apr 2011
Article Copyright 2011 by Imran S Mohamed
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid