格式化数据透视表

数据透视表外观

如何创建数据透视表展示了如何创建一个简单的数据透视表。本文进一步讨论了如何通过设置属性来自定义数据透视表的外观。

设置数据透视表格式选项

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

设置自动套用格式和数据透视表样式类型

下面的代码示例说明了如何使用设置自动格式类型和数据透视表样式类型自动格式类型和数据透视表样式类型特性。

// 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 还允许对行字段、列字段和页字段的格式进行微调控制。

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

下面的代码示例显示了如何访问行字段、访问特定行、设置小计、应用自动排序以及使用 autoShow 选项。

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

清除数据透视字段

数据透视字段集合有一个名为的方法[清除（）](https://reference.aspose.com/cells/java/com.aspose.cells/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");

整合功能

将 ConsolidationFunction 应用于数据透视表的数据字段

Aspose.Cells 可用于将 ConsolidationFunction 应用于数据透视表的数据字段（或值字段）。在Microsoft Excel中，您可以右键单击值字段然后选择值字段设置…选项，然后选择选项卡值汇总依据.从那里，您可以选择您选择的任何 ConsolidationFunction，例如 Sum、Count、Average、Max、Min、Product、Distinct Count 等。

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

• 合并函数.AVERAGE
• 合并函数.COUNT
• 合并函数.COUNT_NUMS
• 合并函数.DISTINCT_COUNT
• 合并函数.MAX
• 合并函数.MIN
• 合并函数.PRODUCT
• 合并函数.STD_DEV
• 合并函数.STD_DEVP
• 合并函数.SUM
• 合并函数.VAR
• 合并函数

以下代码适用平均的合并功能到第一个数据字段（或值字段）和非重复计数合并功能到第二个数据字段（或值字段）。

Microsoft 仅 Excel 2013 支持 DistinctCount 合并函数。

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