windowsforms-devexpress-dot-xtrapivotgrid-dot-pivotgridcontrol-3a571eef.md
Allows you to customize field value cells.
Namespace : DevExpress.XtraPivotGrid
Assembly : DevExpress.XtraPivotGrid.v25.2.dll
NuGet Package : DevExpress.Win.PivotGrid
public event PivotCustomFieldValueCellsEventHandler CustomFieldValueCells
Public Event CustomFieldValueCells As PivotCustomFieldValueCellsEventHandler
The CustomFieldValueCells event's data class is PivotCustomFieldValueCellsEventArgs. The following properties provide information specific to this event:
| Property | Description |
|---|---|
| ColumnCount | Gets the number of columns in the pivot grid. Inherited from PivotCustomFieldValueCellsEventArgsBase. |
| IsUpdateRequired | Gets whether the area where the field value cells reside needs to be redrawn after the event is handled. Inherited from PivotCustomFieldValueCellsEventArgsBase. |
| RowCount | Gets the number of rows in the pivot grid. Inherited from PivotCustomFieldValueCellsEventArgsBase. |
The event data class exposes the following methods:
The CustomFieldValueCells event occurs when the layout of the Pivot Grid Control is changed, allowing you to customize column and row headers: field value cells, data field, total and grand total headers.
Use the event parameter’s PivotCustomFieldValueCellsEventArgsBase<T1, T2>.GetCell method, to obtain data related to an individual cell, by its index. This method returns a FieldValueCell object, which provides the data. Use the PivotCustomFieldValueCellsEventArgsBase.GetCellCount method to obtain the total number of field value cells. Column/row headers can also be identified by their column/row. Use the PivotCustomFieldValueCellsEventArgsBase<T1, T2>.FindCell method to obtain the header whose column/row matches a specific condition.
The CustomFieldValueCells event allows you to specify the location of grand total headers using the PivotCustomFieldValueCellsEventArgsBase.SetGrandTotalLocation method. To obtain the current location of grand total headers, use the PivotCustomFieldValueCellsEventArgsBase.GetGrandTotalLocation method.
When handling the CustomFieldValueCells event, you can also remove individual cells with their nested columns and rows via the PivotCustomFieldValueCellsEventArgsBase.Remove method.
The PivotCustomFieldValueCellsEventArgsBase<T1, T2>.Split method allows you to split field value cells that have more than one nested cell. This method splits cells that match the specified condition (or, optionally, only the first matching cell) in a custom manner defined by the FieldValueSplitData objects.
Note
Custom values provided via the PivotGridControl.CustomCellValue, PivotGridControl.CustomSummary and PivotGridControl.CustomCellDisplayText events are not available when handling the CustomFieldValueCells event, because it is raised prior to these events.
The following example demonstrates how to split field value cells. In this example, the Grand Total column header is split into two cells: Price and Count.
Handle the CustomFieldValueCells event and call the event parameter’s Split method. Cells that should be split are identified by a predicate that returns true for those cells. The quantity, size, and captions of newly created cells are specified by an array of cell definitions (the FieldValueSplitData objects).
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Windows.Forms;
using DevExpress.XtraPivotGrid;
using DevExpress.XtraPivotGrid.Data;
namespace XtraPivotGrid_SplittingCells {
public partial class Form1 : Form {
public Form1() {
InitializeComponent();
pivotGridControl1.CustomFieldValueCells +=
new PivotCustomFieldValueCellsEventHandler(pivotGrid_CustomFieldValueCells);
}
void Form1_Load(object sender, EventArgs e) {
PivotHelper.FillPivot(pivotGridControl1);
pivotGridControl1.DataSource = PivotHelper.GetDataTable();
pivotGridControl1.BestFit();
}
protected void pivotGrid_CustomFieldValueCells(object sender,
PivotCustomFieldValueCellsEventArgs e) {
PivotGridControl pivot = sender as PivotGridControl;
if (pivot.DataSource == null) return;
if (radioGroup1.SelectedIndex == 0) return;
// Creates a predicate that returns true for the Grand Total headers,
// and false for any other column/row header.
// Only cells that match this predicate are split.
Predicate<FieldValueCell> condition =
new Predicate<FieldValueCell>(delegate(FieldValueCell matchCell) {
return matchCell.ValueType == PivotGridValueType.GrandTotal &&
matchCell.Field == null;
});
// Creates a list of cell definitions that represent newly created cells.
// Two definitions are added to the list. The first one identifies the Price cell,
// which has two nested cells (the Retail Price and Wholesale Price
// data field headers). The second one identifies the Count cell with
// one nested cell (the Quantity data field header).
List<FieldValueSplitData> cells = new List<FieldValueSplitData>(2);
cells.Add(new FieldValueSplitData("Price", 2));
cells.Add(new FieldValueSplitData("Count", 1));
// Performs splitting.
e.Split(true, condition, cells);
}
void pivotGridControl1_FieldValueDisplayText(object sender, PivotFieldDisplayTextEventArgs e) {
PivotGridControl pivot = sender as PivotGridControl;
if (e.Field == pivot.Fields[PivotHelper.Month])
{
e.DisplayText = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName((int)e.Value);
}
}
void radioGroup1_SelectedIndexChanged(object sender, EventArgs e) {
this.pivotGridControl1.LayoutChanged();
}
}
}
Imports System
Imports System.Collections.Generic
Imports System.Globalization
Imports System.Windows.Forms
Imports DevExpress.XtraPivotGrid
Imports DevExpress.XtraPivotGrid.Data
Namespace XtraPivotGrid_SplittingCells
Partial Public Class Form1
Inherits Form
Public Sub New()
InitializeComponent()
AddHandler pivotGridControl1.CustomFieldValueCells, AddressOf pivotGrid_CustomFieldValueCells
End Sub
Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
PivotHelper.FillPivot(pivotGridControl1)
pivotGridControl1.DataSource = PivotHelper.GetDataTable()
pivotGridControl1.BestFit()
End Sub
Protected Sub pivotGrid_CustomFieldValueCells(ByVal sender As Object,
ByVal e As PivotCustomFieldValueCellsEventArgs)
If pivotGridControl1.DataSource Is Nothing Then
Return
End If
If radioGroup1.SelectedIndex = 0 Then
Return
End If
' Creates a predicate that returns true for the Grand Total headers,
' and false for any other column/row header.
' Only cells that match this predicate are split.
Dim condition As New Predicate(Of FieldValueCell)(Function(matchCell As FieldValueCell) matchCell.ValueType =
PivotGridValueType.GrandTotal AndAlso matchCell.Field Is Nothing)
' Creates a list of cell definitions that represent newly created cells.
' Two definitions are added to the list. The first one identifies the Price cell,
' which has two nested cells (the Retail Price and Wholesale Price
' data field headers). The second one identifies the Count cell with
' one nested cell (the Quantity data field header).
Dim cells As New List(Of FieldValueSplitData)(2)
cells.Add(New FieldValueSplitData("Price", 2))
cells.Add(New FieldValueSplitData("Count", 1))
' Performs splitting.
e.Split(True, condition, cells)
End Sub
Private Sub pivotGridControl1_FieldValueDisplayText(ByVal sender As Object,
ByVal e As PivotFieldDisplayTextEventArgs) Handles pivotGridControl1.FieldValueDisplayText
Dim pivot As PivotGridControl = TryCast(sender, PivotGridControl)
If e.Field Is pivot.Fields(PivotHelper.Month) Then
e.DisplayText = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(CInt((e.Value)))
End If
End Sub
Private Sub radioGroup1_SelectedIndexChanged(ByVal sender As Object,
ByVal e As EventArgs) Handles radioGroup1.SelectedIndexChanged
Me.pivotGridControl1.LayoutChanged()
End Sub
End Class
End Namespace
The following example demonstrates how handle the CustomFieldValueCells event to hide specific rows and columns. In this example, the event handler iterates through all row headers and removes rows that correspond to the “Employee B” field value, and that are not Total Rows.
View Example: How to Hide Specific Rows and Columns
using System;
using System.Globalization;
using System.Windows.Forms;
using DevExpress.XtraPivotGrid;
namespace XtraPivotGrid_HidingColumnsAndRows {
public partial class Form1 : Form {
public Form1() {
InitializeComponent();
pivotGridControl1.CustomFieldValueCells +=
new PivotCustomFieldValueCellsEventHandler(pivotGrid_CustomFieldValueCells);
}
void Form1_Load(object sender, EventArgs e) {
PivotHelper.FillPivot(pivotGridControl1);
pivotGridControl1.DataSource = PivotHelper.GetDataTable();
pivotGridControl1.BestFit();
}
// Handles the CustomFieldValueCells event to remove
// specific rows.
protected void pivotGrid_CustomFieldValueCells(object sender,
PivotCustomFieldValueCellsEventArgs e) {
PivotGridControl pivot = sender as PivotGridControl;
if (pivot.DataSource == null) return;
if (radioGroup1.SelectedIndex == 0) return;
// Iterates through all row headers.
for (int i = e.GetCellCount(false) - 1; i >= 0; i--) {
FieldValueCell cell = e.GetCell(false, i);
if (cell == null) continue;
// If the current header corresponds to the "Employee B"
// field value, and is not the Total Row header,
// it is removed with all corresponding rows.
if (object.Equals(cell.Value, "Employee B") &&
cell.ValueType != PivotGridValueType.Total)
e.Remove(cell);
}
}
void pivotGridControl1_FieldValueDisplayText(object sender,
PivotFieldDisplayTextEventArgs e) {
PivotGridControl pivot = sender as PivotGridControl;
if (e.Field == pivot.Fields[PivotHelper.Month]) {
e.DisplayText = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName((int)e.Value);
}
}
void radioGroup1_SelectedIndexChanged(object sender, EventArgs e) {
this.pivotGridControl1.LayoutChanged();
}
}
}
Imports System
Imports System.Globalization
Imports System.Windows.Forms
Imports DevExpress.XtraPivotGrid
Namespace XtraPivotGrid_HidingColumnsAndRows
Partial Public Class Form1
Inherits Form
Public Sub New()
InitializeComponent()
AddHandler pivotGridControl1.CustomFieldValueCells, AddressOf pivotGrid_CustomFieldValueCells
End Sub
Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
PivotHelper.FillPivot(pivotGridControl1)
pivotGridControl1.DataSource = PivotHelper.GetDataTable()
pivotGridControl1.BestFit()
End Sub
' Handles the CustomFieldValueCells event to remove
' specific rows.
Protected Sub pivotGrid_CustomFieldValueCells(ByVal sender As Object,
ByVal e As PivotCustomFieldValueCellsEventArgs)
Dim pivot As PivotGridControl = TryCast(sender, PivotGridControl)
If pivot.DataSource Is Nothing Then
Return
End If
If radioGroup1.SelectedIndex = 0 Then
Return
End If
' Iterates through all row headers.
For i As Integer = e.GetCellCount(False) - 1 To 0 Step -1
Dim cell As FieldValueCell = e.GetCell(False, i)
If cell Is Nothing Then
Continue For
End If
' If the current header corresponds to the "Employee B"
' field value, and is not the Total Row header,
' it is removed with all corresponding rows.
If Object.Equals(cell.Value, "Employee B") AndAlso cell.ValueType <> PivotGridValueType.Total Then
e.Remove(cell)
End If
Next i
End Sub
Private Sub pivotGridControl1_FieldValueDisplayText(ByVal sender As Object,
ByVal e As PivotFieldDisplayTextEventArgs) Handles pivotGridControl1.FieldValueDisplayText
Dim pivot As PivotGridControl = TryCast(sender, PivotGridControl)
If e.Field Is pivot.Fields(PivotHelper.Month) Then
e.DisplayText = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(CInt((e.Value)))
End If
End Sub
Private Sub radioGroup1_SelectedIndexChanged(ByVal sender As Object,
ByVal e As EventArgs) Handles radioGroup1.SelectedIndexChanged
Me.pivotGridControl1.LayoutChanged()
pivotGridControl1.BestFit()
End Sub
End Class
End Namespace
The example below shows how to handle the CustomFieldValueCells event to hide empty columns and rows.
View Example: How to Hide Empty Field Values (Columns/Rows)
using System;
using System.Globalization;
using System.Windows.Forms;
using DevExpress.XtraPivotGrid;
namespace XtraPivotGrid_HidingColumnsAndRows {
public partial class Form1 : Form {
public Form1() {
InitializeComponent();
pivotGridControl1.CustomFieldValueCells +=
new PivotCustomFieldValueCellsEventHandler(pivotGrid_CustomFieldValueCells);
}
void Form1_Load(object sender, EventArgs e) {
PivotHelper.FillPivot(pivotGridControl1);
pivotGridControl1.DataSource = PivotHelper.GetDataTable();
pivotGridControl1.BestFit();
}
protected void pivotGrid_CustomFieldValueCells(object sender, PivotCustomFieldValueCellsEventArgs e) {
if (radioGroup1.SelectedIndex == 0) return;
HideEmptyValues(true, e);
HideEmptyValues(false, e);
}
private void HideEmptyValues(bool isColumn, PivotCustomFieldValueCellsEventArgs e) {
for (int i = e.GetCellCount(isColumn) - 1; i >= 0; i--) {
FieldValueCell cell = e.GetCell(isColumn, i);
if (cell == null) continue;
if (cell.EndLevel == e.GetLevelCount(isColumn) - 1) {
if (IsValueEmpty(isColumn, cell.MaxIndex, e)) {
e.Remove(cell);
}
}
}
}
private bool IsValueEmpty( bool isColumn, int valueIndex, PivotCustomFieldValueCellsEventArgs e) {
if (isColumn)
return IsCollumnEmpty(valueIndex, e);
else
return IsRowEmpty(valueIndex, e);
}
private bool IsRowEmpty(int rowIndex, PivotCustomFieldValueCellsEventArgs e) {
for (int j = 0; j < e.ColumnCount ; j++) {
decimal value = Convert.ToDecimal(e.GetCellValue(j, rowIndex));
if (value != 0)
return false;
}
return true;
}
private bool IsCollumnEmpty(int columnIndex, PivotCustomFieldValueCellsEventArgs e) {
for (int j = 0; j < e.RowCount; j++) {
decimal value = Convert.ToDecimal(e.GetCellValue(columnIndex, j));
if (value != 0)
return false;
}
return true;
}
void pivotGridControl1_FieldValueDisplayText(object sender, PivotFieldDisplayTextEventArgs e) {
if (object.ReferenceEquals(e.Field, pivotGridControl1.Fields[PivotHelper.Month])) {
e.DisplayText = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName((int)e.Value);
}
}
void radioGroup1_SelectedIndexChanged(object sender, EventArgs e) {
this.pivotGridControl1.LayoutChanged();
pivotGridControl1.BestFit();
}
}
}
Imports System
Imports System.Globalization
Imports System.Windows.Forms
Imports DevExpress.XtraPivotGrid
Namespace XtraPivotGrid_HidingColumnsAndRows
Public Partial Class Form1
Inherits Form
Public Sub New()
InitializeComponent()
AddHandler pivotGridControl1.CustomFieldValueCells, New PivotCustomFieldValueCellsEventHandler(AddressOf pivotGrid_CustomFieldValueCells)
End Sub
Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs)
FillPivot(pivotGridControl1)
pivotGridControl1.DataSource = GetDataTable()
pivotGridControl1.BestFit()
End Sub
Protected Sub pivotGrid_CustomFieldValueCells(ByVal sender As Object, ByVal e As PivotCustomFieldValueCellsEventArgs)
If radioGroup1.SelectedIndex = 0 Then Return
HideEmptyValues(True, e)
HideEmptyValues(False, e)
End Sub
Private Sub HideEmptyValues(ByVal isColumn As Boolean, ByVal e As PivotCustomFieldValueCellsEventArgs)
For i As Integer = e.GetCellCount(isColumn) - 1 To 0 Step -1
Dim cell As FieldValueCell = e.GetCell(isColumn, i)
If cell Is Nothing Then Continue For
If cell.EndLevel = e.GetLevelCount(isColumn) - 1 Then
If IsValueEmpty(isColumn, cell.MaxIndex, e) Then
e.Remove(cell)
End If
End If
Next
End Sub
Private Function IsValueEmpty(ByVal isColumn As Boolean, ByVal valueIndex As Integer, ByVal e As PivotCustomFieldValueCellsEventArgs) As Boolean
If isColumn Then
Return IsCollumnEmpty(valueIndex, e)
Else
Return IsRowEmpty(valueIndex, e)
End If
End Function
Private Function IsRowEmpty(ByVal rowIndex As Integer, ByVal e As PivotCustomFieldValueCellsEventArgs) As Boolean
For j As Integer = 0 To e.ColumnCount - 1
Dim value As Decimal = Convert.ToDecimal(e.GetCellValue(j, rowIndex))
If value <> 0 Then Return False
Next
Return True
End Function
Private Function IsCollumnEmpty(ByVal columnIndex As Integer, ByVal e As PivotCustomFieldValueCellsEventArgs) As Boolean
For j As Integer = 0 To e.RowCount - 1
Dim value As Decimal = Convert.ToDecimal(e.GetCellValue(columnIndex, j))
If value <> 0 Then Return False
Next
Return True
End Function
Private Sub pivotGridControl1_FieldValueDisplayText(ByVal sender As Object, ByVal e As PivotFieldDisplayTextEventArgs)
If ReferenceEquals(e.Field, pivotGridControl1.Fields(Month)) Then
e.DisplayText = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(CInt(e.Value))
End If
End Sub
Private Sub radioGroup1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
pivotGridControl1.LayoutChanged()
pivotGridControl1.BestFit()
End Sub
End Class
End Namespace
The following code snippets (auto-collected from DevExpress Examples) contain references to the CustomFieldValueCells event.
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.
winforms-pivot-grid-hide-specific-columns-and-rows/CS/Form1.cs#L10
InitializeComponent();
pivotGridControl1.CustomFieldValueCells +=
new PivotCustomFieldValueCellsEventHandler(pivotGrid_CustomFieldValueCells);
winforms-pivot-split-field-value-cells/CS/Form1.cs#L12
InitializeComponent();
pivotGridControl1.CustomFieldValueCells +=
new PivotCustomFieldValueCellsEventHandler(pivotGrid_CustomFieldValueCells);
winforms-pivot-grid-hide-empty-columns-and-rows/CS/Form1.cs#L10
InitializeComponent();
pivotGridControl1.CustomFieldValueCells +=
new PivotCustomFieldValueCellsEventHandler(pivotGrid_CustomFieldValueCells);
winforms-pivot-grid-hide-specific-columns-and-rows/VB/Form1.vb#L12
InitializeComponent()
AddHandler pivotGridControl1.CustomFieldValueCells, AddressOf pivotGrid_CustomFieldValueCells
End Sub
winforms-pivot-split-field-value-cells/VB/Form1.vb#L14
InitializeComponent()
AddHandler pivotGridControl1.CustomFieldValueCells, AddressOf pivotGrid_CustomFieldValueCells
End Sub
winforms-pivot-grid-hide-empty-columns-and-rows/VB/Form1.vb#L13
InitializeComponent()
AddHandler pivotGridControl1.CustomFieldValueCells, New PivotCustomFieldValueCellsEventHandler(AddressOf pivotGrid_CustomFieldValueCells)
End Sub
See Also