Excel Chart with MFC

[use4ut]

Dear all,

I have a problem to draw a series of x-y plots from Excel Chart by automation using MFC. The problem is that my x-values for different series are different. MSDN example just gives an example of how to draw a series curve when x-values are the same for them. I appreciate your help.

[Tom Frohman]

First create a plot with your first series.

Then add a new series. For example:

Series ser;

ser=chart.NewSeries();

Then use Xvalues and values to set the x and y ranges of the new series.

ser.XValues(COleVariant("=C1:C5"));
ser.Values(COleVariant("=D1:D5"));

I'm feeling lazy so I haven't tried this but it should work.

[use4ut]

Tks Tom,

I think the method you send me is from VBA macro. In MFC, the real problem is how to get an SeriesCollection or Series object from the _Chart object we just created. Pls read the following code.
VARIANT var;
_Chart chart;
chart.SeriesCollection(COleVariant((short)1));
SeriesCollection ser;
ser.NewSeries();
ser.Add(var,covOptional,covOptional,covOptional,covOptional);

Is there anyway, we can do sth like
chart.ser.NewSeries()?

Thanks again.

[Tom Frohman]

In MFC, the real problem is how to get an SeriesCollection or Series object from the _Chart object we just created.

Series s1;

s1 = chart.SeriesCollection(COleVariant((short)1));

I think the method you send me is from VBA macro.

Exactly. All the C++ Office Automation I've done has started by recording a VBA macro and then translating the result into C++.

[use4ut]

Tks Tom,

I still have one problem: I searched excel.h and there is only one method that I can new a series. That is
LPDISPATCH SeriesCollection NewSeries();

I tried:

Series ser;
SeriesCollection serCol;
ser=chart.SeriesCollection(COleVariant((short)1));
/////////////////////////////////////////////////////////////
lpDisp=ser.GetParent();
serCol.AttachDispatch(lipDisp);
serCol.NewSeries();

The error message is "method is not found"

Thanks.

[Tom Frohman]

Okay, I tried it out this time and the following worked for me.
I had an existing worksheet with the x data in columns A and C and the Y data in columns B and D.



COleVariant covTrue((short)TRUE),
covFalse((short)FALSE),
covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR),covWidth((short)40);

sheets=book.GetSheets();
sheet=sheets.GetItem(COleVariant((long)1));
sheet.Activate();

left = 100;
top = 10;
width = 350;
height = 250;
lpdisp = sheet.ChartObjects(covOptional);
chartobjects.AttachDispatch(lpdisp);
ChartObject chartobject = chartobjects.Add(left, top, width, height);

chart.AttachDispatch(chartobject.GetChart());

lpdisp = sheet.GetRange(COleVariant("A1"), COleVariant("B7"));
// Range containing the data to be charted.

range.AttachDispatch(lpdisp);
range.Select();
range.Activate();
//VARIANT var; // ChartWizard needs a Variant for the Source range.
var.vt = VT_DISPATCH; // .vt is the usable member of the tagVARIANT
// Struct. Its value is a union of options.
var.pdispVal = lpdisp; // Assign IDispatch pointer
// of the Source range to var.
covTF=covFalse;
chart.ChartWizard(var, // Source.
COleVariant((short)-4100), // Gallery = xy.
COleVariant((short)6), // Format, use default.
COleVariant((short)2), // PlotBy xlColumns.
COleVariant((short)1), // CategoryLabels.
COleVariant((short)1), // SeriesLabels.
covTF, // HasLegend.
COleVariant("Title"), // Title.
COleVariant("Frequency"), // CategoryTitle.
COleVariant("Whatever"), // ValueTitles.
covOptional // ExtraTitle.
);
chart.SetChartType((long)74);// had a problem with the chart type in the wizard
// this is my workaround


The main part is here.


SeriesCollection serc;
serc=chart.SeriesCollection(covOptional);
Series ser;
ser=serc.NewSeries();

lpdisp=sheet.GetRange(COleVariant("C2"), COleVariant("C7"));
var.pdispVal = lpdisp;
ser.SetXValues(var);

lpdisp=sheet.GetRange(COleVariant("D2"), COleVariant("D7"));
var.pdispVal = lpdisp;
ser.SetValues(var);

[use4ut]

It is great that this really worked out. Tks Tom for your really help and I think that you are a realy guru coding EXCEL by MFC.

[joebarthib]

Hi

I"m facing a problem using SeriesCollection from my C++ program. I call SeriesCollection as a method, passing it the index of the Series I want. But it seems it doesn't give me the expected Series object, as whatever I try to do with it (in particular, trying to get its Name), it doesn't work. But the SeriesCollection isn't empty, its Count is 5!
I also tried calling the SeriesCollection method with no parameter, then getting the series from the Item method, but it doesn't work either :-(
Have you faced this? I've spent the afternoon trying to find the solution...
Thank you very much for your help !

Here's my code:
(...) // I get a valid pChart Chart object
IDispatch* pSeries;
VARIANT result;
VariantInit(&result);
VARIANT itemn;
itemn.vt = VT_I4;
itemn.lVal = 2;
m_hr=OLEMethod(DISPATCH_METHOD, &result, pChart, L"SeriesCollection", 1, itemn);
pSeries=result.pdispVal;
VariantInit(&result);
m_hr=OLEMethod(DISPATCH_PROPERTYGET, &result, pSeries, L"Name", 0);
sLegendText = result.bstrVal;

where OLEMethod (successfully used everywhere in my program) is from a CodeProject article, it calls Invoke:
HRESULT OLEMethod(int nType, VARIANT *pvResult, IDispatch *pDisp,LPOLESTR ptName, int cArgs...)
{
if(!pDisp) return E_FAIL;

va_list marker;
va_start(marker, cArgs);

DISPPARAMS dp = { NULL, NULL, 0, 0 };
DISPID dispidNamed = DISPID_PROPERTYPUT;
DISPID dispID;
char szName[200];


// Convert down to ANSI
WideCharToMultiByte(CP_ACP, 0, ptName, -1, szName, 256, NULL, NULL);

// Get DISPID for name passed...
HRESULT hr= pDisp->GetIDsOfNames(IID_NULL, &ptName, 1, LOCALE_USER_DEFAULT, &dispID);
if(FAILED(hr)) {
return hr;
}
// Allocate memory for arguments...
VARIANT *pArgs = new VARIANT[cArgs+1];
// Extract arguments...
for(int i=0; i<cArgs; i++) {
pArgs[i] = va_arg(marker, VARIANT);
}

// Build DISPPARAMS
dp.cArgs = cArgs;
dp.rgvarg = pArgs;

// Handle special-case for property-puts!
if(nType & DISPATCH_PROPERTYPUT) {
dp.cNamedArgs = 1;
dp.rgdispidNamedArgs = &dispidNamed;
}

// Make the call!
hr = pDisp->Invoke(dispID, IID_NULL, LOCALE_SYSTEM_DEFAULT, nType, &dp, pvResult, NULL, NULL);
if(FAILED(hr)) {
return hr;
}
// End variable-argument section...
va_end(marker);

delete [] pArgs;
return hr;
}

[Tom Frohman]

Could your problem be the way you handle/convert the returned string?

m_hr=OLEMethod(DISPATCH_PROPERTYGET, &result, pSeries, L"Name", 0);
sLegendText = result.bstrVal;

The declaration of sLegendText isn't shown.

Compare this to the end of my example:

{
VARIANT result;
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSeries, L"Name", 0);
_bstr_t tmp(result.bstrVal, FALSE);
CString cs(static_cast<const char*>(tmp)); 
MessageBox(0,cs," ",MB_OK);
}

[\Code]


I tried this out using my example program and I was able to set and retrieve the name of the series. I got the series object in two steps. First I got the seriescollection object and then I retrieved the series from the collection using Item. You tried to retrieve the series directly from the chart using the seriescollection method.

IDispatch *pXlChartObjects;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"ChartObjects", 0);
pXlChartObjects = result.pdispVal;
//VariantClear(&result);
}

IDispatch *pXlChartObject;
{
//VARIANT result;
//VariantInit(&result);
VARIANT left, top, width, height;
left.vt = VT_R8;
left.dblVal = 600.;
top.vt = VT_R8;
top.dblVal = 600.;
width.vt = VT_R8;
width.dblVal = 200.;
height.vt = VT_R8;
height.dblVal = 200.;

VARIANT result;
AutoWrap(DISPATCH_METHOD, &result, pXlChartObjects, L"ADD", 4, left, top, width, height);
pXlChartObject = result.pdispVal;
//VariantClear(&result);
VariantClear(&left);
VariantClear(&top);
VariantClear(&width);
VariantClear(&height);
}

IDispatch *pXlChart;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlChartObject, L"Chart", 0);
pXlChart = result.pdispVal;
}

{
VARIANT result;
VARIANT maintitle;
maintitle.vt=VT_BSTR;
maintitle.bstrVal=::SysAllocString(L"Main Title");

VARIANT ytitle;
ytitle.vt=VT_BSTR;
ytitle.bstrVal=::SysAllocString(L"Ytitle");
VARIANT xtitle;
xtitle.vt=VT_BSTR;
xtitle.bstrVal=::SysAllocString(L"Xtitle");
VARIANT gall;
gall.vt=VT_I4;
gall.lVal=-4169;

VariantInit(&result);
//AutoWrap(DISPATCH_METHOD, &result, pXlChart, L"ChartWizard", 1, darange);
AutoWrap(DISPATCH_METHOD, &result, pXlChart, L"ChartWizard", 11, vopt,ytitle,xtitle,maintitle,vopt,vopt,vopt,vopt,vopt,gall,darange);
VariantClear(&result);
VariantClear(&maintitle);
VariantClear(&ytitle);
VariantClear(&xtitle);
VariantClear(&gall);
}
IDispatch *pXlSeriesCollections;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlChart, L"SeriesCollection", 0);
pXlSeriesCollections = result.pdispVal;
//VariantClear(&result);
}



IDispatch *pXlSeries;
{
VARIANT itno;
itno.vt=VT_I4;
itno.lVal=1;
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_METHOD, &result, pXlSeriesCollections, L"Item", 1,itno);
pXlSeries = result.pdispVal;
//VariantClear(&result);
}

{
VARIANT sername;
sername.vt=VT_BSTR;
sername.bstrVal = ::SysAllocString(L"This is my series");
VARIANT result;
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlSeries, L"Name", 1, sername);

}

{
VARIANT result;
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSeries, L"Name", 0);
_bstr_t tmp(result.bstrVal, FALSE);
CString cs(static_cast<const char*>(tmp)); 
MessageBox(0,cs," ",MB_OK);
}

[joebarthib]

Hi Tom

Thank you for your answer. sLegendText is a CString, and I've successfully used CString variables at other places to get the bstrVal of a Variant (but maybe that wasn't 'clean'?). Here, the problem is that the OLEMethod (and thus, the Invoke it internally calls, similarly to your AutoWrap I guess?) returns a weird 0x800A03EC HRESULT. I had also tried to get the Series in two steps like you, same result.

But I think I found the problem. The macro I recorded was

ActiveChart.SeriesCollection(1).Name = "=""foo"""

When I tried to execute this macro from Excel, I got the error: "Unable to set the Name property of the Series class". After googling about this, this might happen because no data is assigned to the chart yet: http://www.mrexcel.com/forum/showthread.php?t=37548

I hadn't written the code that fills the table associated to the SeriesCollection yet, but I'm pretty sure if I do it BEFORE I try to rename the SeriesCollection, it will work this time. But it's quite strange that even if there's no data, you can manually get/set the Series name in Excel, but not programmatically!

Cheers,

Thibaud