Where is documentation for excel manipulating commands?

[falconium]

For example, the following gives you the object of active sheet:
QAxObject *excelWorkSheet = pexcel->querySubObject("ActiveSheet");

But, where can I find the list of commands that I can give to excel objects?
I tried to find Excel COM docs, but they were for VB and totally confusing.
My aim is to create a chart from a table.

QAxObject* excel = new QAxObject("Excel.Application", 0);
    if (!excel)
    {
        QMessageBox::critical(this,
                              "Error while creating excel object!",
                              "No excel object can be instantiated!\n"
                              "Please, check if you have MS Excel installed.");
        log("Excel object cannot be created (is not Excel installed?)!", 1);
        return;
    }
    //QAxObject* application = excel->querySubObject("Application()");
    QAxObject* workbooks = excel->querySubObject("Workbooks()");
    if (!workbooks)
    {
        QMessageBox::critical(this,
                              "Error while creating excel object!",
                              "No excel object can be instantiated!\n"
                              "Workbooks in Excel document can be found.");
        log("Excel object cannot be created (is not Excel installed?)!", 1);
        return;
    }
    QAxObject* workbook = workbooks->querySubObject("Add()");
    QAxObject* worksheet = workbook->querySubObject("Worksheets(int)", 1 );
    ChartView* active = activeMdiChild();
    if (!active)
    {
        log("There is no activated chart window!", 1);
        return;
    }
    QStandardItemModel *model = active->getModel();
    if (!model)
    {
        log("There is no data in chart window!", 1);
        return;
    }
    for (int x=1; x<model->columnCount()+1; x++)
    {
        QAxObject *range = worksheet->querySubObject("Range(QString)",
                                                     getExcelColumnName(x) + "1" +
                                                     ":" +
                                                     getExcelColumnName(x) + "1"
                                                     );
        range->setProperty("Value", model->horizontalHeaderItem(x-1)->text());
        delete range;
    }
    for (int y=1; y<model->rowCount()+1; y++)
        for (int x=1; x<model->columnCount()+1; x++)
        {
            QAxObject *range = worksheet->querySubObject("Range(QString)",
                                                         getExcelColumnName(x) + QString("%1").arg(y+1) +
                                                         ":" +
                                                         getExcelColumnName(x) + QString("%1").arg(y+1)
                                                         );
            range->setProperty("Value", model->item(y-1, x-1)->data(Qt::EditRole));
            delete range;
        }

QAxObject* excel = new QAxObject("Excel.Application", 0);
    if (!excel)
    {
        QMessageBox::critical(this,
                              "Error while creating excel object!",
                              "No excel object can be instantiated!\n"
                              "Please, check if you have MS Excel installed.");
        log("Excel object cannot be created (is not Excel installed?)!", 1);
        return;
    }
    //QAxObject* application = excel->querySubObject("Application()");
    QAxObject* workbooks = excel->querySubObject("Workbooks()");
    if (!workbooks)
    {
        QMessageBox::critical(this,
                              "Error while creating excel object!",
                              "No excel object can be instantiated!\n"
                              "Workbooks in Excel document can be found.");
        log("Excel object cannot be created (is not Excel installed?)!", 1);
        return;
    }
    QAxObject* workbook = workbooks->querySubObject("Add()");
    QAxObject* worksheet = workbook->querySubObject("Worksheets(int)", 1 );
    ChartView* active = activeMdiChild();
    if (!active)
    {
        log("There is no activated chart window!", 1);
        return;
    }
    QStandardItemModel *model = active->getModel();
    if (!model)
    {
        log("There is no data in chart window!", 1);
        return;
    }
    for (int x=1; x<model->columnCount()+1; x++)
    {
        QAxObject *range = worksheet->querySubObject("Range(QString)",
                                                     getExcelColumnName(x) + "1" +
                                                     ":" +
                                                     getExcelColumnName(x) + "1"
                                                     );
        range->setProperty("Value", model->horizontalHeaderItem(x-1)->text());
        delete range;
    }
    for (int y=1; y<model->rowCount()+1; y++)
        for (int x=1; x<model->columnCount()+1; x++)
        {
            QAxObject *range = worksheet->querySubObject("Range(QString)",
                                                         getExcelColumnName(x) + QString("%1").arg(y+1) +
                                                         ":" +
                                                         getExcelColumnName(x) + QString("%1").arg(y+1)
                                                         );
            range->setProperty("Value", model->item(y-1, x-1)->data(Qt::EditRole));
            delete range;
        }

To copy to clipboard, switch view to plain text mode 

QAxObject *chart = worksheet->querySubObject("ChartObjects.Add(int, int, int, int)", 200,200,200,200);

workbook->dynamicCall("SaveAs (const QString&)", fileName);
    workbook->dynamicCall("Close()");
    excel->dynamicCall("Quit()");

workbook->dynamicCall("SaveAs (const QString&)", fileName);
    workbook->dynamicCall("Close()");
    excel->dynamicCall("Quit()");

To copy to clipboard, switch view to plain text mode 

[falconium]

I have found some Object Model manual on the site of MS, but it is quite hard to find out the use cases in Qt...
http://office.microsoft.com/en-us/ex...080550097.aspx

[ChrisW67]

At a guess* this:

QAxObject *chart = worksheet->querySubObject("ChartObjects.Add(int, int, int, int)", 200,200,200,200);

QAxObject *chart = worksheet->querySubObject("ChartObjects.Add(int, int, int, int)", 200,200,200,200);

To copy to clipboard, switch view to plain text mode 

should be this:

QAxObject *charts = worksheet->querySubObject("ChartObjects");
QAxObject *newChart = charts->dynamicCall("Add(int, int, int, int)", 200,200,200,200);

QAxObject *charts = worksheet->querySubObject("ChartObjects");
QAxObject *newChart = charts->dynamicCall("Add(int, int, int, int)", 200,200,200,200);

To copy to clipboard, switch view to plain text mode 

I have used dumpcpp (it's in the docs) to get a more natural set of C++ proxy classes for the ActiveX objects. Never done it with Excel though.

* I mean it.

[falconium]

Yes, I have figured it out so far... but I'm wasting a lot of time despite having the excel object model doc since almost everything seems to be special.

QAxObject *chart = worksheet->querySubObject("ChartObjects()");
chart->dynamicCall("Add(long, long, long, long)", 200, 200, 200, 200);
chart->dynamicCall("Select()");
chart->setProperty("ChartType(long)", "xlXYScatterLines"); // 74?
QAxObject *charttitle = chart->querySubObject("ChartTitle()");
charttitle->setProperty("Text(const QString&)", "X");

Now I'm stucked at the indicated lines... ehh, let's see this dumpcpp. Thanks!

[falconium]

OK, now I have extracted the header and source files of excel by dumpcpp and have found the following in them:

inline Excel::XlChartType ChartType() const; //Returns the value of ChartType
inline void SetChartType(Excel::XlChartType value); //Sets the value of the ChartType property

inline Excel::XlChartType ChartType() const; //Returns the value of ChartType
inline void SetChartType(Excel::XlChartType value); //Sets the value of the ChartType property

To copy to clipboard, switch view to plain text mode 

So, I suppose I would have to use the SetChartType command instead of the interrogation, but it doesn't work when I specify either 'long' or 'XlChartType' as parameters.

QAxObject *chart = worksheet->querySubObject("ChartObjects()");
chart->dynamicCall("Add(long, long, long, long)", 200, 200, 200, 200);
chart->setProperty("SetChartType(  /*what comes here?*/  )",   /*what comes here?*/  );

QAxObject *chart = worksheet->querySubObject("ChartObjects()");
chart->dynamicCall("Add(long, long, long, long)", 200, 200, 200, 200);
chart->setProperty("SetChartType(  /*what comes here?*/  )",   /*what comes here?*/  );

To copy to clipboard, switch view to plain text mode 

What should I use here?

Thanks!

[ChrisW67]

You should use the proxy classes that dumpcpp has created for you and not dynamicCall() and setProperty(). In the generated header you should find classes for the Excel Application, Workbooks, Workbook, ChartObjects etc. Each of these should have its properties and methods exposed with standard C++ methods. All of this will be, by default anyway, wrapped in a namespace Excel. So you code becomes much more natural, for example:

// Include the generated proxy classed definitions
#include "excel.h"
 
Excel::Application * excel = new Excel::Application(this);
Excel::Worksheets *sheets = excel->WorkSheets();
qDebug() << sheets->Count();
etc...

// Include the generated proxy classed definitions
#include "excel.h"

Excel::Application * excel = new Excel::Application(this);
Excel::Worksheets *sheets = excel->WorkSheets();
qDebug() << sheets->Count();
etc...

To copy to clipboard, switch view to plain text mode 

You will probably need the Microsoft Office type library (MSO.DLL) dumped to be able to build the Excel files.