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

    Neque porro quisquam est, qui dolorem ipsum quia dolor sit amet, consectetur, adipisci velit, sed quia non numquam eius modi tempora incidunt ut labore et dolore magnam aliquam quaerat voluptatem. -Cicero

  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
    860
    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

    Neque porro quisquam est, qui dolorem ipsum quia dolor sit amet, consectetur, adipisci velit, sed quia non numquam eius modi tempora incidunt ut labore et dolore magnam aliquam quaerat voluptatem. -Cicero

  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
    860
    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

    Neque porro quisquam est, qui dolorem ipsum quia dolor sit amet, consectetur, adipisci velit, sed quia non numquam eius modi tempora incidunt ut labore et dolore magnam aliquam quaerat voluptatem. -Cicero

  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
    860

    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

    Neque porro quisquam est, qui dolorem ipsum quia dolor sit amet, consectetur, adipisci velit, sed quia non numquam eius modi tempora incidunt ut labore et dolore magnam aliquam quaerat voluptatem. -Cicero

  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
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center