PivotDataField.NumberFormatLocal Property

Specifies a local number format used to display summary values within the data field.

Namespace: DevExpress.Spreadsheet

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

NuGet Package: DevExpress.Spreadsheet.Core

Declaration

C#

string NumberFormatLocal { get; set; }

VB.NET

Property NumberFormatLocal As String

Property Value

Type	Description
String	The number format to apply to the data field.

Remarks

When you create a PivotTable report, numbers in the data area are formatted with the General number format. The Spreadsheet allows you to use Microsoft Excel number formats to display numeric values in a data field as currency, percentages, decimals, and so on.

Use the NumberFormatLocal property to specify a number format based on the local culture defined by the DocumentOptions.Culture property.

C#

using DevExpress.Spreadsheet;
using System.Globalization;
// ...

// Change culture for the Spreadsheet control.
spreadsheetControl1.Options.Culture = new CultureInfo("de-DE");

// Load a document.
var workbook = spreadsheetControl1.Document;
workbook.LoadDocument(@"Documents\PivotTable.xlsx", DocumentFormat.Xlsx);

var sourceWorksheet = workbook.Worksheets["Data"];
var worksheet = workbook.Worksheets.Add();
workbook.Worksheets.ActiveWorksheet = worksheet;

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

// Add the "Product" field to the row axis area.
pivotTable.RowFields.Add(pivotTable.Fields["Product"]);
// Add the "Customer" field to the column axis area.
pivotTable.ColumnFields.Add(pivotTable.Fields["Customer"]);
// Add the "Amount" field to the data area.
var dataField = pivotTable.DataFields.Add(pivotTable.Fields["Amount"]);
// Specify a number format for the data field in the German culture.
dataField.NumberFormatLocal = "#.##0,00\\ [$€-407]";

VB.NET

Imports DevExpress.Spreadsheet
Imports System.Globalization
' ...

' Change culture for the Spreadsheet control.
spreadsheetControl1.Options.Culture = New CultureInfo("de-DE")

' Load a document.
Dim workbook As IWorkbook = spreadsheetControl1.Document
workbook.LoadDocument("Documents\PivotTable.xlsx", DocumentFormat.Xlsx)

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

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

' Add the "Product" field to the row axis area.
pivotTable.RowFields.Add(pivotTable.Fields("Product"))
' Add the "Customer" field to the column axis area.
pivotTable.ColumnFields.Add(pivotTable.Fields("Customer"))
' Add the "Amount" field to the data area.
Dim dataField As PivotDataField = pivotTable.DataFields.Add(pivotTable.Fields("Amount"))
' Specify a number format for the data field in the German culture.
dataField.NumberFormatLocal = "#.##0,00\ [$€-407]"

The following image demonstrates the result:

Use the PivotDataField.NumberFormat option to obtain or specify a number format in the invariant culture.