Adding series to Excel chart exception

Question

I have an excel file filled with some data. I am trying to open the second sheet and create a chart. The problem is that the Series are giving me either a System.Runtime.InteropServices.COMException was caught or if I un-comment the commented lines a No overload for method 'SeriesCollection' takes '0' arguments. Here is the code that I have:

Microsoft.Office.Interop.Excel.ChartObjects chartObjs = (Microsoft.Office.Interop.Excel.ChartObjects)ws.ChartObjects(Type.Missing);
            Microsoft.Office.Interop.Excel.ChartObject chartObj = chartObjs.Add(100, 20, 300, 300);
            Microsoft.Office.Interop.Excel.Chart xlChart = chartObj.Chart;

            Range rg1 = ws.get_Range("A1", "D" + rowcount);
            rg1.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

            xlChart.SetSourceData(rg1, Microsoft.Office.Interop.Excel.XlRowCol.xlColumns);
            xlChart.ChartType = XlChartType.xlLine;
            xlChart.Legend.Position = XlLegendPosition.xlLegendPositionBottom;

            Axis axis = (Axis)xlChart.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);

            axis.MaximumScaleIsAuto = false;
            axis.MaximumScale = 3;

            Axis Xaxis = (Axis)xlChart.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlCategory, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);
            Xaxis.TickLabels.Orientation = XlTickLabelOrientation.xlTickLabelOrientationDownward;

            //SeriesCollection seriesCollection = (SeriesCollection)xlChart.SeriesCollection();

            Series s1 = (Series)xlChart.SeriesCollection(1);
            s1.Name = "Serie1";
            s1.MarkerStyle = XlMarkerStyle.xlMarkerStyleCircle;

            //seriesCollection.NewSeries();

            Series s2 = (Series)xlChart.SeriesCollection(2);
            s2.Name = "Serie2";
            s2.MarkerStyle = XlMarkerStyle.xlMarkerStyleNone;

            //seriesCollection.NewSeries();

            Series s3 = (Series)xlChart.SeriesCollection(3);
            s3.Name = "Serie3";
            s3.MarkerStyle = XlMarkerStyle.xlMarkerStyleNone;

If I keep the comments, the error says invalid parameter and is shown on that line: Series s2 = (Series)xlChart.SeriesCollection(2); If I remove the comments, I get the second exception on that line:

SeriesCollection seriesCollection = (SeriesCollection)xlChart.SeriesCollection();

If I add 1 as a parameter, then the chart is not displayed properly. Do you have any suggestions how to make it work?

Christian Sauer · Accepted Answer · 2014-05-28 12:01:23Z

1

Argh that stuff still gives me nightmares. There was some weirdness around SeriesCollection - but I cannot remember exactly what it was.

Try to re-include that line //SeriesCollection seriesCollection = (SeriesCollection)xlChart.SeriesCollection(); and refernece the seriesCollection object everywhere. Alos it could be, that the index for SeriesCollection is zero - based, can you try that?

answered May 28, 2014 at 12:01

Christian Sauer

11.2k12 gold badges57 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Apostrofix Over a year ago

I've tried to add 0 as parameter, but still the same exception occurs: System.Runtime.InteropServices.COMException, and the chart is therefore not displayed properly.

Apostrofix Over a year ago

I can see that someone has the same problem in the answers of this topic: stackoverflow.com/questions/5573972/excel-charts-c?rq=1 Unfortunately, it hasn't being resolved.

Christian Sauer Over a year ago

Whats the content of the Comexception? is there some clue? Additionally, try to use array indexing [] on the seriesCollection

Apostrofix Over a year ago

It just says Invalid Parameter. Can you give an example of the array indexing []

Apostrofix Over a year ago

I found the problem. When declaring the SeriesCollection, I had to put Type.Missing as an argument. I have marked your solution as the answer. Thank you for the help!

|

CrashM · Accepted Answer · 2016-09-26 14:15:25Z

0

By Default when you create a new chart it doesn't have any series so you can't select it using chartPage.SeriesCollection(1). You need to create a series first.

In order to add a new series you need to use something like:

SeriesCollection seriesCollection = (SeriesCollection)xlChart.SeriesCollection();

Series s1 = seriesCollection.NewSeries();
s1.Name = "Serie1";
s1.MarkerStyle = XlMarkerStyle.xlMarkerStyleCircle;

Series s2 = seriesCollection.NewSeries();
s2.Name = "Serie2";
s2.MarkerStyle = XlMarkerStyle.xlMarkerStyleNone;

You may also need to add the values to the series rather than to the chart, eg:

Series ser = sc.NewSeries();
ser.XValues = _excelWorksheet.Range[YourRange];
ser.Values = _excelWorksheet.Range[YourRange];

edited Sep 26, 2016 at 14:15

answered Sep 26, 2016 at 13:48

CrashM

851 silver badge9 bronze badges

Collectives™ on Stack Overflow

Adding series to Excel chart exception

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related