Solved QAxObject excel Sort Column
-
Follow this example and other google example, i manage to perform the basic operation about an excel sheet. However, i cannot find a method to perform a "Sort Column" action.
In VB , it's something like
Range("a1:a10").Sort Key1:=Range("a1")
Does anyone know how I can write a SortColumn() via QAxObject ?
-
Hi, it's (almost :-) the same when using QAxObject, try something like this:
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()");
-
Thanks a lot!!! This is the syntax I'm looking for!
sheet->querySubObject("Range(a1:a10)")->dynamicCall("Sort(Key1:=QAxObject*)",sheet->querySubObject("Range(a1)")->asVariant());
I wonder for other similar operation, we still have to look to MS's VBA documentation. And then translate/mirror to QT, am i right? Is there any qt doc about the possible or common parameters/candidates for members like
dynamicCall(const char *, QList<QVariant> &) : QVariant
-
@Allllex
I haven't usedQAxObject
, so I hope I'm not speaking out of turn, but, yes, for calling VBA-type stuff from Qt or other frameworks (which I have done) I do expect to have to refer to VBA docs. There are so many possibilities, it's not really up to the calling framework to document what you can do, in my experience you have to find VBA docs/examples and adapt to your particular wrapper functions. -
Seems you confirm what i expect.
I will look up VBA docs. Thanks!