Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

QAxObject and Excel



  • Hi for all.
    I am trying this code but it does not work. I get a crash at the execution of the instruction
    --->:QAxObject* workbook = workbooks->querySubObject( "Open(const QString&)", "C:\myTest\1.xls" );
    0_1522860114590_Immagine.png
    0_1522860428202_Immagine2.png
    The code seems correct. Other examples are made like this. The xls file is also in the correct folder but it does not work

    #include <QCoreApplication>
    #include <QAxObject>
    
    int main(int argc, char *argv[])
    {
    
         QCoreApplication a(argc, argv);
    
         QAxObject* excel = new QAxObject( "Excel.Application", 0 );
         QAxObject* workbooks = excel->querySubObject( "Workbooks" );
         QAxObject* workbook = workbooks->querySubObject( "Open(const QString&)", "C:\\myTest\\1.xls" );
         QAxObject* sheets = workbook->querySubObject( "Worksheets" );
         QList<QVariantList> data; //Data list from excel, each QVariantList is worksheet row
    
         //worksheets count
         int count = sheets->dynamicCall("Count()").toInt();
    
         count = sheets->property("Count").toInt();
         for (int i=1; i<=count; i++) //cycle through sheets
          {
             //sheet pointer
             QAxObject* sheet = sheets->querySubObject( "Item( int )", i );
    
             QAxObject* rows = sheet->querySubObject( "Rows" );
             int rowCount = rows->dynamicCall( "Count()" ).toInt(); //unfortunately, always returns 255, so you have to check somehow validity of cell values
             QAxObject* columns = sheet->querySubObject( "Columns" );
             int columnCount = columns->property("Count").toInt();
             for (int row=1; row <= rowCount; row++)
             {
                 QVariantList dataRow;
                 bool isEmpty = true; //when all the cells of row are empty, it means that file is at end (of course, it maybe not right for different excel files. it's just criteria to calculate somehow row count for my file)
                 for (int column=1; column <= columnCount; column++)
                 {
                     //Do something usefule here
                 }
                 if (isEmpty) //criteria to get out of cycle
                     break;
                 data.append(dataRow);
    
             }
         }
    
         workbook->dynamicCall("Close()");
         excel->dynamicCall("Quit()");
         return a.exec();
    }
    
    


  • Hi, QAxObject does not work well in a without a message pump to handle the COM events, so instead of a console application, you could put your code inside a Qt Widgets app.
    So create a new, vanilla Widgets app, add axcontainer to the QT += line in the .pro file and edit the mainwindow.cpp:

    ui->setupUi(this);
    // inside MainWindow's ctor, add this:
    
    auto excel     = new QAxObject("Excel.Application");
    auto workbooks = excel->querySubObject("Workbooks");
    auto workbook  = workbooks->querySubObject("Open(const QString&)","C:\\myTest\\1.xls");
    auto sheets    = workbook->querySubObject("Worksheets");
    
    QList<QVariantList> data; //Data list from excel, each QVariantList is worksheet row
    
    //worksheets count
    int count = sheets->dynamicCall("Count()").toInt();
    
    count = sheets->property("Count").toInt();
    for (int i=1; i<=count; i++) //cycle through sheets
    {
    //sheet pointer
        QAxObject* sheet = sheets->querySubObject( "Item( int )", i );
    
        QAxObject* rows = sheet->querySubObject( "Rows" );
        int rowCount = rows->dynamicCall( "Count()" ).toInt(); //unfortunately, always returns 255, so you have to check somehow validity of cell values
        QAxObject* columns = sheet->querySubObject( "Columns" );
        int columnCount = columns->property("Count").toInt();
        for (int row=1; row <= rowCount; row++)
        {
            QVariantList dataRow;
            bool isEmpty = true; //when all the cells of row are empty, it means that file is at end (of course, it maybe not right for different excel files. it's just criteria to calculate somehow row count for my file)
            for (int column=1; column <= columnCount; column++)
            {
               qDebug() << row << column;
                 //Do something usefule here
            }
            if (isEmpty) //criteria to get out of cycle
                break;
            data.append(dataRow);
        }
    }
    
    workbook->dynamicCall("Close()");
    excel->dynamicCall("Quit()");
    }


  • Hi again, correction to my previous post:

    QAxObject works without a message pump and works in a console app. You only have to change your QCoreApplication to QApplication.

    Change these 2 lines in your example:

    #include <QApplication>      //here
    #include <QAxObject>
    
    int main(int argc, char *argv[])
    {
       QApplication a(argc, argv);    // and here
    
       QAxObject* excel = new QAxObject( "Excel.Application", 0 );
    
       ...
    

  • Qt Champions 2019

    @newbieQTDev You should always check the pointers before you use them! So, check whether workbook is a valid pointer before dereferencing it.



  • Hi hskoglund.
    Thank for your replay
    I changed the code how you said but i got the same error.



  • Hmm, I did a small test Qt console app with your code (with the changes: #include <QApplication> and QApplication a(argc, argv);
    and an Excel test file and it worked fine. When I insert qDebug() << row << column in the inner loop it printed all the columns and rows.

    Oh, there's one more reason for the error to occur: if you are running your Qt program on a PC without any Excel installed.


Log in to reply