格式化数据透视表

数据透视表外观

《如何创建透视表》(/cells/zh/java/create-pivot-table/)展示了如何创建一个简单的透视表。这篇文章进一步讨论了如何通过设置属性自定义透视表的外观。

设置数据透视表格式选项

PivotTable 类允许您为透视表设置各种格式选项。

设置自动格式和透视表样式类型

接下来的代码示例说明了如何使用 AutoFormatType 属性和 PivotTableStyleType 属性设置自动格式类型和透视表样式类型。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(SetAutoFormatandPivotTableStyleTypes.class) + "PivotTables/";
// Load a template file
Workbook workbook = new Workbook(dataDir + "PivotTable.xls");
int pivotindex = 0;
// Get the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(pivotindex);
// Accessing the PivotTable
PivotTable pivotTable = worksheet.getPivotTables().get(pivotindex);
//Setting the PivotTable report is automatically formatted for Excel 2003 formats
pivotTable.setAutoFormat(true);
//Setting the PivotTable atuoformat type.
pivotTable.setAutoFormatType(PivotTableAutoFormatType.CLASSIC);
//Setting the PivotTable's Styles for Excel 2007/2010 formats e.g XLSX.
pivotTable.setPivotTableStyleType(PivotTableStyleType.PIVOT_TABLE_STYLE_LIGHT_1);

设置格式选项

接下来的代码示例说明了如何为透视表报告设置多项格式选项,包括为行和列添加总计。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(SettingFormatOptions.class) + "PivotTables/";
// Load a template file
Workbook workbook = new Workbook(dataDir + "PivotTable.xls");
// Get the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
PivotTable pivotTable = worksheet.getPivotTables().get(0);
// Dragging the third field to the data area.
pivotTable.addFieldToArea(PivotFieldType.DATA, 2);
// Show grand totals for rows.
pivotTable.setRowGrand(true);
// Show grand totals for columns.
pivotTable.setColumnGrand(true);
// Display a custom string in cells that contain null values.
pivotTable.setDisplayNullString(true);
pivotTable.setNullString("null");
// Setting the layout
pivotTable.setPageFieldOrder(PrintOrderType.DOWN_THEN_OVER);

设置透视字段格式选项

除了控制整个数据透视表的格式外,Aspose.Cells for Java 还允许对行字段、列字段和页面字段的格式进行精细控制。

设置行、列和页面字段格式

接下来的代码示例显示了如何访问行字段,访问特定行,设置小计,应用自动排序,并使用自动显示选项。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(SetRowColumnPageFieldsFormat.class) + "PivotTables/";
// Load a template file
Workbook workbook = new Workbook(dataDir + "PivotTable.xls");
// Get the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
PivotTable pivotTable = worksheet.getPivotTables().get(0);
// Accessing the row fields.
PivotFieldCollection pivotFields = pivotTable.getRowFields();
// Accessing the first row field in the row fields.
PivotField pivotField = pivotFields.get(0);
// Setting Subtotals.
pivotField.setSubtotals(PivotFieldSubtotalType.SUM, true);
pivotField.setSubtotals(PivotFieldSubtotalType.COUNT, true);
// Setting autosort options. Setting the field auto sort.
pivotField.setAutoSort(true);
// Setting the field auto sort ascend.
pivotField.setAscendSort(true);
// Setting the field auto sort using the field itself.
pivotField.setAutoSortField(-1);
// Setting autoShow options. Setting the field auto show.
pivotField.setAutoShow(true);
// Setting the field auto show ascend.
pivotField.setAscendShow(false);
// Setting the auto show using field(data field).
pivotField.setAutoShowField(0);

设置数据字段格式

下面的代码行演示了如何格式化数据字段。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(SettingDataFieldFormat.class) + "PivotTables/";
// Load a template file
Workbook workbook = new Workbook(dataDir + "PivotTable.xls");
// Get the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
PivotTable pivotTable = worksheet.getPivotTables().get(0);
// Accessing the data fields.
PivotFieldCollection pivotFields = pivotTable.getDataFields();
// Accessing the first data field in the data fields.
PivotField pivotField = pivotFields.get(0);
// Setting data display format
pivotField.setDataDisplayFormat(PivotFieldDataDisplayFormat.PERCENTAGE_OF);
// Setting the base field.
pivotField.setBaseFieldIndex(1);
// Setting the base item.
pivotField.setBaseItemPosition(PivotItemPosition.NEXT);
// Setting number format
pivotField.setNumber(10);

修改透视表的快速样式

接下来的代码示例展示了如何修改应用于透视表的快速样式。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ModifyPivotTableQuickStyle.class) + "PivotTables/";
// Open the template file containing the pivot table.
Workbook wb = new Workbook(dataDir + "sample1.xlsx");
// Add Pivot Table style
Style style1 = wb.createStyle();
com.aspose.cells.Font font1 = style1.getFont();
font1.setColor(Color.getRed());
Style style2 = wb.createStyle();
com.aspose.cells.Font font2 = style2.getFont();
font2.setColor(Color.getBlue());
int i = wb.getWorksheets().getTableStyles().addPivotTableStyle("tt");
// Get and Set the table style for different categories
TableStyle ts = wb.getWorksheets().getTableStyles().get(i);
int index = ts.getTableStyleElements().add(TableStyleElementType.FIRST_COLUMN);
TableStyleElement e = ts.getTableStyleElements().get(index);
e.setElementStyle(style1);
index = ts.getTableStyleElements().add(TableStyleElementType.GRAND_TOTAL_ROW);
e = ts.getTableStyleElements().get(index);
e.setElementStyle(style2);
// Set Pivot Table style name
PivotTable pt = wb.getWorksheets().get(0).getPivotTables().get(0);
pt.setPivotTableStyleName("tt");
// Save the file.
wb.save(dataDir + "ModifyPivotTableQuickStyle_out.xlsx");

清除数据透视字段

PivotFieldCollection有一个名为clear()的方法用于清除数据透视表字段。在清除页面、列、行或数据等区域的数据透视表字段时使用它。 下面的代码示例显示了如何清除数据区域中的所有透视字段。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ClearPivotFields.class) + "PivotTables/";
// Load a template file
Workbook workbook = new Workbook(dataDir + "PivotTable.xls");
// Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
// Get the pivot tables in the sheet
PivotTableCollection pivotTables = sheet.getPivotTables();
// Get the first PivotTable
PivotTable pivotTable = pivotTables.get(0);
// Clear all the data fields
pivotTable.getDataFields().clear();
// Add new data field
pivotTable.addFieldToArea(PivotFieldType.DATA, "Betrag Netto FW");
// Set the refresh data flag on
pivotTable.setRefreshDataFlag(false);
// Refresh and calculate the pivot table data
pivotTable.refreshData();
pivotTable.calculateData();
// Save the Excel file
workbook.save(dataDir + "ClearPivotFields_out.xlsx");

合并功能

应用合并功能到数据字段的数据透视表

Aspose.Cells可用于应用合并功能到数据透视表的数据字段(或值字段)。在Microsoft Excel中,您可以右键单击值字段,然后选择值字段设置…选项,然后选择汇总方式选项卡。从那里,您可以选择任何您喜欢的合并功能,如求和、计数、平均值、最大值、最小值、乘积、去重计数等。

Aspose.Cells提供ConsolidationFunction枚举以支持以下合并功能。

以下代码将平均值合并功能应用于第一个数据字段(或值字段),并将去重计数合并功能应用于第二个数据字段(或值字段)。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ConsolidationFunctions.class) + "PivotTables/";
// Create workbook from source excel file
Workbook workbook = new Workbook(dataDir + "sample1.xlsx");
// Access the first worksheet of the workbook
Worksheet worksheet = workbook.getWorksheets().get(0);
// Access the first pivot table of the worksheet
PivotTable pivotTable = worksheet.getPivotTables().get(0);
// Apply Average consolidation function to first data field
pivotTable.getDataFields().get(0).setFunction(ConsolidationFunction.AVERAGE);
// Apply DistinctCount consolidation function to second data field
pivotTable.getDataFields().get(1).setFunction(ConsolidationFunction.DISTINCT_COUNT);
// Calculate the data to make changes affect
pivotTable.calculateData();
// Save the workbook
workbook.save(dataDir + "ConsolidationFunctions_out.xlsx");