-
October 29th, 2003, 05:59 PM
#1
Excel Chart with MFC
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.
-
October 30th, 2003, 09:23 AM
#2
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.
Last edited by Tom Frohman; October 30th, 2003 at 09:58 AM.
Verere testudinem! (Fear the turtle)
Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy. -Albert Einstein
Robots are trying to steal my luggage.
-
October 30th, 2003, 11:39 AM
#3
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.
-
October 31st, 2003, 07:52 AM
#4
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++.
Verere testudinem! (Fear the turtle)
Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy. -Albert Einstein
Robots are trying to steal my luggage.
-
October 31st, 2003, 09:55 AM
#5
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.
-
October 31st, 2003, 02:15 PM
#6
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);
Verere testudinem! (Fear the turtle)
Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy. -Albert Einstein
Robots are trying to steal my luggage.
-
October 31st, 2003, 04:41 PM
#7
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.
-
October 13th, 2009, 10:45 AM
#8
Re: Excel Chart with MFC
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;
}
-
October 14th, 2009, 08:44 AM
#9
Re: Excel Chart with MFC
Could your problem be the way you handle/convert the returned string?
Code:
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:
Code:
{
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.
Code:
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);
}
Last edited by Tom Frohman; October 14th, 2009 at 07:44 PM.
Verere testudinem! (Fear the turtle)
Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy. -Albert Einstein
Robots are trying to steal my luggage.
-
October 15th, 2009, 03:26 AM
#10
Re: Excel Chart with MFC
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
Code:
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|