Update Cached Data and Embedded XLSX for Charts in DOCX, PPTX

I’ve released a new module, ChartUpdater.cs, which updates cached data and the embedded XLSX for charts in WordprocessingML and PresentationML.  This enables you to create a document that contains a ‘template’ chart that is formatted as you like, and then programmatically update the chart from C#.  This means that after modifying the chart with your new data, categories, and series, your users can right-click on the chart and edit the data for the chart.

For instructions on how to download and work with Open-Xml-PowerTools, see Open XML Installation Center

To examine and run the example code that shows how to use ChartUpdater.cs, open the OpenXmlPowerToolsExamples solution in Visual Studio.  You will find the solution in the OpenXmlPowerToolsExamples directory that is a sub-directory of the OxPt directory.

If your document contains multiple charts, identifying the specific chart that you want to update is problematic, so the approach that I take is to surround the chart with a rich text content control with a specific content control tag.  The ChartUpdater.UpdateChart method takes the content control tag as a parameter.  This signature of the UpdateChart method is:

public static bool UpdateChart(WordprocessingDocument wDoc, string contentControlTag, ChartData chartData)

After updating the chart data and the data in the embedded spreadsheet, ChartUpdater removes the content control.  It is not left in the document.

The ChartData class contains an array of the series names, an array of the category names, the category data type, a format code for the category, and a two dimension array of the data values for the chart:

public class ChartData
{
    public string[] SeriesNames;

    public ChartDataType CategoryDataType;
    public int CategoryFormatCode;
    public string[] CategoryNames;

    public double[][] Values;
}

Here is example code that initializes an instance of the ChartData class, and then updates a chart in an open WordprocessingML document:

var chart1Data = new ChartData
{
    SeriesNames = new[] {
        "Car",
        "Truck",
        "Van",
        "Bike",
        "Boat",
    },
    CategoryDataType = ChartDataType.String,
    CategoryNames = new[] {
        "Q1",
        "Q2",
        "Q3",
        "Q4",
    },
    Values = new double[][] {
    new double[] {
        100, 310, 220, 450,
    },
    new double[] {
        200, 300, 350, 411,
    },
    new double[] {
        80, 120, 140, 600,
    },
    new double[] {
        120, 100, 140, 400,
    },
    new double[] {
        200, 210, 210, 480,
    },
},
};
ChartUpdater.UpdateChart(wDoc, "Chart1", chart1Data);

The categories can be of type Number, String, or DateTime.

If you supply a category type of Number or DateTime, you need to specify a format code.  The format codes, as defined in the Open XML standard are:

Format Codes
0 - general
1 - 0
2 - 0.00
3 - #,##0
4 - #,##0.00
9 - 0%
10 - 0.00%
11 - 0.00E+00
12 - # ?/?
13 - # ??/??
14 - mm-dd-yy
15 - d-mmm-yy
16 - d-mmm
17 - mmm-yy
18 - h:mm AM/PM
19 - h:mm:ss AM/PM
20 - h:mm
21 - h:mm:ss
22 - m/d/yy h:mm
37 - #,##0 ;(#,##0)
38 - #,##0 ;[Red](#,##0)
39 - #,##0.00;(#,##0.00)
40 - #,##0.00;[Red](#,##0.00)
45 - mm:ss
46 - [h]:mm:ss
47 - mmss.0
48 - ##0.0E+0
49 - @

The following example code shows how to update a chart where the categories are dates:

var chart4Data = new ChartData
{
    SeriesNames = new[] {
        "Car",
        "Truck",
        "Van",
    },
    CategoryDataType = ChartDataType.DateTime,
    CategoryFormatCode = 14,
    CategoryNames = new[] {
        ToExcelInteger(new DateTime(2013, 9, 1)),
        ToExcelInteger(new DateTime(2013, 9, 2)),
        ToExcelInteger(new DateTime(2013, 9, 3)),
        ToExcelInteger(new DateTime(2013, 9, 4)),
        ToExcelInteger(new DateTime(2013, 9, 5)),
        ToExcelInteger(new DateTime(2013, 9, 6)),
        ToExcelInteger(new DateTime(2013, 9, 7)),
        ToExcelInteger(new DateTime(2013, 9, 8)),
        ToExcelInteger(new DateTime(2013, 9, 9)),
        ToExcelInteger(new DateTime(2013, 9, 10)),
        ToExcelInteger(new DateTime(2013, 9, 11)),
        ToExcelInteger(new DateTime(2013, 9, 12)),
        ToExcelInteger(new DateTime(2013, 9, 13)),
        ToExcelInteger(new DateTime(2013, 9, 14)),
        ToExcelInteger(new DateTime(2013, 9, 15)),
        ToExcelInteger(new DateTime(2013, 9, 16)),
        ToExcelInteger(new DateTime(2013, 9, 17)),
        ToExcelInteger(new DateTime(2013, 9, 18)),
        ToExcelInteger(new DateTime(2013, 9, 19)),
        ToExcelInteger(new DateTime(2013, 9, 20)),
    },
    Values = new double[][] {
    new double[] {
        1, 2, 3, 2, 3, 4, 5, 4, 5, 6, 5, 4, 5, 6, 7, 8, 7, 8, 8, 9,
    },
    new double[] {
        2, 3, 3, 4, 4, 5, 6, 7, 8, 7, 8, 9, 9, 9, 7, 8, 9, 9, 10, 11,
    },
    new double[] {
        2, 3, 3, 3, 3, 2, 2, 2, 3, 2, 3, 3, 4, 4, 4, 3, 4, 5, 5, 4,
    },
},
};
ChartUpdater.UpdateChart(wDoc, "Chart4", chart4Data);

The ToExcelInteger method is as follows:

private static string ToExcelInteger(DateTime dateTime)
{
    return dateTime.ToOADate().ToString();
}

Download – Example Code

Cheers, Eric