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