Excel report library: adding second series to same chart

[ElectroLund]

Apparently the only series-to-chart function in the Excel Report Library (excelreport.h) is ExcelRpt_ChartSetSourceRange.  But this function doesn't seemt o add successive series.  It only supports charts for one series.

So I'm dipping my toes into the excel2000.h library.  Wow!  There are a lot of good posts on the forums for this library, but I haven't quite found one regarding how to add another X series to the same chart.

Ideas?

[GreyGrey]

Hi ElectroLund,

I'm also having trouble finding a function that does that. Does assigning another series to the chart using the same function call not add it to the chart? Looking at an MSDN article about Series and Data Points in Chart Controls, it mentioned that when you try to assign multiple series to the same chart, the chart area attempts to plot the series together. Does the ExcelRpt_ChartSetSourceRange function call assign another series to the chart or will it erase the old series if you call it again?

[ElectroLund]

In my experience, ExcelRpt_ChartSetSourceRange simply replaces the existing series.  So I wrote a function in the vein of the Excel Report library style that adds a series to an existing chart (I had the audacity to name it similarly too, although that was mostly for consistency):

HRESULT CVIFUNC ExcelRpt_ChartAddSeries(CAObjHandle chartHandle, VARIANT yData, VARIANT xData, int marker)
{
	HRESULT __result = S_OK;
	CAObjHandle Series, SerisHandle = 0;

	// Open Series
	__caErrChk (Excel_ChartSeriesCollection(logLinearityChartSheet, NULL, CA_DEFAULT_VAL,&Series));

	//Get Series Handle
	__caErrChk (Excel_SeriesCollectionNewSeries(Series,NULL,&SerisHandle));

	// Set data Array
	__caErrChk (Excel_SetProperty(SerisHandle,NULL,Excel_SeriesValues,CAVT_VARIANT,yData));

	// Set X array
	__caErrChk (Excel_SetProperty(SerisHandle, NULL, Excel_SeriesXValues,CAVT_VARIANT, xData));

	// add markers?
	if (!maker)
		__caErrChk (Excel_SetProperty(SerisHandle, NULL, Excel_SeriesMarkerStyle, CAVT_LONG, ExcelConst_xlMarkerStyleNone));

Error:
	CA_DiscardObjHandle(Series);
	CA_DiscardObjHandle(SerisHandle);

	return __result;
}

In my case, I chose to pass VARIANT parameters, but you might want to instead pass a string range.  I decided on VARIANTS because I wanted to add both series from ranges as well as threshold lines (not originating from a range in a worksheet).  That function looks like this:

/*********
Note: call with ExRConst_Category to place vertical line, ExRConst_Value with horizontal line
**********/

HRESULT CVIFUNC ExcelRpt_ChartAddThresholdSeries(CAObjHandle chartHandle, enum ExREnum_ExAxisType axis, double value)
{
	HRESULT __result = S_OK;
	double maxScale, minScale, x[2]={0}, y[2]={0};
	LPSAFEARRAY safeArrayY,safeArrayX;
	VARIANT variantDataY, variantDataX;

	switch (axis)
	{
		case ExRConst_Category:
			// look up the max axis scale values
			__caErrChk (ExcelRpt_GetChartAxisAttribute(chartHandle, ExRConst_Value, ExRConst_Primary, ER_CH_ATTR_MAXIMUM_SCALE, &maxScale));
			__caErrChk (ExcelRpt_GetChartAxisAttribute(chartHandle, ExRConst_Value, ExRConst_Primary, ER_CH_ATTR_MINIMUM_SCALE, &minScale));
			
			x[0] = x[1] = value;
			
			y[0] = minScale;
			y[1] = maxScale;
			break;
			
		case ExRConst_Value:
			// look up the max axis scale values
			__caErrChk (ExcelRpt_GetChartAxisAttribute(chartHandle, ExRConst_Category, ExRConst_Primary, ER_CH_ATTR_MAXIMUM_SCALE, &maxScale));
			__caErrChk (ExcelRpt_GetChartAxisAttribute(chartHandle, ExRConst_Category, ExRConst_Primary, ER_CH_ATTR_MINIMUM_SCALE, &minScale));
			
			x[0] = minScale;
			x[1] = maxScale;
			
			y[0] = y[1] = value;
			break;
	}
	
	// Y array
	__caErrChk (CA_Array1DToSafeArray(y,CAVT_DOUBLE,2,&safeArrayY));
	__caErrChk (CA_VariantSetSafeArray(&variantDataY,CAVT_DOUBLE,safeArrayY));

	// X array
	__caErrChk (CA_Array1DToSafeArray(x,CAVT_DOUBLE,2,&safeArrayX));
	__caErrChk (CA_VariantSetSafeArray(&variantDataX,CAVT_DOUBLE,safeArrayX));

	// add the series to the chart
	__caErrChk (ExcelRpt_ChartAddSeries(chartHandle, variantDataY, variantDataX, FALSE));

Error:

	return __result;
}

Have fun!

[ElectroLund]

Hmm, I'm still having problems with the first part: adding an existing series (column range) in a worksheet to an existing chart.

If you find a way, let me know!

[GreyGrey]

I have been finding it increasingly hard to find resources that give examples of using excel functions in C. I don't know of a way to add another series to the chart. Have you tried adding both series to the chart at the same time by specifying both ranges in the range input by inputting a string like "A2:A100,C2:C100"?

[ElectroLund]

Same here.  

I can confirm that, if one were fortunate enough to have all their chart series on the same worksheet, calling Excel_ChartSetSourceData with the combined ranges magically adds the multiple series as one would hope.

But in my case, it just doesn't work to have these series on the same sheet.  I need to have them spread across several.

So my solution as of today?   I'm making an Excel template file that my application will open and then save as a new file.

Pros: all the formatting (including a bunch that isn't possible with the Excel Report library) is already done.

Cons: template file must follow the application

[GreyGrey]

You might try reaching out to Microsoft support and see if they know of a way to add a series to an existing chart using C or C++. They should know whether or not that function exists.