Formatting Pivot Table
Pivot Table Appearance
How to Create a Pivot Table explains how to create a simple pivot table. This article describes how to customize a pivot table’s appearance by setting various properties:
- Pivot table format options
- Pivot fields format options
- Data field format options
How to Set Pivot Table Format Options
The PivotTable class controls the overall pivot table and can be formatted in a number of ways.
How to Set the AutoFormat Type
Microsoft Excel offers a number of different pre-set report formats. Aspose.Cells for Node.js via C++ support these formatting options too. To access them:
- Set PivotTable.setIsAutoFormat(value) to true.
- Assign a formatting option from the PivotTableAutoFormatType enumeration.
const AsposeCells = require("aspose.cells.node"); | |
//For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
//The path to the documents directory. | |
var dataDir = RunExamples.GetDataDir("."); | |
//Load a template file | |
var workbook = new AsposeCells.Workbook(dataDir + "Book1.xls"); | |
var pivotindex = 0; | |
//Get the first worksheet | |
var worksheet = workbook.getWorksheets().get(0); | |
//Accessing the PivotTable | |
var pivotTable = worksheet.getPivotTables().get(pivotindex); | |
//Setting the PivotTable report is automatically formatted | |
pivotTable.setIsAutoFormat(true); | |
//Setting the PivotTable atuoformat type. | |
pivotTable.setAutoFormatType(AsposeCells.PivotTableAutoFormatType.Report5); | |
//Saving the Excel file | |
workbook.save(dataDir + "output.xls"); |
How to Set Format Options
The code sample below shows how to format the pivot table to show grand totals for rows and columns, and how to set the report’s field order. It also shows how to set a customer string for null values.
const AsposeCells = require("aspose.cells.node"); | |
//For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
//The path to the documents directory. | |
var dataDir = RunExamples.GetDataDir("."); | |
//Load a template file | |
var workbook = new AsposeCells.Workbook(dataDir + "Book1.xls"); | |
//Get the first worksheet | |
var worksheet = workbook.getWorksheets().get(0); | |
var pivotindex = 0; | |
//Accessing the PivotTable | |
var pivotTable = worksheet.getPivotTables().get(pivotindex); | |
//Setting the PivotTable report shows grand totals for rows. | |
pivotTable.setRowGrand(true); | |
//Setting the PivotTable report shows grand totals for columns. | |
pivotTable.setColumnGrand(true); | |
//Setting the PivotTable report displays a custom string in cells that contain null values. | |
pivotTable.setDisplayNullString(true); | |
pivotTable.setNullString("null"); | |
//Setting the PivotTable report's layout | |
pivotTable.setPageFieldOrder(AsposeCells.PrintOrderType.DownThenOver); | |
//Saving the Excel file | |
workbook.save(dataDir + "output.xls"); |
Formatting Look and Feel Manually
To formatting how the pivot table report looks manually, instead of using pre-set report formats, use the PivotTable.formatAll(style) and PivotTable.format(row, column, style) methods. Create a style object for your desired formatting, for example:
const AsposeCells = require("aspose.cells.node"); | |
//For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
//The path to the documents directory. | |
var dataDir = RunExamples.GetDataDir(".") | |
//Load a template file | |
var workbook = new AsposeCells.Workbook(dataDir + "Book1.xls"); | |
//Get the first worksheet | |
var worksheet = workbook.getWorksheets().get(0); | |
var pivot = worksheet.getPivotTables().get(0); | |
pivot.setPivotTableStyleType(AsposeCells.PivotTableStyleType.PivotTableStyleDark1); | |
var style = workbook.createStyle(); | |
style.getFont().setName("Arial Black"); | |
style.setPattern(AsposeCells.BackgroundType.Solid); | |
style.setForegroundColor(AsposeCells.Color.Yellow); | |
pivot.formatAll(style); | |
//Saving the Excel file | |
workbook.save(dataDir + "output.xls"); |
How to Set Pivot Field Format Options
The PivotField class represents a field in a pivot table and can be formatted in a number of ways. The code sample below shows how to:
- Access row fields.
- Setting subtotals.
- Setting autosort.
- Setting autoshow.
How to Set Row/Column/Page Fields Format
const AsposeCells = require("aspose.cells.node"); | |
//For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
//The path to the documents directory. | |
var dataDir = RunExamples.GetDataDir("."); | |
//Load a template file | |
var workbook = new AsposeCells.Workbook(dataDir + "Book1.xls"); | |
//Get the first worksheet | |
var worksheet = workbook.getWorksheets().get(0); | |
var pivotindex = 0; | |
//Accessing the PivotTable | |
var pivotTable = worksheet.getPivotTables().get(pivotindex); | |
//Setting the PivotTable report shows grand totals for rows. | |
pivotTable.setRowGrand(true); | |
//Accessing the row fields. | |
var pivotFields = pivotTable.getRowFields(); | |
//Accessing the first row field in the row fields. | |
var pivotField = pivotFields.get(0); | |
//Setting Subtotals. | |
pivotField.setSubtotals(AsposeCells.PivotFieldSubtotalType.Sum, true); | |
pivotField.setSubtotals(AsposeCells.PivotFieldSubtotalType.Count, true); | |
//Setting autosort options. | |
//Setting the field auto sort. | |
pivotField.setIsAutoSort(true); | |
//Setting the field auto sort ascend. | |
pivotField.setIsAscendSort(true); | |
//Setting the field auto sort using the field itself. | |
pivotField.setAutoSortField(-5); | |
//Setting autoShow options. | |
//Setting the field auto show. | |
pivotField.setIsAutoShow(true); | |
//Setting the field auto show ascend. | |
pivotField.setIsAscendShow(false); | |
//Setting the auto show using field(data field). | |
pivotField.setAutoShowField(0); | |
//Saving the Excel file | |
workbook.save(dataDir + "output.xls"); |
How to Set Data fields format
The code sample below shows how to set display formats and number format for data fields.
const AsposeCells = require("aspose.cells.node"); | |
//For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
//The path to the documents directory. | |
var dataDir = RunExamples.GetDataDir("."); | |
//Load a template file | |
var workbook = new AsposeCells.Workbook(dataDir + "Book1.xls"); | |
//Get the first worksheet | |
var worksheet = workbook.getWorksheets().get(0); | |
var pivotindex = 0; | |
//Accessing the PivotTable | |
var pivotTable = worksheet.getPivotTables().get(pivotindex); | |
//Accessing the data fields. | |
var pivotFields = pivotTable.getDataFields(); | |
//Accessing the first data field in the data fields. | |
var pivotField = pivotFields.get(0); | |
//Setting data display format | |
pivotField.showValuesAs(AsposeCells.PivotFieldDataDisplayFormat.PercentageOf, 1, AsposeCells.PivotItemPositionType.Next, 0); | |
//Setting number format | |
pivotField.setNumber(10); | |
//Saving the Excel file | |
workbook.save(dataDir + "output.xls"); |
How to Clear Pivot Fields
The PivotFieldCollection has a method named clear() that allows you to clear pivot fields. Use it when you want to clear all the pivot fields in the areas, for example, page, column, row or data. The code sample below shows how to clear all the pivot fields in a data area.
const AsposeCells = require("aspose.cells.node"); | |
//For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
//The path to the documents directory. | |
var dataDir = RunExamples.GetDataDir(".") | |
//Load a template file | |
var workbook = new AsposeCells.Workbook(dataDir + "Book1.xls"); | |
//Get the first worksheet | |
var sheet = workbook.getWorksheets().get(0); | |
//Get the pivot tables in the sheet | |
var pivotTables = sheet.getPivotTables(); | |
//Get the first PivotTable | |
var pivotTable = pivotTables.get(0); | |
//Clear all the data fields | |
pivotTable.getDataFields().clear(); | |
//Add new data field | |
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Data, "Betrag Netto FW"); | |
//Set the refresh data flag on | |
pivotTable.setRefreshDataFlag(true); | |
//Refresh and calculate the pivot table data | |
pivotTable.refreshData(); | |
pivotTable.calculateData(); | |
pivotTable.setRefreshDataFlag(false); | |
//Saving the Excel file | |
workbook.save(dataDir + "output.xls"); |