You can set up conditional formats within cells that determine the formatting of the cell based on the outcome of a conditional statement. You can use a named style to specify various formatting options such as borders and colors to apply if the condition statement is valid, that is, if the operation is satisfied.

For example, you may want to change the background color of a cell based on the value of the cell. If the value is below 100 then the background color would be changed to red. The condition statement is "less than 100" and consists of a comparison operator "less than" and a condition, in this case a single constant "100". The condition can be a constant (expressed as a string) or an expression. Some condition statements have two conditions and an operator: for instance, if the cell value is between 0 and 100, then change the background color. In this case, the comparison operator is "between" and the first condition is 0 and the last condition is 100. For a complete list of operations, refer to the ComparisonOperator enumeration. For a list of the types of expressions, refer to the CalcEngine.Expression object. For more information about the possible style settings, refer to Creating and Applying a Style for Cells.

If two conditional formats are set to the same cell, the second conditional format takes effect.

The conditional formatting can be done using the ConditionalFormat class and any of these members of the SheetView class:

• GetConditionalFormats method
• SetConditionalFormat methods
• ClearConditionalFormats method

When you use the GetConditionalFormat methods, the conditions, operator, and style information are returned as a ConditionalFormat object. The first condition can be either a string or expression (FirstCondition or FirstConditionExpression.) Similarly, the last condition can be a string or expression (LastCondition or LastConditionExpression). If only one condition is set, it is in the FirstCondition and the LastCondition is null. The ComparisonOperator is the comparison operator for the conditional format. The style settings to apply to the cell when the condition statement is true are set as a NamedStyle object.

Refer to the examples below to see how to set conditional formatting for a range of cells that would result in different background colors, for instance, depending on the value in the cell.

For some cell types that allow input of multiple data types, such as general cell type, conditional formatting works whether you type in numbers or strings. For example, if you have conditional formatting set for values between various ranges such as 10 to 20 and 20 to 30, then typing 16 results in the formatting for the range 10 to 20. If you then type 16m, the cell treats this like a string and since "16m" is between strings "10" and "20", the conditional formatting still applies.

You can also use these members of the IConditionalFormatSupport interface:

• ConditionalFormatIsRowUsed method
• ConditionalFormatNextNonEmptyColumnInRow method

Use the ClearConditionalFormats method to clear only the conditional formats of a cell without affecting the other formatting or the contents of the cell.

Use the Model.DefaultSheetStyleModel.ConditionalFormatIsRowUsed method to determine whether a row in the style model contains style settings.

Use the Model.DefaultSheetStyleModel.ConditionalFormatNextNonEmptyColumnInRow method to return the index of the next non-empty column in a row in the model.Use the Model.DefaultSheetStyleModel.GetValidConditionalFormat method to return the style information for the first valid condition for the cell at the specified row and column in the model.

Using Code

Define styles and then set a conditional format for a cell.

Example

This example code sets the conditional format for a cell based on the numeric value of the data. It changes the coloring of the cell based on the value of temperature data. To see how it works, type a number in cell B2, then either change cells or go out of edit mode to see the formatting applied.

FarPoint.Win.Spread.NamedStyle styleCold = new FarPoint.Win.Spread.NamedStyle();
FarPoint.Win.Spread.NamedStyle styleCool = new FarPoint.Win.Spread.NamedStyle();
FarPoint.Win.Spread.NamedStyle styleMild = new FarPoint.Win.Spread.NamedStyle();
FarPoint.Win.Spread.NamedStyle styleWarm = new FarPoint.Win.Spread.NamedStyle();
FarPoint.Win.Spread.NamedStyle styleHot = new FarPoint.Win.Spread.NamedStyle();

styleCold.BackColor = Color.Blue;
styleCold.ForeColor = Color.White;
styleCool.BackColor = Color.Cyan;
styleMild.BackColor = Color.Lime;
styleWarm.BackColor = Color.Yellow;
styleHot.BackColor = Color.Red;

for (int col = 0; col < 6; col++)
{
  fpSpread1.ActiveSheet.SetConditionalFormat(0, col, styleCold, FarPoint.Win.Spread.ComparisonOperator.LessThanOrEqualTo, "32");
  fpSpread1.ActiveSheet.SetConditionalFormat(0, col, styleCool, FarPoint.Win.Spread.ComparisonOperator.Between, "32", "55");
  fpSpread1.ActiveSheet.SetConditionalFormat(0, col, styleMild, FarPoint.Win.Spread.ComparisonOperator.Between, "55", "75");
  fpSpread1.ActiveSheet.SetConditionalFormat(0, col, styleWarm, FarPoint.Win.Spread.ComparisonOperator.Between, "75", "85");
  fpSpread1.ActiveSheet.SetConditionalFormat(0, col, styleHot, FarPoint.Win.Spread.ComparisonOperator.GreaterThan, "85");
}

Dim styleCold As New FarPoint.Win.Spread.NamedStyle()
Dim styleCool As New FarPoint.Win.Spread.NamedStyle()
Dim styleMild As New FarPoint.Win.Spread.NamedStyle()
Dim styleWarm As New FarPoint.Win.Spread.NamedStyle()
Dim styleHot As New FarPoint.Win.Spread.NamedStyle()

styleCold.BackColor = Color.Blue
styleCold.ForeColor = Color.White
styleCool.BackColor = Color.Cyan
styleMild.BackColor = Color.Lime
styleWarm.BackColor = Color.Yellow
styleHot.BackColor = Color.Red

For col As Integer = 0 To 5
  fpSpread1.ActiveSheet.SetConditionalFormat(0, col, styleCold, FarPoint.Win.Spread.ComparisonOperator.LessThanOrEqualTo, "32")
  fpSpread1.ActiveSheet.SetConditionalFormat(0, col, styleCool, FarPoint.Win.Spread.ComparisonOperator.Between, "32", "55")
  fpSpread1.ActiveSheet.SetConditionalFormat(0, col, styleMild, FarPoint.Win.Spread.ComparisonOperator.Between, "55", "75")
  fpSpread1.ActiveSheet.SetConditionalFormat(0, col, styleWarm, FarPoint.Win.Spread.ComparisonOperator.Between, "75", "85")
  fpSpread1.ActiveSheet.SetConditionalFormat(0, col, styleHot, FarPoint.Win.Spread.ComparisonOperator.GreaterThan, "85")
Next col

Copyright © GrapeCity, inc. All rights reserved.