Problem with trendline in c# .net for exporting to a chart in excel


  • Hey everybody,

    I'm new to this forum and I have a question for a problem that took several hours of my nerves and I can't get trough the problem.

    I have a xlXYScatter Chart which I create in c# to export to excel table. All the data is importet correcrly and all charts are drawn correctly but when I try to create a Trendline in the chart it gives me allways the same error: "Trendlines method of series class failed". I don't know what's wrong so if anybody could help me i would be very gradful. Tnx. the code is below:

    public void GenerateGraphArea2(Excel._Worksheet oWS, Int32 i)
                Excel._Workbook oWB;
                Excel.Series oSeries;
                Excel.Range oResizeRangeBar;
                Excel._Chart oChart;
                Excel.Axis oAxis;
                Int32 j = i + 1;

                oResizeRangeBar = oWS.get_Range("E" + 2, "E" + j).get_Resize(Missing.Value, Missing.Value);

                                    = Excel.XlLineStyle.xlDouble;
                        = Excel.XlBorderWeight.xlThick;

                //Add a Chart for the selected data.
                oWB = (Excel._Workbook)oWS.Parent;
                oChart = (Excel._Chart)oWB.Charts.Add(Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value);

                //Use the ChartWizard to create a new chart from the selected data.

                oResizeRangeBar = oWS.get_Range("E" + 2, "E" + j).get_Resize(
                         Missing.Value, Missing.Value);

                oChart.ChartWizard(oResizeRangeBar, Excel.XlChartType.xlXYScatter, Excel.XlRowCol.xlColumns,
                   Excel.XlRowCol.xlColumns, Missing.Value, Missing.Value, false,"Linear Relationship between Planned and Actual Effort",
                     "Planned Effort","Actual Effort", Missing.Value);
                oChart.ChartType = Excel.XlChartType.xlXYScatter;
                oSeries = (Excel.Series)oChart.SeriesCollection(1);
                oSeries.XValues = oWS.get_Range("D2", "D" + j);

                oAxis = (Excel.Axis)oChart.Axes(Excel.XlCategoryType.xlCategoryScale, Excel.XlAxisGroup.xlPrimary);
                oAxis.HasMajorGridlines = true;
               Excel.Trendlines oTrend = (Excel.Trendlines)oSeries.Trendlines(1);
               oTrend.Add(Excel.XlTrendlineType.xlLinear, Missing.Value, Missing.Value, 0, 0, Missing.Value,true, true, Missing.Value);
    //-----------------------------------------------------------------------------------/here is the problem

                oChart.Location(Excel.XlChartLocation.xlLocationAutomatic, oWS.Name);
                //Move the chart so as not to cover your data.
                oResizeRangeBar = (Excel.Range)oWS.Rows.get_Item(i + 30, Missing.Value);
                oWS.Shapes.Item("Chart 2").Top = (float)(double)oResizeRangeBar.Top;
                oResizeRangeBar = (Excel.Range)oWS.Columns.get_Item(1, Missing.Value);
                oWS.Shapes.Item("Chart 2").Left = (float)(double)oResizeRangeBar.Left;
                oWS.Shapes.Item("Chart 2").Width = 120;
                oWS.Shapes.Item("Chart 2").Height = 300;

    Thursday, February 22, 2007 11:41 AM