QAxobject : How to get possible querysubject options?

[ankit thakar]

While working with QAxObject ,

First:

excel = new QAxObject( "Excel.Application", 0 );
excel->setProperty("Visible", false);
workbooks = excel->querySubObject( "Workbooks" );
workbook = workbooks->querySubObject( "Open(const QString&)", QString("D:\t1.xlsx") );

In third line let's say I want all possible Object list to which I can query.
Is there any way to do that?

Second:-
If we are working with Excel through QAxobject and if I want to create file,
How is it possible?
Because for above the prerequisite is that t1.xlsx file should be created in advance.
But my idea is if file is not there QAxobject should create it.

[hskoglund]

Hi, if you want to find out all possible Objects then you need a IDispatch pointer to Excel.Application (which you can then use to obtain ITypeInfo, ITypeLib pointers to enumerate all the stuff).
How to get that IDispatch interface you can read more [here]
(https://stackoverflow.com/questions/20002811/obtaining-the-excel-application-idispatch-within-a-dll-thats-been-loaded-into)

The Second question, one way to create a file using Excel is to begin with a new empty worksheet and the call SaveAs() to save the worksheet to a disk (.xlsx) file, example here:

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

auto row       = 1;
auto column    = 1;
sheet->querySubObject("Cells(Int,Int)",row++,column)->setProperty("Value","Hello");
sheet->querySubObject("Cells(Int,Int)",row++,column)->setProperty("Value","Excel");

workbook->dynamicCall("SaveAs(QString&)",QString("C:\\Temp\\TestFile"));
workbook->dynamicCall("Close()");
excel->dynamicCall("Quit()");

[ankit thakar]

Hi @hskoglund :-
Thanks for the answer.
But, I didn't get how to attach excelApplication with iDispatch pointer.
Can you please elaborate it more?

[hskoglund]

Hi, sorry I'm on vacation, but when I come back next week I'll promise I'll check it for you...

[hskoglund]

@ankit-thakar Hi, I tested Excel with IDispatch pointer, works fine.

Here's how to do it: create a vanilla widget application.
In the .pro file, add axcontainer like this:
QT += core gui axcontainer
Leave mainwindow.h and mainwindow.cpp unchanged. Change main.cpp to this:

#include "mainwindow.h"
#include <QApplication>

#include "qaxobject.h"
#include "quuid.h"
#include "qdebug.h"

#include <atlbase.h>

void partyOnTypeLibrary(CComPtr<ITypeLib> pTypeLib)
{
// BSTR to QString converter
    auto bstr2QString = [](BSTR bstr) { return QString::fromUtf16((ushort*) bstr); };

// show type library's doc strings
    CComBSTR bstrName,bstrDocString;
    pTypeLib->GetDocumentation(-1,&bstrName,&bstrDocString,NULL,NULL);
    qDebug() << bstr2QString(bstrName) << bstr2QString(bstrDocString) << ":\n";

// step thru all the type info
    for (UINT u = 0; (u < pTypeLib->GetTypeInfoCount()); ++u)
    {
    // get the iTypeInfo ptr
        CComPtr<ITypeInfo> pTypeInfo;
        if FAILED(pTypeLib->GetTypeInfo(u,&pTypeInfo))
            continue;

    // get type flavor and name
        TYPEKIND typeKind;
        if FAILED(pTypeLib->GetTypeInfoType(u,&typeKind))
            continue;

        CComBSTR bstrName;
        if FAILED(pTypeInfo->GetDocumentation(-1,&bstrName,NULL,NULL,NULL))
            continue;
        QString sName = bstr2QString(bstrName);

    // get the type attribute
        TYPEATTR* pTypeAttr;
        if FAILED(pTypeInfo->GetTypeAttr(&pTypeAttr))
            continue;

    // check the type flavor, we support enums, interfaces and coclasses
        if (TKIND_ENUM == typeKind)
        {
            QString sEnums;
            for (int v = 0; (v < pTypeAttr->cVars); ++v)
            {
                VARDESC* pVarDesc;
                if FAILED(pTypeInfo->GetVarDesc(v,&pVarDesc))
                    break;

                if (v > 0)
                    sEnums += " ";
                if (VAR_CONST == pVarDesc->varkind)
                    sEnums += QString::number(pVarDesc->lpvarValue->lVal);

                pTypeInfo->ReleaseVarDesc(pVarDesc);
            }

            qDebug() << "Enum type:" << sName << sEnums;
        }

        if (TKIND_INTERFACE == typeKind)
            qDebug() << "Interface type:" << sName;

        if (TKIND_DISPATCH == typeKind)
            qDebug() << "IDispatch callable type:" << sName;

    // any function descriptors? they usually exist for interfaces and IDispatch
        for (int f = 0; (f < pTypeAttr->cFuncs); ++f)
        {
            FUNCDESC* pFuncDesc;
            if FAILED(pTypeInfo->GetFuncDesc(f,&pFuncDesc))
                break;

            if (pFuncDesc->wFuncFlags > 0)
                continue;   // skip these chaps (boring)

            if (pFuncDesc->invkind > INVOKE_PROPERTYGET)
                continue;   // skip put and putref properties

        // get the prop/func name and all the arg names
            QStringList slNames;
            BSTR aBS[1000];     // 1000 should suffice
            UINT uNames = 0;
            if FAILED(pTypeInfo->GetNames(pFuncDesc->memid,aBS,1000,&uNames))
                break;

            for (uint u = 0; (u < uNames); ++u)
                slNames.append(bstr2QString(aBS[u]));

            QString sKind = "Property";
            if (pFuncDesc->invkind == INVOKE_FUNC)
                sKind = "Function";
            QString sPropOrFuncName = slNames.first();
            slNames.removeFirst();

            qDebug() << sKind << sPropOrFuncName << slNames;
        }
        qDebug() << "";

    // we only care about coclasses at this point
        if (TKIND_COCLASS != typeKind)
            continue;

        qDebug() << "CoClass:" << bstr2QString(bstrName) << ":";

    // step through the implemented types for this coclass
        for (int i = 0; (i < pTypeAttr->cImplTypes); ++i)
        {
            HREFTYPE hRefType;
            if FAILED(pTypeInfo->GetRefTypeOfImplType(i,&hRefType))
                continue;

            CComPtr<ITypeInfo> pRefTypeInfo;
            if FAILED(pTypeInfo->GetRefTypeInfo(hRefType,&pRefTypeInfo))
                continue;

            CComBSTR bstrInterfaceName;
            if FAILED(pRefTypeInfo->GetDocumentation(-1,&bstrInterfaceName,NULL,NULL,NULL))
                continue;

            qDebug() << "        " << bstr2QString(bstrInterfaceName);
        }
    }
}

void main(int argc, char *argv[])
{
    QApplication a(argc, argv);

// launch Excel and try to obtain an IDispatch ptr
    auto excel = new QAxObject("Excel.Application");
    CComPtr<IDispatch> pDispatch;
    excel->queryInterface(QUuid(IID_IDispatch),(void**) &pDispatch);

    if (nullptr == pDispatch)
        return qDebug("Error: an IDispatch interface to \"Excel.Application\" was not found.");

// get the ITypeInfo ptr
    UINT uTypeInfo;
    HRESULT hr = pDispatch->GetTypeInfoCount(&uTypeInfo);
    if (FAILED(hr) || (uTypeInfo < 1))
        return qDebug("Sorry, could not locate any type information");
    if (1 != uTypeInfo)
        return qDebug("Expected GetTypeInfoCount() to return 1 but alas");

    CComPtr<ITypeInfo> pTypeInfo;
    if FAILED(pDispatch->GetTypeInfo(0,LOCALE_SYSTEM_DEFAULT,&pTypeInfo))
        return qDebug("Error: GetTypeInfo() failed");

// ok have ITypeInfo, use it to get the ITypeLib
    CComPtr<ITypeLib> pTypeLib;
    UINT uTypeInfoIndex;
    if FAILED(pTypeInfo->GetContainingTypeLib(&pTypeLib,&uTypeInfoIndex))
        return qDebug("Error: GetContainingTypeLib() failed");

// party on this type library
    partyOnTypeLibrary(pTypeLib);

// sayonara
    excel->dynamicCall("Quit()");
}

For this I used Qt 5.11.1 and MSVC2015 32-bit compiler. I haven't tested with any other compiler. The 32-bit MinGW compiler I think should work as long as the old ATL .h files are present (the program needs ATLBase.h).

Also I have only tested Excel 2003 and Excel 2010. Beware of big outputs, more than 10000 lines. Here's an excerpt from somewhere in the middle:

...
IDispatch callable type: "Range"
"Property" "Application" ()
"Property" "Creator" ()
"Property" "Parent" ()
"Function" "Activate" ()
"Property" "AddIndent" ()
"Property" "Address" ("RowAbsolute", "ColumnAbsolute", "ReferenceStyle", "External", "RelativeTo")
"Property" "AddressLocal" ("RowAbsolute", "ColumnAbsolute", "ReferenceStyle", "External", "RelativeTo")
"Function" "AdvancedFilter" ("Action", "CriteriaRange", "CopyToRange", "Unique")
"Function" "ApplyNames" ("Names", "IgnoreRelativeAbsolute", "UseRowColumnNames", "OmitColumn", "OmitRow", "Order", "AppendLast")
....