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 related to a pivot field, it is often useful to group them into sections. Aspose.Cells for Node.js via C++ also provides this feature using the PivotTable.groupBy() method.

How to 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.groupBy() method. It then refreshes and calculates data of the pivot table and saves the workbook as 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

const AsposeCells = require("aspose.cells.node");

//For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
//Load sample workbook
var wb = new AsposeCells.Workbook("sampleGroupPivotFieldsInPivotTable.xlsx");
//Access the second worksheet
var ws = wb.getWorksheets().get(1);
//Access the pivot table
var pt = ws.getPivotTables().get(0);
//Specify the start and end date time
var dtStart = new Date(2008, 1, 1);
var dtEnd = new Date(2008, 9, 5);
//Specify the group type list, we want to group by months and quarters
var groupTypeList = [AsposeCells.PivotGroupByType.Months, AsposeCells.PivotGroupByType.Quarters];

//Apply the grouping on first pivot field
var 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");