Conditional formatting enables users to highlight cells with a certain color, depending upon the cell's value. The FlexPivotGrid control lets users apply conditional formatting to an individual cell or a range of cells to let them visualize data more clearly for analysis and comparison. The C1FlexPivotGrid class extends this functionality from the OwnerDraw feature of C1FlexGrid.

Let's say a user wants to analyze the variation in Extended Price of products on the basis of Geography, that is City and Country. The user wants to know the cities in which the Extended Price of the product is less than 100 so that he/she can make price adjustments in future. In such a case, the user can apply conditional formatting to highlight and compare Extended Price.

The image given below shows a FlexPivotGrid highlighting Extended prices greater than 100 and segregated by Product, Country and City.

Implementation

Complete the following steps for applying conditional formatting to C1FlexPivotGrid. This implementation uses the sample created in Binding FlexPivot to Data Source in Code topic.

1. Create an instance of CellStyle class, and initialize a constant field Value with 100 in code view.
   • Visual Basic
   • C#

   • Visual Basic

   Private cellValue As CellStyle
   Const Value As Integer = 100
   

   • C#

   CellStyle cellValue;
   const int Value = 100;
   

2. Add the following code in Form's constructor to create a default view that displays Extended Price in Values list, Product Name in Columns list, City and Country fields in Columns list.
   • Visual Basic
   • C#

   • Visual Basic

   Dim fp = Me.FlexPivotPage1.PivotEngine
   fp.ValueFields.Add("ExtendedPrice")
   fp.RowFields.Add("Country", "City")
   fp.ColumnFields.Add("ProductName")
   

   • C#

   var fp = this.flexPivotPage1.FlexPivotEngine;
   fp.ValueFields.Add("ExtendedPrice");
   fp.RowFields.Add("Country", "City");
   fp.ColumnFields.Add("ProductName");
   

3. Add the following code to the Form's constructor for configuring grid and styling grid cells.
   • Visual Basic
   • C#

   • Visual Basic

   ' configure grid
   Dim grid = Me.FlexPivotPage1.FlexPivotGrid
   
   ' style used to show 'big values'
   cellValue = grid.Styles.Add("cellValue")
   cellValue.BackColor = Color.LightGreen
   
   ' owner draw to apply the style
   grid.DrawMode = DrawModeEnum.OwnerDraw
   

   • C#

   // configure grid
   var grid = this.flexPivotPage1.FlexPivotGrid;
   
   // style used to show 'big values'
   cellValue = grid.Styles.Add("cellValue");
   cellValue.BackColor = Color.LightGreen;
   
   // owner draw to apply the style
   grid.DrawMode = DrawModeEnum.OwnerDraw;
   

4. Subscribe grid_OwnerDrawCell event to apply conditional formatting.
   • Visual Basic
   • C#

   • Visual Basic

   AddHandler grid.OwnerDrawCell, AddressOf grid_OwnerDrawCell
   

   • C#

   grid.OwnerDrawCell += grid_OwnerDrawCell;
   

5. Add the following code to the event handler created for grid_OwnerDrawCell event.
   • Visual Basic
   • C#

   • Visual Basic

   Private Sub grid_OwnerDrawCell(sender As Object, e As OwnerDrawCellEventArgs)
       Dim grid = TryCast(sender, C1.Win.C1FlexGrid.C1FlexGrid)
       If e.Row >= grid.Rows.Fixed AndAlso e.Col >= grid.Cols.Fixed AndAlso TypeOf grid(e.Row, e.Col) Is Double Then
           Dim value__1 = CDbl(grid(e.Row, e.Col))
           If value__1 > Value Then
               e.Style = cellValue
           End If
       End If
   End Sub
   

   • C#

   private void grid_OwnerDrawCell(object sender, OwnerDrawCellEventArgs e)
   {
       var grid = sender as C1.Win.C1FlexGrid.C1FlexGrid;
       if (e.Row >= grid.Rows.Fixed &&
           e.Col >= grid.Cols.Fixed &&
           grid[e.Row, e.Col] is double)
       {
           var value = (double)grid[e.Row, e.Col];
           if (value > Value)
           {
               e.Style = cellValue;
           }
       }
   }
   

6. Run the application to see that the extended prices greater than 100 highlighted in green color.