The SpreadsheetControl provides a comprehensive and straightforward Pivot Table API that enables you to create and modify pivot tables in code.
In particular, you can perform the following actions.
- Create a pivot table using a cell range as a data source or base your report on the data cache of the existing pivot table.
ImportantCurrently, 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.
- Organize the structure of a pivot table by adding and arranging its fields.
- Calculate summaries against data fields using a wide range of preset aggregate functions.
- Change source data for a pivot table.
- Refresh a pivot table to obtain the latest data from the source.
- Move a pivot table to a new location.
- Display a pivot table in a compact, outline, or tabular form.
- Format a pivot table by applying a built-in or custom style.
- Display or hide subtotals and grand totals for rows and columns.
- Insert a blank row after each group of items.
- Sort field items in descending or ascending order.
- Apply a filter to a pivot table to show or hide specific items, construct the filter expression to display only items that meet the specified condition, or filter report data based on calculated values.
- Create calculated fields and items.
- Group items in a PivotTable report in a custom way to create new subsets of data.
For details, refer to the Pivot Tables section of examples.
To learn the basics of working with pivot tables in the SpreadsheetControl, watch the following introductory video.