Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

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 used QAxObject, 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.



  • @JonB

    Seems you confirm what i expect.
    I will look up VBA docs. Thanks!


Log in to reply