Skip to main content

DevExpress v24.2 Update — Your Feedback Matters

Our What's New in v24.2 webpage includes product-specific surveys. Your response to our survey questions will help us measure product satisfaction for features released in this major update and help us refine our plans for our next major release.

Take the survey Not interested

TdxSpreadSheetConditionalFormattingRuleTopBottomValues Class

A Top/Bottom Values conditional formatting rule.

#Declaration

Delphi
TdxSpreadSheetConditionalFormattingRuleTopBottomValues = class(
    TdxSpreadSheetConditionalFormattingRuleStyleBased
)

#Remarks

A Top/Bottom Values rule allows you to apply custom formatting to a number of cells with the highest or lowest values in target areas.

VCL Spreadsheet: A Top/Bottom Rule Example

#Main API Members

The list below outlines key members of the TdxSpreadSheetConditionalFormattingRuleTopBottomValues class. These members allow you to configure Top/Bottom Values rule settings.

#Common Rule API Members

Areas
Allows you to manage the conditional formatting rule’s target areas.
Clone
Copies the rule between different sets of target areas.
Index | StopIfTrue
Specify how the rule interacts with other conditional formatting rules applied to the same cells.

#Rule-Specific API Members

Direction
Specifies if the rule formats cells with highest or lowest values.
Style
Allows you to define the appearance of formatted cells.
Value | ValueType
Specify the absolute or relative number of formatted cells.

#General-Purpose API Members

Assign
Copies compatible settings between conditional formatting rules.
BeginUpdate | EndUpdate
Allow you to avoid excessive redraw operations during batch rule setting changes.
GetDetails
Returns the conditional formatting rule’s name displayed in the Conditional Formatting Rules Manager dialog.
LoadFromStream | SaveToStream
Allow you to store conditional formatting rule settings in a stream.
Owner
Provides access to the parent conditional formatting controller.

#Create a Top/Bottom Values Rule

To create a Top/Bottom Values conditional formatting rule, you can call one of the overloaded Add procedures of the corresponding conditional formatting controller and pass a reference to the TdxSpreadSheetConditionalFormattingRuleTopBottomValues class as the ARuleClass parameter:

TcxDataControllerConditionalFormatting.Add
Creates a new conditional formatting rule for a Data Grid, Tree List, or Vertical Grid control.
TdxSpreadSheetConditionalFormatting.Add
Creates a new conditional formatting rule in a spreadsheet document.

Alternatively, you can call the constructor of the TdxSpreadSheetConditionalFormattingRuleTopBottomValues class and pass the target conditional formatting controller as the AOwner parameter.

#Delete a Conditional Formatting Rule

To delete an individual Top/Bottom Values conditional formatting rule, do one of the following:

Alternatively, you can call the TdxSpreadSheetCustomConditionalFormatting.Clear procedure to delete all rules in a conditional formatting controller.

#Other Style-Based Conditional Formatting Rule Classes

You can also use the following style-based rules to apply spreadsheet-compatible style settings to cells that meet specific conditions:

TdxSpreadSheetConditionalFormattingRuleAboveOrBelowAverage
An Above or Below Average conditional formatting rule.
TdxSpreadSheetConditionalFormattingRuleCellIs
A Cell Is conditional formatting rule.
TdxSpreadSheetConditionalFormattingRuleDuplicateValues
A Duplicate Values conditional formatting rule.
TdxSpreadSheetConditionalFormattingRuleExpression
An Expression conditional formatting rule.
TdxSpreadSheetConditionalFormattingRuleUniqueValues
A Unique Values conditional formatting rule.

#Code Examples

#Spreadsheet: Apply Custom Formatting to Top and Bottom Values

The following code example creates two Top/Bottom Values conditional formatting rules and applies them to the top and bottom 10% of values in the selected cell range in the currently visible worksheet in a TdxSpreadSheet control:

var
  ATableView: TdxSpreadSheetTableView;
  ATopBottomRule: TdxSpreadSheetConditionalFormattingRuleTopBottomValues;
begin
  ATableView := dxSpreadSheet1.ActiveSheetAsTable;
  if ATableView.Selection.Count = 0 then Exit;
  ATableView.ConditionalFormatting.BeginUpdate;  // Initiates the following batch change
  try
    ATableView.ConditionalFormatting.Add(ATableView.Selection.Area,
      TdxSpreadSheetConditionalFormattingRuleTopBottomValues, ATopBottomRule);
    ATopBottomRule.Style.Brush.BackgroundColor := clBlue;
    ATopBottomRule.Style.Brush.ForegroundColor := clPurple;
    ATopBottomRule.Style.Brush.Style := sscfsDiagonalStrip;
    ATopBottomRule.Style.Font.Style := [fsBold];
    ATopBottomRule.Style.Font.Color := clWhite;
    ATopBottomRule.Direction := tbvdTop;
    ATopBottomRule.ValueType := tbvvtPercent;
    ATableView.ConditionalFormatting.Add(ATableView.Selection.Area,
      TdxSpreadSheetConditionalFormattingRuleTopBottomValues, ATopBottomRule);
    ATopBottomRule.Style.Brush.BackgroundColor := clGreen;
    ATopBottomRule.Style.Brush.ForegroundColor := clLime;
    ATopBottomRule.Style.Brush.Style := sscfsRevDiagonalStrip;
    ATopBottomRule.Style.Font.Color := clWhite;
    ATopBottomRule.Style.Font.Style := [fsBold];
    ATopBottomRule.Direction := tbvdBottom;
    ATopBottomRule.ValueType := tbvvtPercent;
  finally
    ATableView.ConditionalFormatting.EndUpdate;  // Calls EndUpdate regardless of the batch operation's success
  end;
end;

VCL Spreadsheet: A Top/Bottom Rule Example

#Indirect TdxSpreadSheetConditionalFormattingRuleTopBottomValues Class References

The TdxSpreadSheetCustomConditionalFormatting.Rules property references the TdxSpreadSheetConditionalFormattingRuleTopBottomValues class as a TdxSpreadSheetCustomConditionalFormattingRule object.

To access all public API members, cast the returned object to the TdxSpreadSheetConditionalFormattingRuleTopBottomValues class. You can call the rule’s ClassType function to identify the actual rule type.

See Also