Group Pivot Fields in the Pivot Table

Possible Usage Scenarios

Microsoft Excel allows you to group pivot fields of the pivot table. When there is a large amount of data relates to a pivot field, it is often useful to group them into sections. Aspose.Cells also provides this feature using the PivotTable.setManualGroupField() method.

Group Pivot Fields in the Pivot Table

The following sample code loads the sample Excel file and performs grouping on the first pivot field using the PivotTable.setManualGroupField() method. It then refreshes and calculates data of the pivot table and saves the workbook as the output Excel file. The screenshot shows the effect of the sample code on the sample Excel file. As you can see in the screenshot, the first pivot field is now grouped by months and quarters.

Sample Code

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java

//Load sample workbook
Workbook wb = new Workbook("sampleGroupPivotFieldsInPivotTable.xlsx");

//Access the second worksheet
Worksheet ws = wb.getWorksheets().get(1);

//Access the pivot table
PivotTable pt = ws.getPivotTables().get(0);

//Specify the start and end date time
DateTime dtStart = new DateTime(2008, 1, 1);//1-Jan-2018
DateTime dtEnd = new DateTime(2008, 9, 5); //5-Sep-2018

//Specify the group type list, we want to group by months and quarters
int[] groupTypeList = new int[2];
groupTypeList[0] = PivotGroupByType.MONTHS;
groupTypeList[1] = PivotGroupByType.QUARTERS;

//Apply the grouping on the pivot field
PivotField field = pt.getRowFields().get(0);
field.groupBy(dtStart, dtEnd, groupTypeList, 1, true);	

//Refresh and calculate pivot table
pt.setRefreshDataFlag(true);
pt.refreshData();
pt.calculateData();
pt.setRefreshDataFlag(false);

//Save the output Excel file
wb.save("outputGroupPivotFieldsInPivotTable.xlsx");