Generating embedded charts via Qt ActiveX

alizadeh91

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)");

m.sue

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.

alizadeh91

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

m.sue

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

alizadeh91

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

m.sue

Now it is :-)

alizadeh91

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

mrjj

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

alizadeh91

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

mrjj

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

alizadeh91

@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?!

mrjj

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

alizadeh91

@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

mrjj

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

alizadeh91

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

mrjj

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

alizadeh91

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

mrjj

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

alizadeh91

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

mrjj

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