How can I change values in a PowerPoint chart using VSTO and C #?
I need to change the original data in a PowerPoint chart using VSTO. The first code snippet shows how I am getting a series from my form object.
SeriesCollection seriesCollection = shape.Chart.SeriesCollection();
Series series = seriesCollection.Item(1);
I tested that it works by calling
series.Delete()
Which will remove the series from the chart. However, if possible, I would like to simply change the series values. I've tried the following:
series.Values = myArray1;
series.XValues = myArray2;
The result is an empty graph. (Series titles and title remain, but data not available).
Does anyone know how this can be done? My real solution is to restore the chart in excel and replace it in PowerPoint, but I was hoping there is a more efficient way.
source to share
Powerpoint schema is based on a basic Excel sheet with chart data stored in cells. If you call the context menu for a chart and click Edit Data ..., Excel will launch with a worksheet containing the values from the chart.
Therefore, you should modify your Powerpoint chart without setting the series values directly. You must do this by updating the cell values in the main Excel worksheet. Here's a working example:
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();
}
There are two hardcoded values in the above code:
var colNumber = 2;
var firstRowNumber = 2;
The chart underlying the table has a predefined structure. For example, for the default chart, it builds the following workbook:
Thus, you can rely on the following assumptions when changing data in a worksheet:
- The first row for a series of values is 2.
- The column corresponding to specific series is
1 + series index
. The serial index here is the same index that you pass to the callSeriesCollection.Item(object Index)
to get the series (starting at1
). So the second worksheet table corresponds to the 1st map series, etc.
UPDATE (for waterfall diagram issue)
The waterfall diagram issue is also reproducible in my environment. I have checked all the other 15 chart types and the same error occurs for the following ones:
- Treemap
- Sunburst
- bar chart
- Box and whisker
- Waterfall
The root cause of the problem is that the call chartData.Activate()
does not actually open the sheet associated with the chart. The problem is specific to Interop because the worksheet opens successfully if you select "Edit Data ..." in PowerPoint.
All of these charts were added in Office 2016. Everything works fine for charts that were available in previous versions of Office. It looks like the problem is related to the problem of integrating Interop assemblies with office 2016. There are still no primary builds for Office 2016, they still ship with the PIA from Office 2013. Unfortunately, I cannot offer a solution or workaround for this particular issue. We have to wait for a fix from the Microsoft team.
source to share