HotXLS Conditional Formatting and Rich Text in Delphi

The rule was there. Excel listed it under Manage Rules, the range was right, the formula was right — and not a single cell was highlighted. This is the most common first contact with programmatic conditional formatting: a cellIs rule describes a condition, but the highlight comes from a separate differential style record, and a rule written without one evaluates happily to 'true, change nothing'. Working with HotXLS from Delphi, where you assemble these pieces yourself instead of clicking through Excel's dialog, that separation between condition and consequence is the first thing to internalize.

UK teams should align this hotxls conditional formatting rich text styles workflow with local governance, audit, and data quality requirements before production release

HotXLS writes conditional formatting natively into both BIFF8 .xls and OOXML .xlsx files, alongside rich text runs and a pooled style model. The three features interact more than the API surface suggests, and this article maps the seams where output diverges from intent.

A condition needs a consequence: the dxf style

On the XLSX worksheet, comparison rules are created with AddConditionalFormat, which returns the index of the new rule in the sheet's ConditionalFormats collection. The returned rule object carries a Style property — the differential format (dxf in OOXML terms, per ECMA-376) that Excel applies when the condition is met. Skipping it produces the invisible rule from the opening paragraph.

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Idx: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('kpi.xlsx');
    Sheet := Book.Sheets[0];

    // Negative variance: light red fill
    Idx := Sheet.AddConditionalFormat('D2:D200', xlsxCfOpLessThan, '0');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    // Duplicate order IDs get flagged the same way
    Idx := Sheet.AddCondFormatDuplicateValues('A2:A200');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFEB9C);

    // Custom formula rule: highlight rows where actual misses 90% of target
    Idx := Sheet.AddCondFormatExpression('B2:B200', '$C2<$B2*0.9');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    Book.SaveAs('kpi-flagged.xlsx');
  finally
    Book.Free;
  end;
end;

Colours here are 32-bit ARGB values, so $FFFFC7CE is the familiar Excel 'light red' with a fully opaque alpha byte in front. The text-based rule family — AddCondFormatContainsText, AddCondFormatBeginsWith, AddCondFormatEndsWith and related flags — follows the same create-then-style pattern, as do AddCondFormatTop10, AddCondFormatAboveAverage, and the blank and error detectors.

Data bars, colour scales, and icon sets paint themselves

The visual rule kinds are the mirror image: they carry their appearance in the rule definition itself, and they ignore the Style property entirely. Assigning a fill to a data bar rule does nothing, which reads like a bug until you know the rule taxonomy. AddCondFormatDataBar takes the bar colour directly; the two- and three-point colour scales take their endpoint colours; AddCondFormatIconSet picks one of 26 icon set types such as icsTrafficLights3.

The interesting parameters on these calls are the value anchors, typed as TXLSCfValueKind: a bar or scale endpoint can sit at the range minimum or maximum, a literal number, a percent, a percentile, or the result of a formula. The default min-of-range and max-of-range anchors look fine on demo data and then mislead on production data with outliers — one extreme value flattens every other bar. For dashboards that get compared across periods, pin the endpoints to fixed numbers or percentiles so that 'half a bar' means the same thing in March and April.

The legacy writer is a subset, not a port

The XLS facade can create exactly four conditional rule shapes: data bars, two- and three-colour scales, and icon sets, written as CF12 records into the BIFF8 stream. There is no creation API for cellIs, expression, or text rules on that side. Rules of those kinds that already exist in a file you open are preserved and written back untouched, so round-tripping a customer's .xls does not damage their formatting — but a generator that needs threshold highlighting in .xls output has to fake it by applying ordinary cell fills from code, or move the deliverable to .xlsx where the full rule family is available.

That asymmetry is worth surfacing early in a project, because it changes the file-format decision for anything dashboard-shaped. A team that has standardized on .xls for compatibility reasons typically discovers this constraint in week three, after the data layer is built.

Rule stacking, priority, and overlapping ranges

Real dashboards rarely have one rule per range. A variance column might carry a data bar for magnitude plus a cellIs rule for the hard threshold, and a row-level expression rule on top for escalations. Each TXLSXConditionalFormat exposes a Priority value, and Excel evaluates competing rules in priority order — so when two rules want to paint the same cell, the outcome is defined by numbers you control, not by the order an auditor happens to read them in the dialog.

The maintainable approach is to treat priorities like z-order in a drawing program: assign them deliberately whenever two rules can touch the same cells, and leave a gap between values so a rule can be slotted in later without renumbering everything. Where rules genuinely do not interact — a data bar in column E, a text rule in column G — let creation order stand and spend the review effort instead on range boundaries. The expensive bugs in this area are almost never priority inversions; they are ranges like B2:B200 on a report that now has 350 rows, where the un-covered tail looks exactly like a healthy row. Derive every rule range from the final row count variable, the same discipline that applies to chart series and validation ranges elsewhere in the library.

One verification habit pays for itself: after generation, open the file in Excel, select the rule range, and walk Manage Rules once per template change. Conditional formatting is one of the few features where the only authoritative renderer is the consuming application, and a sixty-second eyeball catches what no unit test of the XML will.

Rich text: many formats within one cell

Rich text in the XLSX model is a list of runs, each with its own font attributes, assembled off to the side and then attached to a cell. The ownership rule is the part that bites: assigning to Cell.RichText transfers ownership of the TXLSXRichText object to the cell, which frees it when the cell is destroyed. Freeing it yourself afterwards is a double-free that may not crash until much later, in unrelated code.

var
  Rich: TXLSXRichText;
  Run: TXLSXRichTextRun;
begin
  Rich := TXLSXRichText.Create;
  Rich.AddRunText('Status: ');
  Run := Rich.AddRunText('OVERDUE');
  Run.Bold := True;
  Run.Color := $FFC00000;
  Run.ColorIsAuto := False;
  Run := Rich.AddRunText(' — escalated to regional manager');
  Run.Italic := True;
  Sheet.Cells[2, 7].RichText := Rich;   // ownership moves to the cell: do not Free
end;

Note the explicit ColorIsAuto := False: a run colour assignment only takes effect once the automatic-colour flag is cleared, and forgetting it yields a bold but stubbornly black run. Runs also support strikethrough, underline variants, and vertical alignment for superscript and subscript, and PlainText flattens the whole list back to a string when exporting or diffing.

The XLS facade has no public API for writing cell-level rich text — runs there are available on comments and text boxes through TextRuns, and rich strings read from an existing file survive a round-trip intact. Like the conditional formatting gap, this nudges mixed-format reporting towards the XLSX writer.

The style pool and the off-by-one that ships

Plain cell styling in the XLSX model goes through pooled collections on the workbook: Fonts.Add, Fills.AddSolid, Borders.Add each return an index into their pool. Those returned indexes are 0-based; the cell-side properties such as FontIndex treat 0 as 'default', so the pool index must be incremented by one on assignment:

HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);  // pool index, 0-based
for Col := 1 to 6 do
  Sheet.Cells[1, Col].FontIndex := HeaderFont + 1;          // cell index, 1-based

Forget the + 1 and every header silently renders in the default font — no exception, no warning, just a workbook that looks unstyled. The second-order mistake is calling Fonts.Add within the row loop: identical font definitions are deduplicated, but creating pool entries per row is wasted work, and some pools return a fresh object on every call. Build the handful of styles once before the loop and reuse the indexes; on hundred-thousand-row reports this is one of the differences detailed in large workbook performance tuning for HotXLS. For one-off semantic looks, both facades also offer ApplyBuiltinStyle on ranges, mapping to Excel's built-in Good, Bad, Neutral, and accent styles without touching the pools directly.

Conditional formatting and rich text are typically the last mile of a report pipeline — the data model and layout decisions that come before them are covered by template-based report generation with HotXLS. The complete rule, run, and style reference lives on the HotXLS Component product page.