Generating embedded charts via Qt ActiveX



  • I've found a code for inserting a chart type worksheet (not embedded charts) in excel using Qt Activex as below:

    The code works fine. I'm wondering where can if find the standards APIs/some examples for dealing with charts specially with embedded charts. Where can I find some example or APIs for dealing with embedded charts in excel via qt Activex.

    I'll be very appreciated if anyone can guide me through generating embedded excel charts with QActivex

        QAxObject *excel = new QAxObject("Excel.Application", 0);
        QAxObject *workbook = excel->querySubObject("Workbooks")->querySubObject("Add");
        QAxObject *worksheet = workbook->querySubObject("Worksheets(1)");
        worksheet->setProperty("Name", "Dati applicazione");
        worksheet->querySubObject("Cells(1,1)")->dynamicCall("SetValue", "Serie");
        worksheet->querySubObject("Cells(1,2)")->dynamicCall("SetValue", "Dati");
    
        QAxObject *cell;
        double dval;
        for (int i = 2; i < 10; ++i) {
            dval = qrand();
            cell = worksheet->querySubObject("Cells(int,int)", i, 1);
            cell->dynamicCall("SetValue(int)", i-1);
            cell = worksheet->querySubObject("Cells(int,int)", i, 2);
            cell->dynamicCall("SetValue(double)", dval);
            cell = worksheet->querySubObject("Cells(int,int)",i,3);
            cell->dynamicCall("SetValue(double)", dval/2.0);
            cell = worksheet->querySubObject("Cells(int,int)", i, 4);
            cell->dynamicCall("SetValue(double)", dval/3.0);
        }
    
        QAxObject *range = worksheet->querySubObject("Range(A2:C9)");
        range->dynamicCall("Select(void)");
    
        QAxObject *chart = workbook->querySubObject("Charts")->querySubObject("Add");
        chart->setProperty("Name", "Report Grafico dei dati");
        chart->setProperty("Chart Type", 73);
    
        QAxObject *series = chart->querySubObject("SeriesCollection");
    
        QAxObject *serie = series->querySubObject("Item (int)", 1);
        QAxObject *xvalues = worksheet->querySubObject("Range(A2:A9)");
        QAxObject *yvalues = worksheet->querySubObject("Range(B2:B9)");
        serie->setProperty("XValues", xvalues->asVariant());
        serie->setProperty("Values", yvalues->asVariant());
    
        serie = series->querySubObject("Item (int)", 2);
        yvalues = worksheet->querySubObject("Range(C2:C9)");
        serie->setProperty("XValues", xvalues->asVariant());
        serie->setProperty("Values", yvalues->asVariant());
    
        serie = series->querySubObject("Item (int)",3);
        yvalues = worksheet->querySubObject("Range(D2:D9)");
        serie->setProperty("XValues", xvalues->asVariant());
        serie->setProperty("Values", yvalues->asVariant());
    
        workbook->dynamicCall("SaveAs(const QString&)", "E:\\test\\test.xls");
        workbook->dynamicCall("Close (Bollean)", false);
        excel->dynamicCall("Quit (void)");
    


  • Hi,
    the code looks like Excel is the one that does the work. You just say what Excel should do. So the commands you send are Excel-commands which should be defined in the Excel documentation. QQxObject just enables the communication with Excel but does not produce the actual charts. You could probably do the same with the same commands using PowerShell.
    -Michael.



  • @m.sue Yes and I assumed that maybe someone has experienced with that since I'm not.



  • How about this: It's for VBA but you should be able to get the information of what is possible and how to do it: https://msdn.microsoft.com/library/office/ff820756.aspx



  • I'm just looking for some hints since I'm quite new in this area. The link you have sent is not english!!!



  • Now it is :-)



  • That didn't help. I'm little confused.What are those APIs in the code I've posted? I'll be really appreciated if anybody help


  • Qt Champions 2016

    @alizadeh91

    What is the difference between the chart object you get with this code and the
    one you refer to as embedded?
    It seems the same
    as
    http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

    You add chart
    AxObject *chart = workbook->querySubObject("Charts")->querySubObject("Add");
    You set type
    chart->setProperty("Chart Type", 73);

    The API is for the programming of Excel. Its Automation API.



  • @mrjj Thanks, The chart that is created is a 'worksheet chart' which is a worksheet which is dedicated to a fullscreen chart. I want to create a embedded chart in a ordinary worksheet.


  • Qt Champions 2016

    @alizadeh91
    Hmm
    I think it comes from the fact we create it
    QAxObject *chart = workbook->querySubObject("Charts")->querySubObject("Add");
    on the workbook

    I wonder if we can ask worksheet->querySubObject("Charts")xxx
    so its added to the collection of the sheet.
    Or
    "ActiveSheet"-->querySubObject("Charts")

    If you see here
    https://msdn.microsoft.com/en-us/library/office/ff194426.aspx

    So I think its about what collection you put it in.



  • @mrjj Thanks again, But I have question in my mind and there are some gaps in my understandings. What is the differences between VBA sample codes in your link and the Activex codes ? How should I convert them to c++? Should I just follow the patterns and guess them?!


  • Qt Champions 2016

    @alizadeh91
    They are using the same model. The syntax is just different. With Qt you are running it via
    a Automation server which VBA also does but its just more natural looking.

    So even syntax not the same, the object its uses and often also actual structure is the same.

    So if you know how to get a embedded chart in VBA, the same will work for Qt.

    Therefore an understanding of the model (of Excel) is needed to use the Qt code.



  • @mrjj Thanks, Could u give me an example (some codes for VBA and Qt which are equivalent). I need a start point to start learning it


  • Qt Champions 2016

    @alizadeh91
    Sadly its not just a 1 to 1 convert.

    VBA:
    Sub charObj()
    Dim myChartObject As ChartObject
    Set myChartObject = ActiveSheet.ChartObjects.Add(Left:=200, Top:=200, _
    Width:=400, Height:=300)
    End Sub

    We can see it adds to ChartObjects of the active sheet. So we know it also has a collection and not
    just the workbook.

    in Qt we do
    QAxObject *workbook = excel->querySubObject("Workbooks")->querySubObject("Add");
    QAxObject *worksheet = workbook->querySubObject("Worksheets(1)");
    Which gives us the sheet.

    There should also be a way to get ActiveSheet like VBA.



  • @mrjj Ok, I've got it and from this I've found that I have to write below code to add a embedded chart in a worksheet:

    worksheet->querySubObject("ChartObjects")->querySubObject("Add")
    

    But it gives following error:

    QAxBase: Error calling IDispatch member ChartObjects: Member not found
    

    I wish there was some examples in web but there is nothing...


  • Qt Champions 2016

    @alizadeh91
    Well

    I had luck with
    QAxObject* test = workbook->querySubObject("ActiveSheet");
    QAxObject* charts = test->querySubObject("ChartObjects");
    QList<QVariant> f; f << 200 << 400 << 200 << 200;
    QVariant ch = charts->dynamicCall("Add(int, int, int, int)", f );

    Added Chart to first sheet

    Now is issue how to get the chart to set it up.

    Not sure what Add returns.
    IF we could get handle to the new chart, rest of code be the same and it would be embedded.



  • @mrjj I don't know why it gives that error that the member is not found (ChartObjects) :(


  • Qt Champions 2016

    @alizadeh91
    Because it must be via ActiveSheet or else it dont know it , i think.
    Or it dont know Add() that way.
    That is the frustrating part. Finding out where it keeps the stuff you want.



  • @mrjj I've changed the code as below and this codes works:

    QAxObject* excel = new QAxObject( "Excel.Application", 0 );
        QAxObject* workbooks = excel->querySubObject( "Workbooks" );
        QAxObject* workbook = workbooks->querySubObject( "Open(const QString&)", "D:\\test.xls" );
        QAxObject* sheets = workbook->querySubObject( "Worksheets" );
        QAxObject* sheet = sheets->querySubObject( "Item( int )", 1 );
        QAxObject* chart = sheet->querySubObject("ChartObjects(int)",1)->querySubObject("Chart");
    

    As seen, ChartObjects(int) has a member called 'Chart' and the code works. So it seems that ChartObjects is not defined by itself or something like that.


  • Qt Champions 2016

    Ah so ("ChartObjects(int)",1)->querySubObject("Chart");
    gives the first chart.
    Good work.
    Thank you for reporting back.



  • @mrjj But still having problem with adding new embedded chart!!!


  • Qt Champions 2016

    @alizadeh91

    Did you try the test code ?.

    It inserts embedded for me.

    QAxObject* test = workbook->querySubObject("ActiveSheet");
    QAxObject* charts = test->querySubObject("ChartObjects");
    QList<QVariant> f; f << 200 << 400 << 200 << 200;
    charts->dynamicCall("Add(int, int, int, int)", f );

    I didnt check if
    ("ChartObjects(int)",1)->querySubObject("Chart"); will give you that.

    From docs, the Add should return the chart object but I could not get it to cast to
    QAxObject* .



  • @mrjj I've changed sheet2->querySubObject("ChartObjects"); to sheet2->querySubObject("ChartObjects()"); and it worked


  • Qt Champions 2016

    Super :)
    oh it must have "()" on.
    The syntax is pretty funky via the automation server.
    Good work.


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.