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