QAxObject excel Sort Column

jay.liu

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

Range.Sort Key1:=Range("A1"), Order1:=xlAscending,Key2:=Range("A1"), Order2:=xlDescending

hskoglund

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);

jsulm

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

hskoglund

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;
}

jay.liu

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

jay.liu

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

JonB

@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

@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());

hskoglund

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);

jay.liu

@hskoglund it's worked,thank you very mach!╰（￣▽￣）╭