Téměř každá část staršího binárního formátu Excelu je tvořena jediným záznamem s čistým dvoubaťovým typem a dvoubaťovou délkou. Buňka je LABELSST nebo NUMBER. Sloučená oblast je MERGEDCELLS. Většinu listu můžete přečíst postupným procházením záznamů a rozhodováním na základě typu. Kontingenční tabulky (PivotTables) tento rytmus narušují. Jedna kontingenční tabulka není záznamem. Je to malý program složený z desítek spolupracujících záznamů rozmístěných na dvou různých místech ve stejném streamu složeného dokumentu OLE a vztahy mezi nimi jsou poziční, bitově zabalené a nekompromisní. Jedná se o strukturu, kterou většina čteček BIFF8 buď zcela přeskakuje, nebo ji uchovává jako surová data (opaque bytes), protože její zápis od nuly znamená reprodukovat každý křížový odkaz, který si Excel sám udržuje.
Důvod, proč je kontingenční tabulka složitá, spočívá v tom, že jde ve skutečnosti o dva spojené artefakty. Existuje pivot cache, což je samostatný snímek zdrojových dat s vlastním substreamem, a zobrazení tabulky (table view), tedy rozvržení, které říká, která pole leží na které ose. Cache a zobrazení na sebe vzájemně odkazují pomocí indexu. Zadejte jeden index nesprávně a soubor se otevře s chybou aktualizace nebo s prázdnou mřížkou bez jakéhokoli varování.
Pivot cache je samostatným substreamem
Cache sídlí v globálním streamu sešitu jako kompletní substream BIFF, ohraničený záznamem BOF, jehož typ dokumentu je 0x0006 (hodnota označující pivot cache, na rozdíl od 0x0005 pro sešit nebo 0x0010 pro pracovní list), a uzavřený odpovídajícím záznamem EOF. Uvnitř tohoto rámce je struktura pevná. Záznam SXDB je hlavičkou cache. Nese počet záznamů, počet polí cache a identifikátor streamu, který zobrazení tabulky uvede pro vazbu na tuto cache. Každý zdrojový sloupec pak přispívá záznamem definice pole SXFDB, následovaným SXFDBType, který jej klasifikuje, a poté jedinečnými hodnotami, které tento sloupec obsahoval, zapsanými jako jeden typovaný záznam položky pro každou odlišnou hodnotu.
Záznamy položek jsou tím, kde cache prokazuje svou užitečnost. Textová hodnota se stává SXSTRING, číselná hodnota SXNUM, logická hodnota SXBOOLEAN a chyba vzorce SXERR. Cache neukládá zdrojovou mřížku. Ukládá odlišné (distinct) hodnoty pro každé pole a tabulku indexů, která říká, jakou odlišnou položku každé pole pro záznam n přijalo. Proto vytváření kontingenční tabulky programově není otázkou pouhého kopírování buněk. Musíte projít zdrojový rozsah, odvodit typ každého pole z hodnot, které obsahuje, odstranit duplicity do typovaného seznamu položek a zaznamenat každý řádek jako n-tici indexů položek. Knihovna HotXLS dělá přesně toto: sloupec s čistě číselnými hodnotami zapíše s položkami SXNUM, sloupec se smíšeným textem se změní na položky SXSTRING a data jsou přenášena jako sériové hodnoty stejnou číselnou cestou.
SXDBB a bitové balení, které ho činí zajímavým
Indexová tabulka pro jednotlivé záznamy je technicky nejzajímavější částí celé struktury a nachází se v záznamu SXDBB. Naivní kódování by uložilo index položky každého pole jako 16bitové slovo. Excel to však nedělá. Zabalí index každého pole přesně do takového počtu bitů, který je nutný k adresování položek daného pole, a nic víc. Šířka je ceil(log2(itemCount + 1)) bitů. Hodnota + 1 je důležitá: tato hodnota navíc představuje značku (sentinel) s významem „prázdné, žádná hodnota pro toto pole v tomto záznamu“. Pole se třemi odlišnými položkami tak musí reprezentovat čtyři stavy, a proto zabírá dva bity, nikoli jeden bit, který by naznačovaly samotné tři položky. Pole bez jakýchkoli položek nepřispívá žádnými bity a při balení se zcela přeskočí. Bity pro jeden záznam jsou zřetězeny napříč všemi poli, a poté další záznam začíná na nové hranici bajtu. Záznamy jsou zarovnány na bajty, nikoli bitově baleny od začátku do konce, což činí náhodný přístup do tabulky proveditelným za cenu několika doplňkových (padding) bitů na řádek. Balení v rámci bajtu probíhá od nejméně významného bitu (least-significant-bit first). Jakmile přijmete tato dvě pravidla, kodér je přímočarým posunovačem bitů a dekodér je jeho zrcadlem.
// 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ůvodem, proč tento detail nelze ignorovat, je strop 8224 bajtů pro jeden záznam BIFF. Každý záznam ve formátu, včetně kontingenčních záznamů, musí svůj obsah vměstnat do maximálně 8224 bajtů. Vytížená pivot cache s tisíci zdrojovými řádky tento limit překročí dlouho předtím, než zapíše všechny řádky. Indexová tabulka je proto rozdělena. HotXLS omezuje tělo jednoho záznamu SXDBB na 8220 bajtů, což je limit záznamu 8224 minus čtyřbajtová hlavička typu a délky. Tuto hodnotu vydělí šířkou jednoho zabaleného řádku v bajtech, aby zjistil, kolik celých řádků se vejde, a poté zapíše tolik pokračovacích záznamů SXDBB, kolik vyžaduje počet řádků. Každé pokračování začíná čistě na hranici záznamu, takže žádný řádek není nikdy rozdělen mezi dva záznamy. Čtečka, která zná bitovou šířku jednoho záznamu, může procházet všechny záznamy SXDBB za sebou, jako by šlo o jedno souvislé pole bitů.
Rozvržení zobrazení: SXLI pro tělo, SXPI pro stránku
Po vytvoření cache tvoří zobrazení tabulky druhou polovinu. Jeho jádrem jsou položky řádků os, tedy řádky těla kontingenční tabulky, které vyjmenovávají každou kombinaci hodnot řádkových a sloupcových polí, jež tabulka vykresluje. Ty jsou přenášeny v záznamech SXLI (typ záznamu 0x00B5, popsaný v [MS-XLS] §2.4.275). Jeden SXLI obsahuje mnoho řádků (opět dokud limit 8224 bajtů nevynutí nový záznam) a používá malý kompresní trik: každý řádek ukládá pouze to, jak se liší od řádku nad ním, vyjádřené jako počet společných prefixů. Hluboce vnořená osa tak neopakuje vnější hodnoty polí na každém řádku. Řádek celkového součtu a první řádek jakéhokoli záznamu vždy resetují tento počet prefixů na nulu, takže čtečka nemusí při rekonstrukci řádku nikdy nahlížet zpět přes hranici záznamu.
Osa stránky, tedy filtrovací rozevírací seznamy ležící nad kontingenční tabulkou, je samostatným záznamem. SXPI (typ záznamu 0x00B6, [MS-XLS] §2.4.276) nese jednu desetibajtovou položku na pole stránky: index pole kontingenční tabulky isxvd, vybranou položku cache iCache, poziční slovo ipos a starší ID objektu objId. Hodnota iCache je tou, kterou je třeba sledovat. Pole stránky, které zobrazuje „(All)“ a nic nefiltruje, ukládá značku 0x7FFD namísto skutečného indexu položky. Programově sestavená kontingenční tabulka se otevře s každým polem stránky nastaveným na „(All)“, dokud volající předem nevybere položku. V tom okamžiku nahradí index cache této položky zmíněnou značku a Excel se otevře s již aplikovaným filtrem. Vedle nich leží podpůrné záznamy popisující jednotlivá pole a jejich formátování: SXVD a SXVDEx pro definice zobrazení polí, SXIVD pro seznamy indexů polí určující pořadí jednotlivých os a SXFormat pro formátování čísel, přičemž každý z nich odkazuje zpět na stejnou cache, na kterou odkazují řádky těla.
Dva zapisovače v jednom: surové bloby a typovaný model
Existuje strukturální důvod, proč HotXLS udržuje dvě zcela oddělené cesty pro zápis kontingenční tabulky, což přímo vyplývá z požadavků na věrnost dat. Když je sešit načten z disku, jeho kontingenční záznamy byly zapsány Excelem nebo jiným tvůrcem a mohou používat varianty záznamů, anomálie v řazení nebo rozšiřující záznamy, které žádný zapisovač třetích stran plně nemodeluje. Jedinou bezpečnou věcí, kterou lze s těmito bajty udělat, je vrátit je nezměněné. Kontingenční tabulka načtená ze souboru je proto označena příznakem FromRawBlobs = True, a při uložení zapisovač přehraje zachované bloby záznamů doslova. Nic se negeneruje znovu, nic se nereinterpretuje a cyklus otevření a uložení je bajtově stabilní.
Kontingenční tabulka vytvořená programem je opačným případem. Neexistují žádné původní bajty k zachování, pouze typovaný objektový model: TXLSPivotCache s poli a seznamy položek a TXLSPivotTable s přiřazením os. Tato tabulka je označena příznakem FromRawBlobs = False, a zapisovač ji serializuje složitější cestou: generuje nový substream cache s BOF = 0x0006, balí indexovou tabulku SXDBB z indexů položek, které typovaný model drží, a sestavuje záznamy SXLI a SXPI podle konfigurace os. Tento příznak umožňuje oběma typům koexistovat v jednom sešitu. Bez něj by zapisovač musel buď zahodit věrnost načtených tabulek, nebo odmítnout generovat nové. Jakékoli rozšiřující záznamy specifické pro tvůrce, které načtená tabulka obsahovala, jsou uchovány jako doplňkové záznamy (supplemental records) dostupné přes seznam SupplementalRecords, takže tabulka zkoumaná přes typovaný model neztrácí části, které model nepopisuje.
Tvorba kontingenční tabulky v kódu
Veškerý výše popsaný mechanismus se nachází za jediným voláním. AddPivotTable tak vrací typovanou TXLSPivotTable s jedním polem pro každý zdrojový sloupec, přičemž každé pole je zpočátku mimo osy. Poté umístíte pole na osy a zvolíte agregaci. Signatura vypadá přesně takto a cache, balení SXDBB i záznamy zobrazení jsou pro vás vytvořeny při uložení.
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;První řádek zdrojového rozsahu je načten jako záhlaví, které pojmenovává pole cache, takže AddRowField('Region') odpovídá sloupci podle textu jeho záhlaví a nikoli podle pozice. Vzhledem k tomu, že vrácená tabulka je typovaným modelem s FromRawBlobs = False, zapisovač zvolí cestu od nuly: sestaví soběstačnou cache, která nezávisí na tom, zda je zdrojový rozsah při aktualizaci stále přítomen. To je přesně vlastnost, kterou chcete, když kontingenční tabulku posíláte příjemci, který může podkladová data přesunout nebo smazat.
Načítání a sjednocování záznamů kontingenční tabulky a cache ze souborů, které jste sami nevytvořili, včetně cesty pro zachování surových blobů, je popsáno v průvodci auditem sešitu a konverzním nástrojem. Pokud zdrojový rozsah obsahuje desítky tisíc řádků a stream SXDBB se rozprostírá přes mnoho pokračovacích záznamů, techniky v poznámkách o výkonu u velkých sešitů zabrání tomu, aby sestavení cache ovládlo čas běhu programu. Oba postupy doplňují zapisovač kontingenčních tabulek, který je dodáván v tabulkové komponentě HotXLS pro Delphi a C++Builder společně s rozhraními API pro buňky, vzorce, grafy a formátování popsanými jinde na tomto blogu.