Search code examples
c#powerpointoffice-interopnetoffice

read/write Values/XValues in a SeriesCollection

migrating an old VBA project to C# using Interop to manipulate Powerpoint and trying to find a way to replicate some of the old solution

I can get my Series Collection using:

NetOffice.PowerPointApi.Series test_sc = (NetOffice.PowerPointApi.Series)sh.Chart.SeriesCollection(1);

but trying to access the Values or XValues isn't working as I expected

I can get the content using either

string[] arr = ((IEnumerable)test_sc.Values).Cast<object>()
                       .Select(x => x.ToString())
                       .ToArray();

or

foreach (object item in (Array)test_sc.Values) { var aa = item;  }

but that doesn't let me update things.

For now I am using the ChartData.WorkBook.Worksheet.Cells.get_Range mechanism to update the underlying sheet but this is complicated by the fact the series isn't always using the same cell range in the worksheet depending on the source file so I can't simple map the old SeriesCollection[1].Values to a Range["b2..m2"] or whatever.

1/ Am I missing something obvious (c# isn't my usual platform, so quite possible) to get read/write access to the Values/XValues for a collection and 2/ Is there a better way that will give me relative access to the underlying range that the series represents

Solution

  • To update the values (on the value axis) you should be able to do that by e.g. a double array:

    double[] someNewValues = {6,3,4,7,3};
PowerPoint.Series ser = chart.SeriesCollection(1);
ser.Values=someNewValues;

    In my experience updating values on the category axis (i.e. ser.XValues) can be more tricky. If it is a scatter plot it doesn't seem to work with double values but converting to floats have worked for me.