Technical Article

Reading Excel 2.0 to 4.0 Files in Delphi with HotXLS

A workbook saved by Excel 4.0 in 1992 begins with the bytes 09 04 06 00, while every .xls from Excel 5.0 onwards begins with the compound-file signature D0 CF 11 E0. A reader that checks for that signature rejects the older file before reading a single cell, and a reader that skips the check and assumes BIFF8 parses the first record as something it is not. Both failures come from the same assumption: that every .xls has a container. Excel 2.0, 3.0 and 4.0 files have none, so the record stream itself has to say which generation wrote it, and the cell records have to be decoded with that generation's layout

These files still turn up, which is the only reason any of this matters. Engineering archives, records-retention stores, laboratory exports from instruments whose control software dates from the early 1990s, and long-running accounting systems all left BIFF2 to BIFF4 workbooks behind. Current Excel builds block several of these formats by default through their File Block settings, so the data set often sits in a folder that nobody can open with the tools on their desk

How a pre-OLE workbook announces itself

HotXLS recognises a pre-OLE workbook from its first record, inside the same TXLSWorkbook.Open call that loads every other .xls. Every .xls from Excel 5.0 onwards is an OLE2 compound file, a small file system inside a file, with the workbook living in a stream named Workbook or Book; parsing one starts by parsing that container, as described in the compound file binary format in Pascal

BIFF2 through BIFF4 skip all of that. The file starts directly with a BOF record, and the record number of that BOF encodes the generation: $0009 for BIFF2, $0209 for BIFF3 and $0409 for BIFF4. The version word inside the body, which later formats rely on, is ignored by these generations, so the record id is the only trustworthy signal. Before committing to the raw path, the HotXLS stream layer checks two more things: the BOF body must be four to six bytes long, and the substream type word must be $0010 for a worksheet, $0020 for a chart or $0040 for a macro sheet. Anything else makes Open return an error code

Those checks matter more than they look. Two bytes of $09 $00 at offset zero are not rare in arbitrary binary data, and a quarantine folder of unidentified .xls files tends to contain renamed text exports, truncated copies and the occasional file from a different program entirely. Three independent fields all agreeing is a far stronger signal than one

Diagram of how HotXLS detects pre-OLE Excel files from Delphi: modern OLE2 container workbooks on one side, raw BIFF2 to BIFF4 record streams on the other, and the BOF record fields checked before the legacy path is committed
Legacy files carry no storage wrapper, so the leading BOF record itself proves the generation, the body length and the substream type before any cell is read

Three generations, three record layouts

The cell records are where the generations diverge most visibly. BIFF2 occupies a contiguous block of low record numbers, $0001 through $0005, for blank, integer, number, label and boolean-or-error cells. Each of those bodies carries a three-byte attribute field where later versions put a two-byte extended format index, so every value sits one byte further into the record. The BIFF2 INTEGER record is its own oddity: an unsigned 16-bit value with no fractional part, a record type that BIFF3 dropped in favour of RK

BIFF3 and BIFF4 abandon the low numbers and reuse the BIFF5 record ids and layouts: $0201, $0203, $0204, $0205 and $027E, each with a two-byte XF index. That reuse causes a specific and easily misdiagnosed failure. A BIFF3 or BIFF4 LABEL record is structurally identical to its BIFF5 counterpart: row, column, format index, then a two-byte character count at offset 6. Apply that layout to a BIFF2 label and the count is assembled from the last attribute byte and the real one-byte length, so a five-character label claims at least 1,280 characters and the reader runs off the end of the record. Go the other way, BIFF2 layout on a BIFF3 record, and the length is read from the high byte of the count, which is zero for any short string. Nothing throws in either direction. The workbook comes back with empty labels or plausible garbage, and HotXLS avoids both by dispatching on the record id together with the generation taken from the BOF

The BIFF2 attribute field hides one more trap. Its first byte holds the format index in the low six bits, and the value $3F means 'the real index is too large for six bits, look at the preceding IXFE record'. HotXLS tracks the most recent IXFE and resolves the escape before assigning the cell its style, so a cell whose format index needs more than six bits still gets its own formatting

Comparison of BIFF2 and BIFF3/BIFF4 cell record layouts read by HotXLS in Delphi, showing per-generation record numbers, the LABEL field layout difference, the misread failure mode, and parallel formula and string record numbering
BIFF2 bodies carry a three-byte attribute block while BIFF3 and BIFF4 reuse the BIFF5 numbers with a two-byte XF index, and mixing the layouts silently misaligns everything after the record

Text in all three generations is a plain byte string with no Unicode anywhere. The encoding comes from the file's CODEPAGE record, which in these files sits inside the one worksheet stream because there is no workbook-level stream to hold it. HotXLS decodes labels and string results through that code page and falls back to Windows-1252 when the record is absent, which covers the western default most of these files were written with. A Japanese or Central European workbook that lacks a CODEPAGE record will therefore decode as Latin text, and the only fix is to know where the file came from

Opening one from Delphi

Detection happens inside Open, so the calling code is the same as for any workbook. The loop below walks the used range and prints each cell twice over when it holds a formula: the translated expression and the value as Excel would display it through the cell's number format

uses
  SysUtils, Variants, lxHandle;

var
  Book: TXLSWorkbook;
  Sheet: TXLSWorksheet;
  Cell: IXLSRange;
  R, C: Integer;
begin
  Book := TXLSWorkbook.Create;
  try
    try
      if Book.Open('archive\1992-inventory.xls') <> 1 then
      begin
        Writeln('not a readable workbook - quarantine for manual review');
        Exit;
      end;
    except
      // Open raises when the file is really a ZIP package,
      // typically an .xlsx that someone renamed to .xls
      on E: Exception do
      begin
        Writeln('rejected: ', E.Message);
        Exit;
      end;
    end;

    Sheet := Book.Sheets[1];          // Sheets[] is 1-based
    for R := Sheet.UsedRange.FirstRow + 1 to Sheet.UsedRange.LastRow + 1 do
      for C := Sheet.UsedRange.FirstCol + 1 to Sheet.UsedRange.LastCol + 1 do
      begin
        Cell := Sheet.Cells[R, C];
        if VarIsEmpty(Cell.Value) or VarIsNull(Cell.Value) then
          Continue;
        if Cell.HasFormula then
          Writeln(Format('R%dC%d  %s  -> %s',
            [R, C, VarToStr(Cell.Formula), Cell.FormattedText]))
        else
          Writeln(Format('R%dC%d  %s', [R, C, Cell.FormattedText]));
      end;
  finally
    Book.Free;
  end;
end;

Watch the index arithmetic. UsedRange bounds are zero-based while both the sheet collection and cell access are one-based, an inconsistency the API keeps for compatibility with existing code. Forget the adjustment and the loop audits the wrong rectangle without reporting anything unusual. The files this path accepts carry a single worksheet, chart or macro-sheet substream, so Sheets[1] is the only sheet you will find; an Excel 4.0 bound workbook (BOF type $0100) falls outside that set and Open returns an error code. Cheap pre-checks that avoid loading a file at all are covered in lightweight workbook inspection

Password-protected files follow the same single-stream rule. The FILEPASS record appears right after the BOF in the worksheet stream, where HotXLS picks it up and switches on the legacy XOR deobfuscation these generations used; pass the password through the Open(FileName, APassWord) overload

What happens to formulas in a BIFF2 to BIFF4 file?

HotXLS reads both halves of a legacy formula: the cached result Excel stored when it last calculated, and the token stream, which is translated into the current calculation model during load. Formula records share a parallel numbering across the three generations, $0006, $0206 and $0406, but the bodies differ in exactly the places a BIFF8 parser would trip over

The cached result starts at byte 7 in BIFF2, because of the attribute field, and at byte 6 in BIFF3 and BIFF4. Non-numeric results use a marker word of $FFFF in the last two bytes of that eight-byte slot, with a discriminator in the first byte: 0 for a string, 1 for a boolean, 2 for an error code, 3 for a blank. A string result does not fit in the slot at all, so the text arrives in the next record, $0007 in BIFF2 with a one-byte length or $0207 in BIFF3 and BIFF4 with a two-byte length

Anatomy of the cached formula result in BIFF2 to BIFF4 FORMULA records read by HotXLS from Delphi: the value starts at byte 7 in BIFF2 and byte 6 in BIFF3 and BIFF4, with a FFFF marker word for strings, booleans, errors and blanks
HotXLS keeps the value Excel last calculated next to the translated expression, and non-numeric results arrive as a marker word plus a discriminator

The token stream is where a naive implementation produces nonsense. BIFF2 prefixes the expression with a one-byte length at offset 16 and starts the tokens at 17; BIFF3 and BIFF4 use a two-byte length and start at 18. Inside the stream, function tokens carry a one-byte function index before BIFF5 and a two-byte index from BIFF5 on. Feed BIFF4 tokens to a BIFF8 translator unchanged and every function call consumes one byte too many, so the result is not a slightly wrong formula but an unrelated one. The HotXLS translator takes the BIFF version as a parameter and reads each token with that generation's widths

Having both halves raises a practical question: which number goes into the migrated workbook? The cached value is what the file's users saw, printed and signed off on in 1992. A recalculation on a modern engine computes what the formula means today, and function behaviour has not stood still for three decades. For an archive migration the cached value is the record, so keep it and run a recalculation only as a cross-check that tells you which cells deserve a human look:

// Cross-check: compare stored results with a fresh recalculation.
// This helper and its CachedText array are your own code
procedure CompareWithRecalc(Book: TXLSWorkbook; Sheet: TXLSWorksheet);
var
  CachedText: array of array of string;
  Rows, Cols, R, C: Integer;
begin
  Rows := Sheet.UsedRange.LastRow + 1;
  Cols := Sheet.UsedRange.LastCol + 1;
  SetLength(CachedText, Rows + 1, Cols + 1);
  for R := 1 to Rows do
    for C := 1 to Cols do
      if Sheet.Cells[R, C].HasFormula then
        CachedText[R, C] := VarToStr(Sheet.Cells[R, C].Value);

  Book.Recalculate;

  for R := 1 to Rows do
    for C := 1 to Cols do
      if Sheet.Cells[R, C].HasFormula and
         (VarToStr(Sheet.Cells[R, C].Value) <> CachedText[R, C]) then
        Writeln(Format('R%dC%d  stored=%s  now=%s  formula=%s',
          [R, C, CachedText[R, C], VarToStr(Sheet.Cells[R, C].Value),
           VarToStr(Sheet.Cells[R, C].Formula)]));
end;

Run this on a copy, or before the save, since Recalculate replaces the stored results in memory. An empty report is the outcome you want; a short one usually points at volatile functions such as NOW() or at references the original author left dangling

Formatting, dates and the General-format trap

HotXLS converts the formatting records of these generations into the same workbook model a modern .xls produces. FONT records (with the separate BIFF2 FONTCOLOR record folded in), the FORMAT strings, and the XF records of each generation, with their fill patterns, border styles and legacy colour indexes, become ordinary fonts, number formats and cell styles that you can read and edit through Font, NumberFormat and the other range properties

Dates benefit directly. When a numeric cell carries a date number format, the reader also applies the workbook's DATEMODE flag, so a 1904-system workbook lands on the same TDateTime scale as a 1900-system one, and FormattedText shows the date the way Excel did. The rules behind the 1462-day offset are covered in date serial numbers, the 1904 system and number formats

The trap is the column that holds dates under a General format. Plenty of 1990s workbooks were filled by macros or by imports that wrote serial numbers without bothering to format them, and the file gives no signal that 33604 means a day in January 1992 rather than a part number. Those cells come back exactly as stored, uncorrected for the date system. Decide per column, from what you know about the source, and convert those columns yourself:

// ColumnHoldsDates is your own lookup, built from knowledge of the
// source system; the legacy file cannot answer this question for you.
// Serial is a local Double; Cell and C come from the first listing
if ColumnHoldsDates(C) and (Pos('d', LowerCase(Cell.NumberFormat)) = 0) then
begin
  // General-formatted serial: no date system correction applied yet
  Serial := Cell.Value;
  if Book.Date1904 then
    Serial := Serial + 1462;
  Writeln(DateToStr(TDateTime(Serial)))
end
else
  Writeln(Cell.FormattedText);

The Pos('d', ...) test is deliberately crude and meant only to skip cells that already carry a day token in their format; a stricter check against your own list of known date formats is worth the extra lines if the archive is large

Writing the migrated copy

A workbook opened from a BIFF2 to BIFF4 file is saved through the BIFF5/BIFF8 or XLSX writers, since there is no reason to produce new files in a format that current Excel blocks. Book.SaveAs('migrated\1992-inventory.xls', xlExcel97) writes a BIFF8 copy that carries the values, the translated formulas and the converted styles, and every later tool in the pipeline can open it without special handling. Keep the original next to it: if a cell turns up a question two years from now, the 1992 bytes are the only authority

Legacy reading, modern writing, recalculation and formatting all live in one library for Delphi and C++Builder; the format coverage is listed on the HotXLS Delphi spreadsheet component page