在透视表中对透视字段进行分组
Contents
[
Hide
]
可能的使用场景
Microsoft Excel 允许您对数据透视表的数据字段进行分组。当大量数据与数据透视表的数据字段相关时,将它们分组通常很有用。Aspose.Cells 也提供了使用 PivotTable.setManualGroupField() 方法的功能。
在透视表中对透视字段进行分组
下面的示例代码加载了 示例Excel文件,并使用 PivotTable.setManualGroupField() 方法对第一个数据透视字段进行分组。然后刷新和计算数据透视表的数据,并将工作簿保存为 输出Excel文件。屏幕截图显示了示例代码对示例Excel文件的影响。如屏幕截图所示,第一个数据透视字段现在按月份和季度分组。
示例代码
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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"); |