ActiveQT + Excel charts

[cia.michele]

Goodevening to everybody,
I should create an excel chart from my QT 4.6 application.
I'using ActiveQT on Mingw (Win XP SO), and I obtain to write some data to an excel sheet.

How I can add a Series to the chart now?
This is my code.


QAxObject * excel = new QAxObject("Excel.Application",0);
QAxObject *workbooks = excel->querySubObject( "Workbooks" );
QAxObject *workbook = workbooks->querySubObject( "Add");
QAxObject *sheets = workbook->querySubObject( "Worksheets" );
QAxObject *charts =workbook->querySubObject("Charts");
QAxObject *chart = charts->querySubObject("Add");

QAxObject *sheet = sheets->querySubObject( "Item( int )", 1 );
sheet->setProperty("Name","Dati applicazione");

QAxObject*cell=sheet->querySubObject("Cells(int,int)",1,1);
cell->dynamicCall("SetValue(String)","Serie");

cell=sheet->querySubObject("Cells(int,int)",1,2);
cell->dynamicCall("SetValue(String)","Dati");

for (int i=2; i<10; i++){
cell=sheet->querySubObject("Cells(int,int)",i,1);
cell->dynamicCall("SetValue(int)",i-1);
cell=sheet->querySubObject("Cells(int,int)",i,2);
cell->dynamicCall("SetValue(double)",qrand());
}

chart->setProperty("Name","Report Grafico dei dati");
chart->setProperty("CharThype",73);

QAxObject *series = chart->property("SeriesCollection");

I got this error:


QAxBase: Error calling IDispatch member SeriesCollection: Member not found


Thanks for your time

Michele

[bst]

Hi,

QAxObject *series = chart->querySubObject("SeriesCollection");

Then try to use NewSeries.

Maybe you should try this before in VBA to see how to do it.

HTH, Bernd

[cia.michele]

hello bst,
thanks for your reply but... no way :(
It got Segmentation Fault and the pointer of debug highlight this code:

bool QAxBase::isNull() const
{
return !d->ptr;
}

so the QAxBase class don't return anything for this code:


QAxObject *series = chart->querySubObject("SeriesCollection");
QAxObject *serie = series->querySubObject("NewSeries");

When I try to exec NewSeries command, it got Segmentation Fault.

I use VBA on excel several time, it show Object and Collection, in this case a collaction give the problem.

It is possible to create a "Collection-equivalent" QAxBase object?
How can I create a new series?
Where could I show how I should use the Excel methods?

Thanks for your time

[bst]

Hi,

doesn't work here either. I've no idea why :-(

What works here is to 'force' Charts.Add to already add a series. This can be done by 'Selecting the source-range' before calling Charts.Add. Here - for one series - it's enough to write into the cells and set XValues and Values later.

HTH, Bernd
--

#include <QtGui/QtGui>
#include <QAxWidget>
#include <QAxObject>

int main(int argc, char *argv[])
{
QApplication a(argc, argv);

QAxWidget *excel = new QAxWidget("Excel.Application", 0);
excel->setProperty("SheetsInNewWorkbook", 1);
// excel->setProperty("Visible", true);
QAxObject *workbooks = excel->querySubObject("Workbooks");
QAxObject *workbook = workbooks->querySubObject("Add");
QAxObject *worksheet = workbook->querySubObject("Worksheets (int)", 1);

worksheet->setProperty("Name","Dati applicazione");

QAxObject *cell = worksheet->querySubObject("Cells(int,int)", 1, 1);
cell->dynamicCall("SetValue(String)", "Serie");

cell = worksheet->querySubObject("Cells(int,int)", 1, 2);
cell->dynamicCall("SetValue(String)", "Dati");

for (int i = 2; i < 10; i++){
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)", qrand());
}

QAxObject *charts = workbook->querySubObject("Charts");
QAxObject *chart = charts->querySubObject("Add");
chart->setProperty("Name", "Report Grafico dei dati");
chart->setProperty("ChartType", 73);

QAxObject * serie = chart->querySubObject("SeriesCollection(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());

workbook->dynamicCall("SaveAs (const QString&)", "E:/test/chartbyqt");
workbook->dynamicCall("Close (Boolean)", false);
excel->dynamicCall("Quit (void)");

return 0;
}

[cia.michele]

mmm interesting!! :)

do u think I could use this way for more than one series?
I should build an excel file, with several sheets: most of these contains (datetime-values) couples, and at the end i should plot all series in a chart.

Probably this way give me the answer.

Thanks a lot for your time, I'll try it soon.

Michele

PS. I saw other way to connect with OCX object such as VOLE, it divide the Object, from Collection, so the NewSeries command can be used, probabily the answer is concernent this.

[bst]

Hi,

this works here. The 'trick' ist the range->dynamicCall("Select (void)");

HTH, Bernd
--

#include <QtGui/QtGui>
#include <QAxWidget>
#include <QAxObject>
#include <QDebug>

int main(int argc, char *argv[])
{
QApplication a(argc, argv);

QAxWidget *excel = new QAxWidget("Excel.Application", 0);
//excel->setProperty("SheetsInNewWorkbook", 3);
//excel->setProperty("Visible", true);
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);
cell = worksheet->querySubObject("Cells(int,int)", i, 4);
cell->dynamicCall("SetValue(double)", dval/3);
}
// Selecting 3 columns will force charts.add to create 3 series !
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("ChartType", 73);

QAxObject * series = chart->querySubObject("SeriesCollection");
// qDebug() << series->property("Count"); // 3 !

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/chartbyqt");
workbook->dynamicCall("Close (Boolean)", false);
excel->dynamicCall("Quit (void)");

return 0;
//return a.exec();
}

[cia.michele]

Dear bst,
I tried your code but... It doesn't run! Give me this error:

QAxBase: Error calling IDispatch member SeriesCollection: Member not found
Now... looking at your code, I saw that you use QAxWidget, while I ever use QAxObject to open Excel.Application, which is the difference? I need to add some libraries? any project configuration? This is my project file:

QT += core gui sql
CONFIG +=qaxcontainer
TARGET = TestThread
TEMPLATE = app


SOURCES += main.cpp\
mythread1.cpp \
dlgok.cpp \
[...]

HEADERS += \
mythread1.h \
dlgok.h \
[...]
FORMS += \
dlgok.ui
there is something wrong?

Thanks for your time.

Michele

Added after 5 minutes:

I'm use Wt 4.7.2 on Windows and Excel 2007.

Thanks for your time.

Michele

[bst]

Hi Michele,


I tried your code but... It doesn't run!

Here - with Win XP and Office 2003 prof. - it runs fine. I'm using Qt 4.5.3 and VS2003, and the following .pro:

TEMPLATE = app
CONFIG += debug console qaxcontainer
TARGET = ExcelChart

SOURCES += ./main.cpp

Addition: Whether you use QAxObject or QAxWidget shouldn't matter here.

cu, Bernd

[cia.michele]

I try it on Office 2010 Prof. It seems run OK! :)
Thanks for your Help!

Michele

[alizadeh91]

How about if I want to create embedded charts in excel?