Worksheet.PivotTables Property

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

Namespace: DevExpress.Spreadsheet

Assembly: DevExpress.Spreadsheet.v19.1.Core.dll

Declaration

C#

PivotTableCollection PivotTables { get; }

VB.NET

ReadOnly Property PivotTables As PivotTableCollection

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.

   • To add a field to the row axis area, use the PivotFieldReferenceCollection.Add method of the PivotTable.RowFields collection.
   • To add a field to the column axis area, use the PivotFieldReferenceCollection.Add method of the PivotTable.ColumnFields collection.
   • To add a field to the report filter area, use the PivotPageFieldCollection.Add method of the PivotTable.PageFields collection.
   • To add a field to the data area, use the PivotDataFieldCollection.Add method of the PivotTable.DataFields collection.

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.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/winforms-spreadsheet-pivot-table-api-t298650

PivotTableActions.vb

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

PivotTableActions.cs

Worksheet sourceWorksheet = workbook.Worksheets["Data1"];
Worksheet worksheet = workbook.Worksheets.Add();
workbook.Worksheets.ActiveWorksheet = worksheet;

// Create a pivot table using the cell range "A1:D41" as the data source.
PivotTable 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"]);

Related GitHub Examples

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.

C#

winforms-spreadsheet-pivot-table-api-t298650/CS/SpreadsheetPivotTableExamples/CodeExamples/PivotCalculatedFieldActions.cs#L14

// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Create a calculated field based on data in the "Sales" field.

spreadsheet-document-api-pivot-table-examples/CS/SpreadsheetDocServerPivotAPI/CodeExamples/PivotCalculatedFieldActions.cs#L14

// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Create a calculated field based on data in the "Sales" field.

wpf-spreadsheet-pivot-table-api-t314425/CS/SpreadsheetWPFPivotTableExamples/CodeExamples/PivotCalculatedFieldActions.cs#L14

// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Create a calculated field based on data in the "Sales" field.

VB.NET

winforms-spreadsheet-pivot-table-api-t298650/VB/SpreadsheetPivotTableExamples/CodeExamples/PivotCalculatedFieldActions.vb#L11

' Access the pivot table by its name in the collection.
Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
' Create a calculated field based on data in the "Sales" field.

spreadsheet-document-api-pivot-table-examples/VB/SpreadsheetDocServerPivotAPI/CodeExamples/PivotCalculatedFieldActions.vb#L11

' Access the pivot table by its name in the collection.
Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
' Create a calculated field based on data in the "Sales" field.

wpf-spreadsheet-pivot-table-api-t314425/VB/SpreadsheetWPFPivotTableExamples/CodeExamples/PivotCalculatedFieldActions.vb#L11

' Access the pivot table by its name in the collection.
Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
' Create a calculated field based on data in the "Sales" field.