Consolidation Function with C++

Consolidation function

Aspose.Cells can be used to apply ConsolidationFunction to data fields (or value fields) of the pivot table. In Microsoft Excel, you can right-click the value field and then select Value Field Settings… option and then select the tab Summarize Values By. From there, you can select any ConsolidationFunction of your choice like Sum, Count, Average, Max, Min, Product, Distinct Count, etc.

Aspose.Cells provides ConsolidationFunction enumeration to support the following consolidation functions.

  • ConsolidationFunction::Average
  • ConsolidationFunction::Count
  • ConsolidationFunction::CountNums
  • ConsolidationFunction::DistinctCount
  • ConsolidationFunction::Max
  • ConsolidationFunction::Min
  • ConsolidationFunction::Product
  • ConsolidationFunction::StdDev
  • ConsolidationFunction::StdDevp
  • ConsolidationFunction::Sum
  • ConsolidationFunction::Var
  • ConsolidationFunction::Varp

Applying ConsolidationFunction to Data Fields of Pivot Table

The following code applies Average consolidation function to the first data field (or value field) and DistinctCount consolidation function to the second data field (or value field).

#include <iostream>
#include "Aspose.Cells.h"

using namespace Aspose::Cells;

int main()
{
    Aspose::Cells::Startup();

    // Source directory path
    U16String srcDir(u"..\\Data\\01_SourceDirectory\\");

    // Output directory path
    U16String outDir(u"..\\Data\\02_OutputDirectory\\");

    // Path of input excel file
    U16String inputFilePath = srcDir + u"Book.xlsx";

    // Path of output excel file
    U16String outputFilePath = outDir + u"output.xlsx";

    // Create workbook from source excel file
    Workbook workbook(inputFilePath);

    // Access the first worksheet of the workbook
    Worksheet worksheet = workbook.GetWorksheets().Get(0);

    // Access the first pivot table of the worksheet
    PivotTable pivotTable = worksheet.GetPivotTables().Get(0);

    // Apply Average consolidation function to first data field
    pivotTable.GetDataFields().Get(0).SetFunction(ConsolidationFunction::Average);

    // Apply DistinctCount consolidation function to second data field
    pivotTable.GetDataFields().Get(1).SetFunction(ConsolidationFunction::DistinctCount);

    // Calculate the data to make changes affect
    pivotTable.CalculateData();

    // Saving the Excel file
    workbook.Save(outputFilePath);

    std::cout << "Pivot table updated and saved successfully!" << std::endl;

    Aspose::Cells::Cleanup();
}
Formatting Pivot Table with C++ Pivot Table and Source Data with C++