HotXLS-dokumentaatio Etusivu Sisältö Yleiskatsaus IXLSWorkbook

TCondFormat / TCondFormatRule and CF12 spec classes

Yksikkö: lxCondFormat

The BIFF8 (.xls) conditional-formatting family supporting Excel 2007+ extension rules. TXLSWorksheet 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 / Color 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 color round-trip (v2.43.0+)

The bar-fill color on a Data Bar rule and the per-stop colors on a Color 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 color 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

Color 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, Color 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 serialized for those three CF12 kinds when saving .xls files. The Data Bar / Color 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 recognizes 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

Esimerkki

// 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;

Katso myös

TXLSWorksheet.AddCondFormatDataBar
TXLSWorksheet.AddCondFormatColorScale2
TXLSWorksheet.AddCondFormatColorScale3
TXLSWorksheet.AddCondFormatIconSet
TXLSXConditionalFormat (XLSX side)