Formatting Pivot Table
Pivot Table Appearance
How to Create a Pivot Table showed how to create a simple pivot table. This article goes further and discusses how to customize a pivot table’s appearance by setting properties.
Setting Pivot Table Format Options
The PivotTable class lets you set various formatting options for a pivot table.
Setting the AutoFormat and PivotTableStyle Types
The code example that follows illustrates how to set the auto format type and the pivot table style type using the AutoFormatType and PivotTableStyleType properties.
// 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); |
Setting Format Options
The code sample that follows illustrates how to set a number of formatting options for a pivot table report, including adding grand totals for rows and columns.
// 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); |
Setting PivotFields Format Options
As well as controlling the formatting of the overall pivot table, Aspose.Cells for Java allows fine-tuned control of the formatting for row fields, column fields and page fields.
Setting Row, Column, and Page Fields Format
The code example that follows shows how to access row fields, access a particular row, set subtotals, apply automatic sorting, and using the autoShow option.
// 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); |
Setting Data Fields Format
The following lines of code illustrate how to format data fields.
// 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); |
Modify a Pivot Table’s Quick Style
The code examples that follow show how to modify the quick style applied to a pivot table.
// 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"); |
Clearing Pivot Fields
PivotFieldCollection has a method named clear() that clears pivot fields. Use it to clear pivot fields in all areas for example, page, column, row or data. The code sample below shows how to clear all the pivot fields in the data area.
// 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"); |
Consolidation Function
Applying ConsolidationFunction to Data Fields of Pivot Table
Aspose.Cells can be used to apply ConsolidationFunction to data fields (or value fields) of the pivot table. In Microsoft Excel, you can right click the value field and then select Value Field Settings… option and then select the tab Summarize Values By. From there, you can select any ConsolidationFunction of your choice like Sum, Count, Average, Max, Min, Product, Distinct Count etc.
Aspose.Cells provides ConsolidationFunction enumeration to support the following consolidation functions.
- ConsolidationFunction.AVERAGE
- ConsolidationFunction.COUNT
- ConsolidationFunction.COUNT_NUMS
- ConsolidationFunction.DISTINCT_COUNT
- ConsolidationFunction.MAX
- ConsolidationFunction.MIN
- ConsolidationFunction.PRODUCT
- ConsolidationFunction.STD_DEV
- ConsolidationFunction.STD_DEVP
- ConsolidationFunction.SUM
- ConsolidationFunction.VAR
- ConsolidationFunction.VARP
The following code applies Average consolidation function to first data field (or value field) and DistinctCount consolidation function to second data field (or value field).
// 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"); |