Qt write/import qlist<qvariantlist> data to excel with qAxObject



  • void MainWindow::dragEnterEvent(QDragEnterEvent *event)
    {
    if(event->mimeData()->hasUrls()){
    event->acceptProposedAction();
    }

    }
    void MainWindow::dropEvent(QDropEvent *event)
    {
    if (event->mimeData()->hasUrls()) {
    const QList<QUrl> files = event->mimeData()->urls();
    if (files.isEmpty())
    return;

        const QString path = files.first().toLocalFile();
        const QString newPath = path+"updated";
    
        QAxObject* excel = new QAxObject( "Excel.Application", this );
        //excel->setProperty("Visible", true);
        //excel->setProperty("DisplayAlerts", false);
        QAxObject* workbooks = excel->querySubObject( "Workbooks" );
        QAxObject* workbook = workbooks->querySubObject( "Open(const QString&)", path );
        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();
    
            //QMessageBox::warning(this, "", QString("%1 - %2").arg(QString::number(columnCount), QString::number(rowCount)));
    
            for (int column=1; column <= qMin(columnCount, 20); column++)
            {
                QVariantList dataRow;
                QVariant value;
                QVariant temp;
                
                for (int row=1; row <= qMin(rowCount, 20); row++)
                {
                    QAxObject* cell = sheet->querySubObject("Cells(int,int)",row,column);
                    value = cell->dynamicCall("Value()");
                    QString temp = value.toString();
                    int indexOfRow = 0;
                    while(temp.isEmpty()) {
                        indexOfRow++;
                        cell = sheet->querySubObject("Cells(int,int)",row-indexOfRow,column);
                        value = cell->dynamicCall("Value()");
                        temp = value.toString();
    
                        QMessageBox::warning(this, "", temp);
                    }
                    dataRow.append(value);
                }
                data.append(dataRow);
            }
        }
        QAxObject *newExcel = new QAxObject( "Excel.Application", this );
        QAxObject *newWorkbooks = newExcel->querySubObject("Workbooks");
        newWorkbooks->dynamicCall("Open()const QString&",newPath);
        newWorkbooks->dynamicCall("Add");
        
        //QVariant variantNewWorkbook = newWorkbooks->asVariant();
        //QVariantList nWL = variantNewWorkbook.
        excel->setProperty("Visible", true);
        excel->setProperty("DisplayAlerts", false);
    
        workbook->dynamicCall("Close()");
        excel->dynamicCall("Quit()");
        newExcel->dynamicCall("Quit()");
    
    }
    

    }
    first of all i m little bit bad in english so, sorry for my bad english.
    i m trying to use "data" list and import/write it to another excel file but i'm stuck so bad. i searched so many forums but couldnt find anything usefull so far.

    Any help would appreciated thx a lot.



  • @oldthrasher Hi,

    What is not working exactly ? Browsing the source file ? saving in the other one ?

    This line seems strange to me, why is there const QString & in the string ? :

     newWorkbooks->dynamicCall("Open()const QString&",newPath);
    


  • If you are working with Xlsx files only, you may be interested by QXlsx library which is probably easier to use IMHO.



  • @gojir4 i am browsing an excel file it's working good. but i'm trying to open another excel(second excel) file and import all the data that is in the first excel file(source file) to second excel file.

    code exports all the data from first excel (source file)
    code not imports the data to second excel.



  • @oldthrasher Did you try to fix the string for calling Open ?

    newWorkbooks->dynamicCall("Open(const QString&)", newPath);
    

    But anyway I see nothing in your code to write the data in the new file created. You are calling Add without any argument.
    You need to loop on data to write them on the new excel file. BTW It could be more efficient to open the new excel file before the loop and then filling it with data at the same time you are reading them from the source file.


 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.