Takmer každá časť staršieho binárneho formátu Excelu je samostatný záznam s čistým dvojbajtovým typom a dvojbajtovou dĺžkou. Bunka je LABELSST alebo NUMBER. Zlúčená oblasť je MERGEDCELLS. Väčšinu pracovného hárka môžete prečítať postupným prechádzaním záznamov jeden po druhom a odosielaním podľa slovného označenia typu. Kontingenčné tabuľky (PivotTables) tento rytmus narúšajú. Jediná kontingenčná tabuľka nie je záznam, je to malý program zložený z desiatok spolupracujúcich záznamov rozmiestnených na dvoch rôznych miestach v tom istom streame zloženého dokumentu OLE, a vzťahy medzi nimi sú pozičné, bitovo zabalené a nekompromisné. Toto je štruktúra, ktorú väčšina čítačiek BIFF8 buď úplne preskočí, alebo zachová ako nepriehľadné bajty, pretože zápis od nuly znamená reprodukovanie každého krížového odkazu, ktorý udržiava samotný Excel.
Dôvod, prečo je kontingenčná tabuľka náročná, je ten, že ide o dva artekty zvarené dohromady. Existuje pivot cache, čo je samostatný snímok zdrojových dát s vlastným podstreamom (substream), a zobrazenie tabuľky (table view), čo je rozloženie určujúce, ktoré polia sedia na ktorej osi. Cache a zobrazenie na seba odkazujú indexom. Ak urobíte chybu v jedinom indexe, súbor sa otvorí s chybou aktualizácie alebo s prázdnou mriežkou bez akéhokoľvek varovania.
Pivot cache je vlastný podstream
Cache žije v globálnom streame zošita ako kompletný podstream BIFF, ohraničený záznamom BOF, ktorého typ dokumentu je 0x0006 (hodnota, ktorá označuje pivot cache, na rozdiel od 0x0005 pre zošit alebo 0x0010 pre hárok) a uzatvorený zodpovedajúcim EOF. Vnútri tohto rámca je štruktúra pevná. Záznam SXDB je hlavička cache. Nesie počet záznamov, počet polí cache a identifikátor streamu, ktorý zobrazenie tabuľky uvedie na naviazanie sa k tejto cache. Každý zdrojový stĺpec potom prispieva záznamom definície poľa SXFDB, za ktorým nasleduje SXFDBType, ktorý ho klasifikuje, a potom jedinečné hodnoty, ktoré daný stĺpec nadobudol, vygenerované ako jeden typovaný záznam položky na každú odlišnú hodnotu.
Záznamy položiek sú miestom, kde cache ukazuje svoj zmysel. Textová hodnota sa stáva SXSTRING, číselná hodnota SXNUM, logická hodnota SXBOOLEAN a chyba vzorca SXERR. Cache neukladá zdrojovú mriežku, ukladá jedinečné hodnoty pre každé pole plus indexovú tabuľku, ktorá pre záznam n hovorí, ktorú jedinečnú položku každé pole nadobudlo. To je dôvod, prečo programové budovanie kontingenčnej tabuľky nie je len otázkou kopírovania buniek. Musíte naskenovať zdrojový rozsah, odvodiť typ každého poľa z hodnôt, ktoré obsahuje, odstrániť duplicity do typovaného zoznamu položiek a zaznamenať každý riadok ako n-ticu indexov položiek. HotXLS robí presne toto: čisto číselný stĺpec sa vygeneruje s položkami SXNUM, stĺpec so zmiešaným textom sa stáva položkami SXSTRING a dátumy sa prenášajú ako sériové hodnoty cez rovnakú číselnú cestu.
SXDBB a bitové balenie, ktoré to robí zaujímavým
Indexová tabuľka pre každý záznam je technicky najzaujímavejšou časťou celej štruktúry a nachádza sa v zázname SXDBB. Naivné kódovanie by uložilo index položky každého poľa ako 16-bitové slovo. Excel to nerobí. Balí index každého poľa presne do počtu bitov potrebných na adresovanie položiek tohto poľa, a nič viac. Šírka je ceil(log2(itemCount + 1)) bitov. Na + 1 záleží: dodatočná hodnota je sentinel (strážca) s významom „prázdny, žiadna hodnota pre toto pole v tomto zázname“, takže pole s tromi jedinečnými položkami potrebuje reprezentovať štyri stavy a preto zaberá dva bity, nie jeden bit, ktorý by naznačovali samotné tri položky. Pole bez akýchkoľvek položiek prispieva nula bitmi a pri balení sa úplne preskočí.
Bity pre jeden záznam sú zreťazené naprieč všetkými poliami, potom sa ďalší záznam začína na novej hranici bajtu. Záznamy sú zarovnané na bajty, nie bitovo zabalené od začiatku do konca, čo robí náhodný prístup do tabuľky realizovateľným za cenu niekoľkých výplňových bitov na riadok. Balenie v rámci bajtu je od najmenej významného bitu (least-significant-bit first). Akonáhle prijmete tieto dve pravidlá, enkóder je priamou bitovou pumpou a dekodér je jeho zrkadlom.
// 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;Dôvod, prečo tento detail nemožno ignorovať, je strop 8224 bajtov pre jeden záznam BIFF. Každý záznam vo formáte, vrátane pivot záznamov, musí zmestiť svoj obsah do maximálne 8224 bajtov a vyťažená pivot cache s tisíckami zdrojových riadkov tento limit prekročí dávno pred tým, ako vygeneruje každý riadok. Indexová tabuľka je preto rozdelená. HotXLS obmedzuje samotné telo SXDBB na 8220 bajtov, čo je limit záznamu 8224 mínus štvorbajtová hlavička záznamu pre typ a dĺžku, vydelí to šírkou jedného zabaleného záznamu v bajtoch, aby zistil, koľko celých riadkov sa zmestí, a potom vygeneruje toľko pokračujúcich záznamov SXDBB, koľko si vyžaduje počet riadkov. Obmedzením je dĺžka 8220 bajtov, nie niečo iné. Každé pokračovanie začína čisto na hranici záznamu, takže žiadny riadok nie je nikdy rozdelený naprieč dvoma záznamami. Čítačka, ktorá pozná bitovú šírku na záznam, môže prechádzať každým SXDBB v poradí, akoby to bolo jedno súvislé bitové pole.
Rozloženie zobrazenia: SXLI pre telo, SXPI pre stranu
Keď je cache postavená, zobrazenie tabuľky je druhou poloviciou. Jeho jadrom sú riadkové položky osi (axis line items), riadky tela pivotu, ktoré vymenúvajú každú kombináciu hodnôt riadkového a stĺpcového poľa, ktorú tabuľka vykresľuje. Tieto sú prenášané v záznamoch SXLI (typ záznamu 0x00B5, popísaný v [MS-XLS] §2.4.275). Jeden SXLI drží veľa riadkov, opäť kým limit 8224 bajtov nevynúti nový záznam, a používa malý kompresný trik: každý riadok ukladá iba to, ako sa líši od riadku nad ním, vyjadrené ako počet spoločných prefixov, takže hlboko vnorená os neopakuje hodnoty vonkajšieho poľa v každom riadku. Riadok celkového súčtu (grand-total) a prvý riadok akéhokoľvek záznamu vždy resetujú tento počet prefixov na nulu, takže čítačka sa nikdy nemusí pozerať späť cez hranicu záznamu, aby zrekonštruovala riadok.
Os stránky (page axis), rozbaľovacie zoznamy filtrov, ktoré sedia nad kontingenčnou tabuľkou, je samostatný záznam. SXPI (typ záznamu 0x00B6, [MS-XLS] §2.4.276) nesie jeden desaťbajtový záznam na pole stránky: index pivot poľa isxvd, vybranú položku cache iCache, slovo pozície ipos a staršie ID objektu objId. Hodnota iCache je tá, ktorú treba sledovať. Pole stránky, ktoré zobrazuje „(All)“ (všetko) a nič nefiltruje, ukladá sentinel 0x7FFD namiesto skutočného indexu položky. Programovo vytvorený pivot sa otvára s každým poľom stránky nastaveným na „(All)“, kým volajúci vopred nevyberie položku, v ktorom bode index cache tejto položky nahradí sentinel a Excel sa otvorí s už aplikovaným filtrom. Vedľa nich sedia podporné záznamy, ktoré popisujú jednotlivé polia a ich formátovanie, SXVD and SXVDEx for field view definitions, SXIVD for the field-index lists that order each axis, and SXFormat for number formatting, each one indexing back into the same cache the body lines reference.
Dva zapisovače v jednom: surové bloby a typovaný model
Existuje štrukturálny dôvod, prečo HotXLS uchováva dve úplne oddelené cesty na zápis kontingenčnej tabuľky, a to priamo z požiadaviek na zachovanie vernosti (fidelity). Keď sa zošit číta z disku, jeho pivot záznamy boli zapísané Excelom alebo iným tvorcom, a môžu používať varianty záznamov, anomálie usporiadania alebo rozširujúce záznamy, ktoré žiadny zapisovač tretej strany plne nemodeluje. Jediná bezpečná vec, ktorú s týmito bajtmi možno urobiť, je vrátiť ich nezmenené. Kontingenčná tabuľka, ktorá prišla zo súboru, má preto nastavený príznak FromRawBlobs = True a pri ukladaní zapisovač znova prehrá zachované bloby záznamov doslova. Nič sa neregeneruje, nič sa nereinterpretuje a cesta cez otvorenie a uloženie je bajtovo stabilná.
Kontingenčná tabuľka, ktorú program vytvoril, je opačný prípad. Neexistujú žiadne pôvodné bajty na zachovanie, iba typovaný objektový model: TXLSPivotCache s jeho poliami a zoznamami položiek a TXLSPivotTable s jeho priradeniami osí. Táto tabuľka má nastavený príznak FromRawBlobs = False a zapisovač ju serializuje zložitejším spôsobom – vygeneruje nový podstream cache BOF = 0x0006, zabalí indexovú tabuľku SXDBB z indexov položiek, ktoré typovaný model drží, a rozloží záznamy SXLI a SXPI z konfigurácie osí. Tento príznak je to, čo umožňuje obom typom koexistovať v jednom zošite. Bez neho by jeden zapisovač musel buď zahodiť vernosť načítaných tabuliek, alebo odmietnuť generovanie nových. Akékoľvek rozširujúce záznamy špecifické pre tvorcu, ktoré načítaná tabuľka niesla, sú uchované ako dodatočné záznamy, dostupné cez zoznam tabuľky SupplementalRecords, takže tabuľka kontrolovaná cez typovaný model nestratí časti, ktoré model nepopisuje.
Vytvorenie kontingenčnej tabuľky v kóde
Všetka vyššie uvedená mašinéria sedí za jedným volaním. AddPivotTable prijíma zdrojový rozsah v notácii A1, cieľovú bunku, kde sa ukotví horný ľavý roh tabuľky, a názov. Analyzuje rozsah, naskenuje ho na odvodenie typov polí a zostavenie cache (znova použije existujúcu cache, ak sa iná tabuľka už viaže na rovnaký rozsah) a vráti typovanú TXLSPivotTable s jedným poľom pre každý zdrojový stĺpec, pričom každé pole je spočiatku mimo osi. Potom umiestnite polia na osi a vyberiete agregáciu. Podpis vyzerá presne takto, pričom cache, balenie SXDBB a záznamy zobrazenia sa vytvoria za vás pri ukladaní.
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;Prvý riadok zdrojového rozsahu sa číta ako hlavička, ktorá pomenúva polia cache, takže AddRowField('Region') priradí stĺpec podľa textu jeho hlavičke, a nie podľa pozície. Pretože vrátená tabuľka je typovaný model s FromRawBlobs = False, zapisovač zvolí cestu od nuly: vytvorí samostatnú cache, ktorá nezávisí od toho, či je zdrojový rozsah v čase aktualizácie stále prítomný, čo je presne vlastnosť, ktorú chcete, keď sa pivot posiela príjemcovi, ktorý môže presunúť alebo vymazať podkladové dáta.
Čítanie a zosúladenie záznamov pivotu a cache súboru, ktorý ste nevyrobili, vrátane cesty zachovania surových blobov, je pokryté v návode na audit zošita a konverzný workbench. Keď zdrojový rozsah siaha do desiatok tisíc riadkov a stream SXDBB pokrýva viacero pokračujúcich záznamov, techniky v poznámkach o výkone pre veľké zošity zabránia tomu, aby zostavenie cache dominovalo vášmu času behu. Obe témy sa spájajú so zapisovačom pivotu, ktorý sa dodáva v kontajneri tabuľkového procesora HotXLS pre Delphi a C++Builder spolu s rozhraniami API pre bunky, vzorce, grafy a formátovanie popísanými inde na tomto blogu.