Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. QAxObject excel Sort Column
Forum Updated to NodeBB v4.3 + New Features

QAxObject excel Sort Column

Scheduled Pinned Locked Moved Solved General and Desktop
9 Posts 4 Posters 1.1k 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.
  • J Offline
    J Offline
    jay.liu
    wrote on 22 Dec 2023, 07:09 last edited by
    #1

    help,please!How to implement this VBA code using C++

    Range.Sort Key1:=Range("A1"), Order1:=xlAscending,Key2:=Range("A1"), Order2:=xlDescending
    
    J 1 Reply Last reply 22 Dec 2023, 07:19
    0
    • H Offline
      H Offline
      hskoglund
      wrote on 27 Dec 2023, 07:10 last edited by
      #8

      Hi, you're on the right track with "Order1:=xlAscending" but in this simple example Qt and C++ does not know the values of xlAscending and xlDescending.

      So you have to get the values from Microsoft's web page and manually insert them in the app, say like this:

      int xlAscending  = 1;
      int xlDescending = 2;
      sheet->querySubObject("Range(a1:a10)")->dynamicCall("Sort(Key1:=QAxObject*,Order1:=(int)",sheet->querySubObject("Range(a1)")->asVariant(),xlDescending);
      
      J 1 Reply Last reply 28 Dec 2023, 02:01
      2
      • J jay.liu
        22 Dec 2023, 07:09

        help,please!How to implement this VBA code using C++

        Range.Sort Key1:=Range("A1"), Order1:=xlAscending,Key2:=Range("A1"), Order2:=xlDescending
        
        J Online
        J Online
        jsulm
        Lifetime Qt Champion
        wrote on 22 Dec 2023, 07:19 last edited by
        #2

        @jay-liu Implement where? Please ask a clear question.

        https://forum.qt.io/topic/113070/qt-code-of-conduct

        J 1 Reply Last reply 25 Dec 2023, 01:16
        0
        • H Offline
          H Offline
          hskoglund
          wrote on 22 Dec 2023, 07:35 last edited by
          #3

          Hi found some old Qt code that does a similar kind of sort in Excel, create a vanilla Widgets app using qmake
          add "axcontainer" in the .pro file

          QT  += core gui widgets axcontainer
          

          and then change the mainwindow.cpp file to:

          #include "mainwindow.h"
          #include "ui_mainwindow.h"
          #include "qaxobject.h"
          #include "qdebug.h"
          
          MainWindow::MainWindow(QWidget *parent) :
              QMainWindow(parent),
              ui(new Ui::MainWindow)
          {
              ui->setupUi(this);
          
          // sorry for bad indentation :-)
          auto randomName = []
          {
              QString s,c;
              do
              {
                  c = QString("abcdehijkmoyprsu").mid(rand() % 20,1);
                  s += c;
              } while (!c.isEmpty() || s.isEmpty());
              return s;
          };
          
          auto excel     = new QAxObject("Excel.Application");
          auto workbooks = excel->querySubObject("Workbooks");
          auto workbook  = workbooks->querySubObject("Add");
          auto sheets    = workbook->querySubObject("Worksheets");
          auto sheet     = sheets->querySubObject("Item(int)",1);
          
          qDebug() << "Fill column with random chars";
          for (int r = 1; (r <= 10); ++r)
              sheet->querySubObject("Cells(int,int)",r,1)->setProperty("Value",randomName());
          
          qDebug() << "Before sorting";
          for (int r = 1; (r <= 10); ++r)
              qDebug() << sheet->querySubObject("Cells(int,int)",r,1)->dynamicCall("Value()");
          
          // do the sort
          sheet->querySubObject("Range(a1:a10)")->dynamicCall("Sort(Key1:=QAxObject*)",sheet->querySubObject("Range(a1)")->asVariant());
          
          qDebug() << "After sorting";
          for (int r = 1; (r <= 10); ++r)
              qDebug() << sheet->querySubObject("Cells(int,int)",r,1)->dynamicCall("Value()");
          
          excel->dynamicCall("Quit()");
          }
          
          MainWindow::~MainWindow()
          {
              delete ui;
          }
          
          J 1 Reply Last reply 25 Dec 2023, 01:03
          1
          • H hskoglund
            22 Dec 2023, 07:35

            Hi found some old Qt code that does a similar kind of sort in Excel, create a vanilla Widgets app using qmake
            add "axcontainer" in the .pro file

            QT  += core gui widgets axcontainer
            

            and then change the mainwindow.cpp file to:

            #include "mainwindow.h"
            #include "ui_mainwindow.h"
            #include "qaxobject.h"
            #include "qdebug.h"
            
            MainWindow::MainWindow(QWidget *parent) :
                QMainWindow(parent),
                ui(new Ui::MainWindow)
            {
                ui->setupUi(this);
            
            // sorry for bad indentation :-)
            auto randomName = []
            {
                QString s,c;
                do
                {
                    c = QString("abcdehijkmoyprsu").mid(rand() % 20,1);
                    s += c;
                } while (!c.isEmpty() || s.isEmpty());
                return s;
            };
            
            auto excel     = new QAxObject("Excel.Application");
            auto workbooks = excel->querySubObject("Workbooks");
            auto workbook  = workbooks->querySubObject("Add");
            auto sheets    = workbook->querySubObject("Worksheets");
            auto sheet     = sheets->querySubObject("Item(int)",1);
            
            qDebug() << "Fill column with random chars";
            for (int r = 1; (r <= 10); ++r)
                sheet->querySubObject("Cells(int,int)",r,1)->setProperty("Value",randomName());
            
            qDebug() << "Before sorting";
            for (int r = 1; (r <= 10); ++r)
                qDebug() << sheet->querySubObject("Cells(int,int)",r,1)->dynamicCall("Value()");
            
            // do the sort
            sheet->querySubObject("Range(a1:a10)")->dynamicCall("Sort(Key1:=QAxObject*)",sheet->querySubObject("Range(a1)")->asVariant());
            
            qDebug() << "After sorting";
            for (int r = 1; (r <= 10); ++r)
                qDebug() << sheet->querySubObject("Cells(int,int)",r,1)->dynamicCall("Value()");
            
            excel->dynamicCall("Quit()");
            }
            
            MainWindow::~MainWindow()
            {
                delete ui;
            }
            
            J Offline
            J Offline
            jay.liu
            wrote on 25 Dec 2023, 01:03 last edited by
            #4

            @hskoglund I have read this topic, but it can only be sorted in ascending order and cannot be sorted in descending order

            JonBJ 1 Reply Last reply 25 Dec 2023, 07:42
            0
            • J jsulm
              22 Dec 2023, 07:19

              @jay-liu Implement where? Please ask a clear question.

              J Offline
              J Offline
              jay.liu
              wrote on 25 Dec 2023, 01:16 last edited by
              #5

              @jsulm sorry,i am a student from china,english is so pool to make it clear,i plan implement it with the module axcontainer and the Class QAxObject.

              1 Reply Last reply
              0
              • J jay.liu
                25 Dec 2023, 01:03

                @hskoglund I have read this topic, but it can only be sorted in ascending order and cannot be sorted in descending order

                JonBJ Offline
                JonBJ Offline
                JonB
                wrote on 25 Dec 2023, 07:42 last edited by
                #6

                @jay-liu said in QAxObject excel Sort Column:

                can only be sorted in ascending order and cannot be sorted in descending order

                https://learn.microsoft.com/en-us/office/vba/api/excel.range.sort shows

                expression.Sort (Key1, Order1,...)

                Order1 Optional XlSortOrder Determines the sort order for the values specified in Key1.

                So presumably you should add that into your dynamicCall("Sort(Key1:=QAxObject*)" call.

                J 1 Reply Last reply 27 Dec 2023, 02:29
                0
                • JonBJ JonB
                  25 Dec 2023, 07:42

                  @jay-liu said in QAxObject excel Sort Column:

                  can only be sorted in ascending order and cannot be sorted in descending order

                  https://learn.microsoft.com/en-us/office/vba/api/excel.range.sort shows

                  expression.Sort (Key1, Order1,...)

                  Order1 Optional XlSortOrder Determines the sort order for the values specified in Key1.

                  So presumably you should add that into your dynamicCall("Sort(Key1:=QAxObject*)" call.

                  J Offline
                  J Offline
                  jay.liu
                  wrote on 27 Dec 2023, 02:29 last edited by
                  #7

                  @JonB I have tried many methods to add this parameter, but it has not taken effect,such as

                  QString orderString = isAsc ? "Sort(Key1:=QAxObject*, Order1:=xlAscending)" : "Sort(Key1:=QAxObject*, Order1:=xlDescending)";
                  range->dynamicCall(orderString.toUtf8(), worksheet->querySubObject("Range(const QString&)", "A1")->asVariant());
                  
                  1 Reply Last reply
                  0
                  • H Offline
                    H Offline
                    hskoglund
                    wrote on 27 Dec 2023, 07:10 last edited by
                    #8

                    Hi, you're on the right track with "Order1:=xlAscending" but in this simple example Qt and C++ does not know the values of xlAscending and xlDescending.

                    So you have to get the values from Microsoft's web page and manually insert them in the app, say like this:

                    int xlAscending  = 1;
                    int xlDescending = 2;
                    sheet->querySubObject("Range(a1:a10)")->dynamicCall("Sort(Key1:=QAxObject*,Order1:=(int)",sheet->querySubObject("Range(a1)")->asVariant(),xlDescending);
                    
                    J 1 Reply Last reply 28 Dec 2023, 02:01
                    2
                    • H hskoglund
                      27 Dec 2023, 07:10

                      Hi, you're on the right track with "Order1:=xlAscending" but in this simple example Qt and C++ does not know the values of xlAscending and xlDescending.

                      So you have to get the values from Microsoft's web page and manually insert them in the app, say like this:

                      int xlAscending  = 1;
                      int xlDescending = 2;
                      sheet->querySubObject("Range(a1:a10)")->dynamicCall("Sort(Key1:=QAxObject*,Order1:=(int)",sheet->querySubObject("Range(a1)")->asVariant(),xlDescending);
                      
                      J Offline
                      J Offline
                      jay.liu
                      wrote on 28 Dec 2023, 02:01 last edited by
                      #9

                      @hskoglund it's worked,thank you very mach!╰( ̄▽ ̄)╭

                      1 Reply Last reply
                      0
                      • J jay.liu has marked this topic as solved on 28 Dec 2023, 02:02

                      1/9

                      22 Dec 2023, 07:09

                      • Login

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