Conditional styles allow you to dynamically change the appearance of cells based on formula-evaluated conditions. When a condition is met, the specified style is automatically applied to the target cells. This is useful for data visualization, highlighting, and alerting.

Creating a Conditional Style Rule

A conditional style rule consists of three components:

1. Formula — A formula expression that evaluates to true or false
2. Apply Range — The range of cells where the style should be applied
3. Style — The WorksheetRangeStyle to apply when the condition is true

var rule = new unvell.ReoGrid.ConditionalStyle.Rule(
    formula: "A1>100",
    applyRange: "A1:A100",
    style: new WorksheetRangeStyle
    {
        Flag = PlainStyleFlag.FillColor | PlainStyleFlag.TextColor,
        BackColor = Color.LightGreen,
        TextColor = Color.DarkGreen,
    }
);

Rule Properties

Constructor Overloads

// Using string range address
new Rule(string formula, string applyRange, WorksheetRangeStyle style,
    WorksheetRangeStyle negativeNumberStyle = null)

// Using RangePosition
new Rule(string formula, RangePosition applyRange, WorksheetRangeStyle style,
    WorksheetRangeStyle negativeNumberStyle = null)

Adding Rules to a Worksheet

Use the ConditionalStyles collection on the worksheet to add, remove, or manage rules:

var sheet = grid.CurrentWorksheet;

// Add a rule: highlight cells where value > 100
sheet.ConditionalStyles.Add(new Rule(
    formula: "A1>100",
    applyRange: "A1:A100",
    style: new WorksheetRangeStyle
    {
        Flag = PlainStyleFlag.FillColor,
        BackColor = Color.Yellow,
    }
));

Multiple Rules

You can add multiple rules. They are evaluated in order, and multiple rules can apply to the same cells:

// Rule 1: Green background for values > 80
sheet.ConditionalStyles.Add(new Rule(
    formula: "B1>80",
    applyRange: "B1:B50",
    style: new WorksheetRangeStyle
    {
        Flag = PlainStyleFlag.FillColor | PlainStyleFlag.TextColor,
        BackColor = Color.LightGreen,
        TextColor = Color.DarkGreen,
    }
));

// Rule 2: Red background for values < 20
sheet.ConditionalStyles.Add(new Rule(
    formula: "B1<20",
    applyRange: "B1:B50",
    style: new WorksheetRangeStyle
    {
        Flag = PlainStyleFlag.FillColor | PlainStyleFlag.TextColor,
        BackColor = Color.LightCoral,
        TextColor = Color.DarkRed,
    }
));

Negative Number Styles

Rules can have a separate style specifically for negative numbers:

sheet.ConditionalStyles.Add(new Rule(
    formula: "C1<>0",
    applyRange: "C1:C100",
    style: new WorksheetRangeStyle
    {
        Flag = PlainStyleFlag.TextColor,
        TextColor = Color.Black,
    },
    negativeNumberStyle: new WorksheetRangeStyle
    {
        Flag = PlainStyleFlag.TextColor,
        TextColor = Color.Red,
    }
));

Managing Rules

Remove a Rule

sheet.ConditionalStyles.Remove(rule);

Clear All Rules

sheet.ConditionalStyles.Clear();

Check Rule Count

int count = sheet.ConditionalStyles.Count;

Iterate Rules

foreach (var rule in sheet.ConditionalStyles)
{
    Console.WriteLine($"Formula: {rule.Formula}, Range: {rule.ApplyRange}");
}

Disable a Rule Temporarily

rule.Disabled = true;   // Disable
rule.Disabled = false;  // Re-enable

Re-Applying Conditional Styles

Conditional styles are evaluated automatically when cell values change. If you need to manually trigger a re-evaluation (e.g., after a bulk data update), call:

sheet.ApplyAllConditionalStyles();

Suspend and Resume Updates

For performance, you can suspend conditional style updates during bulk operations:

// Suspend updates
sheet.SuspendConditionalStyleUpdate = true;

// Perform bulk data operations
for (int i = 0; i < 10000; i++)
{
    sheet[i, 0] = i;
}

// Resume updates and re-apply
sheet.SuspendConditionalStyleUpdate = false;
sheet.ApplyAllConditionalStyles();

Checking Applied Styles

On a Cell

Each cell has a ConditionalStyles property containing the list of currently applied conditional styles:

var cell = sheet.Cells["A1"];
if (cell.ConditionalStyles != null && cell.ConditionalStyles.Count > 0)
{
    foreach (var style in cell.ConditionalStyles)
    {
        Console.WriteLine($"Applied: BackColor={style.BackColor}");
    }
}

Event: Conditional Style Cells Changed

Listen for changes to cells affected by conditional styles:

sheet.ConditionalStyleCellChanged += (s, e) =>
{
    Console.WriteLine("Conditional style cells were updated");
};

Complete Example

Here is a complete example that highlights sales data with color-coded rules:

var sheet = grid.CurrentWorksheet;

// Set up headers
sheet["A1"] = "Product";
sheet["B1"] = "Sales";

// Set up data
sheet["A2:A6"] = new object[] { "Widget A", "Widget B", "Widget C", "Widget D", "Widget E" };
sheet["B2:B6"] = new object[] { 150, 45, 88, 12, 200 };

// Rule: High performers (green)
sheet.ConditionalStyles.Add(new Rule(
    formula: "B2>=100",
    applyRange: "A2:B6",
    style: new WorksheetRangeStyle
    {
        Flag = PlainStyleFlag.FillColor | PlainStyleFlag.FontStyleBold,
        BackColor = Color.LightGreen,
        Bold = true,
    }
));

// Rule: Low performers (red)
sheet.ConditionalStyles.Add(new Rule(
    formula: "B2<50",
    applyRange: "A2:B6",
    style: new WorksheetRangeStyle
    {
        Flag = PlainStyleFlag.FillColor | PlainStyleFlag.TextColor,
        BackColor = Color.MistyRose,
        TextColor = Color.DarkRed,
    }
));

Related Topics

• Cell Style — Setting cell styles
• Data Format — Formatting cell data
• Formula — Using formulas in ReoGrid