QAxObject excel Sort Column
-
wrote on 22 Dec 2023, 07:09 last edited by
help,please!How to implement this VBA code using C++
Range.Sort Key1:=Range("A1"), Order1:=xlAscending,Key2:=Range("A1"), Order2:=xlDescending
-
wrote on 27 Dec 2023, 07:10 last edited by
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);
-
help,please!How to implement this VBA code using C++
Range.Sort Key1:=Range("A1"), Order1:=xlAscending,Key2:=Range("A1"), Order2:=xlDescending
@jay-liu Implement where? Please ask a clear question.
-
wrote on 22 Dec 2023, 07:35 last edited by
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 fileQT += 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; }
-
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 fileQT += 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; }
wrote on 25 Dec 2023, 01:03 last edited by@hskoglund I have read this topic, but it can only be sorted in ascending order and cannot be sorted in descending order
-
@hskoglund I have read this topic, but it can only be sorted in ascending order and cannot be sorted in descending order
wrote on 25 Dec 2023, 07:42 last edited by@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. -
@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.wrote on 27 Dec 2023, 02:29 last edited by@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());
-
wrote on 27 Dec 2023, 07:10 last edited by
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);
-
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);
wrote on 28 Dec 2023, 02:01 last edited by@hskoglund it's worked,thank you very mach!╰( ̄▽ ̄)╭
-
1/9