HotXLS 2.376.0 fixed a BIFF record length drift in its classic XLS writer: the SXEx emitter for PivotTable views declared a 24-byte body in its header, then appended 26 bytes. A BIFF reader trusts the declared length, so the two surplus bytes desynchronised everything downstream, and workbooks pairing a PivotTable with a chart sheet lost the chart on reopen
The interesting part is not the off-by-one word. It is the distance between the mistake and the symptom. Nothing failed at the point of the bug. The pivot records serialised cleanly, the file wrote without an error, Excel opened it, and the damage only surfaced hundreds of bytes downstream in a completely unrelated substream. That distance is characteristic of every length-prefixed binary format, and it is worth understanding before you write another emitter for one
Why does one wrong record length destroy an entire worksheet stream?
A BIFF8 workbook stream has no framing beyond its own arithmetic. Every record is a 4-byte header of record id (2 bytes) plus body length (2 bytes), followed by exactly that many payload bytes ([MS-XLS] 2.1.4). There is no separator, no magic byte, no checksum, no resynchronisation point. The reader lands on the next record only because the previous record told it the truth about its own size. The declared length is not metadata about the record; it is the pointer to the next one. So trace what the two surplus bytes did. The reader consumed the SXEx header, skipped the 24 bytes the header promised, and landed two bytes early, on a pair of zeros left over from the oversized body. It read those zeros as a record id of $0000, then read the following worksheet EOF record id ($000A) as that phantom record's length, and dutifully skipped ten bytes into whatever came next. From there every header was read at a wrong offset. In the failing workbook that produced a chart sheet whose _Chart was nil after reopening, and a debug dump showing $18AF being interpreted as a record id. Neither of those values appears anywhere near the pivot code
The emitter and the writer never compare notes
The structural reason the drift was possible is that HotXLS builds a BIFF record as a TXLSBlob whose header and payload are two independent facts. EmitSXEx writes the record id, then Blob.AddWord(24) for the length, then appends the body field by field. That 24 is a hand-counted constant, never derived from or checked against the bytes that follow it. The write path does not close the gap either: AddRec forwards the blob to TXLSBlobList.Append, which copies Data.DataLength bytes verbatim into the output stream. DataLength is the real byte count, so the writer faithfully emits 26 bytes of body behind a header claiming 24. Both halves do exactly what they were told, and the contradiction between them is nobody's job to notice. HotXLS already avoids this where it replays preserved payloads: TXLSWorkbook.StoreDConnBlobs computes its header length word from the actual body length rather than a literal, which is precisely why blob replay has never drifted
What [MS-XLS] 2.4.282 pins down about SXEx
The spec is unambiguous about the size, which is what made the fix mechanical. [MS-XLS] 2.4.282 defines the SXEx body as a 4-byte grbit followed by ten 2-byte fields: csxformat, cchErrorString, cchNullString, cchTag, csxselect, crwPage, ccolPage, cchPageFieldStyle, cchTableStyle and cchVacateStyle. Four plus twenty is twenty-four. The old emitter wrote eleven zero words where the spec defines ten, and the anonymous AddWord(0) calls carried no field names, so counting them by eye during review was exactly as reliable as it sounds. The preallocation was the clue that the layout had been understood and the loop had not: TXLSBlob.Create(28) asks for exactly four header bytes plus a 24-byte body, yet the blob grew past that hint on every single call, and grew silently, because AdjustBufferSize reallocates on demand. A capacity hint the code immediately overruns is worth a second look in any serialiser
function EmitSXEx(Table: TXLSPivotTable; DataList: TXLSBlobList): Integer;
var
Blob: TXLSBlob;
begin
Blob := TXLSBlob.Create(28); // 4-byte header + 24-byte body
Blob.AddWord($00C6);
Blob.AddWord(24);
Blob.AddByte($02);
Blob.AddByte($00); // grbit1 = fPrintTitles
Blob.AddByte($00);
Blob.AddByte($00); // grbit2
// Ten zero words complete the 24-byte body per [MS-XLS] 2.4.282.
// The declared length MUST match the bytes written, or every record
// after this one misparses.
Blob.AddWord(0); // csxformat
Blob.AddWord(0); // cchErrorString
Blob.AddWord(0); // cchNullString
Blob.AddWord(0); // cchTag
Blob.AddWord(0); // csxselect
Blob.AddWord(0); // crwPage
Blob.AddWord(0); // ccolPage
Blob.AddWord(0); // cchPageFieldStyle
Blob.AddWord(0); // cchTableStyle
Blob.AddWord(0); // cchVacateStyle
AddRec(DataList, Blob);
Result := 1;
end;Why did this survive an entire PivotTable test suite?
Because the existing pivot tests never round-tripped through a file. They built a workbook, asserted against the in-memory model, and stopped there, and in-memory assertions cannot see a length mismatch that only exists in the serialised byte stream. The record set covered by writing BIFF8 PivotTable records from Delphi was well tested by that standard and still shipped a stream-corrupting emitter. The defect also needed a second feature to become visible: a pivoted worksheet followed by nothing much still reopened, because the corruption ran off the end of a substream nobody inspected. Only the combination of a PivotTable and a chart sheet, where chart sheets and drawings occupy a substream that follows the worksheet, turned a silent misalignment into a visibly missing object
// PivotChartRoundTripThroughLinkRecords, condensed
Wb.Sheets.Add.Name := 'Report';
Wb.Sheets[2].AddPivotTable('Data!A1:B3', 2, 2, 'SalesPivot');
Wb.Sheets.AddChartSheet('PivotView', TXLSChartType(2), '', '', '',
Series, No3D, PivotInfo);
Assert.AreEqual(1, Wb.SaveAs(TempPath));
Wb.Free;
Wb := TXLSWorkbook.Create;
Wb.Open(TempPath); // the misparse happens here
Model := Wb.Sheets[3]._Chart.GetChartModel;
Assert.IsTrue(Model.IsPivotChart);Before the fix, Wb.Sheets[3]._Chart was nil at that line, because the reader had lost the substream boundary long before it reached the chart BOF. The assertion that finally caught a pivot serialisation bug was an assertion about a chart
How to read a misaligned BIFF stream back to the first bad record
Walk the header chain and print it, because a desynchronised BIFF stream announces itself structurally long before the data looks wrong. Start at the substream BOF ($0809), read id and length, advance by four plus the length, and repeat. While the stream is aligned you land on plausible record ids and the chain terminates exactly on EOF ($000A). Once it drifts, you get ids that do not exist, lengths that overrun the buffer, or a chain that walks straight past where EOF should have been
// Walk a BIFF record stream and stop at the first header that cannot be real
procedure ScanRecords(Buf: PByte; Size: LongWord);
var
Pos: LongWord;
Id, Len: Word;
begin
Pos := 0;
while Pos + 4 <= Size do
begin
Id := PWord(Buf + Pos)^;
Len := PWord(Buf + Pos + 2)^;
// A zero id is never a legal record, and a body that runs past the
// buffer is proof the chain already drifted somewhere upstream.
if (Id = 0) or (Pos + 4 + LongWord(Len) > Size) then
begin
WriteLn(Format('desync at %d: id=$%.4x len=%d', [Pos, Id, Len]));
Break;
end;
WriteLn(Format('%6d id=$%.4x len=%d', [Pos, Id, Len]));
if Id = $000A then
WriteLn('-- EOF, substream ends cleanly --');
Inc(Pos, 4 + LongWord(Len));
end;
end;Then read the output backwards, and hold on to one rule: the first record that fails to parse is almost never the culprit. It is the victim. The culprit is the record immediately before it, the last one that parsed without complaint, because a liar about its own length always parses fine. In this case the walk stopped on a phantom $0000 record, and the record before it was SXEx. Compare that record's declared length against the field list in the spec, byte by byte, and the arithmetic either adds up or it does not. If the walk never even reaches a sane first record, the problem is a layer lower, in the OLE2 compound file that holds the Workbook stream, and no amount of record-level dumping will help
An emitter that cannot lie about its own length
The durable fix is not a correct constant, it is removing the opportunity to write an incorrect one. Reserve the length word, emit the body, then patch the header from the byte count you actually produced. HotXLS exposes what that needs: TXLSBlob.DataLength gives the current offset and SetWord writes back into an already-emitted position
function BeginRecord(Blob: TXLSBlob; RecId: Word): LongWord;
begin
Blob.AddWord(RecId);
Result := Blob.DataLength; // remember where the length word sits
Blob.AddWord(0); // placeholder, patched by EndRecord
end;
procedure EndRecord(Blob: TXLSBlob; LenPos: LongWord);
var
Body: LongWord;
begin
Body := Blob.DataLength - LenPos - SizeOf(Word);
if Body > 8224 then
raise Exception.Create('BIFF body exceeds 8224 bytes, split with Continue');
Blob.SetWord(Word(Body), LenPos);
end;Be honest about where that guarantee stops. A blanket assertion that emitted bytes equal 2 + 2 + declared holds only for records that fit under the BIFF8 limit of 8224 payload bytes. Oversize bodies legitimately declare 8224 in the header and continue in $003C Continue records, which is exactly what the HotXLS pivot cache and connection writers do for large payloads, so the invariant is conditional: below the limit the emitted blob length must equal the declared length plus four, above it the splitter owns the arithmetic instead. Encode that distinction in the helper rather than in a comment. The same reasoning transfers to every tag-length-value format, not just BIFF. An emitter that declares a size before it knows one has written a claim the code cannot check and the reviewer cannot count, and it works right up until a second feature lands downstream of the first
The BIFF8 writer, the pivot record emitters and the chart substream discussed here ship as part of the HotXLS Delphi spreadsheet component for Delphi and C++Builder, which reads and writes XLS, XLSX and ODS without Excel installed