BIFF8 56-Colour Palette: OKLab Colour Mapping in HotXLS

The dark blue $000033 sits 51 units from black and 77 units from the classic navy entry $000080 when distance is measured as plain Euclidean distance over RGB channel values. A converter that writes classic .xls files with nearest-RGB matching therefore paints a dark blue header row black, and black is what every reader that goes by the palette index alone displays. The format leaves the writer no room: a cell format stores a palette index, and the palette holds 56 entries. Getting the header right takes two things, a distance measure that tracks what people see, and a way to rewrite the palette itself when a workbook uses more distinct colours than the palette can hold

Where the 56-colour limit comes from

A BIFF8 font, fill or border record never carries an RGB value. It carries a colour index, and the workbook-global Palette record ($0092, [MS-XLS] §2.4.188) supplies exactly 56 opaque RGB entries for indexes 8 through 63. Indexes 0 to 7 are fixed copies of the eight basic colours. Values above 63 are not colours at all but tokens: $40 is the system foreground, $41 the system background, $4D to $4F are chart text, chart background and the neutral chart colour, and $7FFF means automatic. Excel 2007 added the XFExt record ($087D, §2.4.355), which can attach a full RGB or theme colour to a cell format, but Excel 97–2003 and any parser that reads only the XF record still see nothing except the index

HotXLS exposes the palette through a public ColorIndex of 1 to 56, which is the physical index minus 7. Three numbering schemes are in play, and ResolveIndexedColor keeps them apart through TXLSIndexedColorSpace: xicsPublicColorIndex for the 1..56 API values, xicsBiffIcv for raw on-disk indexes, and xicsOoxmlIndexed, where 64 and 65 mean system foreground and background. Raw BIFF indexes are also checked against the role you pass as TXLSIndexedColorRole, because a font, a fill and a chart accept different token sets; $51 is legal for a font and invalid for a fill

The Boolean result of ResolveIndexedColor is where callers trip up. It returns True only when it obtained a concrete ARGB value, and the short overload never asks the operating system for desktop colours. A system or automatic token therefore comes back False with Res.Kind set to xickSystem or xickAutomatic, and code that reads False as "no colour" quietly discards the meaning of the token. The one exception is $4F, the neutral chart colour, which resolves to black without help. For real RGB values on the other tokens, pass a TXLSTryResolveSystemColor method to the long overload and apply your own policy for the UI, an export or a headless service:

type
  TExportColors = class
    function ResolveSystemColor(AColor: TXLSSystemColorRole;
      ARole: TXLSIndexedColorRole; out AArgb: LongWord): Boolean;
  end;

function TExportColors.ResolveSystemColor(AColor: TXLSSystemColorRole;
  ARole: TXLSIndexedColorRole; out AArgb: LongWord): Boolean;
begin
  // Headless export: fixed colours instead of the current desktop theme
  Result := True;
  case AColor of
    xscrWindowText, xscrChartText, xscrToolTipText:
      AArgb := $FF000000;
    xscrWindowBackground, xscrChartBackground:
      AArgb := $FFFFFFFF;
  else
    Result := False;
  end;
end;

var
  Colors: TExportColors;   // created once by your export code
  Res: TXLSIndexedColorResolution;
begin
  // $41 is the system background token, valid for a fill
  if Workbook.ResolveIndexedColor($41, xicsBiffIcv, xicrFillBackground,
    Colors.ResolveSystemColor, Res) then
    PaintBackground(Res.ARGB)        // your own drawing routine
  else if Res.Kind = xickInvalid then
    RejectToken(Res.RawIndex);       // your own error handling
end;

Why does RGB distance pick the wrong palette entry?

sRGB channel values are gamma-encoded, so equal steps in the numbers are not equal steps in what a person sees, and the error is largest in the dark, saturated tones that corporate templates love. In RGB, $000033 is 51 from black and 77 from navy, so black wins. Once each channel is linearised and the colour is projected into OKLab, the squared distances are about 0.0312 to black and 0.0235 to navy, and navy wins. The match is ColorIndex 11 at physical slot 18, and the Classic and XLSX engines give the same answer

The default palette adds a wrinkle. Navy appears twice, at ColorIndex 11 and again at 25, so $000033 is exactly as close to both. A matcher that scans in a different order, or uses less-than-or-equal, picks 25 on one run and 11 on another build, and two generated files stop diffing cleanly. HotXLS scans from slot 8 upwards with a strict less-than comparison, so a tie always goes to the lower index

OKLab is a deliberate trade. CIEDE2000 would rank some hue pairs more accurately, but its piecewise corrections make it slow and harder to reason about inside an iterative loop. OKLab costs a linearisation, two small matrix products and three cube roots per colour, after which a plain squared distance is a good enough stand-in for perceived difference. For a 56-entry palette scan that runs on every colour assignment and inside a clustering pass, the cheaper, smoother metric is the right pick

What does NearestIndexedColor return?

NearestIndexedColor takes an ARGB value and fills a TXLSNearestIndexedColorMatch record without changing the workbook. Each workbook keeps the 56 palette colours together with their OKLab coordinates and a palette generation counter, so a query costs one input conversion and one fixed scan. Resetting the palette rebuilds that cache, changing a single entry updates just that slot, and a query against a cache that is out of step with the current generation returns False instead of guessing

Alpha follows a narrow rule. An alpha byte of zero is treated as opaque, which matches how most Delphi code passes plain RGB values, and $FF is opaque by definition. Anything in between is rejected: the function returns False with ColorIndex 0 and PaletteSlot -1, since a palette entry has nowhere to store transparency

var
  Match: TXLSNearestIndexedColorMatch;
begin
  if Workbook.NearestIndexedColor($FF000033, Match) then
  begin
    // Default palette: Match.ColorIndex = 11, Match.PaletteSlot = 18,
    // Match.ARGB = $FF000080
    if not Match.ExactMatch then
      LogApproximation(Match.InputARGB, Match.ARGB,   // your own logger
        Match.DistanceSquared);
  end;
end;

The same matcher sits behind the RGB setters. Assigning Interior.Color, Font.Color or Border.Color on a Classic workbook keeps the exact colour for the XFExt record and picks the index stored in the XF record with this scan, and the fill and border writers resolve RGB and theme colours to an index the same way. Whatever NearestIndexedColor reports for a colour is the slot an index-only reader will show

Fitting a true-colour workbook into 56 slots

Matching against the default palette is fine for a handful of colours. A brand template with a dozen near-identical blues needs more: the palette itself has to change. BuildBiffPalettePlan computes a complete proposal for all 56 slots and leaves the workbook untouched, so you can log it, compare it against a threshold or throw it away

The planner starts with ScanIndexedColorUsage. Any slot referenced by index from a font, fill, border, conditional format, shape, comment, chart font or worksheet gridline is locked, because changing that entry would recolour every consumer of the index at once. The targets are the direct RGB and resolved theme colours from fonts, fills, gradient stops, borders, conditional format styles, data bars and colour scales. Each target is weighted by the larger of its reference count and its definition count, and a conditional format counts every cell its ranges cover, so a fill painted down a whole column outweighs a colour used in one comment. Placement then follows a fixed order:

• Locked slots keep their current colour unconditionally
• A target that already exists in the palette keeps its lowest matching slot, and that slot becomes fixed
• If the remaining distinct targets fit into the free slots, each gets an exact slot, assigned in ascending ARGB order
• Otherwise Quantized is set, each free slot is seeded with the target whose distance to its nearest existing centre, multiplied by its weight, is largest, and up to 16 rounds of weighted k-means in OKLab move only the free centres until assignments stop changing

The overflow path deserves a clear-eyed reading. K-means here is a bounded local optimisation, and a free slot ends up holding a cluster centre converted back to sRGB with clamping, which can be a colour no cell used verbatim. What the planner does deliver is repeatability: the same workbook always produces the same plan. The plan also reports how far it had to bend through WeightedError, MaxDistanceSquared, ExactTargetWeight and TotalTargetWeight, which is enough for a batch job to refuse a save when the approximation gets too coarse for a brand guideline. The Targets array shows, per input colour, the slot it maps to and its squared distance

const
  MaxAcceptedError = 0.002;   // your threshold, in squared OKLab units
var
  Plan: TXLSBiffPalettePlan;
  I: Integer;
begin
  Plan := Workbook.BuildBiffPalettePlan;   // read-only
  if Plan.Quantized and (Plan.MaxDistanceSquared > MaxAcceptedError) then
    raise Exception.Create('Too many distinct colors for a BIFF8 palette');
  for I := 0 to High(Plan.Slots) do
    if Plan.Slots[I].Changed then
      LogSlot(Plan.Slots[I].ColorIndex, Plan.Slots[I].SourceARGB,
        Plan.Slots[I].TargetARGB);           // your own logger
  if not Workbook.ApplyBiffPalettePlan(Plan) then
    raise Exception.Create('The palette changed after planning');
end;

When does ApplyBiffPalettePlan refuse a plan?

ApplyBiffPalettePlan checks the whole plan before it writes a single slot and returns False, palette untouched, if anything disagrees with the current workbook. The plan carries SourcePaletteGeneration and SourcePaletteHash, a 64-bit FNV-1a hash over the 56 source colours. Validation also re-checks every public and physical index, every source colour, that no locked slot is marked as changed, that the locked and changed counts add up, and that every target is opaque

Any real palette change between planning and applying makes the plan stale, and that includes a successful earlier application of the same plan. Plans are single-use in practice: build, inspect, apply, and build again if you need another. A valid plan with no changed slots succeeds without advancing the generation. A real change advances it once and refreshes the matcher once. On the Classic engine the new colours go straight into the palette; on the XLSX engine they become custom indexed colours, which HasCustomIndexedColor and CustomIndexedColorCount report and which the XLSX writer emits as an indexedColors block

Opting in for XLS saves and XLSX-to-XLS conversion

The BiffPaletteSavePolicy property defaults to xbpsPreserve, so a workbook's palette only changes when you ask. Setting xbpsOptimizeTrueColors makes a Classic workbook build and apply a fresh plan inside SaveAs, and only when the target format is xlExcel97; BIFF5, CSV, HTML, PDF, XLSX and the other writers ignore the setting. After a successful save the optimised palette stays in the workbook, so later queries and saves see the same mapping. If the save fails, is cancelled or raises, the original 56 colours and the original generation are restored

For generated reports, turn the optimising policy on. Those workbooks are built from RGB and theme colours, nobody hand-tuned their palette, and an index-only reader shows them far closer to the original with it. Keep xbpsPreserve for workbooks whose palette is part of the contract, such as a template that downstream code edits by ColorIndex, where a slot quietly changing its colour would break that code

XLSX sources take a different route. SaveXLSXWorkbookAsXLS in lxXlsxExport builds one plan from the loaded TXLSXWorkbook and writes it into the destination palette before converting any style, so every converted cell is matched against the final palette. The workbook audit and conversion workbench demo runs exactly this path. Theme colours go through the same planner once their tint is resolved to RGB; chart fills that should stay live theme references are a separate case, covered in the GelFrame theme colour chart fills article, where binary XLS stores a scheme index instead of a flattened colour

// Classic workbook: opt in, applies to BIFF8 saves only
Workbook.BiffPaletteSavePolicy := xbpsOptimizeTrueColors;
if Workbook.SaveAs('report.xls', xlExcel97) <> 1 then
  HandleSaveFailure;   // your own handler; palette already restored

// XLSX model to BIFF8 with one deterministic palette plan
XWorkbook := TXLSXWorkbook.Create;
try
  if XWorkbook.Open('report.xlsx') = 1 then
    if SaveXLSXWorkbookAsXLS(XWorkbook, 'report.xls') <> 1 then
      HandleSaveFailure;
finally
  XWorkbook.Free;
end;

The palette calls (ResolveIndexedColor, NearestIndexedColor, ScanIndexedColorUsage, BuildBiffPalettePlan and ApplyBiffPalettePlan) have the same signatures on IXLSWorkbook and TXLSXWorkbook, from Delphi and C++Builder. The trial build on the HotXLS Delphi Excel component page is enough to run a plan against your most colourful template and read MaxDistanceSquared before anything is written