创建数据透视表
Contents
[
Hide
]
创建数据透视表
使用Aspose.Cells创建数据透视表
使用Aspose.Cells,可以向电子表格中添加数据透视表。 Aspose.Cells具有许多专门用于创建和控制数据透视表的特殊类。 这些类用于创建和设置PivotTable对象的属性,用作数据透视表的构建模块。
数据透视表对象为:
- PivotField:它表示数据透视表中的一个字段。
- PivotFieldCollection:它表示数据透视表中所有 PivotField 对象的集合。
- PivotTable:它表示数据透视表。
- PivotTableCollection:它表示工作表上所有数据透视表对象的集合。
创建简单的数据透视表
使用Aspose.Cells创建数据透视表,请按照以下步骤进行:
- 使用 Cell 对象的 setValue 方法向工作表单元格添加一些数据。这些数据将作为数据透视表的数据源。
- 通过调用 PivotTableCollection 类的 add 方法在工作表中添加数据透视表,封装在 Worksheet 对象中。
- 通过传递 PivotTable 索引从 PivotTableCollection 中访问 PivotTable 对象。
- 使用 PivotTable 对象封装的任何数据透视表对象(如上所述)来管理数据透视表。
在将单元格范围指定为数据源时,范围必须从左上到右下进行设置。例如,“A1:C3” 是有效的;“C3:A1” 是无效的。
下面的代码示例显示了如何根据上述基本步骤创建一个简单的数据透视表。执行代码时,会在工作表中添加一个数据透视表:
基于相应字段创建数据透视表
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 | |
// The path to the resource directory | |
String dataDir = Utils.getSharedDataDir(CreatePivotTable.class) + "PivotTables/"; | |
//Instantiating a Workbook object | |
Workbook workbook = new Workbook(); | |
//Obtaining the reference of the newly added worksheet | |
int sheetIndex = workbook.getWorksheets().add(); | |
Worksheet sheet = workbook.getWorksheets().get(sheetIndex); | |
Cells cells = sheet.getCells(); | |
//Setting the value to the cells | |
Cell cell = cells.get("A1"); | |
cell.setValue("Sport"); | |
cell = cells.get("B1"); | |
cell.setValue("Quarter"); | |
cell = cells.get("C1"); | |
cell.setValue("Sales"); | |
cell = cells.get("A2"); | |
cell.setValue("Golf"); | |
cell = cells.get("A3"); | |
cell.setValue("Golf"); | |
cell = cells.get("A4"); | |
cell.setValue("Tennis"); | |
cell = cells.get("A5"); | |
cell.setValue("Tennis"); | |
cell = cells.get("A6"); | |
cell.setValue("Tennis"); | |
cell = cells.get("A7"); | |
cell.setValue("Tennis"); | |
cell = cells.get("A8"); | |
cell.setValue("Golf"); | |
cell = cells.get("B2"); | |
cell.setValue("Qtr3"); | |
cell = cells.get("B3"); | |
cell.setValue("Qtr4"); | |
cell = cells.get("B4"); | |
cell.setValue("Qtr3"); | |
cell = cells.get("B5"); | |
cell.setValue("Qtr4"); | |
cell = cells.get("B6"); | |
cell.setValue("Qtr3"); | |
cell = cells.get("B7"); | |
cell.setValue("Qtr4"); | |
cell = cells.get("B8"); | |
cell.setValue("Qtr3"); | |
cell = cells.get("C2"); | |
cell.setValue(1500); | |
cell = cells.get("C3"); | |
cell.setValue(2000); | |
cell = cells.get("C4"); | |
cell.setValue(600); | |
cell = cells.get("C5"); | |
cell.setValue(1500); | |
cell = cells.get("C6"); | |
cell.setValue(4070); | |
cell = cells.get("C7"); | |
cell.setValue(5000); | |
cell = cells.get("C8"); | |
cell.setValue(6430); | |
PivotTableCollection pivotTables = sheet.getPivotTables(); | |
//Adding a PivotTable to the worksheet | |
int index = pivotTables.add("=A1:C8", "E3", "PivotTable2"); | |
//Accessing the instance of the newly added PivotTable | |
PivotTable pivotTable = pivotTables.get(index); | |
//Unshowing grand totals for rows. | |
pivotTable.setRowGrand(false); | |
//Dragging the first field to the row area. | |
pivotTable.addFieldToArea(PivotFieldType.ROW, 0); | |
//Dragging the second field to the column area. | |
pivotTable.addFieldToArea(PivotFieldType.COLUMN, 1); | |
//Dragging the third field to the data area. | |
pivotTable.addFieldToArea(PivotFieldType.DATA, 2); | |
//Saving the Excel file | |
workbook.save(dataDir + "CreatePivotTable_out.xls"); |