The BIFF PivotCache substream stores a PivotTable cached dataset separately from the view that displays it, and HotXLS reads and writes that substream by inspecting record bodies rather than trusting record numbers. That distinction is the whole story: the same record number carries two incompatible body layouts depending on which writer produced the file, so the reader decides the framing from the first record body it sees
You meet this layer the moment a PivotTable has to survive a round trip. A pivot view without its cache is a shell, and Excel will rebuild the cache from the source range when it opens the file, which is fine right up to the point where the source range is gone, the data was pasted from a query, or the workbook is an archived close that must not change when someone opens it
Two structures, two places in the file
The cached data and the cache definition live in different parts of the workbook, and conflating them is the first thing to get right. The cached records form their own substream, given in [MS-XLS] §2.1.7.12 as PIVOTCACHE = SXDB SXDBEx *SXFORMULA *FDB *DBB EOF. Note what is absent: there is no BOF at the head of that production
The definition sits in the workbook globals instead, as PIVOTCACHEDEFINITION = SXStreamID SXVS [SXSRC] [SXADDLCACHE] (§2.1.7.20.3), positioned after the formatting records and before the BoundSheet and Country records. So a single cache is described in two places that are hundreds of records apart, and the link between them is a stream identifier that has to agree in three locations at once
Each cache belongs in a stream under _SX_DB_CUR whose name is the four-digit uppercase hexadecimal spelling of its identifier. SXStreamID.idStm, the idstm field repeated in the SXDB header, and that stream name must all match. When you allocate a new identifier, reserve every number already read from the file first, or a new cache can claim a number that belongs to an older cache the reader has not walked to yet
One more identifier catches people. The iCache value in a pivot view is the zero-based position of the corresponding SXStreamID in the global sequence, not a cache identifier you get to choose. On write it has to be mapped from the cache object to its actual output position, and existing views have to be renumbered along with it, or upgrading one cache silently points a view at a different one
var
Book: TXLSWorkbook;
Cache: TXLSPivotCache;
Field: TXLSPivotCacheField;
V: TXLSPivotCacheValue;
begin
Book := TXLSWorkbook.Create(nil);
try
Book.LoadFromFile('sales.xls');
Cache := Book.PivotCaches.Add;
Cache.SourceRangeSheet := 'Data';
Cache.SourceFirstRow := 1; Cache.SourceFirstCol := 1;
Cache.SourceLastRow := 500; Cache.SourceLastCol := 6;
Cache.SourceDataType := 1; // SXVS SHEET, MS-XLS 2.4.317
Cache.RefreshOnLoad := False; // trust the cached records
Cache.SaveData := True;
Field := Cache.AddField('Region', xlpcftString);
V.ValueType := xlpcftString;
V.StrValue := 'North';
Field.FindOrAddItem(V);
Cache.SetRecordCount(0); // clear, then size the record grid
Cache.SetRecordCount(500);
Book.StorePivotCaches;
finally
Book.Free;
end;
end;The double SetRecordCount is not superstition. RecordCount is a plain property write that does not allocate, and the internal growth path only initializes the newly added rows, so a cache whose count was set through the header path can end up with a zero-length index grid. Writes to RecordIndices are then discarded without error. Setting the count to zero and back re-establishes the grid, and it has to happen after every field has been added, because the row width comes from the field count
Why can a record number not tell you the body layout?
Because record numbers and body layouts changed at different times, so the mapping between them is not a function. One number in the legacy set only ever appears in files from older writers, which makes it a reliable signal in one direction. Another number is genuinely ambiguous: it appears both in correct files and in a range of intermediate versions that used the new number with the old body layout
The framing therefore has to be decided from the body, and once per cache substream rather than per record. HotXLS latches the dialect from the length of the first SXDBB record in each substream. In the specification framing, one SXDBB holds exactly one cache record, so its length equals one row width. In the older packed framing, the first record holds as many rows as fit, so for any cache with more than one row it is at least two row widths. The comparison is decisive whenever the two predictions differ
When they do not differ, the reader takes the specification reading, on the principle that files written by Excel outnumber files written by an intermediate build. That blind spot is narrow by construction and, when it does occur, the file itself still replays byte for byte. Only the typed indices exposed to callers are affected
The index width lives in a different record
SXDBB (§2.4.276) carries one index per cache field whose distinct-value flag is set, in field order, and the width of each index is decided elsewhere: the corresponding SXFDB field record (§2.4.283) declares a short-items flag, and that flag says whether the index occupies two bytes or one. Two records, one implied contract, and a single sentence in the specification connecting them
That coupling is exactly where a home-grown encoding goes wrong. An earlier HotXLS writer packed each field into the minimum number of bits, padding to a byte boundary between rows, which is defensible in isolation and directly contradicts the width the same writer had just declared in SXFDB. A field with three distinct values was described as one byte wide in one record and occupied two bits in the other. The fix was not to correct the arithmetic but to extract the width decision into one function that both emitters call, so the two records can no longer drift apart. That is the same class of defect described in BIFF record length declaration drift, where a declared size and an actual body part company
The consequence of not reading these records at all is worth spelling out, because it is easy to underestimate. When the reader skipped the record indices, every cache loaded from a file reported index zero for every field of every row, which means every row pointed at the first value of every field. That is not merely reduced introspection: the pivot evaluation path and the cache-to-cell fill path both consume that grid. And a round-trip test cannot detect it, because a cache still on raw replay is written back from its original bytes
// Provenance flags tell you what you are holding and what may be rewritten
if Cache.FromRawBlobs then
begin
Writeln('stream id : ', IntToHex(Cache.StreamId, 4));
Writeln('legacy framing : ', Cache.RawFramingIsLegacy);
Writeln('own storage : ', Cache.RawHasStorageStream);
Writeln('model complete : ', Cache.RawModelIsComplete);
// Re-emitting is only lossless when every record has a model here
if Cache.CanUpgradeFraming then
Writeln('safe to rewrite with the current emitters');
end;When is rewriting a cache lossless?
Only when three conditions hold together, and CanUpgradeFraming is the single property that answers the question. The cache must still be on raw replay, the substream must be in one of the framings this library previously wrote incorrectly, and the reader must have built a complete typed model of every record in it. A cache Excel wrote never qualifies, because its substream carries records HotXLS has no model for, and re-emitting from the model would drop them
The completeness test is stricter than it first appears. A record the reader kept only as opaque bytes marks the model incomplete. So does a declared count of formula records that the emitter cannot reproduce, because re-emitting would rewrite a declaration of several formula records as a declaration of none, and a value in the file that cannot be reproduced is equivalent to a record that cannot be reproduced
Deliberate conservatism runs through the writer too. Indices are clamped into the legal range rather than encoded as an out-of-band sentinel, because the specification defines an index into the distinct-value sequence and nothing else, and an empty cell is itself a value in that sequence. A cache record body exceeding the BIFF record ceiling is not written at all, which would require thousands of cache fields and is unreachable within the BIFF8 column limit anyway; the fallback is that Excel refreshes from the source range, which is defined behavior rather than a corrupt file
Dates carry the last cross-record dependency. The serial-to-date conversion depends on the workbook date system, and the record emitter cannot see the workbook, so the base-date choice is passed in as a parameter that defaults to the 1900 system and is supplied by the workbook-level save path. Under the 1900 system the serial number is the value directly; the 1904 system differs by 1462 days. The wider treatment of date serials is in date serials, the 1904 system and number formats
If you are working at the view layer rather than the cache layer, the records that describe the visible pivot are covered in the BIFF8 PivotTable record set, and the calculation-side behavior in calculated fields, calculated items and refresh. All three layers ship in the HotXLS Delphi spreadsheet component, which is what makes it possible to load a legacy workbook, inspect what its cache actually contains, and decide whether rewriting it is safe before you do it