Nesten alle deler av det eldre binære Excel-formatet er en enkelt post med en ryddig to-byte type og en to-byte lengde. En celle er en LABELSST eller en NUMBER. Et flettet område er en MERGEDCELLS. Du kan lese det meste av et regneark ved å gå gjennom postene én etter én og videresende basert på typeordet. PivotTables bryter denne rytmen. En enkelt pivottabell er ikke én post; det er et lite program bestående av dusinvis av samarbeidende poster spredt over to forskjellige steder i den samme OLE-dokumentstrømmen, og forholdene mellom dem er posisjonelle, bit-pakkede og utilgivende. Dette er strukturen de fleste BIFF8-lesere enten hopper helt over eller bevarer som ugjennomsiktige byte, fordi å skrive en fra bunnen av betyr å gjenskape alle kryssreferanser Excel selv opprettholder.
Grunnen til at en pivottabell er vanskelig, er at den egentlig er to produkter sveiset sammen. Det finnes en pivot-cache, et selvstendig øyeblikksbilde av kildedataene med sin egen understrøm, og det finnes et tabellvisningsoppsett som forteller hvilke felt som sitter på hvilken akse. Cachen og visningen refererer til hverandre med indeks. Gjør du én indeks feil, åpnes filen med en oppdateringsfeil eller et helt tomt rutenett.
Pivot-cachen er en egen understrøm
Cachen lever i arbeidsbokens globale strøm som en komplett BIFF understrøm, rammet inn av en BOF-post hvis dokumenttype er 0x0006 (verdien som markerer en pivot-cache, i motsetning til 0x0005 for arbeidsboken eller 0x0010 for et regneark) og lukket av den samsvarende EOF. Inne i denne rammen er strukturen fast. En SXDB-post er cache-headeren. Den bærer postantallet, antall cachefelt og strømidentifikatoren tabellvisningen vil oppgi for å binde seg to denne cachen. Hver kildekolonne bidrar deretter med en SXFDB-feltdefinisjonspost etterfulgt av en SXFDBType som klassifiserer den, og deretter de unike verdiene denne kolonnen tok, sendt ut som én typet elementpost per distinkte verdi.
Elementpostene er der cachen gjør nytte for seg. En tekstverdi blir en SXSTRING, en numerisk verdi en SXNUM, en logisk verdi en SXBOOLEAN og en formelfeil en SXERR. Cachen lagrer ikke kilderutenettet; den lagrer de distinkte verdiene per felt pluss en indekstabell som forteller, for post n, hvilket distinkt element hvert felt tok. Det er derfor det å bygge en pivottabell programmatisk ikke handler om å kopiere celler. Du må skanne kildeområdet, utlede hvert felts type fra verdiene det inneholder, fjerne duplikater til en typet elementliste, og registrere hver rad som en tuppel av elementindekser. HotXLS gjør akkurat dette: En ren numerisk kolonne sendes ut med SXNUM-elementer, en kolonne med blandet tekst blir til SXSTRING-elementer, og datoer bæres som serielle verdier gjennom den samme numeriske banen.
SXDBB og bit-pakkingen som gjør det interessant
Indekstabellen per post er den teknisk mest spesielle delen av hele strukturen, og den lever i SXDBB-posten. Den enkle kodingen ville lagret hvert felts elementindeks som et 16-bit ord. Excel gjør ikke det. Den pakker hvert felts indeks inn i akkurat det antall bits som kreves for å adressere feltets elementer, og ikke mer. Bredden er ceil(log2(itemCount + 1)) bits. Det + 1 betyr noe: Den ekstra verdien er en vaktpost som betyr "blank, ingen verdi for dette feltet i denne posten", så et felt med tre distinkte elementer må representere fire tilstander og tar derfor to bits, ikke den ene biten som tre elementer alene ville tilsi. Et felt uten noen elementer i det hele tatt bidrar med null bits og hoppes helt over under pakkingen.
Bitene for én post slås sammen på tvers av alle felt, og deretter starter neste post på en ny bytegrense. Poster er byte-justerte, ikke bit-pakkede fra ende til annen, noe som gjør tilfeldig tilgang to tabellen håndterbar på bekostning av noen få utfyllingsbits per rad. Pakkingen innenfor en byte er med minst betydningsfulle bit først. Når du godtar disse to reglene, er koderen en enkel bitpumpe, og dekoderen er dens speilbilde.
// 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;Grunnen til at denne detaljen ikke kan ignoreres, er taket på 8224 byte for en enkelt BIFF-post. Hver post i formatet, inkludert pivot-poster, må passe sitt innhold i maksimalt 8224 byte, og en aktiv pivot-cache med tusenvis av kilderader vil blåse forbi dette lenge før den har sendt ut hver rad. Derfor er indekstabellen delt opp. HotXLS begrenser en enkelt SXDBB-kropp til 8220 byte, som er grensen på 8224 minus den fire-byte post-headeren for type og lengde, deler dette på byte-bredden til én pakket rad for å finne ut hvor mange hele rader som passer, og sender deretter ut så mange fortsettende SXDBB-poster som radantallet krever. Hver fortsettelse starter rent på en postgrense, slik at ingen rad noen gang deles over to poster. En leser som kjenner bit-bredden per post, kan gå gjennom hver SXDBB i rekkefølge som om de var én enkelt sammenhengende bit-array.
Tabelloppsettet: SXLI for kroppen, SXPI for siden
Med cachen bygget, er tabellvisningen den andre halvdelen. Kjernen er akselinjeelementene, radene i pivotkroppen som ramser opp enhver kombinasjon av radfelt- og kolonnefeltverdier som tabellen tegner. Disse bæres i SXLI-poster (posttype 0x00B5, beskrevet i [MS-XLS] §2.4.275). Én SXLI inneholder mange linjer, igjen inntil grensen på 8224 byte tvinger frem en ny post, og den bruker et lite komprimeringstriks: Hver linje lagrer bare hvordan den skiller seg fra linjen over, uttrykt som en felles prefikstelling, slik at en dypt nestet akse ikke gjentar de ytre feltverdiene på hver rad. Totalsumlinjen og den første linjen i enhver post tilbakestiller alltid denne prefikstellingen til null, slik at en leser aldri må se tilbake over en postgrense for å rekonstruere en linje.
Sideaksen, filter-nedtrekkslistene som sitter over en pivottabell, er en egen post. SXPI (posttype 0x00B6, [MS-XLS] §2.4.276) bærer én ti-byte oppføring per sidefelt: Pivotfeltindeksen isxvd, det valgte cache-elementet iCache, et posisjonsord ipos og en eldre objekt-ID objId. Verdien iCache er den man må passe på. Et sidefelt som viser "(All)", som ikke filtrerer noe, lagrer vaktpostverdien 0x7FFD i stedet for en faktisk elementindeks. En programmatisk bygget pivot åpnes med hvert sidefelt satt to "(All)" til kaller forhåndsvelger et element, og på det tidspunktet erstatter dette elementets cacheindeks vaktposten, og Excel åpnes med filteret already applied. Ved siden av disse sitter støttepostene som beskriver enkeltfelt og formateringen deres, SXVD og SXVDEx for feltvisningsdefinisjoner, SXIVD for feltindekslistene som ordner hver akse, og SXFormat for tallformatering, der hver enkelt indekserer tilbake til den samme cachen som kroppslinjene refererer til.
To skrivere i én: rå blokker og den typede modellen
Det er en strukturell grunn til at HotXLS beholder to helt separate baner for å skrive en pivottabell, og det kommer direkte fra kravene to nøyaktighet. Når en arbeidsbok leses fra disken, ble pivot-postene skrevet av Excel eller av en annen produsent, og de kan bruke postvarianter, rekkefølgefeil eller utvidelsesposter som ingen tredjeparts-skriver fullt ut modellerer. Det eneste trygge å gjøre med disse bytene er å gi dem tilbake uendret. Så en pivottabell som kom inn fra en fil blir flagget med FromRawBlobs = True, og ved lagring spiller skriveren av de bevarte postblokkene ordrett. Ingenting gjenopprettes, ingenting reinterpreteres, og en round-trip gjennom åpning og lagring er byte-stabil.
En pivottabell programmet bygget er det motsatte tilfellet. Det finnes ingen originale byte å bevare, bare den typede objektmodellen: En TXLSPivotCache med sine felt og elementlister, og en TXLSPivotTable med sine aksetildelinger. Denne tabellen flagges med FromRawBlobs = False, og skriveren serialiserer den på den vanskelige måten: Den sender ut en ny BOF = 0x0006 cache-understrøm, pakker den SXDBB-indekstabellen fra elementindeksene den typede modellen har, og legger ut SXLI- og SXPI-postene fra aksekonfigurasjonen. Flagget er det som lar begge typer eksistere side om side i én arbeidsbok. Uten det måtte en enkelt skriver enten forkaste nøyaktigheten til innleste tabeller or refuse to generate new ones. Eventuelle produsentspesifikke utvidelsesposter en innlest tabell bar med seg beholdes som tilleggsposter, tilgjengelige via tabellens SupplementalRecords-liste, slik at en tabell som inspiseres via den typede modellen ikke mister delene modellen ikke beskriver.
Bygge en pivottabell i kode
Alt av maskineriet over sitter bak ett enkelt kall. AddPivotTable tar kildeområdet i A1-notasjon, målcella der tabellens øvre venstre hjørne forankres, og et navn. Den tolker området, skanner det for å utlede felttyper og bygge cachen (og gjenbruker en eksisterende cache hvis en annen tabell allerede knytter seg til det samme området), og returnerer en typet TXLSPivotTable med ett felt per kildekolonne, der hvert felt innledningsvis ligger utenfor aksen. Deretter plasserer du felt på aksene og velger en aggregering. Signaturen er akkurat dette, og cachen, SXDBB-pakkingen og visningspostene produseres for deg ved lagring.
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ørste raden i kildeområdet leses som headeren som navngir cachefeltene, så AddRowField('Region') matches a column by its header text rather than by position. Fordi den returnerte tabellen er en typet modell med FromRawBlobs = False, skriveren tar veien fra bunnen av: Den bygger en selvstendig cache som ikke avhenger av at kildeområdet fortsatt er til stede ved oppdatering, noe som er akkurat den egenskapen du ønsker når pivoten skal sendes til en mottaker som kan flytte eller slette de underliggende dataene.
Lesing og avstemming av pivot- og cache-poster for en fil du ikke har produsert selv, inkludert banen for bevaring av rå blokker, er dekket i gjennomgangen av revisjons- og konverteringsverktøyet for arbeidsbøker. Når kildeområdet strekker seg to titusener av rader og SXDBB-strømmen spenner over 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.