PivotTable.DataFields Property

Provides access to the collection of fields located in the data area of the PivotTable report.

Namespace: DevExpress.Spreadsheet

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

NuGet Package: DevExpress.Spreadsheet.Core

Declaration

C#

PivotDataFieldCollection DataFields { get; }

VB.NET

ReadOnly Property DataFields As PivotDataFieldCollection

Property Value

Type	Description
PivotDataFieldCollection	A PivotDataFieldCollection object that represents the collection of data fields.

Remarks

Use the DataFields property to get access to the collection that stores all data fields added to the PivotTable report. An individual data field is an object of the PivotDataField type, which can be accessed by its index or name in the collection.

To add a data field to the pivot table, use the PivotDataFieldCollection.Add method. To add a field to the data area at the specified position, use the PivotDataFieldCollection.Insert method.

To remove a data field from the PivotTable report, use the PivotDataFieldCollection.Remove or PivotDataFieldCollection.RemoveAt method. You can also use the PivotDataFieldCollection.Clear method to remove all fields from the PivotTable’s data area at once.

Example

View Example

PivotFieldActions.vb

Dim sourceWorksheet As Worksheet = workbook.Worksheets("Data1")
Dim worksheet As Worksheet = workbook.Worksheets.Add()
workbook.Worksheets.ActiveWorksheet = worksheet

' Create a pivot table.
Dim pivotTable As PivotTable = worksheet.PivotTables.Add(sourceWorksheet("A1:D41"), worksheet("B2"))

' Add the "Product" field to the row axis area.
pivotTable.RowFields.Add(pivotTable.Fields("Product"))
' Add the "Category" field to the column axis area.
pivotTable.ColumnFields.Add(pivotTable.Fields("Category"))
' Add the "Sales" field to the data area and specify the custom field name.
Dim dataField As PivotDataField = pivotTable.DataFields.Add(pivotTable.Fields("Sales"), "Sales(Sum)")
' Specify the number format for the "Sales" field.
dataField.NumberFormat = "_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* "" - ""??_);_(@_)"
' Add the "Region" field to the filter area.
pivotTable.PageFields.Add(pivotTable.Fields("Region"))

PivotFieldActions.cs

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

// Create a pivot table.
PivotTable pivotTable = worksheet.PivotTables.Add(sourceWorksheet["A1:D41"], worksheet["B2"]);

// Add the "Product" field to the row axis area.
pivotTable.RowFields.Add(pivotTable.Fields["Product"]);
// Add the "Category" field to the column axis area.
pivotTable.ColumnFields.Add(pivotTable.Fields["Category"]);
// Add the "Sales" field to the data area and specify the custom field name.
PivotDataField dataField = pivotTable.DataFields.Add(pivotTable.Fields["Sales"], "Sales(Sum)");
// Specify the number format for the "Sales" field.
dataField.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* "" - ""??_);_(@_)";
// Add the "Region" field to the filter area.
pivotTable.PageFields.Add(pivotTable.Fields["Region"]);

Related GitHub Examples

The following code snippets (auto-collected from DevExpress Examples) contain references to the DataFields 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#

wpf-spreadsheet-pivot-table-api-examples/CS/SpreadsheetWPFPivotTableExamples/CodeExamples/ValueFieldSettingsActions.cs#L41

// Access the data field by its index in the collection.
PivotDataField dataField = pivotTable.DataFields[0];
// Display the difference in product sales between the current quarter and the previous quarter.

winforms-spreadsheet-pivot-table-api/CS/SpreadsheetPivotTableExamples/CodeExamples/ValueFieldSettingsActions.cs#L41

// Access the data field by its index in the collection.
PivotDataField dataField = pivotTable.DataFields[0];
// Display the difference in product sales between the current quarter and the previous quarter.

spreadsheet-document-api-pivot-table-examples/CS/SpreadsheetDocServerPivotAPI/CodeExamples/ValueFieldSettingsActions.cs#L41

// Access the data field by its index in the collection.
PivotDataField dataField = pivotTable.DataFields[0];
// Display the difference in product sales between the current quarter and the previous quarter.

VB.NET

wpf-spreadsheet-pivot-table-api-examples/VB/SpreadsheetWPFPivotTableExamples/CodeExamples/ValueFieldSettingsActions.vb#L37

' Access the data field by its index in the collection.
Dim dataField As PivotDataField = pivotTable.DataFields(0)
' Display the difference in product sales between the current quarter and the previous quarter.

winforms-spreadsheet-pivot-table-api/VB/SpreadsheetPivotTableExamples/CodeExamples/ValueFieldSettingsActions.vb#L37

' Access the data field by its index in the collection.
Dim dataField As PivotDataField = pivotTable.DataFields(0)
' Display the difference in product sales between the current quarter and the previous quarter.

spreadsheet-document-api-pivot-table-examples/VB/SpreadsheetDocServerPivotAPI/CodeExamples/ValueFieldSettingsActions.vb#L41

' Access the data field by its index in the collection.
Dim dataField As PivotDataField = pivotTable.DataFields(0)
' Display the difference in product sales between the current quarter and the previous quarter.