How can I change the values in a PowerPoint chart using VSTO and C#?

Question

I need to change the source data in a PowerPoint chart using VSTO. The first code snippet shows how I get the series from my shape object.

SeriesCollection seriesCollection = shape.Chart.SeriesCollection();
Series series = seriesCollection.Item(1);

I've tested that it works by calling

series.Delete()

Which will remove the series from the chart. However, if possible, I would like to just change the values of the series instead. I've tried the following:

series.Values = myArray1;
series.XValues = myArray2;

Which results in an empty graph. (The Series names and title remain, but the data is missing).

Does anyone know how this can be accomplished? My current solution is to regenerate the chart in excel and replace the one in PowerPoint, but I was hoping there is a more efficient way.

Answer 1

Powerpoint chart is based on underlying Excel worksheet with chart data stored in the cells. If you invoke context menu for the chart and click "Edit Data...", Excel application will be launched with worksheet containing values from the chart.

Therefore you should modify Powerpoint chart not by setting series values directly. You should do it by updating cells values in underlying Excel worksheet. Here is a working sample:

static void Main(string[] args)
{
    var powerPointApp = new Microsoft.Office.Interop.PowerPoint.Application();
    var presentation = powerPointApp.Presentations.Open(@"Presentation1.pptx");
    var slide = presentation.Slides[1];
    var shape = slide.Shapes[1];

    var chartData = shape.Chart.ChartData;
    chartData.Activate();

    var workbook = chartData.Workbook;
    workbook.Application.Visible = false;
    var dataSheet = workbook.Worksheets[1];

    double[] newData = {1, 2, 3, 4, 5};

    var colNumber = 2;
    var firstRowNumber = 2;

    //  Clearing previous data
    dataSheet.UsedRange.Columns[colNumber, Type.Missing].Clear();

    for (var i = 0; i < newData.Length; ++i)
    {
        dataSheet.Cells[firstRowNumber + i, colNumber] = newData[i];
    }

    //  Saving and closing Excel workbook before presentation could be saved.
    workbook.Close(true);
    presentation.Save();
    presentation.Close();
    powerPointApp.Quit();
}

In code above there are two hardcoded values:

var colNumber = 2;
var firstRowNumber = 2;

Chart underlying worksheet has a predefined structure. For example for default chart it builds following workbook:

So you could rely on following assumptions when modifying the data in worksheet:

• The first row for the values series is equal to 2.
• The column corresponding to specific series is equal to 1 + series index. Series index here is the same index that you pass to SeriesCollection.Item(object Index) call to get the series (starting from 1). So 2nd worksheet column correspond to the 1st chart series and so on.

UPDATE (For problem with Waterfall chart)

The problem with Waterfall chart also reproduces in my environment. I have checked all other 15 chart types and the same error happens for following of them:

• Treemap
• Sunburst
• Histogram
• Box & Whisker
• Waterfall

The root cause of the problem is that chartData.Activate() call does not actually open worksheet associated with the chart. The problem is specific to Interop because the worksheet is opened successfully if you select "Edit data..." in PowerPoint application.

All these charts were added in Office 2016. Everything works fine for charts that were available in previous versions of the Office. Seems like the issue is caused by problem of Interop assemblies intergration with 2016 office. There are still no Primary Interop Assemblies for Office 2016, it still ships with PIA from Office 2013. Unfortunately, I can't propose a fix or workaround for this specific problem. We have to wait for the fix from Microsoft team.