PivotDataField.NumberFormat Property

Gets or sets a format string used to display summary values within the data field.

Namespace: DevExpress.Spreadsheet

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

Declaration

C#

string NumberFormat { get; set; }

VB.NET

Property NumberFormat As String

Property Value

Type	Description
String	A String value that specifies the number format to be applied to the data field.

Remarks

By default, when you create a PivotTable report, numbers in the data area are formatted with the General number format. To change the default number format, use the NumberFormat property. By applying a different number format, you can display numeric values in a data field as percentages, monetary values, and so on.

The following example demonstrates how to apply a number format to a data field in the PivotTable report.

Note

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

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 NumberFormat 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#L20

// Specify the number format for the data field.
dataField.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* "" - ""??_);_(@_)";
#endregion #AddCalculatedField

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

// Specify the number format for the data field.
dataField.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* "" - ""??_);_(@_)";
#endregion #AddCalculatedField

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

// Specify the number format for the data field.
dataField.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* "" - ""??_);_(@_)";
#endregion #AddCalculatedField

VB.NET

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

            ' Specify the number format for the data field.
            dataField.NumberFormat = "_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* "" - ""??_);_(@_)"
'            #End Region ' #AddCalculatedField

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

            ' Specify the number format for the data field.
            dataField.NumberFormat = "_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* "" - ""??_);_(@_)"
'            #End Region ' #AddCalculatedField

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

            ' Specify the number format for the data field.
            dataField.NumberFormat = "_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* "" - ""??_);_(@_)"
'            #End Region ' #AddCalculatedField