QAxObject Excel and Pivot Chart

Exaecho

Hello everyone,

I have just taken over a well started QT C++ project and I need to make some modifications.
I need your help to guide me a little bit.

I have never used QAxObject, rather new to QT/C++, and I am really struggling to set up a pivot table in Excel 2010.

The project as it stands already creates an .xlsb file, inserts data into it and creates a chart with one column on the x-axis and one on the y-axis.

I now need to create a pivot chart with those same two columns and another as a filter.

I have not been able to find any useful resources other than this one: https://docs.microsoft.com/fr-fr/office/vba/api/excel.xlpivotcelltype

The list of functions referring to the pivot table : XlPivotCellType, XlPivotConditionScope, XlPivotFieldCalculation, XlPivotFieldDataType, XlPivotFieldOrientation, XlPivotFieldRepeatLabels, XlPivotFilterType, XlPivotFormatType, XlPivotLineType, XlPivotTableMissingItems, XlPivotTableSourceType, XlPivotTableVersionList.

I have no idea how to use these functions to create my pivot table.
Here is the functional source code used to create a simple chart in the excel sheet:

void ResultFileExcelSheet::addGraphic(const QRect& position, const GraphicData& graphic)
{
LOG(ActivityLog::Debug,QString("Add graphics at (%1,%2 : %3x%4)").arg(position.x()).arg(position.y()).arg(position.height()).arg(position.width()));
    //this->worksheet->querySubObject("ChartObjects()")->setProperty("Placement","xlFreeFloating");
    QAxObject* chartobject = this->worksheet->querySubObject("ChartObjects()");
    //chartobject->setProperty("Placement","xlFreeFloating");
    //chartobject->setProperty("Placement","xlMoveAndSize");
    QAxObject* chart = chartobject->querySubObject("Add(int,int,int,int)",position.x(),position.y(),position.width(),position.height())->querySubObject("Chart()");
    QString graphType;
    //qDebug()<<graphic.graphicType;

    if(graphic.graphicType == "4")
    {
        graphType = "xlLine";
    }
    else if(graphic.graphicType == "75")
    {
        graphType = "xlXYScatterLinesNoMarkers";
    }
    else if(graphic.graphicType == "65")
    {
        graphType = "xlLineMarkers";
    }
    else if(graphic.graphicType == "-4169")
    {
        graphType = "xlXYScatter";
    }
    else
    {
        graphType = "xlLine";
    }

    chart->setProperty("ChartType",graphType);
    //chart->setProperty("ChartType",graphic.graphicType);
    chart->querySubObject("Legend")->setProperty("Position",-4107);
    //chart->setProperty("Placement","xlMove");
    //chart->querySubObject("Shapes")->setProperty("Placement","xlMoveAndSize");
    //chart->setProperty("Placement","xlMoveAndSize");
    chart->setProperty("HasTitle","True");
    chart->querySubObject("ChartTitle")->setProperty("Text",graphic.graphicName);
    chart->querySubObject("Axes(xlCategory)")->querySubObject("Border")->setProperty("LineStyle",-4142);
    //chart->querySubObject("Axes(xlCategory)")->querySubObject("TickLabels")->setProperty("NumberFormat","date");
    //chart->querySubObject("Axes(xlCategory)")->setProperty("CategoryType","xlTimeScale");//distribtion temps reel

QAxObject* series = chart->querySubObject("SeriesCollection");

// Axe X
QAxObject* xValues = this->getRange(graphic.xRange);

// Axe Y
QAxObject* serie = series->querySubObject("NewSeries()");
QAxObject* yValue = this->getRange(graphic.yRange);
serie->setProperty("XValues",xValues->asVariant());
serie->setProperty("Values",yValue->asVariant());
serie->setProperty("Name",this->worksheet->querySubObject("Cells(int,int)",graphic.yRange.ay-1,graphic.yRange.ax)->dynamicCall("Value").toString());
}

This function is called several times by another one :

void ResultFileExcelSheet::setGraphics(const CommandData& command, uint heigth , uint width)
{
    QRect graph(this->getRange(RangeValue{0,0,this->size - 1,0})->property("Width").toInt() + 10 , 60, width , heigth);
    for(ResultFileExcelSheetArea* area : this->area)
    {
        for(const GraphicData& graphic : area->getGraphicsData(command))
        {
            this->addGraphic(graph, graphic);
            graph.setY(graph.y() + heigth - 40);
        }
    }
}

So, I would need your help to give me more guidance on how to create the pivot table.
Don't hesitate if you have any additional questions.

Thanks in advance!

Have a nice day!

Exaecho

JonB

@Exaecho
Hello and welcome.

This really is a question about Excel Pivot Tables or Charts or whatever. Nothing to do with Qt. You might start from https://docs.microsoft.com/en-us/office/vba/api/excel.pivottable. You would/might be better going to an "Office VBA" forum to ask how to achieve your aim.

The Qt QAxObject is a very thin wrapper around the COM object. If you have the VBA code for a task it's not too difficult to translate it into using QAxObject.

Exaecho

@JonB

Thank you for the link and your answer.

I will do those things !

Thanks again & have a nice day !