Outline Subtotals | Document Solutions for Excel, Java Edition

Outline Subtotals

In This Topic

Sometimes, the amount of data in a spreadsheet is so huge that it becomes difficult to analyze it. In such cases, you can apply outline to organize the sorted data into groups. The outline data can be collapsed or expanded to hide or show specific groups from viewing. You can also derive meaningful and summarized insights from outline data by applying the subtotals to the grouped values.

In DsExcel, you can apply outline subtotals to organize the sorted data into groups and display subtotals at the end of each group.

Create Outline Subtotals

The outline subtotals are created using the subtotal method of IRange interface. The method provides different parameters to group by fields, assign subtotal function, replace existing subtotals, add page breaks and place summary data.

The below sample data is used to create outline subtotals:

Java	Copy Code
IWorksheet worksheet = workbook.getWorksheets().get(0); // Defining data in the range worksheet.getRange("A1:C20") .setValue(new Object[][] { { "Item", "Units", "Unit Price" }, { "Pen Set", 62, 4.99 }, { "Binder", 29, 1.99 }, { "Pen Set", 55, 12.49 }, { "Binder", 81, 19.99 }, { "Pen Set", 42, 23.95 }, { "Pencil", 35, 4.99 }, { "Desk", 3, 275 }, { "Desk", 2, 125 }, { "Pencil", 7, 1.29 }, { "Pen Set", 16, 15.99 }, { "Pen", 76, 1.99 }, { "Binder", 28, 8.99 }, { "Binder", 57, 19.99 }, { "Pen", 64, 8.99 }, { "Pencil", 14, 1.29 }, { "Pen", 15, 19.99 }, { "Binder", 11, 4.99 }, { "Pen Set", 96, 4.99 }, { "Binder", 94, 19.99 } });

Java

Copy Code

IWorksheet worksheet = workbook.getWorksheets().get(0);

// Defining data in the range
worksheet.getRange("A1:C20")
        .setValue(new Object[][] { { "Item", "Units", "Unit Price" }, { "Pen Set", 62, 4.99 },
                { "Binder", 29, 1.99 }, { "Pen Set", 55, 12.49 }, { "Binder", 81, 19.99 },
                { "Pen Set", 42, 23.95 }, { "Pencil", 35, 4.99 }, { "Desk", 3, 275 }, { "Desk", 2, 125 },
                { "Pencil", 7, 1.29 }, { "Pen Set", 16, 15.99 }, { "Pen", 76, 1.99 }, { "Binder", 28, 8.99 },
                { "Binder", 57, 19.99 }, { "Pen", 64, 8.99 }, { "Pencil", 14, 1.29 }, { "Pen", 15, 19.99 },
                { "Binder", 11, 4.99 }, { "Pen Set", 96, 4.99 }, { "Binder", 94, 19.99 } });

Refer to the below example code to create outline subtotals.

Java	Copy Code
IWorksheet _worksheet = workbook.getWorksheets().get(0); // Sort by value, use Sort() method. _worksheet.getRange("A2:C20").sort(_worksheet.getRange("A2:A20"), null, SortOrientation.Columns); // Create groups and sub-total the grouped values using Subtotal() method _worksheet.getRange("A1:D20").subtotal(1, ConsolidationFunction.Sum, new int[] { 2, 3 }); // Save workbook workbook.save("391-CreateSubtotals.xlsx", SaveFileFormat.Xlsx);

Java

Copy Code

IWorksheet _worksheet = workbook.getWorksheets().get(0);

// Sort by value, use Sort() method.
_worksheet.getRange("A2:C20").sort(_worksheet.getRange("A2:A20"), null, SortOrientation.Columns);

// Create groups and sub-total the grouped values using Subtotal() method
_worksheet.getRange("A1:D20").subtotal(1, ConsolidationFunction.Sum, new int[] { 2, 3 });

// Save workbook
workbook.save("391-CreateSubtotals.xlsx", SaveFileFormat.Xlsx);

Remove Outline Subtotals

The outline subtotals can be removed using the removeSubtotal method of the IRange interface.

Refer to the below example code to remove outline subtotals.

Java	Copy Code
Workbook workbook = CreateSubtotals(); IWorksheet _worksheet = workbook.getWorksheets().get(0); // Remove Subtotals, pass the cell range inclusive of the subtotal/total rows _worksheet.getRange("A1:C26").removeSubtotal(); // Save workbook workbook.save("392-RemoveSubtotals.xlsx", SaveFileFormat.Xlsx);

Java

Copy Code

Workbook workbook = CreateSubtotals();
IWorksheet _worksheet = workbook.getWorksheets().get(0);

// Remove Subtotals, pass the cell range inclusive of the subtotal/total rows
_worksheet.getRange("A1:C26").removeSubtotal();

// Save workbook
workbook.save("392-RemoveSubtotals.xlsx", SaveFileFormat.Xlsx);