Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]General Information
[Collapse]WinForms Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Build an Application
 [Collapse]Controls and Libraries
  [Expand]Forms and User Controls
  [Expand]Messages, Notifications and Dialogs
  [Expand]Editors and Simple Controls
  [Expand]Ribbon, Bars and Menu
  [Expand]Application UI Manager
  [Expand]Docking Library
  [Expand]Data Grid
  [Expand]Vertical Grid
  [Expand]Pivot Grid
  [Expand]Tree List
  [Expand]Chart Control
  [Expand]Diagrams
  [Expand]Gauges
  [Expand]Map Control
  [Expand]Scheduler
  [Collapse]Spreadsheet
   [Expand]Product Information
    Product Structure
    Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Expand]Cell Basics
   [Expand]Spreadsheet Formulas
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
    Data Grouping
   [Collapse]Pivot Table Overview
     Pivot Table Structure
     Pivot Table API
   [Expand]Charting Overview
    Printing
    Events
   [Expand]Mail Merge Overview
    Protection
    Find and Replace
   [Expand]Visual Elements
    Services
   [Expand]Shortcuts
   [Expand]Examples
  [Expand]Rich Text Editor
  [Expand]Spell Checker
  [Expand]Form Layout Managers
  [Expand]Navigation Controls
  [Expand]Printing-Exporting
  [Expand]PDF Viewer
   Reporting
  [Expand]Snap
  [Expand]TreeMap Control
 [Expand]Common Features
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Expand]Reporting
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

Pivot Table API

This topic provides a brief overview of the Spreadsheet Pivot Table API used to create and modify pivot tables in code.

All pivot tables in a worksheet are stored in the PivotTableCollection object, which you can access using the Worksheet.PivotTables property. The PivotTableCollection interface provides the basic methods of working with pivot tables in code. Use the PivotTableCollection.Add method to create a pivot table based on the cell range in a source worksheet or using the data cache of the existing PivotTable report (PivotCache). For an example on how to insert a pivot table, refer to the How to: Create a Pivot Table article.

Important

Currently, the SpreadsheetControl uses only worksheet data as a data source for a pivot table. External data sources (such as ODC files, OLAP cubes, relational databases, XML files, etc.) are not supported.

An individual pivot table is represented by the PivotTable object and can be accessed by its name in the pivot table collection. You can change the source data for your pivot table or move it to a new location in a worksheet by using the PivotTable.ChangeDataSource and PivotTable.MoveTo methods, respectively.

Expanded PivotTable Structure

After you insert and position a pivot table in a worksheet, you should fill it with data by adding necessary fields (columns of the source range) to the report. All fields are stored in the PivotFieldCollection accessible from the PivotTable.Fields property. To add a field to the PivotTable report, access this field by its name in the collection (by default, the label of the corresponding column is used as the field name) and move it to the required PivotTable area:

Subsequently, you can move the desired field to another area of the pivot table to change the report layout, or you can re-order fields in a specific area using the MoveDown, MoveUp, MoveToBeginning or MoveToEnd method called for the field whose position you wish to change. To remove a field from the pivot table, use the Remove or RemoveAt method of the collection containing this field.

In addition to fields from the source data, the pivot table may also contain calculated fields, whose values are produced based on custom formulas. To add a calculated field to the PivotTable report, use the PivotCalculatedFieldCollection.Add method of the PivotTable.CalculatedFields collection.

Each non-calculated field is made up of items - unique data entries contained in the field. All field items are stored in the PivotItemCollection collection accessible using the PivotField.Items property. You can sort items within a field (PivotField.SortType, PivotField.SortItems), filter them (PivotTable.Filters), or group them to create new subsets of data (PivotField.GroupItems).

A pivot field may also contain one or more calculated items. To add a calculated item to the PivotTable field, use the PivotCalculatedItemCollection.Add method of the PivotField.CalculatedItems collection.

Expanded PivotTable Settings and Capabilities

After you create a pivot table and populate it with data, you can modify its settings to improve the readability and comprehension of your report. You can adjust the report layout, apply a built-in or custom style to the pivot table, filter field items to display only significant values, and more. To do this, use properties of the PivotTable object listed in the table below.

Property

Description

PivotTable.Layout Provides access to the layout settings of the pivot table that enable you to show the report in a compact, outline or tabular form,
display or hide subtotals and grand totals, insert a blank row after each group of items, etc.
PivotTable.Style Allows you to set the predefined or custom style to be applied to the pivot table.
PivotTable.BandedColumns
PivotTable.BandedRows
Allow you to apply the banded column/row formatting to the pivot table.
PivotTable.ShowColumnHeaders
PivotTable.ShowRowHeaders
Allow you to format column and row headers in the PivotTable report.
PivotTable.View Provides access to the display options of the pivot table.
PivotTable.Filters Allows you to specify filtering criteria to show specific values in the pivot table.
PivotTable.Behavior Allows you to apply restrictions on different PivotTable operations.

Expanded See Also

How would you rate this topic?​​​​​​​