How to: Apply Conditional Formatting to Data Cells

• Nov 13, 2018
• 2 minutes to read

The following sample applies format rules to Pivot Grid Control’s data cells. The FormatConditionRuleDataBar class allows you to see a cell value relative to other cells. A longer bar corresponds to a higher value, and a shorter bar corresponds to a lower value. Cells placed at the intersection of ‘Year’ column and ‘Sales Person’ row are painted with yellow gradient.

The image below shows the result.

View Example

Form1.cs

using System.Windows.Forms;
using DevExpress.XtraPivotGrid;
using DevExpress.XtraEditors;

namespace WinFormsPivotGridFormatRules
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            salesPersonTableAdapter1.Fill(nwindDataSet1.SalesPerson);

            // Creates a new FormatRule object.
            PivotGridFormatRule newRule = new PivotGridFormatRule();

            // Sets a Measure.
            newRule.Measure = fieldExtendedPrice1;

            // Creates and specifies a new Settings object.
            newRule.Settings = new FormatRuleFieldIntersectionSettings{
                Column = fieldOrderYear1, 
                Row = fieldSalesPerson1 
            };

            // Creates a new Rule object and sets its parameters.
            newRule.Rule = new FormatConditionRuleDataBar{ 
                PredefinedName = "Yellow Gradient" 
            };

            // Adds the rule to the collection.
            pivotGridControl1.FormatRules.Add(newRule);
        }
    }
}

Form1.vb

Imports System.Windows.Forms
Imports DevExpress.XtraPivotGrid
Imports DevExpress.XtraEditors

Namespace WinFormsPivotGridFormatRules
    Partial Public Class Form1
        Inherits Form

        Public Sub New()
            InitializeComponent()
            salesPersonTableAdapter1.Fill(nwindDataSet1.SalesPerson)

            ' Creates a new FormatRule object.
            Dim newRule As New PivotGridFormatRule()

            ' Sets a Measure.
            newRule.Measure = fieldExtendedPrice1

            ' Creates and specifies a new Settings object.
            newRule.Settings = New FormatRuleFieldIntersectionSettings With
            {
                .Column = fieldOrderYear1,
                .Row = fieldSalesPerson1
            }

            ' Creates a new Rule object and sets its parameters.
            newRule.Rule = New FormatConditionRuleDataBar With
            {
                .PredefinedName = "Yellow Gradient"
            }

            ' Adds the rule to the collection.
            pivotGridControl1.FormatRules.Add(newRule)
        End Sub
    End Class
End Namespace