Generating embedded charts via Qt ActiveX

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.

alizadeh91

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

mrjj

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

alizadeh91

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

mrjj

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