BIFF SupBook and XTI External Link Classification in Delphi

Open an old xls, save it again, and the add-in formula that called into a registered analysis library now points at an empty reference inside the workbook itself. HotXLS traces that silent corruption to one bad assumption: that a BIFF SupBook record is either self or an external file. [MS-XLS] defines seven kinds, not two

Why does a saved workbook lose its add-in links?

Because the classification test was structural instead of typed. The traditional shortcut reads a SupBook record ($01AE), checks whether it carries the self marker, and if not, treats whatever string follows as a document URL. Every record that is neither of those two things falls through into a default branch, and the default branch is almost always "this is the workbook itself". An add-in supporting link, a same-sheet link, an unused slot and a truncated record all end up wearing the same wrong label. Nothing throws while this happens: the record parsed, the formula recompiled, the file saved without a warning, and the defect surfaces three weeks later when someone notices a column of zeros where a currency conversion used to be. [MS-XLS] §2.4.271 describes a record that can be a self-reference, a same-sheet reference, an add-in function container, an external workbook with a virtual path and a sheet-name table, a DDE or OLE data link, or an unused placeholder — and a seventh state that is not in the specification but exists on real disks, the record that does not parse. The fix is not a better heuristic; it is refusing to have a heuristic at all

The seven kinds a SupBook record can carry

HotXLS declares the supporting-link taxonomy as a closed enumeration in lxExternSheet.pas, and every downstream decision switches on it. Nine enumeration values cover the seven categories, because the DDE and OLE case needs a provisional state before it can be resolved:

type
  TXLSSupportingLinkKind = (
    slkUnknown,           // failed to parse, or trailing bytes remained
    slkSelf,              // this workbook
    slkSameSheet,         // U+0000 marker
    slkAddIn,             // add-in function container
    slkExternalWorkbook,  // virtual path + sheet-name table
    slkDde,               // resolved from ExternName flags
    slkOle,               // resolved from ExternName flags
    slkDdeOrOle,          // one of the two, not yet known which
    slkUnused);           // single-space placeholder

  TXLSFormulaReferenceClass = (
    frcInternal,
    frcExternalWorkbook,
    frcExternalOther,
    frcUnknownOrMalformed);

  TXLSXtiInfo = record
    XtiIndex    : Integer;   // zero-based, as stored in ExternSheet.rgXTI
    ExternID    : Integer;   // one-based, the internal convention
    SupBookIndex: Integer;
    Sheet1Index : Integer;
    Sheet2Index : Integer;
    LinkKind    : TXLSSupportingLinkKind;
  end;

The dispatch is sentinel-driven, not string-driven. A field value of $0401 marks the self record. A sheet count of one paired with $3A01 marks an add-in container. Only a value in the range 1 to $00FF means an encoded virtual path follows, and only then does HotXLS decode a string at all. Anything outside those three shapes stays slkUnknown, and a record whose sheet-name table does not consume the record body exactly is downgraded back to slkUnknown even when the head looked plausible

The sentinel-driven ladder HotXLS uses to classify a BIFF SupBook record into seven kinds, decoding a string only for values in the encoded-path range and falling back to an unknown kind rather than to a default branch — Each kind is reached by a sentinel rather than by a string test, and a record that matches none of the shapes stays unknown instead of falling into a default branch that means this workbook

Why does the same-sheet marker decode as an empty string?

Because the general-purpose BIFF string reader destroys the byte the classification depends on. The same-sheet supporting link is a one-character string whose single character is U+0000, and TXLSBlob.GetBiffString hands that back as an empty WideString, indistinguishable from a genuinely empty path — which is exactly the input a self-reference heuristic answers "self" to. HotXLS therefore reads the raw first code point out of the record body rather than trusting the decoded value:

StringOffset := offset;
FDocUrl := Data.GetBiffString(offset, False, True);
FirstChar := $FFFF;
if val = 1 then
begin
  StringOptions := Data.GetByte(StringOffset + 2);
  if (StringOptions and $01) = 0 then
    FirstChar := Data.GetByte(StringOffset + 3)     // compressed, one byte
  else
    FirstChar := Data.GetWord(StringOffset + 3);    // wide, two bytes
end;

if FirstChar = 0 then
  FKind := slkSameSheet
else if (Length(FDocUrl) = 1) and (FDocUrl[1] = WideChar(#32)) then
  FKind := slkUnused
else if Pos(WideChar(#3), FDocUrl) > 0 then
  FKind := slkDdeOrOle
else if FDocUrl <> '' then
  FKind := slkExternalWorkbook;

Note the compressed-versus-wide branch. The option byte sits at a fixed offset from the string header and the first code point is one byte or two depending on bit 0, so reading it as a byte unconditionally works on most files and fails on the ones written by localised builds — the worst possible distribution for a bug. The unused placeholder is caught the same way, by its literal single-space payload, and the DDE or OLE case by the U+0003 separator embedded in the encoded path

Why HotXLS reads the raw first code point out of a BIFF SupBook record body instead of the decoded string, because the general-purpose string reader turns the same-sheet U+0000 marker into an empty value — The same-sheet marker is a one-character string whose character is U+0000, so the general string reader folds it into an empty value and only the raw code point at the option-byte offset keeps it

Why can DDE and OLE not be separated at SupBook time?

Because the SupBook record does not carry the distinguishing bits. It tells you the link is one of the two; the fOle and fOleLink flags that decide which live in the ExternName record ($0023) arriving later in the stream. HotXLS records slkDdeOrOle at parse time and narrows it in ParseExternalName, and if no ExternName ever arrives the kind stays provisional forever — which is correct, because the file genuinely does not say. Every consumer downstream treats that provisional value as a real value rather than a missing one, so no caller has to invent a tiebreak. Guessing "probably DDE" here would buy a tidier enumeration and a class of wrong answers nobody could trace back:

if FKind = slkDdeOrOle then
begin
  if Data.DataLength < 2 then
    Exit;
  Flags := Data.GetWord(0);
  if (Flags and $0010) <> 0 then
    FKind := slkOle
  else if (Flags and $0008) <> 0 then
    FKind := slkDde;
end;

XTI indexes are zero-based on disk and one-based inside

HotXLS performs the off-by-one conversion exactly once, at the point a token enters the internal syntax tree, and nowhere else. PtgNameX.ixti ([MS-XLS] §2.5.198.85) is a zero-based index into the rgXTI array of the ExternSheet record ($0017, §2.4.106), while the library's internal ExternID convention is one-based with zero reserved for "no external sheet". The BIFF8 read path does FExternID := wValue + 1 when it decodes a tNameX token and the write path emits StoreExternID - 1, leaving the raw token view and the on-disk semantics untouched. Getting this wrong is unusually hard to catch: external defined names resolve to the neighbouring entry, and in a file with a single XTI entry index 0 becomes index 1, misses, and the name degrades silently. A regression that only exercises recompiled formula text never sees it, because recompilation never touches the disk index at all — the same trap that makes defined names spanning sheets and workbooks worth testing against real byte streams. Resolution is bounded on both ends: TlxExternSheetSheet.TryResolveXti returns False for a negative index or a missing entry, TXLSSupBook.TryGetKind returns False for a SupBook index outside the array, and ClassifyXti then maps slkSelf and slkSameSheet to frcInternal, slkExternalWorkbook to frcExternalWorkbook, and slkAddIn, slkDde, slkOle and slkDdeOrOle to frcExternalOther. Everything else, every out-of-range path included, lands on frcUnknownOrMalformed

HotXLS converting the zero-based XTI index of a BIFF PtgNameX token into its one-based internal ExternID at a single point, with bounded resolution on both ends and the classification map that consumes it — The off-by-one between the zero-based disk index and the one-based internal ExternID is applied once, as a token enters the syntax tree, and every unresolvable index lands on the malformed class

Classifying a formula before freezing it

TXLSCompiledFormula.ClassifyReferences scans the preserved BIFF token stream directly instead of decompiling the formula and searching for square brackets. Bracket-hunting in formula text is a text heuristic wearing a parser's coat: it matches string literals, it matches structured references, and it misses external defined names entirely, since those carry no brackets in decompiled form. The token scan looks only at PtgNameX, PtgRef3d, PtgArea3d, PtgRefErr3d and PtgAreaErr3d, falling back to a syntax-tree walk when no BIFF stream survives. Merging is deliberately pessimistic — the fixed priority is frcUnknownOrMalformed, then frcExternalWorkbook, then frcExternalOther, then frcInternal — so a single unreadable token poisons the whole formula. For an external defined name the name index is validated too: one-based, in range, and backed by a retained ExternName record

var
  Wb   : TXLSWorkbook;
  Sheet: TXLSWorksheet;
  i    : Integer;
begin
  Wb := TXLSWorkbook.Create;
  try
    Wb.Open('quarterly.xls');
    for i := 1 to Wb.Sheets.Count do        // Sheets is one-based
    begin
      Sheet := Wb.Sheets[i];
      // freezes ONLY formulas classified frcExternalWorkbook;
      // internal, add-in, DDE/OLE and malformed references stay formulas
      Sheet.ConvertFormulasToValues(True);
    end;
    Wb.SaveAs('quarterly-detached.xls');
  finally
    Wb.Free;
  end;
end;

The OnlyExternal parameter is where the taxonomy pays for itself. Freezing a formula is irreversible, so the operation has to prove a reference is an external workbook rather than merely suspect it. Add-in calls survive, DDE and OLE links survive, and anything the parser could not fully understand survives, because the safe outcome of uncertainty is to change nothing. The same discipline governs rebinding formulas copied between workbooks, where a misclassified reference rebinds to the wrong book instead of failing loudly

Records that will not parse are written back untouched

HotXLS keeps the original SupBook payload and re-emits it byte for byte when the record was never edited. A parse failure sets slkUnknown and clears the derived state, but the captured body stays in FRawData and the store path prefers it over any reconstruction as long as the item is not dirty and is not the self record. The alternative — normalising an unparsed record into a self-reference so the writer has something well-formed to emit — converts a record you did not understand into a record that is definitively wrong. That principle is the same contract applied to VBA projects and their external references across a load-and-save cycle, and it is the difference between a library that round-trips real-world files and one that round-trips the files its test suite happens to contain. A workbook that has passed through fifteen years of Excel versions, a report generator and two migration tools will contain records nobody currently alive designed. Write them back as you found them

Typed classification of SupBook and XTI records shipped in HotXLS 2.361.2 to 2.361.4, together with bounded XTI resolution and the safer ConvertFormulasToValues path described here. If you maintain Delphi or C++Builder code that reads legacy xls files carrying add-in calls, DDE or OLE links, or external defined names, the HotXLS Delphi spreadsheet component handles the whole taxonomy natively, with no Excel installation and no OLE automation on the machine doing the work