.NET Framework 4.5.2+
.NET Framework 4.5.2+
.NET Standard 2.0+
Row

Worksheet.PivotTables Property

Provides access to the collection of pivot tables in a worksheet.

Namespace: DevExpress.Spreadsheet

Assembly: DevExpress.Spreadsheet.v21.2.Core.dll

Declaration

PivotTableCollection PivotTables { get; }

Property Value

Type Description
PivotTableCollection

A PivotTableCollection object specifying the worksheet’s collection of pivot tables.

Example

The following example demonstrates how to create a pivot table based on a cell range in a worksheet.

  1. To create a new pivot table, use the PivotTableCollection.Add method of the Worksheet.PivotTables collection accessed for a worksheet where the report should be located. Pass a cell range containing the source data and specify the report’s location on the destination worksheet.
  2. Fill the created pivot table with data by adding necessary fields to it. All pivot fields are stored in the PivotFieldCollection accessible from the PivotTable.Fields property. To add a field to the PivotTable report, access the required field by its name in the collection (by default, field names originate from the column labels in the source range) and move it to one of four PivotTable areas.

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 PivotFieldReferenceBase.MoveDown, PivotFieldReferenceBase.MoveUp, PivotFieldReferenceBase.MoveToBeginning or PivotFieldReferenceBase.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.

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"))

The following code snippets (auto-collected from DevExpress Examples) contain references to the PivotTables property.

Note

The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.

See Also