PivotTable Interface
Represents a PivotTable report on a worksheet.
Namespace: DevExpress.Spreadsheet
Assembly: DevExpress.Spreadsheet.v18.2.Core.dll
Declaration
Related API Members
The following members return PivotTable objects:
Remarks
All pivot tables in a worksheet are stored in the PivotTableCollection collection, returned by the Worksheet.PivotTables property. An individual PivotTable object can be accessed by its name or index in the collection using the PivotTableCollection.Item property.
To create a new PivotTable report, use the PivotTableCollection.Add method. To populate the created report with data, add necessary PivotField objects to the required PivotTable areas: the row area (represented by the PivotTable.RowFields collection), column area (represented by the PivotTable.ColumnFields collection), data area (represented by the PivotTable.DataFields collection) or report filter area (represented by the PivotTable.PageFields collection).
Your pivot table may also contain one or more calculated fields, whose values are the results of a custom formula calculation. To add a calculated field to the report, use the PivotTable.CalculatedFields property, which provides access to the PivotTable’s collection of calculated fields (PivotCalculatedFieldCollection).
After the pivot table is created, you can use various members of the PivotTable object to modify or adjust your PivotTable report. For example, you can change the source data for the pivot table (PivotTable.ChangeDataSource), move it to a new location (PivotTable.MoveTo), adjust the report layout (PivotTable.Layout), apply a built-in or custom style (PivotTable.Style) and manage style options (PivotTable.ShowColumnHeaders, PivotTable.ShowRowHeaders, PivotTable.BandedRows, PivotTable.BandedColumns).
To clear the PivotTable report by removing all its fields, use the PivotTable.Clear method. To completely delete the pivot table from the worksheet, remove it from the PivotTableCollection by using the PivotTableCollection.Remove or PivotTableCollection.RemoveAt method.
For details on how to create and manage pivot tables in code, refer to the Pivot Tables example section.
Example
Note
A complete sample project is available at https://github.com/DevExpress-Examples/winforms-spreadsheet-pivot-table-api-t298650
Dim sourceWorksheet As Worksheet = workbook.Worksheets("Data1")
Dim worksheet As Worksheet = workbook.Worksheets.Add()
workbook.Worksheets.ActiveWorksheet = worksheet
' Create a pivot table using the cell range "A1:D41" as the data source.
Dim pivotTable As PivotTable = worksheet.PivotTables.Add(sourceWorksheet("A1:D41"), worksheet("B2"))
' Add the "Category" field to the row axis area.
pivotTable.RowFields.Add(pivotTable.Fields("Category"))
' Add the "Product" field to the row axis area.
pivotTable.RowFields.Add(pivotTable.Fields("Product"))
' Add the "Sales" field to the data area.
pivotTable.DataFields.Add(pivotTable.Fields("Sales"))