PivotTableCollection.Add(Range, Range) Method
Creates a pivot table with the default name based on data in the specified cell range and returns the newly created PivotTable object.
Namespace: DevExpress.Spreadsheet
Assembly: DevExpress.Spreadsheet.v19.1.Core.dll
Declaration
Parameters
| Name | Type | Description |
|---|---|---|
| sourceRange | Range | A Range object that specifies a cell range containing the source data for the pivot table. |
| location | Range | A Range object that specifies a cell or cell range in the upper-left corner of the PivotTable report’s destination range. |
Returns
| Type | Description |
|---|---|
| PivotTable | A PivotTable object that specifies the newly created pivot table. |
Example
The following example demonstrates how to create a pivot table based on a cell range in a worksheet.
- 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.
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
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"))
Related GitHub Examples
The following code snippets (auto-collected from DevExpress Examples) contain references to the Add(Range, Range) method.
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.