Technical Article

BIFF8 FontIndex skips 4: rich text runs in HotXLS for Delphi

A BIFF8 workbook with eight FONT records contains an XF record whose font field reads 8. Count from zero and that is one slot past the end of the table, yet Excel renders the cell in the eighth font without complaint. The value is legal: [MS-XLS] §2.5.129 numbers the first four FONT records 0 to 3, skips 4 entirely, and numbers every later record by its one-based position

So the fifth FONT record is ifnt 5, and a well-formed file never needs a value larger than its FONT record count. The rule reads like a typo in the spec, which is exactly why people "correct" it, and because cell formats, shared-string rich text and comment runs all carry the same FontIndex type, a reading that differs between paths binds some fonts correctly and others one record off. It belongs to the same family as the length and encoding quirks in decoding BIFF8 XLUnicodeString cch and fHigh — the file is fine, the reading of it is not

What [MS-XLS] §2.5.129 says, and how to check it on a real file

[MS-XLS] §2.5.129 defines FontIndex as a record position with a hole in it. Values below 4 are zero-based, values above 4 are one-based, and the value 4 MUST NOT be used. XF records point at fonts with it, the FormatRun entries of a rich shared string use it, and so do the TXO formatting runs described in §2.5.272

You need not take the spec's word for it. Dump an .xls that Excel itself saved with any BIFF record viewer, count the FONT records (record type $0031), then scan the font field at the start of every XF record ($00E0). The highest value stays within the record count and the number 4 is missing from the list. If you find a 4, or a value above the FONT count, the file was written by something other than Excel and its font numbering deserves a closer look. For a diagnostic tool or a test, the whole mapping fits in two functions

// Your own helpers, not part of any library
// [MS-XLS] 2.5.129 FontIndex: 0..3 zero-based, > 4 one-based, 4 invalid
function FontIndexToRecordNo(Ifnt: Word): Integer;  // 1-based FONT record
begin
  if Ifnt < 4 then
    Result := Ifnt + 1
  else if Ifnt > 4 then
    Result := Ifnt
  else
    Result := -1;  // 4 must not occur
end;

function RecordNoToFontIndex(RecordNo: Integer): Word;
begin
  if RecordNo <= 4 then
    Result := RecordNo - 1
  else
    Result := RecordNo;
end;
FontIndex mapping per MS-XLS 2.5.129 where ifnt 0 to 3 are zero-based FONT record positions, ifnt 5 and above are one-based and the value 4 never occurs, with the FontIndexToRecordNo conversion
The fifth FONT record is ifnt 5, so in a file with N FONT records the largest legal ifnt is N and the value 4 is never stored

How HotXLS numbers fonts in XF records and rich text runs

HotXLS reads and writes every BIFF8 font reference in spec numbering. On save the font table is compacted down to the fonts something actually references, and each surviving font's position becomes a FontIndex: positions 1 to 4 are written out as 0 to 3, position 5 and above go out unchanged. Loading reverses it, so any value of 5 or more moves down by one to find the font in memory and anything below 5 stays put. The same conversion is applied to XF records, to rich text runs in the shared string table, to comment and text box runs, and to the font references inside classic chart records

For application code the visible part is TextRuns on TXLSComment and TXLSTextBox. TextRuns.FontIndex[I] returns the file numbering exactly as read, with 4 skipped. Run indexes are 1-based, and CharIndex[I] is the character offset where run I starts. Exposing the file number rather than a zero-based table slot is the better default here: what the API reports matches what a record dump shows. The cost is one call to the helper above before you treat the value as a table position

var
  Book: IXLSWorkbook;
  Note: TXLSComment;
  I: Integer;
  Ifnt: Word;
begin
  Book := TXLSWorkbook.Create;
  if Book.Open('review-notes.xls') <> 1 then
    raise Exception.Create('Cannot open review-notes.xls');
  Note := Book.Sheets[1].Range['C2', 'C2'].Comment;
  if Note <> nil then
    for I := 1 to Note.TextRuns.Count do
    begin
      Ifnt := Note.TextRuns.FontIndex[I];   // file numbering, 4 skipped
      // your own sanity check: 4 means the file breaks 2.5.129
      if Ifnt = 4 then
        raise Exception.CreateFmt('Run %d uses invalid ifnt 4', [I]);
      Writeln(Format('run %d at char %d: ifnt %d = FONT record #%d',
        [I, Note.TextRuns.CharIndex[I], Ifnt, FontIndexToRecordNo(Ifnt)]));
    end;
end;

After a save, the number stored for a run may differ from the one you read or set. The save renumbers runs together with the compacted font table, so the number changes and the font it points at does not

Why a zero-based fix shifts every custom font

The trap usually starts with a reasonable-looking bug report. Somebody dumps a workbook with eight FONT records, sees an XF pointing at font 8, concludes that the writer produced an out-of-range index, and switches both the writer and the reader to plain zero-based arithmetic. The round-trip test still passes, because the patched writer and the patched reader agree with each other. Excel does not agree. A file written that way stores the first custom font as ifnt 4, which Excel resolves to the default font, and every later custom font lands one record early. Reading Excel's own files goes the other way: each font binds one record late

The zero-based misreading in which a writer and reader treat one-based FontIndex values as zero-based, storing the first custom font as the forbidden ifnt 4 that Excel resolves to the default font and landing every later font one record early while round trips between the two still pass
A writer and reader that share the same misreading pass every save-then-reopen test, whilst every font Excel opens lands one record off

Two signals give the misreading away before it ships. One is a test that asserts ifnt < FontCount. It looks like a bounds check, and it actually writes the misreading down as a requirement. A test that maps each written ifnt back to a FONT record name through the spec formula catches the shift on its very first run

The other signal is code that disagrees with itself. A complete BIFF8 reader and writer applies the skip-4 rule in several places: XF output, XF input, shared-string run remapping, comment runs, chart font records. When a change moves some of those to zero-based numbering and leaves the rest alone, the untouched sites are the evidence, so suspect the change before you suspect them. A partial change can hide for a long time, because fonts used in rich text are usually also referenced by some cell format

Why do comment font runs break only on the second save?

Comment and text box runs can survive one save and break on the next when two writer habits meet. The first habit is asymmetric pruning: keep the first N-1 FONT records in place unconditionally and drop only the last one when no XF references it. The second is writing the TXO formatting runs ([MS-XLS] §2.4.329) back byte for byte without renumbering them. Each habit passes a single-save test on its own. Together they set up a font loss that waits one generation

The reason lies in what Excel writes. Excel-authored .xls files commonly end with an unreferenced trailing font; on a Chinese-locale system it is a 9 pt DengXian. On the first save, the font that only a comment run uses is therefore never last, and nothing visibly moves. That save drops the trailing font, though, and promotes the comment-only font to the last position. The second save finds a last font with no XF reference and discards it. The run's ifnt now points past the end of the table, and Excel falls back to the default font. If the workbook gained a font in between, the run binds to the newcomer instead. Notes that get opened, annotated and saved again and again, like the ones in building a comments and hyperlinks review workflow, are where this shows up first

Font table across two saves where the unreferenced trailing FONT record Excel writes is dropped first, the comment-only font becomes last and is then discarded on the second save when TXO formatting runs are written back without counting them as font references
The first save looks clean because the trailing font takes the loss; the comment font only disappears on the second, so a font check has to span more than one save

HotXLS handles comment and text box runs the same way it handles shared-string runs. Before the font table is compacted, every run on every worksheet is converted from its skip-4 FontIndex to a table slot and counted as a reference, which keeps a font that only a run uses. On the way out, the runs go through the same slot-to-saved-index table that renumbers the XF records. The closing TxOLastRun entry carries no font and is written as it was read. Because of that renumbering, comparing FontIndex values before and after a save tells you nothing; compare the font names they resolve to instead

Copies, row inserts and runs that cross workbooks

In the classic engine, Range.Copy, CopyRange, Sheets.AddCopy and the cell shifts behind Range.Insert and Range.Delete all go through one cell-copy path, and a shift is just a copy followed by a clear. A copy that carried only the comment text and author would turn a two-run note into a one-font note after a single row insert, without the user going anywhere near the comment

HotXLS copies each run's start offset and font along with the text. Inside one workbook the FontIndex is reused as is. Across workbooks the font moves by value: the skip-4 number is converted to a slot, the font is matched or added in the destination font table by its attributes, and the resulting slot is converted back to file numbering. Rich text in shared strings goes through the same conversion when Sheets.AddCopy copies a sheet into another workbook, so the two kinds of runs cannot drift apart. The font-table side follows the same by-value approach as the formula side covered in cross-workbook copy and formula rebinding

Two edge cases are worth knowing about when you write tests for this:

  • An in-place paste, where source and destination resolve to the same comment, keeps its runs; nothing is cleared before it has been read
  • Sheets.AddCopy also copies comments anchored on cells that have no stored cell record, which a loop over stored cells alone would miss

On the XLSX engine, copies clone comment runs by value, and the comments part is read and written with rFont, strike, u and vertAlign. A run's font name, strikethrough, underline and superscript or subscript setting therefore come back intact after save and reopen

A regression test that survives renumbering

Every trap in this article passes an in-memory test. The zero-based misreading lives in a matched writer and reader, the comment-font loss needs two saves, and lost comment runs on XLSX only show after a reopen. So the test has to reopen from disk, run through at least two save generations, and check run structure rather than raw font numbers. Start from a workbook that Excel itself saved, with a two-run comment on C2 whose second run uses a font nothing else in the file references; that is the font the second save would otherwise drop

procedure CheckNoteAcrossTwoSaves(const SrcFile, Out1, Out2: string);
var
  Book: IXLSWorkbook;
  Note: TXLSComment;
  RunCount: Integer;
  SecondRunAt: Word;
begin
  // Assert calls are your own test checks
  Book := TXLSWorkbook.Create;
  Assert(Book.Open(SrcFile) = 1);          // Excel-saved, C2 has two runs
  Note := Book.Sheets[1].Range['C2', 'C2'].Comment;
  Assert(Note <> nil);
  RunCount := Note.TextRuns.Count;
  SecondRunAt := Note.TextRuns.CharIndex[2];

  Book.Sheets[1].Range['C2', 'C2'].Copy(Book.Sheets[1].Range['E5', 'E5']);
  Book.Sheets[1].Range['C1', 'C1'].Insert(xlShiftDown);   // C2 moves to C3
  Assert(Book.SaveAs(Out1) = 1);

  Book := TXLSWorkbook.Create;               // generation two
  Assert(Book.Open(Out1) = 1);
  Assert(Book.SaveAs(Out2) = 1);

  Book := TXLSWorkbook.Create;               // reopen, never trust memory
  Assert(Book.Open(Out2) = 1);
  Note := Book.Sheets[1].Range['C3', 'C3'].Comment;
  Assert((Note <> nil) and (Note.TextRuns.Count = RunCount));
  Assert(Note.TextRuns.CharIndex[2] = SecondRunAt);
  Assert(Note.TextRuns.FontIndex[2] <> 4);
  Note := Book.Sheets[1].Range['E5', 'E5'].Comment;
  Assert((Note <> nil) and (Note.TextRuns.Count = RunCount));
end;

The run count and start offsets catch flattened notes. The font itself needs one more step outside the test: open Out2 in a record viewer, run each run's ifnt through FontIndexToRecordNo, and confirm the FONT record it lands on carries the same font name as in the source file. In Excel, a lost font shows up as the default typeface on the second run of the C3 note

Classic XLS still turns up in plenty of Delphi and C++Builder projects, and every font consumer in a BIFF8 file has to agree on §2.5.129 for the output to open correctly in Excel. HotXLS, the Delphi spreadsheet component applies the skip-4 numbering across cell formats, shared strings, comments, text boxes and charts, renumbers runs on every save and moves them by value between workbooks, and reads and writes both XLS and XLSX without Excel or OLE automation