Pivot Table Settings

Spread.Services Documentation
Pivot Table Settings
Collapse All Expand All

Spread.Services Documentation > Developer's Guide > Manage Data in Spread Component > Use Pivot Table > 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;

### 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;

### 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;

### 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();

Copyright © GrapeCity, inc. All rights reserved.