TXLSXConditionalFormats is the rule collection exposed by TXLSXWorksheet.ConditionalFormats and supports cell, visual, text, ranking and average rules
Collection methods
function Add(const ARange: WideString; AOp: TXLSXCfOperator; const AFormula1: WideString): Integer;
function AddDataBar(const ARange: WideString; AColor: LongWord; AMinKind: TXLSCfValueKind; const AMinValue: WideString; AMaxKind: TXLSCfValueKind; const AMaxValue: WideString): Integer;
function AddColorScale2(...): Integer;
function AddColorScale3(...): Integer;
function AddIconSet(const ARange: WideString; ASetType: TXLSIconSetType): Integer;
function AddExpression(const ARange, AFormula: WideString): Integer;
function AddTextRule(const ARange: WideString; AKind: TXLSCfKind; const AText: WideString): Integer;
function AddTop10(const ARange: WideString; ARank: Integer; ABottom, APercent: Boolean): Integer;
function AddAboveAverage(const ARange: WideString; AAbove: Boolean): Integer;
function AddSimpleRule(const ARange: WideString; AKind: TXLSCfKind): Integer;
Rule members
| Range, Op, Formula1 and Formula2 | Define a standard cell comparison rule |
| Style | Lazy differential format used by cell and expression rules |
| DataBar, ColorScale and IconSet | Visual payloads for their respective rule kinds |
| Priority and StopIfTrue | Control worksheet rule order and short-circuit behavior |
| Text, Rank, Percent, Bottom and AboveAverage | Parameters for text, top or bottom and average rules |
| TimePeriod | Relative-date band for time-period rules |
Rule target ranges
As of 2.384.76, worksheet-local targets accept single cells, cell rectangles, whole columns such as $B:$D, and whole rows such as $5:$8; reversed endpoints are normalized, and spaces, tabs or line breaks separate multiple areas
Absolute and mixed references retain their original Range text; matching uses rows 1 through 1048576 and columns A through XFD, skips malformed or out-of-grid areas, and keeps valid sibling areas active without raising a new setter exception
Sheet-qualified and external references are not target areas; each rule belongs to its worksheet, and dollar signs must occur in ordinary A1 reference positions
Rule lookup, cell and range deletion, and the conditional-format evaluator use the same target geometry; aggregate rules count overlapping areas once and visit only stored cell values, even when the target spans the full grid
As of 2.384.77, row and column edits also transform whole-axis rule targets within the worksheet grid; an extent spanning every row or column remains complete when that axis is edited, while edits to the other axis move, shrink or remove the target
Range cloning retains the complete-coverage contract: every target area must fit inside the source rectangle before the rule is copied, and partial intersection alone does not copy a rule; whole-axis targets can be cloned from an entire-row or entire-column source when the destination stays within the grid
Interior edits preserve relative expression and cell-comparison formula domains by splitting a target when needed and merging parts that remain equivalent at one common anchor; the library does not reproduce the extra end-of-grid formatting that Excel can copy implicitly during insertion
Example
var Index: Integer;
begin
Index := Sheet.ConditionalFormats.AddExpression('B2:B100', 'B2<0');
Sheet.ConditionalFormats[Index].Style.SetFillBgColor($0000FF);
Sheet.ConditionalFormats.AddColorScale3('C2:C100', $0000FF, $00FFFF, $00FF00, cfvMinOfRange, '', cfvPercentile, '50', cfvMaxOfRange, '');
end;
Conditional number formats
Style.SetNumFormatId sets a differential number format independently of the target cell's base format; ID 0 is an explicit General reset, while an absent override leaves the base format in effect
Custom format IDs are 164 + Index, where Index is the zero-based result of Workbook.NumberFormats.Add; XLSX saving retains formats referenced only by conditional rules and updates the IDs when the format pool is compacted
ODS conditional styles
ODS import and export retain the differential styles of cell comparison and expression rules: font color, name, size, bold, italic and underline, solid or transparent background, and number formats supported by the ODS number-format converter
Explicit black, normal font weight, no underline and transparent background remain overrides; attributes absent from the rule continue to use the target cell's base formatting
Theme and indexed colors export as resolved colors, so their appearance survives but their theme or palette identity does not round-trip through ODS; font scheme metadata has no ODS mapping
ODS uses ordinary single or double underline rather than accounting underline, and patterned fills are represented by a background color; border and alignment overrides are not exposed by TXLSDxfStyle
The verified Excel 16 ODS import retains highlight colors, explicit resets, underline and percentage formatting but ignores conditional font family and size; LibreOffice 26.2.5.2 reads those named-style properties, and its converted XLSX retains them in Excel
As of 2.384.75, ODS cell comparison and expression rules use compact repeated blank carriers across their complete targets, including whole rows, whole columns and wide ranges; overlapping rules keep their order, and carrier-only padding does not create stored cells when reopened by HotXLS
See also