Unsolved QAxobject : How to get possible querysubject options?
-
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. -
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()");
-
Hi @hskoglund :-
Thanks for the answer.
But, I didn't get how to attach excelApplication with iDispatch pointer.
Can you please elaborate it more? -
Hi, sorry I'm on vacation, but when I come back next week I'll promise I'll check it for you...
-
@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") ....