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

Setting Pivot Table Format Options

The PivotTable class controls the overall pivot table and can be formatted in a number of ways.

Setting the AutoFormat Type

Microsoft Excel offers a number of different pre-set report formats. Aspose.Cells support these formatting options too. To access them:

1. Set PivotTable.IsAutoFormat to true.
2. Assign a formatting option from the PivotTableAutoFormatType enumeration.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Load a template file
Workbook workbook = new Workbook(dataDir + "Book1.xls");

int pivotindex = 0;

// Get the first worksheet
Worksheet worksheet = workbook.Worksheets[0];

// Accessing the PivotTable
PivotTable pivotTable = worksheet.PivotTables[pivotindex];

// Setting the PivotTable report is automatically formatted
pivotTable.IsAutoFormat = true;

// Setting the PivotTable atuoformat type.
pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Report5;
            
// Saving the Excel file
workbook.Save(dataDir + "output.xls");

Setting 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.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Load a template file
Workbook workbook = new Workbook(dataDir + "Book1.xls");

// Get the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
int pivotindex = 0;

// Accessing the PivotTable
PivotTable pivotTable = worksheet.PivotTables[pivotindex];

// Setting the PivotTable report shows grand totals for rows.
pivotTable.RowGrand = true;

// Setting the PivotTable report shows grand totals for columns.
pivotTable.ColumnGrand = true;

// Setting the PivotTable report displays a custom string in cells that contain null values.
pivotTable.DisplayNullString = true;
pivotTable.NullString = "null";

// Setting the PivotTable report's layout
pivotTable.PageFieldOrder = 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.Format() and PivotTable.FormatAll() methods. Create a style object for your desired formatting, for example:

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Load a template file
Workbook workbook = new Workbook(dataDir + "Book1.xls");

// Get the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
var pivot = workbook.Worksheets[0].PivotTables[0];

pivot.PivotTableStyleType = PivotTableStyleType.PivotTableStyleDark1;

Style style = workbook.CreateStyle();
style.Font.Name = "Arial Black";
style.ForegroundColor = Color.Yellow;
style.Pattern = BackgroundType.Solid;

pivot.FormatAll(style);

// Saving the Excel file
workbook.Save(dataDir + "output.xls");

Setting 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.

Setting Row/Column/Page Fields Format

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Load a template file
Workbook workbook = new Workbook(dataDir + "Book1.xls");

// Get the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
int pivotindex = 0;

// Accessing the PivotTable
PivotTable pivotTable = worksheet.PivotTables[pivotindex];

// Setting the PivotTable report shows grand totals for rows.
pivotTable.RowGrand = true;

// Accessing the row fields.
Aspose.Cells.Pivot.PivotFieldCollection pivotFields = pivotTable.RowFields;

// Accessing the first row field in the row fields.
Aspose.Cells.Pivot.PivotField pivotField = pivotFields[0];

// Setting Subtotals.
pivotField.SetSubtotals(Aspose.Cells.Pivot.PivotFieldSubtotalType.Sum, true);
pivotField.SetSubtotals(Aspose.Cells.Pivot.PivotFieldSubtotalType.Count, true);

// Setting autosort options.
// Setting the field auto sort.
pivotField.IsAutoSort = true;

// Setting the field auto sort ascend.
pivotField.IsAscendSort = true;

// Setting the field auto sort using the field itself.
pivotField.AutoSortField = -5;

// Setting autoShow options.
// Setting the field auto show.
pivotField.IsAutoShow = true;

// Setting the field auto show ascend.
pivotField.IsAscendShow = false;

// Setting the auto show using field(data field).
pivotField.AutoShowField = 0;

// Saving the Excel file
workbook.Save(dataDir + "output.xls");

Setting Data fields format

The code sample below shows how to set display formats and number format for data fields.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Load a template file
Workbook workbook = new Workbook(dataDir + "Book1.xls");

// Get the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
int pivotindex = 0;

// Accessing the PivotTable
PivotTable pivotTable = worksheet.PivotTables[pivotindex];
// Accessing the data fields.
Aspose.Cells.Pivot.PivotFieldCollection pivotFields = pivotTable.DataFields;

// Accessing the first data field in the data fields.
Aspose.Cells.Pivot.PivotField pivotField = pivotFields[0];

// Setting data display format
pivotField.ShowValuesSetting.CalculationType = PivotFieldDataDisplayFormat.PercentageOf;

// Setting the base field.
pivotField.ShowValuesSetting.BaseFieldIndex = 1;

// Setting the base item.
pivotField.ShowValuesSetting.BaseItemPositionType = Aspose.Cells.Pivot.PivotItemPositionType.Next;

// Setting number format
pivotField.Number = 10;
// Saving the Excel file
workbook.Save(dataDir + "output.xls");

Clearing 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.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Load a template file
Workbook workbook = new Workbook(dataDir + "Book1.xls");

// Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];

// Get the pivot tables in the sheet
PivotTableCollection pivotTables = sheet.PivotTables;


// Get the first PivotTable
PivotTable pivotTable = pivotTables[0];

// Clear all the data fields
pivotTable.DataFields.Clear();

// Add new data field
pivotTable.AddFieldToArea(PivotFieldType.Data, "Betrag Netto FW");

// Set the refresh data flag on
pivotTable.RefreshDataFlag = false;

// Refresh and calculate the pivot table data
pivotTable.RefreshData();
pivotTable.CalculateData();

// Saving the Excel file
workbook.Save(dataDir + "output.xls");