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

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

To copy to clipboard, switch view to plain text mode 

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;
}

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

To copy to clipboard, switch view to plain text mode 

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

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

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

To copy to clipboard, switch view to plain text mode 

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;
}

#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;
}

To copy to clipboard, switch view to plain text mode 

[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();
}

#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();
}

To copy to clipboard, switch view to plain text mode 

[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

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

To copy to clipboard, switch view to plain text mode 

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?