none
Can't add a trendline to an Excel chart using Office.Interop.Excel and C#

    Question

  • I am not able to add a trendline to an Excel chart using 
    Microsoft.Office.Interop.Excel library from Visual Studio 2008, using C#.  
    Works OK with VB .Net, however.
    
    The following code works as expected using VB .Net:
    oChart.SeriesCollection(1).Select() 
    oChart.SeriesCollection(1).Trendlines.Add(Excel.XlTrendlineType.xlPolynomial, 
    Order:=3, Forward:=0, Backward:=0, DisplayEquation:=True, 
    DisplayRSquared:=True).Select()
    
    But, neither of these equivalent C# lines compile:
    oChart.SeriesCollection(1).Select();
    oChart.SeriesCollection(1).Trendlines.Add(
                        Excel.XlTrendlineType.xlPolynomial, //type
                        3,                                  // order
                        Missing.Value,                      // period
                        Missing.Value,                      // forward
                        Missing.Value,                      // backward
                        Missing.Value,                      // intercept
                        true,                               // display equation
                        true,                               // display R squared
                        Missing.Value).Select();
    
    These C# lines seem to work:
    Excel.Series oSeries = (Excel.Series)oChart.SeriesCollection(1);
    oSeries.Select();
    
    But, I still can't get the trendline Add to work, for example:
    
    oSeries.Trendlines.Add( ...)
    
    Any ideas why the trendlines automation does not work?
    
    Thank you,
    Barry
    Friday, March 07, 2008 6:35 PM

Answers

  • The following C# code works for me. I think the issue in your code is that Series.Trendlines is actually a method, not a property collection (see http://msdn2.microsoft.com/en-us/library/microsoft.office.interop.excel.series.trendlines.aspx). Because C# does not support optional parameters, you must pass System.Type.Missing to the optional index parameter of the Trendlines method to return the entire collection. Also, some explicit casts for some object return values are required, because C# always enforces string type checking.

     

    Excel.Series series = (Excel.Series)oChart.SeriesCollection(1);
    Excel.Trendlines trendlines = (Excel.Trendlines)series.Trendlines(System.Type.Missing);

    Excel.Trendline newTrendline = trendlines.Add(
        Microsoft.Office.Interop.Excel.XlTrendlineType.xlPolynomial, 3,
        System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing,
        true, true, System.Type.Missing);
    newTrendline.Select();

     

    Please note that this forum is specifically about features in VSTO (Visual Studio Tools for Office) solutions. The following newsgroups are established for general automation questions:

     

    Working with the Office Primary Interop Assemblies (PIA): office.developer.automation newsgroup

    http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.officedev&lang=en&cr=US

     

    General programming issues: excel.programming newsgroup

    http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.excel.programming&lang=en&cr=US

     

    I hope this helps,

    McLean Schofield

    Friday, March 07, 2008 9:50 PM

All replies