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
- Start Microsoft Word 2007 (or later).
- Navigate to the ‘Insert’ tab.
- Click on the Chart icon in the illustrations box.
- For this example, add a Column 3d chart by selecting it from the column templates.
- This will now open up a spreadsheet with some default values. Close this window.
- The Chart will now appear in the Word document
- Save the document and close it.
Implementation Details
To update a chart in the Word document, you will need to:
- Update the spreadsheet cells
- Get the Chart part within the docx file
- Find the element that corresponds to the spreadsheet cell
- Update the element
- 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
{
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
{
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
