Applying Subtotal and Changing Direction of Outline Summary Rows below Detail

Example

Screenshots comparing 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.

todo:image_alt_text

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.

todo:image_alt_text

Java code to apply subtotal and change direction of outline summary rows below detail

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

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
//directories
String sourceDir = Utils.Get_SourceDirectory();
String outputDir = Utils.Get_OutputDirectory();
// Create workbook from source Excel file
Workbook workbook = new Workbook(sourceDir + "SampleSubtotal.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("A2", "B11");
// Apply subtotal, the consolidation function is Sum and it will applied to
// Second column (B) in the list
cells.subtotal(ca, 0, ConsolidationFunction.SUM, new int[] { 1 }, true, false, true);
// Set the direction of outline summary
worksheet.getOutline().setSummaryRowBelow(true);
// Save the excel file
workbook.save(outputDir + "ASubtotal_out.xlsx");