Pivot Table Settings

You can modify the setting of the pivot table created in a spreadsheet by performing the following tasks:

Configure pivot table fields
Add field function
Filter pivot table
Managing pivot field level
Refresh pivot table

Configure pivot table fields

You can configure the fields of your pivot table using the properties and methods of the IPivotCaches interface and IPivotTables interface.

Refer to the following example code to configure the pivot table fields in a worksheet.

C#	Copy Code
// Configuring pivot table fields var field_Category = pivottable.PivotFields["Category"]; field_Category.Orientation = PivotFieldOrientation.RowField; var field_Product = pivottable.PivotFields["Product"]; field_Product.Orientation = PivotFieldOrientation.ColumnField; var field_Amount = pivottable.PivotFields["Amount"]; field_Amount.Orientation = PivotFieldOrientation.DataField; var field_Country = pivottable.PivotFields["Country"]; field_Country.Orientation = PivotFieldOrientation.PageField;

Copy Code

// Configuring pivot table fields
var field_Category = pivottable.PivotFields["Category"];
field_Category.Orientation = PivotFieldOrientation.RowField;

var field_Product = pivottable.PivotFields["Product"];
field_Product.Orientation = PivotFieldOrientation.ColumnField;

var field_Amount = pivottable.PivotFields["Amount"];
field_Amount.Orientation = PivotFieldOrientation.DataField;

var field_Country = pivottable.PivotFields["Country"];
field_Country.Orientation = PivotFieldOrientation.PageField;

Add field function

Refer to the following example code to add field function in a pivot table.

C#	Copy Code
//Set field amount function field_Amount.Function = ConsolidationFunction.Average;

Filter pivot table

Refer to the following example code to filter a pivot table.

C#	Copy Code
var field_product = pivottable.PivotFields[1]; field_product.Orientation = PivotFieldOrientation.RowField; var field_Amount = pivottable.PivotFields[3]; field_Amount.Orientation = PivotFieldOrientation.DataField; var field_Country = pivottable.PivotFields[5]; field_Country.Orientation = PivotFieldOrientation.PageField; //row field filter. field_product.PivotItems["Apple"].Visible = false; field_product.PivotItems["Beans"].Visible = false; field_product.PivotItems["Orange"].Visible = false; //page filter. field_Country.PivotItems["United States"].Visible = false; field_Country.PivotItems["Canada"].Visible = false;

Copy Code

var field_product = pivottable.PivotFields[1];
field_product.Orientation = PivotFieldOrientation.RowField;

var field_Amount = pivottable.PivotFields[3];
field_Amount.Orientation = PivotFieldOrientation.DataField;

var field_Country = pivottable.PivotFields[5];
field_Country.Orientation = PivotFieldOrientation.PageField;

//row field filter.
field_product.PivotItems["Apple"].Visible = false;
field_product.PivotItems["Beans"].Visible = false;
field_product.PivotItems["Orange"].Visible = false;

//page filter.
field_Country.PivotItems["United States"].Visible = false;
field_Country.PivotItems["Canada"].Visible = false;

Managing pivot field level

Refer to the following example code to manage the field level of a pivot table.

C#	Copy Code
//product in level 1. var field_product = pivottable.PivotFields["Product"]; field_product.Orientation = PivotFieldOrientation.RowField; //category in level 2. var field_category = pivottable.PivotFields["Category"]; field_category.Orientation = PivotFieldOrientation.RowField; var field_Amount = pivottable.PivotFields[3]; field_Amount.Orientation = PivotFieldOrientation.DataField; //category will in level 1 and product in level 2. field_product.Position = 1; field_category.Position = 0;

Copy Code

//product in level 1.
var field_product = pivottable.PivotFields["Product"];
field_product.Orientation = PivotFieldOrientation.RowField;

//category in level 2.
var field_category = pivottable.PivotFields["Category"];
field_category.Orientation = PivotFieldOrientation.RowField;

var field_Amount = pivottable.PivotFields[3];
field_Amount.Orientation = PivotFieldOrientation.DataField;

//category will in level 1 and product in level 2.
field_product.Position = 1;
field_category.Position = 0;

Refresh pivot table

Refer to the following example code to refresh a pivot table.

C#	Copy Code
var field_product = pivottable.PivotFields["Product"]; field_product.Orientation = PivotFieldOrientation.RowField; var field_Amount = pivottable.PivotFields[3]; field_Amount.Orientation = PivotFieldOrientation.DataField; //change pivot cache's source data. worksheet.Range["D8"].Value = 3000; //sync cache's data to pivot table. worksheet.PivotTables[0].Refresh();

Copy Code

var field_product = pivottable.PivotFields["Product"];
field_product.Orientation = PivotFieldOrientation.RowField;

var field_Amount = pivottable.PivotFields[3];
field_Amount.Orientation = PivotFieldOrientation.DataField;

//change pivot cache's source data.
worksheet.Range["D8"].Value = 3000;

//sync cache's data to pivot table.
worksheet.PivotTables[0].Refresh();