ComponentOne FlexPivot for WinForms
Applying Conditional Formatting
Data Blending Features > Applying Conditional Formatting

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.


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.
    Private cellValue As CellStyle
    Const Value As Integer = 100
    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.
    Dim fp = Me.c1FlexPivotPage1.FlexPivotEngine
    fp.RowFields.Add("Country", "City")
    var fp = this.c1FlexPivotPage1.FlexPivotEngine;
    fp.RowFields.Add("Country", "City");
  3. Add the following code to the Form's constructor for configuring grid and styling grid cells.
    ' configure grid
    Dim grid = Me.c1FlexPivotPage1.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
    // configure grid
    var grid = this.c1FlexPivotPage1.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.
    AddHandler grid.OwnerDrawCell, AddressOf grid_OwnerDrawCell
    grid.OwnerDrawCell += grid_OwnerDrawCell;
  5. Add the following code to the event handler created for grid_OwnerDrawCell event.
    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
    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.