Adding Trendlines to Excel Chart

[ACowans]

I am trying to add a trendline to a chart in excel. Does anyone have an example on how to do this? I am using excelreport.fp functions to open excel, input the data, and make a chart. How do I use functions from excel2000.fp to make a trendline. I'm using CVI 8.1 and Excel 2007.

[Eric_B.]

ACowans,

I don't have a good example for you, but what problems are you having?  You can use the Excel_TrendlinesAdd function to add a trendline to your chart, but if you're having trouble with doing that, please let me know and I can see what is going on.

[ebalci]

Hi,

Trendline functions are available in excel2000.fp, not in excelreport.fp

excel2000 is a full library, but it is hard to find documentation for the functions.

So it can be hard to perform what you really want to do, without many trial-errors.

Trend functions are also available in Advanced Analysis Library of CVI.

So, you can get the data in Excel sheets into CVI and calculate the linear approximation using LinFit or PolyFit functions.

Later, you can add this linear approximation data into your Excel file using excelreport functions which are better documented. 

It looks like a long workaround but it may be better than struggling with excel2000 functions if you cannot find good information about them.

Hope this helps, 

[ACowans]

I have tried to use the Excel_TrendlinesAdd function, but I get a return value of -2147467262. I looked up what this was and it says interface not supported. Here is what I have:

static ExcelObj_App appHandle = 0;

static ExcelObj_Workbooks booksHandle = 0;

static ExcelObj_Workbook bookHandle = 0;

static ExcelObj_Worksheet worksheetHandle = 0;

static ExcelObj_ChartObject chartobj1Handle = 0;

static ExcelObj_ChartObject chartobj2Handle = 0;

static ExcelObj_Chart chart1Handle = 0;

static ExcelObj_Chart chart2Handle = 0;

static ExcelObj_ChartGroup chartgroupHandle = 0;

static ExcelObj_Trendline trend1Handle = 0;

static ExcelObj_Trendline trend2Handle = 0;

static ERRORINFO errorinfo;

ExcelRpt_ApplicationNew (VTRUE, &appHandle);

ExcelRpt_WorkbookNew (appHandle, &bookHandle);

ExcelRpt_GetWorksheetFromIndex (bookHandle, 1, &worksheetHandle);

ExcelRpt_WriteDataFromTableControl (worksheetHandle, "A1:M50", panelHandle, MAIN_TABLE);

ExcelRpt_SetCellValue (worksheetHandle, "M1", ExRConst_dataString, infostr);

// Make line graphs

ExcelRpt_ChartNew (bookHandle, -1, &chart1Handle);

result = ExcelRpt_ChartWizard (chart1Handle, worksheetHandle,

 "C1:C50,E1:E50",

ExRConst_GalleryXYScatter, 0,

ExRConst_Columns, 1, 1, 0,

"Tint VS Signal", "Tint", "Signal",

NULL);

if (result <0) {

MessagePopup("error", "error");

}

result = Excel_TrendlinesAdd (chart1Handle, &errorinfo,

ExcelConst_xlLinear, CA_DEFAULT_VAL,

CA_DEFAULT_VAL, CA_DEFAULT_VAL,

CA_DEFAULT_VAL, CA_DEFAULT_VAL,

CA_DEFAULT_VAL, CA_DEFAULT_VAL,

CA_DEFAULT_VAL, &trend1Handle);

Is it a problem with my variables? 

[Eric_B. · Accepted solution]

ACowans,

Your variables were fine, but there is a little more structure you have to set up.  In Excel, when you have to set a trendline, you have to select a series, so I started looking for methods that would take the chart handle and return the series.  From there, I found that the trendlines function actually needs a handle to the series' trendline collection, so I had to find a method that would return the trendlines collection as well.  Here is the code that I had to include to present a trendline:

ExcelObj_Series seriesOne=0;
ExcelObj_Trendline trendHandle = 0;
CAObjHandle trendlines = 0;
ERRORINFO info;

Excel_ChartSeriesCollection (chartHandle, &info, CA_VariantInt (1), &seriesOne);
Excel_SeriesTrendlines(seriesOne, &info, CA_DEFAULT_VAL, &trendlines);
Excel_TrendlinesAdd (trendlines, &info, ExcelConst_xlLinear, CA_DEFAULT_VAL, CA_DEFAULT_VAL, CA_DEFAULT_VAL, CA_DEFAULT_VAL, CA_DEFAULT_VAL,
                    CA_DEFAULT_VAL, CA_DEFAULT_VAL, CA_DEFAULT_VAL, &trendHandle);

The first method takes in the chart handle and returns the series handle (for series 1...change the parameter of CA_VariantInt(1) to get different series).  The second method takes in the series handle, and the 3rd parameter (CA_DEFAULT_VAL) means that the function returns the trendlines collection rather than an individual trendline.  The trendline collection is needed for the next method.  The third method actually adds the trendline.

Try implementing this and let me know if you run into any problems.

Message Edited by Eric B. on 11-05-2009 03:23 PM

[ACowans]

Thanks, that worked great! Getting the series collection was I was missing and confused about.