Per-worksheet conditional-formatting rules covering both the
cell-value
cfRule type="cellIs" kind and the Excel
2007+ extension kinds —
Data Bar,
Colour Scale (2-stop / 3-stop), and
Icon Set. Write side ships in v2.36.0; the
reader recognises the same three extension rule kinds on .xlsx
open since v2.38.0, populating
Kind and the matching
spec object so a load-edit-save round-trip preserves the rule
without falling back to a cell-value approximation. The
collection lives on
TXLSXWorksheet.ConditionalFormats.
Declared in
lxHandleX.
The reader parses the OOXML <cfRule type="dataBar|colorScale|iconSet">
envelope plus the inner <dataBar> /
<colorScale> / <iconSet>
body, walking child <cfvo> entries to recover
each threshold's type + val, and child
<color> entries to recover the bar / per-stop
colours. Icon Set rules also round-trip the iconSet
family name, reverse flag, and showValue
flag (mapped to ShowOnly). Excel-saved
rgb="AARRGGBB" colours are converted to the same BGR
LongWord convention used by the writer.
TXLSXCfOperator declaration
type
TXLSXCfOperator = (
xlsxCfOpEqual,
xlsxCfOpNotEqual,
xlsxCfOpGreaterThan,
xlsxCfOpLessThan,
xlsxCfOpGreaterOrEqual,
xlsxCfOpLessOrEqual,
xlsxCfOpBetween,
xlsxCfOpNotBetween);
xlsxCfOpBetween and xlsxCfOpNotBetween use
both Formula1 and Formula2; the others use
Formula1 only.
TXLSXConditionalFormat declaration
type
TXLSXConditionalFormat = class
constructor Create(const ARange: WideString; AOp: TXLSXCfOperator;
const AFormula1, AFormula2: WideString);
property Range: WideString;
property Op: TXLSXCfOperator;
property Formula1: WideString;
property Formula2: WideString;
// v2.36.0+ — Excel 2007+ extension rule kinds
property Kind: TXLSCfKind; // cfkCellIs (default) / cfkDataBar / cfkColorScale* / cfkIconSet
property DataBar: TXLSDataBarSpec; // non-nil for cfkDataBar
property ColorScale: TXLSColorScaleSpec; // non-nil for cfkColorScale2/3
property IconSet: TXLSIconSetSpec; // non-nil for cfkIconSet
// v2.45.0+ — OOXML cfRule priority attribute
property Priority: Integer; // 0 = writer assigns by index, positive = user-pinned
end;
The spec classes (TXLSDataBarSpec,
TXLSColorScaleSpec, TXLSIconSetSpec) and
threshold-kind enumerations (TXLSCfValueKind,
TXLSIconSetType) are shared with the BIFF8 side
— same type names, same colour convention (Delphi BGR
LongWord), so format-agnostic helpers can target both
backends with a single code path.
TXLSXConditionalFormat members
| Range |
OOXML sqref string. A single range
('A1:A10') or a space-separated list of ranges
('A1:A10 C1:C10') are both legal. |
| Op |
Comparison operator. |
| Formula1, Formula2 |
Comparison thresholds. Formula2 is only used for
between / notBetween rules. |
TXLSXConditionalFormats declaration
type
TXLSXConditionalFormats = class
function Add(const ARange: WideString; AOp: TXLSXCfOperator;
const AFormula1: WideString): Integer; overload;
function Add(const ARange: WideString; AOp: TXLSXCfOperator;
const AFormula1, AFormula2: WideString): Integer; overload;
procedure Clear;
property Count: Integer;
property Items[Index: Integer]: TXLSXConditionalFormat; default;
end;
Worksheet shortcuts: Worksheet.AddConditionalFormat
appends cell-value rules; the v2.36.0
AddCondFormatDataBar /
AddCondFormatColorScale2 /
AddCondFormatColorScale3 /
AddCondFormatIconSet helpers append the extension
rule kinds with the correct Kind and spec object
pre-populated.
Example
begin
// Cell-value: highlight values greater than 100 in B1:B10.
Worksheet.AddConditionalFormat('B1:B10', xlsxCfOpGreaterThan, '100');
// Cell-value: values between 1 and 5 in C1:C20.
Worksheet.AddConditionalFormat('C1:C20', xlsxCfOpBetween, '1', '5');
// Data Bar: blue bar over D1:D10 using the default min/max-of-range bounds.
Worksheet.AddCondFormatDataBar('D1:D10', $00FF0000);
// 3-color scale: classic Red-Yellow-Green over E1:E10.
Worksheet.AddCondFormatColorScale3('E1:E10',
$000000FF, $0000FFFF, $0000FF00);
// Icon Set: 3 Arrows (Colored) over F1:F10.
Worksheet.AddCondFormatIconSet('F1:F10', icsArrows3);
end;
See also