Excel Chart with MFC
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 10 of 10

Thread: Excel Chart with MFC

  1. #1
    Join Date
    Oct 2003
    Posts
    7

    Question 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.

  2. #2
    Join Date
    Apr 2002
    Location
    Michigan, USA
    Posts
    864
    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.

  3. #3
    Join Date
    Oct 2003
    Posts
    7
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Michigan, USA
    Posts
    864
    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.

  5. #5
    Join Date
    Oct 2003
    Posts
    7
    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Michigan, USA
    Posts
    864
    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.

  7. #7
    Join Date
    Oct 2003
    Posts
    7

    Thumbs up

    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.

  8. #8
    Join Date
    Dec 2005
    Posts
    3

    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;
    }

  9. #9
    Join Date
    Apr 2002
    Location
    Michigan, USA
    Posts
    864

    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 08: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.

  10. #10
    Join Date
    Dec 2005
    Posts
    3

    Resolved 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
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center