Bijna elk onderdeel van het legacy binaire Excel-formaat is een enkel record met een helder type van twee bytes en een lengte van twee bytes. Een cel is een LABELSST of een NUMBER. Een samengevoegd gebied is een MERGEDCELLS. U kunt het grootste deel van een werkblad lezen door de records één voor één te doorlopen en te routeren op basis van het type. PivotTables doorbreken dat ritme. Een enkele draaitabel is geen record, maar een klein programma dat bestaat uit tientallen samenwerkende records die zijn verdeeld over twee verschillende locaties in dezelfde stream van het OLE compound document. De relaties daartussen zijn positioneel, bit-packed en onbarmhartig. Dit is de structuur die de meeste BIFF8-lezers ofwel volledig overslaan, ofwel behouden als ondoorzichtige bytes, omdat het volledig zelf schrijven ervan vereist dat u elke kruisverwijzing reproduceert die Excel zelf bijhoudt.
De reden waarom een draaitabel complex is, is dat deze eigenlijk bestaat uit twee samengevoegde artefacten. Er is de pivot-cache, een op zichzelf staande momentopname van de brongegevens met een eigen substream, en er is de tabelweergave, de lay-out die bepaalt welke velden op welke as staan. De cache en de weergave verwijzen naar elkaar via een index. Als u één index verkeerd invoert, opent het bestand met een vernieuwingsfout of een leeg raster.
De pivot-cache is een eigen substream
De cache bevindt zich in de globals-stream van het werkboek als een complete BIFF-substream, ingekaderd door een BOF-record waarvan het documenttype is 0x0006 (de waarde die een pivot-cache markeert, in tegenstelling tot 0x0005 voor het werkboek of 0x0010 voor een werkblad) en afgesloten door de bijbehorende EOF. Binnen dat kader ligt de structuur vast. Een SXDB-record is de cache-header. Dit bevat het aantal records, het aantal cachevelden en de stream-identificatie die de tabelweergave zal gebruiken om zich aan deze cache te koppelen. Elke bronkolom levert vervolgens een SXFDB-velddefinitierecord, gevolgd door een SXFDBType die het classificeert, en ten slotte de unieke waarden die die kolom aannam, gegenereerd als één getypeerd itemrecord per unieke waarde.
De itemrecords zijn de plek waar de cache zijn nut bewijst. Een tekstwaarde wordt een SXSTRING, een numerieke waarde een SXNUM, een logische waarde een SXBOOLEAN en een formulefout een SXERR. De cache slaat niet het bronraster op, maar de unieke waarden per veld plus een index-tabel die voor record n aangeeft welk uniek item elk veld aannam. Daarom is het programmatisch bouwen van een draaitabel niet simpelweg een kwestie van cellen kopiëren. U moet het bronbereik scannen, het type van elk veld afleiden uit de waarden die het bevat, deze ontdubbelen in een getypeerde itemlijst en elke rij vastleggen als een tupel van item-indexen. HotXLS doet exact dit: een volledig numerieke kolom wordt gegenereerd met SXNUM-items, een gemengde tekstkolom wordt SXSTRING-items en datums worden als seriële waarden via hetzelfde numerieke pad verwerkt.
SXDBB en de bit-packing die het interessant maakt
De index-tabel per record is technisch gezien het meest opmerkelijke deel van de gehele structuur, en bevindt zich in het SXDBB-record. De naïeve codering zou de item-index van elk veld opslaan als een 16-bits woord. Excel doet dat niet. Het pakt de index van elk veld in exact het aantal bits in dat nodig is om de items van dat veld te adresseren, en niets meer. De breedte bedraagt ceil(log2(itemCount + 1)) bits. De + 1 is belangrijk: de extra waarde is een sentinel die "leeg, geen waarde voor dit veld in dit record" betekent. Een veld met drie unieke items moet dus vier toestanden kunnen vertegenwoordigen en neemt daarom twee bits in beslag, in plaats van de ene bit die drie items alleen zouden suggereren. Een veld zonder items levert nul bits op en wordt volledig overgeslagen tijdens het inpakken.
// Width of one field's index in the SXDBB stream.
// citmTotal distinct items need ceil(log2(citmTotal + 1)) bits,
// the +1 reserving a "blank" sentinel value.
function BitsForFieldItems(itemCount: Integer): Integer;
var
capacity: Integer;
begin
Result := 0;
if itemCount <= 0 then
Exit; // empty field contributes zero bits
Result := 1;
capacity := 2;
while capacity < itemCount + 1 do
begin
Inc(Result);
capacity := capacity * 2;
end;
end;De reden waarom dit detail niet kan worden genegeerd, is het plafond van 8224 bytes voor een enkel BIFF-record. Elk record in dit formaat, inclusief pivot-records, moet zijn payload in maximaal 8224 bytes passen. Een actieve pivot-cache met duizenden bronrijen zal die grens ruim passeren voordat alle rijen zijn weggeschreven. De index-tabel is daarom opgesplitst. HotXLS begrenst het lichaam van een enkel SXDBB-record op 8220 bytes (de recordlimiet van 8224 minus de vier bytes voor de header met type en lengte), deelt dat door de bytebreedte van één ingepakt record om te bepalen hoeveel volledige rijen erin passen, en genereert vervolgens zoveel vervolg-SXDBB-records als het aantal rijen vereist. Elk vervolg start netjes op een recordgrens, zodat er nooit een rij over twee records wordt gesplitst. Een lezer die de bitbreedte per record kent, kan door alle opeenvolgende SXDBB-records lopen alsof het één aaneengesloten bit-array betreft.
De weergavelay-out: SXLI voor het lichaam, SXPI voor de pagina
Zodra de cache is gebouwd, vormt de tabelweergave de tweede helft. De kern hiervan bestaat uit de as-regelitems (axis line items), de rijen van het pivot-lichaam die elke combinatie van rijveld- en kolomveldwaarden weergeven die de tabel tekent. Deze bevinden zich in SXLI-records (recordtype 0x00B5, beschreven in [MS-XLS] §2.4.275). Eén SXLI bevat veel regels, wederom totdat de limiet van 8224 bytes dwingt tot een nieuw record. Het maakt gebruik van een compressietruc: elke regel slaat alleen op waarin deze verschilt van de regel erboven, uitgedrukt als een gemeenschappelijke prefix-telling, zodat een diep geneste as niet de buitenste veldwaarden op elke rij herhaalt. De eindtotaalregel en de eerste regel van elk record zetten die prefix-telling altijd terug naar nul, zodat een lezer nooit over een recordgrens hoeft terug te kijken om een regel te reconstrueren.
De paginas-as, de filter-dropdowns die zich boven een draaitabel bevinden, is een afzonderlijk record. SXPI (recordtype 0x00B6, [MS-XLS] §2.4.276) bevat één vermelding van tien bytes per paginaveld: de pivotveld-index isxvd, het geselecteerde cache-item iCache, een positiewoord ipos en een legacy object-id objId. De waarde van iCache is belangrijk. Een paginaveld dat "(Alles)" toont en niets filtert, slaat de sentinel 0x7FFD op in plaats van een echte item-index. Een programmatisch opgebouwde pivot opent met elk paginaveld ingesteld op "(Alles)" totdat de aanroeper vooraf een item selecteert. Op dat moment vervangt de cache-index van dat item de sentinel en opent Excel met het filter al toegepast. Daarnaast zijn er de ondersteunende records die individuele velden en hun opmaak beschrijven: SXVD en SXVDEx voor veldweergavedefinities, SXIVD voor de veldindexlijsten die elke as ordenen, en SXFormat voor getalopmaak, die elk terugverwijzen naar dezelfde cache waarnaar de lichaamsregels verwijzen.
Twee schrijvers in één: ruwe blobs en het getypeerde model
Er is een structurele reden waarom HotXLS twee volledig gescheiden paden aanhoudt voor het schrijven van een draaitabel, en die komt voort uit de eis voor getrouwheid (fidelity). Wanneer een werkboek van schijf wordt gelezen, zijn de pivot-records geschreven door Excel of een andere applicatie. Ze kunnen recordvarianten, afwijkende volgorden of extensierecords bevatten die geen enkele externe schrijver volledig modelleert. Het enige veilige dat u met die bytes kunt doen, is ze ongewijzigd teruggeven. Een draaitabel die uit een bestand afkomstig is krijgt daarom de vlag FromRawBlobs = True, en bei het opslaan speelt de schrijver de bewaarde record-blobs letterlijk af. Niets wordt opnieuw gegenereerd of geïnterpreteerd, waardoor het opslaan byte-stabiel is.
Een draaitabel die door het programma zelf is opgebouwd is het tegenovergestelde geval. Er zijn geen originele bytes om te bewaren, alleen het getypeerde object model: een TXLSPivotCache met zijn velden en itemlijsten, en een TXLSPivotTable met zijn astoewijzingen. Die tabel krijgt de vlag FromRawBlobs = False, en de schrijver serialiseert deze handmatig. Hij genereert een nieuwe BOF = 0x0006 cache-substream, pakt de SXDBB index-tabel in op basis van de item-indexen in het model en bouwt de SXLI- en SXPI-records op uit de asconfiguratie. Deze vlag zorgt ervoor dat beide typen in één werkboek kunnen coexisteren. Zonder dit zou een schrijver ofwel de getrouwheid van ingelezen tabellen moeten opofferen, ofwel weigeren nieuwe te genereren. Eventuele producenten-specifieke extensierecords van een ingelezen tabel worden bewaard als aanvullende records (bereikbaar via de lijst SupplementalRecords), zodat een tabel die via het model wordt geïnspecteerd de onderdelen die het model niet beschrijft niet verliest.
Een draaitabel bouwen in code
Al de bovenstaande logica bevindt zich achter één enkele aanroep. AddPivotTable accepteert het bronbereik in A1-notatie, de doelcel waar de linkerbovenhoek van de tabel wordt verankerd, en een naam. Het parseert het bereik, scant dit om veldtypen af te leiden en de cache te bouwen (waarbij een bestaande cache wordt hergebruikt als een andere tabel al aan hetzelfde bereik is gekoppeld) en retourneert een getypeerde TXLSPivotTable met één veld per bronkolom, waarbij elk veld in eerste instantie buiten de assen staat. Vervolgens plaatst u velden op de assen en kiest u een aggregatie. De signatuur is exact als volgt, en de cache, de SXDBB-inpakking en de weergaverecords worden bij het opslaan automatisch voor u gegenereerd:
uses
lxHandle, lxPivot;
var
Book : TXLSWorkbook;
Sheet: IXLSWorkSheet;
Pivot: TXLSPivotTable;
begin
Book := TXLSWorkbook.Create;
try
Book.Open('Sales.xls');
Sheet := Book.Sheets[1];
// Source A1:E500 on 'Data'; anchor the pivot at row 3, col 1.
Pivot := Sheet.AddPivotTable('Data!$A$1:$E$500', 3, 1, 'SalesByRegion');
if Pivot <> nil then
begin
Pivot.AddRowField('Region');
Pivot.AddColumnField('Quarter');
Pivot.AddDataFieldByName('Revenue', xlpaSum);
end;
Book.SaveAs('Sales-Pivot.xls');
finally
Book.Free;
end;
end;De eerste rij van het bronbereik wordt gelezen als de header die de cachevelden benoemt, zodat AddRowField('Region') een kolom zoekt op basis van de headertekst in plaats van de positie. Omdat de geretourneerde tabel een model is met FromRawBlobs = False, de schrijver kiest het pad vanaf nul: hij bouwt een op zichzelf staande cache die er niet van afhankelijk is dat het bronbereik nog aanwezig is op het moment van vernieuwen. Dit is exact de eigenschap die u wilt wanneer de draaitabel wordt verzonden naar een ontvanger die de onderliggende gegevens kan verplaatsen of verwijderen.
Reading and reconciling the pivot and cache records of a file you did not produce, including the raw-blob preservation path, is covered in the workbook audit and conversion workbench walkthrough. When the source range runs to tens of thousands of rows and the SXDBB stream spans many continued records, the techniques in the large-workbook performance notes keep the cache build from dominating your runtime. Both pair with the pivot writer that ships in the HotXLS spreadsheet component for Delphi and C++Builder, alongside the cell, formula, chart, and formatting APIs covered elsewhere on this blog.