Nästan varje del av det äldre binära Excel-formatet är en enskild post med en ren tvåbytes typ och en tvåbytes längd. A cell är en LABELSST eller en NUMBER. En sammanslagen region är en MERGEDCELLS. Du kan läsa det mesta av ett kalkylblad genom att gå igenom poster en i taget och utföra åtgärder baserat på typordet. PivotTabeller bryter den rytmen. En enskild pivottabell är inte en post, det är ett litet program som består av dussintals samarbetande poster spridda över två olika platser i samma OLE-sammansatta dokumentström, och relationerna mellan dem är positionella, bit-packade och oförlåtande. Detta är strukturen som de flesta BIFF8-läsare antingen hoppar över helt eller bevarar som opaka bytes, eftersom att skriva en från grunden innebär att reproducera varje korsreferens som Excel självt upprätthåller
Anledningen till att en pivottabell är svår är att det egentligen är två artefakter som är svetsade samman. Det finns en pivot-cache, en fristående ögonblicksbild av källbladsdata med en egen underström, och det finns en tabellvy, layouten som anger vilka fält som sitter på vilken axel. Cachen och vyn refererar till varandra via index. Blir ett index fel öppnas filen med ett uppdateringsfel eller ett helt tomt rutnät
Pivot-cachen är en helt egen underström
Cachen lever i arbetsbokens globala ström som en komplett BIFF-underström, inramad av en BOF-post vars dokumenttyp är 0x0006 (värdet som markerar en pivot-cache, till skillnad från 0x0005 för arbetsboken eller 0x0010 för ett kalkylblad) och stängd av matchande EOF. Inuti den ramen är strukturen fast. En SXDB-post är cache-huvudet. Det bär på postantalet, antalet cache-fält och strömidentifieraren som tabellvyn kommer att citera för att binda sig till denna cache. Varje källkolumn bidrar sedan med en SXFDB-fältdefinitions-post följd av en SXFDBType som klassificerar den, och sedan de unika värden som den kolumnen tog, utskrivna som en typad elementpost per unikt värde
Elementposterna är där cachen gör nytta. Ett textvärde blir en SXSTRING, ett numeriskt värde en SXNUM, ett logiskt värde en SXBOOLEAN och ett formelfel en SXERR. Cachen lagrar inte källrutnätet, den lagrar de unika värdena per fält plus en indextabell som anger, för post n, vilket unikt element varje fält tog. Det är därför att bygga en pivottabell programmatiskt inte handlar om att kopiera celler. Du måste skanna källintervallet, härleda varje fälts typ från värdena det innehåller, deduplicera dem till en typad elementlista och registrera varje rad som en tupel av elementindex. HotXLS gör exakt detta: en helt numerisk kolumn skrivs ut med SXNUM-element, en blandad textkolumn blir till SXSTRING-element och datum transporteras som löpande serienummer via samma numeriska sökväg
SXDBB och bitpackningen som gör det intressant
Indextabellen per post är den enskilt mest tekniskt intressanta delen av hela strukturen, och den lever i SXDBB-posten. Den naiva kodningen skulle spara varje fälts elementindex som ett 16-bitars ord. Excel gör inte så. Det packar varje fälts index i exakt det antal bitar som krävs för att adressera det fältets element, och inte mer. Bredden är ceil(log2(itemCount + 1)) bitar. + 1 spelar roll: det extra värdet är en vaktpost som betyder "tomt, inget värde för detta fält i denna post", så ett fält med tre unika element måste representera fyra tillstånd och tar därför två bitar, inte den enda bit som tre element enbart skulle antyda. Ett fält utan några element alls bidrar med noll bitar och hoppas över helt under packningen
Bitarna för en post konkateneras över alla fält, varpå nästa post börjar på en ny bytegräns. Poster är bytejusterade, inte bitpackade ände till ände, vilket gör slumpmässig åtkomst till tabellen hanterbar på bekostnad av några fyllnadsbitar per rad. Packningen inuti en byte görs med den minst signifikanta biten först. När du väl har accepterat dessa två regler är kodaren en enkel bitpump, och avkodaren är dess spegel
// 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;Anledningen till att denna detalj inte kan ignoreras is taket på 8224 bytes för en enskild BIFF-post. Varje post i formatet, pivotposter inkluderade, måste rymma sin nyttolast i högst 8224 bytes, och en aktiv pivot-cache med tusentals källrader kommer att spränga den gränsen långt innan den har skrivt ut varje rad. Så indextabellen delas upp. HotXLS begränsar en enskild SXDBB-kropp till 8220 bytes, vilket är postgränsen på 8224 minus posthuvudet på fyra bytes för typ och längd, delar detta med bytebredden för en packad post för att få veta hur många hela rader som får plats, och skriver sedan ut så många fortsatta SXDBB-poster som radantalet kräver. Varje fortsättning startar rent på en postgräns, så ingen rad klipps någonsin av över två poster. En läsare som känner till bitbredden per post kan stega igenom varje SXDBB i sekvens som om de vore en enda sammanhängande bit-array
Vy-layouten: SXLI för kroppen, SXPI för sidan
Med cachen byggd är tabellvyn den andra halvan. Dess kärna är axellinjeposterna, raderna i pivotkroppen som räknar upp varje kombination av radfälts- och kolumnfältsvärden som tabellen ritar. Dessa transporteras i SXLI-poster (posttyp 0x00B5, beskriven i [MS-XLS] §2.4.275). En SXLI rymmer många linjer, återigen tills gränsen på 8224 bytes tvingar fram en ny post, och den använder ett litet komprimeringsknep: varje linje lagrar endast hur den skiljer sig från linjen ovanför, uttryckt som ett gemensamt prefixantal, så en djupt nästlad axel inte upprepar de yttre fältvärdena på varje rad. Den totala summan och den första raden i valfri post återställer alltid prefixantalet till noll så att en läsare aldrig behöver titta tillbaka över en postgräns för att återskapa en linje
Sidaxeln, filterrullgardinsmenyerna som sitter ovanför en pivottabell, är en separat post. SXPI (posttyp 0x00B6, [MS-XLS] §2.4.276) bär en tiobytes post per sidfält: pivotfältindexet isxvd, det valda cache-elementet iCache, ett positionsord ipos och ett äldre objekt-id objId. Värdet iCache är det man ska hålla koll på. Ett sidfält som visar "(Alla)", vilket inte filtrerar någonting, lagrar vaktposten 0x7FFD snarare än ett verkligt elementindex. En programmatiskt byggd pivot öppnas med varje sidfält inställt på "(Alla)" tills anroparen i förväg väljer ett element, varvid det elementets cache-index ersätter vaktposten och Excel öppnas med filtret redan tillämpat. Vid sidan av dessa sitter stödposterna som beskriver enskilda fält och deras formatering, SXVD och SXVDEx för fältvydefinitioner, SXIVD för fältindexlistorna som sorterar varje axel samt SXFormat för talformatering, där var och en indexerar tillbaka till samma cache som kroppsraderna refererar till
Två skrivare i en: råa blobar och den typade modellen
Det finns en strukturell anledning till att HotXLS behåller två helt separata vägar för att skriva en pivottabell, och det kommer direkt från kraven på trohet. När en arbetsbok läses från disken skrevs dess pivotposter av Excel eller av någon annan producent, och de kan använda postvarianter, ordningsavvikelser eller utökade poster som ingen tredjepartsskrivare helt modellerar. Det enda säkra att göra med dessa bytes är att ge dem tillbaka oförändrade. Så en pivottabell som kom in från en fil flaggas med FromRawBlobs = True, och vid sparning spelar skrivaren upp de bevarade postblobarna ordagrant. Ingenting återskapas, ingenting omtolkas, och en sparrunda genom öppning och sparning är byte-stabil
En pivottabell som programmet har byggt är det motsatta fallet. Det finns inga originalbytes att bevara, endast den typade objektmodellen: en TXLSPivotCache med dess fält och elementlistor, och en TXLSPivotTable med dess axeltilldelningar. Den tabellen flaggas med FromRawBlobs = False, och skrivaren serialiserar den den svåra vägen, genom att generera en ny BOF = 0x0006-cacheunderström, packa SXDBB-indextabellen från elementindexen som den typade modellen håller, samt lägga ut SXLI- och SXPI-posterna från axelkonfigurationen. Flaggan är vad som gör att båda sorterna kan samexistera i en arbetsbok. Utan den skulle en enskild skrivare antingen behöva förkasta troheten hos inlästa tabeller eller vägra att generera nya. Alla tillverkarspecifika utökade poster som en inläst tabell bar på behålls som kompletterande poster, nåbara via tabellens SupplementalRecords-lista, så att en tabell som inspekteras via den typade modellen inte förlorar de delar som modellen inte beskriver
Att bygga en pivottabell i kod
Hela maskineriet ovan sitter bakom ett enda anrop. AddPivotTable tar källintervallet i A1-notation, målcellen där tabellens övre vänstra hörn förankras, samt ett namn. Det tolkar intervallet, skannar det för att härleda fälttyper och bygga cachen (och återanvänder en befintlig cache om en annan tabell redan binder till samma intervall), och returnerar en typad TXLSPivotTable med ett fält per källkolumn, där varje fält initialt ligger utanför axeln. Du placerar sedan fält på axlarna och väljer en aggregering. Signaturen är exakt denna, och cachen, SXDBB-packningen och vy-posterna skapas alla åt dig vid sparningstillfället
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;Den första raden i källintervallet läses som rubriken som namnger cache-fälten, så AddRowField('Region') matchar en kolumn med dess rubriktext snarare än efter position. Eftersom den returnerade tabellen är en typad modell med FromRawBlobs = False, tar skrivaren sökvägen från grunden: den bygger en fristående cache som inte beror på att källintervallet fortfarande är närvarande vid uppdateringstillfället, vilket är exakt den egenskap du vill ha när pivoten ska skickas till en mottagare som kan flytta eller radera underliggande data
Att läsa och stämma av pivot- och cache-poster för en fil du inte har producerat, inklusive bevarande-sökvägen för råa blobar, beskrivs i genomgången av arbetsbokens granskning och konvertering. När källintervallet uppgår till tiotusentals rader och SXDBB-strömmen sträcker sig över många fortsatta poster, förhindrar teknikerna i anteckningarna om prestanda för stora arbetsböcker att cache-bygget dominerar din körtid. Båda hör ihop med den pivot-skrivare som levereras i HotXLS spreadsheet component för Delphi och C++Builder tillsammans med de API:er för celler, formler, diagram och formatering som beskrivs på andra ställen i denna blogg