Applying Subtotal and Changing Direction of Outline Summary Rows below Detail with C++

This article will explain how to apply Subtotal to data and change the direction of outline summary rows below detail.

You can apply Subtotal to data using the Worksheet.Cells.Subtotal() method. It takes the following parameters:

• CellArea – The range to apply subtotal on
• GroupBy – The field to group by, as a zero-based integer offset
• Function – The subtotal function.
• TotalList – An array of zero-based field offsets, indicating the fields to which the subtotals are added.
• Replace – Indicates whether to replace the current subtotals
• PageBreaks – Indicates whether to add page break between groups
• SummaryBelowData – Indicates whether to add summary below data.

Also, you can control the direction of outline summary rows below detail as shown in the following screenshot using the Worksheet.Outline.SummaryRowBelow property. You can open this setting in Microsoft Excel using Data > Outline > Settings.

Images of source and output files

The following screenshot shows the source Excel file used in the sample code below, which contains some data in columns A and B.

The following screenshot shows the output Excel file generated by the sample code. As you can see, subtotal has been applied to range A2:B11 and the direction of the outline is summary rows below detail.

C++ code to apply subtotal and change the direction of outline summary rows

Here is the sample code to achieve the output as shown above.

#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\\");

    // Create workbook from source Excel file
    Workbook workbook(srcDir + u"Book1.xlsx");

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

    // Get the Cells collection in the first worksheet
    Cells cells = worksheet.GetCells();

    // Create a CellArea, i.e., A2:B11
    CellArea ca = CellArea::CreateCellArea(u"A2", u"B11");

    // Apply subtotal; the consolidation function is Sum and it will be applied to the second column (B) in the list
    cells.Subtotal(ca, 0, ConsolidationFunction::Sum, { 1 }, true, false, true);

    // Set the direction of outline summary
    worksheet.GetOutline().SetSummaryRowBelow(true);

    // Save the Excel file
    workbook.Save(outDir + u"output_out.xlsx");

    std::cout << "Subtotal applied successfully!" << std::endl;

    Aspose::Cells::Cleanup();
}