TCondFormat / TCondFormatRule and CF12 spec classes

Unit: lxCondFormat

The BIFF8 (.xls) conditional-formatting family supporting Excel 2007+ extension rules. IXLSWorksheet keeps a collection of TCondFormat entries; each entry covers one or more cell ranges and holds an ordered list of TCondFormatRule objects, each rule being either a legacy cell-value rule or a CF12 Data Bar / Colour Scale / Icon Set rule. The four Sheet.AddCondFormat* entry points (DataBar, ColorScale2, ColorScale3, IconSet) construct rules of the correct subtype. Available since v2.34.0.

Threshold kind enumeration

type
  TXLSCfValueKind = (
    cfvNumber      = 0,
    cfvMinOfRange  = 1,
    cfvMaxOfRange  = 2,
    cfvPercent     = 3,
    cfvPercentile  = 4,
    cfvFormula     = 5,
    cfvAutoMin     = 6,  // Excel 2010+ data-bar only
    cfvAutoMax     = 7); // Excel 2010+ data-bar only

Icon-set family enumeration

type
  TXLSIconSetType = (
    icsArrows3, icsArrows3Gray, icsFlags3,
    icsTrafficLights3, icsTrafficLightsRimmed3, icsSigns3,
    icsSymbols3, icsSymbolsUncircled3,
    icsArrows4, icsArrows4Gray, icsRedToBlack4,
    icsRatings4, icsTrafficLights4,
    icsArrows5, icsArrows5Gray,
    icsRatings5, icsQuarters5);

The 17 baseline Excel 2007 icon families. The numeric stop count (3, 4, or 5) is encoded in the enum name.

Threshold value (cfvo)

type
  TXLSCfValue = class
    constructor Create(AKind: TXLSCfValueKind;
      const AValue: WideString; AColor: LongWord);
    procedure SetThemeColor(ThemeId: Word; Tint: Single); // v2.43.0+
    procedure ClearThemeColor;                            // v2.43.0+
    property Kind: TXLSCfValueKind;
    property Value: WideString;       // numeric literal or formula text
    property Color: LongWord;         // BGR RGB for ColorScale stops
    property IsThemeColor: Boolean;    // v2.43.0+ true = theme mode active
    property ThemeColorId: Word;       // v2.43.0+ theme palette index
    property ThemeColorTint: Single;   // v2.43.0+ -1.0 .. 0.0 .. +1.0
  end;

Data bar payload

type
  TXLSDataBarSpec = class
    procedure SetThemeColor(ThemeId: Word; Tint: Single); // v2.43.0+ — opt bar fill into theme mode
    procedure ClearThemeColor;                            // v2.43.0+ — revert to RGB Color
    property Min: TXLSCfValue;
    property Max: TXLSCfValue;
    property Color: LongWord;         // bar fill
    property ShowValue: Boolean;      // false = hide cell text
    property MinLength: Byte;         // 0..100 percent
    property MaxLength: Byte;         // 0..100 percent
    property IsThemeColor: Boolean;    // v2.43.0+ true = theme mode active
    property ThemeColorId: Word;       // v2.43.0+ theme palette index
    property ThemeColorTint: Single;   // v2.43.0+ -1.0 .. 0.0 .. +1.0
  end;

XLSX theme colour round-trip (v2.43.0+)

The bar-fill colour on a Data Bar rule and the per-stop colours on a Colour Scale rule can be set against a workbook theme index plus a tint value via SetThemeColor(ThemeId, Tint) instead of a frozen RGB. The XLSX writer emits <color theme="N"/> when Tint is exactly 0.0 or <color theme="N" tint="0.5"/> when non-zero, matching Excel's own "shortest form" output. The reader parses both attribute combinations and falls back to the RGB rgb= path when neither theme nor tint is present. Two modes are mutually exclusive per slot; whichever Set was called last wins. IsThemeColor reflects the active mode for inspection.

BIFF8 CF12 stores the resolved RGB colour only — theme mode round-trips on the XLSX backend exclusively in this release. iconSet rules have no <color> elements so the theme-mode addition does not affect them.

Colour scale payload

type
  TXLSColorScaleSpec = class
    constructor Create(IsThreeStop: Boolean);
    procedure SetStop(I: Integer; Kind: TXLSCfValueKind;
      const Value: WideString; Color: LongWord);
    property StopCount: Integer;      // 2 or 3
    property Stops[I: Integer]: TXLSCfValue; default;
  end;

Icon set payload

type
  TXLSIconSetSpec = class
    constructor Create(ASetType: TXLSIconSetType);
    procedure SetThreshold(I: Integer; Kind: TXLSCfValueKind;
      const Value: WideString);
    // Per-stop icon override (v2.44.0+).
    procedure SetIconOverride(I: Integer;
      OverrideSet: TXLSIconSetType; IconId: Byte);
    procedure ClearIconOverride(I: Integer);
    property SetType: TXLSIconSetType;
    property Reverse: Boolean;        // reverse the icon order
    property ShowOnly: Boolean;       // true = icon only, hide cell text
    property IconCount: Integer;      // 3, 4 or 5 (derived from SetType)
    property Thresholds[I: Integer]: TXLSCfValue;
    property HasIconOverride[I: Integer]: Boolean;        // v2.44.0+
    property IconOverrideSet[I: Integer]: TXLSIconSetType; // v2.44.0+
    property IconOverrideId[I: Integer]: Byte;             // v2.44.0+
  end;

Per-stop icon override (v2.44.0+)

Each stop in an Icon Set rule can override its display icon with any icon from any of the 17 built-in icon families, identified by an (OverrideSet, IconId) pair. XLSX emits <cfIcon iconSet="..." iconId="N"/> per overridden stop. BIFF8 CF12 continues to render the family-default icon because the BIFF8 wire format has no slot for per-stop override — this is an XLSX-only capability in the current release. HasIconOverride[i] returns True only for stops that have been explicitly opted in via SetIconOverride; the default-icon stops continue to use the family's stop-position default.

Rule

type
  TCondFormatRule = class
    property Kind: TXLSCfKind;
    property cfType: Word;            // CF-record subtype
    property Operator_: Word;         // comparison operator for cellIs
    property DataBar: TXLSDataBarSpec;       // non-nil for Data Bar rules
    property ColorScale: TXLSColorScaleSpec; // non-nil for Color Scale rules
    property IconSet: TXLSIconSetSpec;       // non-nil for Icon Set rules
    property Style: TXLSDxfStyle;        // DXF override; lazy-created (v2.35.0+)
    property DxfBlob: TXLSBlob;       // raw DXF bytes from Parse (v2.35.0+)
    property Priority: Word;          // CF12 ipriority; 0 = writer assigns (v2.45.0+)
  end;

Exactly one of DataBar / ColorScale / IconSet is non-nil per CF12 rule, matching the rule's Kind. Legacy cellIs rules (non-CF12) leave all three nil and use Operator_ + Formula1 / Formula2.

The Style property (v2.35.0+) is lazy-created on first read; the returned object is owned by the rule and freed in its destructor. Set HasXxx via the corresponding SetXxx call on Style. The BIFF8 reader keeps the raw bytes in DxfBlob in addition to decoding them into Style (v2.35.1+), so a load-edit-save round-trip reflects any post-load mutation of Style; if the user doesn't touch it, the saved file carries the same overrides as the original.

On BIFF8 CF12 Data Bar, Colour Scale, and Icon Set rules, [MS-XLS] requires the inline DXF block to be empty. Starting with v2.87.4, HotXLS follows that rule: style overrides assigned through Rule.Style are not serialised for those three CF12 kinds when saving .xls files. The Data Bar / Colour Scale / Icon Set rule configuration itself is still preserved through the CF12 kind-specific tail. XLSX conditional-formatting output is unaffected.

Container

type
  TCondFormat = class
    procedure ClearRow(row: Integer);
    procedure ClearCol(col: Integer);
    procedure ClearRange(row1, col1, row2, col2: Integer);
    procedure MoveRanges(row1, col1, row2, col2,
      drow, dcol: Integer);
    function  RuleCount: Integer;                // v2.40.0+
    function  Rule(I: Integer): TCondFormatRule;  // v2.40.0+
    property Range[i: Integer]: TCondRange; default;
    property IsEmpty: Boolean;
    property IsExt12: Boolean;       // true = emits CONDFMT12/CF12
    property IsShadowed: Boolean;    // v2.37.0+ — duplicate CONDFMT marker
    property TotalRange: TCondRange; // v2.37.0+ — merged-extent range
  end;

Cross-version shadow detection (v2.37.0+)

Excel-saved files often carry both an Excel 2003 cell-value CONDFMT and an Excel 2007+ CONDFMT12 covering the same sqref — the cell-value record is a cross-version fallback the older Excel can still render. The reader flags the older entry via IsShadowed = True after detecting an exact bounding-box match on TotalRange, so user-facing code that iterates the conditional-format collection can skip the duplicate. The writer continues to emit both record families on save for Excel 2003 ↔ Excel 2007+ compatibility.

Wire format

On BIFF8 SaveAs(xlExcel97), CF12 rules emit CONDFMT12 ($0879) + CF12 ($087A) records alongside the legacy CONDFMT ($01B0) + CF ($01B1) for cross-version compatibility with Excel 2003. The reader recognises the modern records and exposes them through the same in-memory rule model so Excel-authored .xls files with extension rules round-trip without data loss.

Example

// Data bar with custom min/max thresholds.
with Sheet.AddCondFormatDataBar('A1:A10', $00FF0000,
  cfvNumber, '0', cfvNumber, '100').DataBar do
begin
  ShowValue := True;
  MinLength := 10;
  MaxLength := 90;
end;

// 3 Arrows icon set, reversed so green points down.
with Sheet.AddCondFormatIconSet('B1:B10', icsArrows3).IconSet do
begin
  Reverse  := True;
  ShowOnly := False;
end;