Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. Qt Creator and other tools
  4. Qt write/import qlist<qvariantlist> data to excel with qAxObject
Forum Update on Monday, May 27th 2025

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

Scheduled Pinned Locked Moved Unsolved Qt Creator and other tools
5 Posts 2 Posters 926 Views
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • O Offline
    O Offline
    oldthrasher
    wrote on 11 Sept 2019, 10:54 last edited by
    #1

    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.

    G 1 Reply Last reply 11 Sept 2019, 15:09
    0
    • O oldthrasher
      11 Sept 2019, 10:54

      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.

      G Offline
      G Offline
      Gojir4
      wrote on 11 Sept 2019, 15:09 last edited by
      #2

      @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);
      
      O 1 Reply Last reply 12 Sept 2019, 07:36
      1
      • G Offline
        G Offline
        Gojir4
        wrote on 11 Sept 2019, 15:15 last edited by
        #3

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

        1 Reply Last reply
        2
        • G Gojir4
          11 Sept 2019, 15:09

          @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);
          
          O Offline
          O Offline
          oldthrasher
          wrote on 12 Sept 2019, 07:36 last edited by
          #4

          @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.

          G 1 Reply Last reply 12 Sept 2019, 08:19
          0
          • O oldthrasher
            12 Sept 2019, 07:36

            @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.

            G Offline
            G Offline
            Gojir4
            wrote on 12 Sept 2019, 08:19 last edited by
            #5

            @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.

            1 Reply Last reply
            1

            1/5

            11 Sept 2019, 10:54

            • Login

            • Login or register to search.
            1 out of 5
            • First post
              1/5
              Last post
            0
            • Categories
            • Recent
            • Tags
            • Popular
            • Users
            • Groups
            • Search
            • Get Qt Extensions
            • Unsolved