This project has moved. For the latest updates, please go here.

How to format Excel chart data series?

Dec 5, 2011 at 6:26 PM

Hi everyone,

I'm trying to change the fill color of individual data series after creating the chart in Excel and according to MSDN (http://msdn.microsoft.com/en-us/library/ff838988.aspx) it should be as simple as this:

Chart.SeriesCollection(1).Interior.Color = RGB(255, 0, 0)

However, Chart.SeriesCollection method in NetOffice does not have this property...

Here is my code...

var myWorkSheet = (Excel.Worksheet)workBook.Worksheets[1];
Excel.Range dataRange = PutSessionData(myWorkSheet , myData);
Excel.ChartObject chart = ((Excel.ChartObjects)myWorkSheet .ChartObjects()).Add(50, 50, 375, 225);                
chart.Chart.SeriesCollection(0) // Interior property not available...

Thanks...

Coordinator
Dec 5, 2011 at 6:58 PM
Edited Dec 5, 2011 at 6:59 PM

Hello netizenk,

the msdn example is a VBA Code-Snippet. programming excel in vba is a little bit easier, you dont need a specific cast for any object.
The type of the collection is not known at design, In .NET you have to cast the collection at runtime, in VBA the script interpreter do that for you.

Excel.Series series = (Excel.Series)chart.Chart.SeriesCollection(1);

please keep in your mind, all collections in office starts with the lower bound index 1.



Dec 5, 2011 at 7:26 PM

Great, thanks a lot, but this gives me a collection of data series and I don't see how to access individual data series to change their color... thanks...

Dec 5, 2011 at 7:54 PM
Edited Dec 5, 2011 at 7:55 PM

To answer myself, data series are Points of the SeriesCollection so here is how to change the individual data series color using NetOffice:

 

                var chartSeries = (Excel.Series) chart.Chart.SeriesCollection(1);
                var p1 = (Excel.Point) chartSeries.Points(1);
                p1.Interior.Color = Color.LightGreen;
                var p2 = (Excel.Point) chartSeries.Points(2);
                p2.Interior.Color = Color.Crimson;
Coordinator
Dec 5, 2011 at 8:55 PM

thanks for the sample code!
see you next time :)
*sebastian