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.

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

AreasAllows you to manage the conditional formatting rule’s target areas.CloneCopies the rule between different sets of target areas.Index | StopIfTrueSpecify how the rule interacts with other conditional formatting rules applied to the same cells.

Rule-Specific API Members

DirectionSpecifies if the rule formats cells with highest or lowest values.StyleAllows you to define the appearance of formatted cells.Value | ValueTypeSpecify the absolute or relative number of formatted cells.

General-Purpose API Members

AssignCopies compatible settings between conditional formatting rules.BeginUpdate | EndUpdateAllow you to avoid excessive redraw operations during batch rule setting changes.GetDetailsReturns the conditional formatting rule’s name displayed in the Conditional Formatting Rules Manager dialog.LoadFromStream | SaveToStreamAllow you to store conditional formatting rule settings in a stream.OwnerProvides 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.AddCreates a new conditional formatting rule for a Data Grid, Tree List, or Vertical Grid control.TdxSpreadSheetConditionalFormatting.AddCreates 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:

Call the TdxSpreadSheetCustomConditionalFormatting.Remove procedure and pass the rule as a parameter.
Release the rule directly in code (call the Free procedure in Delphi or use the delete keyword in C++Builder).

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:

TdxSpreadSheetConditionalFormattingRuleAboveOrBelowAverageAn Above or Below Average conditional formatting rule.TdxSpreadSheetConditionalFormattingRuleCellIsA Cell Is conditional formatting rule.TdxSpreadSheetConditionalFormattingRuleDuplicateValuesA Duplicate Values conditional formatting rule.TdxSpreadSheetConditionalFormattingRuleExpressionAn Expression conditional formatting rule.TdxSpreadSheetConditionalFormattingRuleUniqueValuesA 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:

Delphi
C++

delphi

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;

cpp

TdxSpreadSheetTableView *ATableView;
  TdxSpreadSheetConditionalFormattingRuleTopBottomValues *ATopBottomRule;
  // ...
  ATableView = dxSpreadSheet1->ActiveSheetAsTable;
  if(ATableView->Selection->Count == 0) { return; }
  ATableView->ConditionalFormatting->BeginUpdate(); // Initiates the following batch change
  try
  {
    ATableView->ConditionalFormatting->Add(ATableView->Selection->Area,
    __classid(TdxSpreadSheetConditionalFormattingRuleTopBottomValues), &ATopBottomRule);
    ATopBottomRule->Style->Brush->BackgroundColor = clBlue;
    ATopBottomRule->Style->Brush->ForegroundColor = clPurple;
    ATopBottomRule->Style->Brush->Style = sscfsRevDiagonalStrip;
    ATopBottomRule->Style->Font->Style = TFontStyles() << fsBold;
    ATopBottomRule->Style->Font->Color = clWhite;
    ATopBottomRule->Direction = tbvdTop;
    ATopBottomRule->ValueType = tbvvtPercent;
    ATableView->ConditionalFormatting->Add(ATableView->Selection->Area,
    __classid(TdxSpreadSheetConditionalFormattingRuleTopBottomValues), &ATopBottomRule);
    ATopBottomRule->Style->Brush->BackgroundColor = clGreen;
    ATopBottomRule->Style->Brush->ForegroundColor = clLime;
    ATopBottomRule->Style->Brush->Style = sscfsDiagonalStrip;
    ATopBottomRule->Style->Font->Color = clWhite;
    ATopBottomRule->Style->Font->Style = TFontStyles() << fsBold;
    ATopBottomRule->Direction = tbvdTop;
    ATopBottomRule->ValueType = tbvvtPercent;
  }
  __finally
  {
    ATableView->ConditionalFormatting->EndUpdate(); // Calls EndUpdate regardless of the batch operation's success
  }

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.

Inheritance

TObject TPersistent TInterfacedPersistent TcxInterfacedPersistent TdxSpreadSheetCustomConditionalFormattingRule TdxSpreadSheetConditionalFormattingCustomRule TdxSpreadSheetConditionalFormattingRuleStyleBased TdxSpreadSheetConditionalFormattingRuleTopBottomValues