Skoro svaki deo nasleđenog Excel binarnog formata je pojedinačni zapis sa čistim dvobajtnim tipom i dvobajtnom dužinom. Ćelija je LABELSST ili NUMBER. Spojeni region je MERGEDCELLS. Možete pročitati većinu radnog lista prolazeći kroz zapise jedan po jedan i granajući se na osnovu reči tipa. PivotTable tabele prekidaju taj ritam. Pojedinačna pivot tabela nije zapis, to je mali program sastavljen od desetina sarađujućih zapisa raspoređenih na dva različita mesta u istom toku OLE složenog dokumenta, a odnosi među njima su pozicioni, bit-pakovani i nemilosrdni. Ovo je struktura koju većina čitača BIFF8 formata ili potpuno preskače ili čuva kao neprozirne bajtove, jer pisanje jedne ispočetka znači reprodukovanje svake unakrsne reference koju sam Excel održava.
Razlog zašto je pivot tabela teška leži u tome što su to zapravo dva spojena artefakta. Postoji pivot keš (pivot cache), samostalni snimak izvornih podataka sa sopstvenim podtokom, i postoji prikaz tabele, raspored koji govori koja polja leže na kojoj osi. Keš i prikaz referenciraju jedan drugog pomoću indeksa. Pogrešite jedan indeks i datoteka se otvara sa greškom osvežavanja ili tiho praznom mrežom.
Pivot keš je sopstveni podtok
Keš živi u globalnom toku radne sveske kao kompletan BIFF podtok, uokviren BOF zapisom čiji je tip dokumenta 0x0006 (vrednost koja označava pivot keš, nasuprot 0x0005 za radnu svesku ili 0x0010 za radni list) i zatvoren odgovarajućim EOF zapisom. Unutar tog okvira struktura je fiksna. Zapis SXDB je zaglavlje keša. On nosi broj zapisa, broj polja keša i identifikator toka koji će prikaz tabele navesti da bi se povezao sa ovim kešom. Svaka izvorna kolona zatim doprinosi SXFDB zapisom definicije polja, praćenim sa SXFDBType koji ga klasifikuje, a zatim jedinstvenim vrednostima koje je ta kolona imala, emitovanim kao jedan tipizirani zapis stavke po različitoj vrednosti.
Zapisi stavki su mesto gde keš opravdava svoje prisustvo. Tekstualna vrednost postaje SXSTRING, numerička vrednost SXNUM, logička vrednost SXBOOLEAN, a greška formule SXERR. Keš ne skladišti izvornu mrežu, on skladišti različite vrednosti po polju plus indeksnu tabelu koja govori, za zapis n, koju različitu stavku je svako polje uzelo. Zbog toga programsko preuzimanje pivot tabele nije stvar kopiranja ćelija. Morate skenirati izvorni opseg, zaključiti tip svakog polja na osnovu vrednosti koje sadrži, ukloniti duplikate u tipiziranu listu stavki i zabeležiti svaki red kao torku (tuple) indeksa stavki. HotXLS radi upravo to: potpuno numerička kolona se emituje sa SXNUM stavkama, mešovita tekstualna kolona postaje SXSTRING stavka, a datumi se prenose kao serijske vrednosti kroz istu numeričku putanju.
SXDBB i pakovanje bitova koje ga čini zanimljivim
Indeksna tabela po zapisu je tehnički najzanimljiviji deo cele strukture, i živi u SXDBB zapisu. Naivno kodiranje bi uskladištilo indeks stavke svakog polja kao 16-bitnu reč. Excel to ne radi. On pakuje indeks svakog polja u tačno onaj broj bitova koji je potreban za adresiranje stavki tog polja, i ništa više. Širina je ceil(log2(itemCount + 1)) bita. + 1 je važno: dodatna vrednost je sentinel koji označava "prazno, nema vrednosti za ovo polje u ovom zapisu", tako da polje sa tri različite stavke mora da predstavi četiri stanja i stoga uzima dva bita, not the one bit that three items alone would suggest. Polje bez stavki uopšte doprinosi sa nula bitova i potpuno se preskače tokom pakovanja.
Bitovi za jedan zapis se nadovezuju (concatenate) kroz sva polja, a zatim sledeći zapis počinje na novoj granici bajta. Zapisi su poravnati po bajtovima, a ne pakovani po bitovima sa kraja na kraj, što čini nasumičan pristup tabeli lakšim po cenu nekoliko bitova za popunjavanje (padding) po redu. Pakovanje unutar bajta ide od najmanje značajnog bita (least-significant-bit). Kada prihvatite ta dva pravila, enkoder je jednostavna pumpa bita, a dekoder je njegovo ogledalo.
// 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;Razlog zašto se ovaj detalj ne može ignorisati jeste granica od 8224 bajta na jednom BIFF zapisu. Svaki zapis u formatu, uključujući pivot zapise, mora da uklopi svoj sadržaj u najviše 8224 bajta, a aktivan pivot keš sa hiljadama izvornih redova će to premašiti daleko pre nego što emituje svaki red. Zbog toga se indeksna tabela deli. HotXLS ograničava jedno telo SXDBB zapisa na 8220 bajtova, što je limit od 8224 bajta minus četvorobajtno zaglavlje zapisa tipa i dužine, deli to sa širinom bajta jednog pakovanog zapisa da bi saznao koliko celih redova staje, a zatim emituje onoliko nastavaka SXDBB zapisa koliko broj redova zahteva. Svaki nastavak počinje čisto na granici zapisa, tako da nijedan red nikada nije podeljen preko dva zapisa. Čitač koji zna širinu bita po zapisu može proći kroz svaki SXDBB redom kao da su jedan neprekidni niz bitova.
Raspored prikaza: SXLI za telo, SXPI za stranicu
Sa izgrađenim kešom, prikaz tabele je druga polovina. Njegova srž su stavke linije ose, redovi pivot tela koji nabrajaju svaku kombinaciju vrednosti polja redova i polja kolona koje tabela iscrtava. Oni se prenose u SXLI zapisima (tip zapisa 0x00B5, opisan u [MS-XLS] §2.4.275). Jedan SXLI drži mnogo linija, ponovo dok limit od 8224 bajta ne nametne nov zapis, i koristi mali trik kompresije: svaka linija čuva samo kako se razlikuje od linije iznad nje, izraženo kroz broj zajedničkih prefiksa, tako da ugnežđena osa ne ponavlja spoljne vrednosti polja u svakom redu. Linija sveukupnog zbira (grand-total) i prva linija bilo kog zapisa uvek resetuju taj broj prefiksa na nulu, tako da čitač nikada ne mora da gleda unazad preko granice zapisa da bi rekonstruisao liniju.
Osa stranice, padajući meniji filtera koji se nalaze iznad pivot tabele, je poseban zapis. SXPI (tip zapisa 0x00B6, [MS-XLS] §2.4.276) prenosi jedan desetobajtni unos po polju stranice: indeks pivot polja isxvd, izabranu stavku keša iCache, reč pozicije ipos i nasleđeni id objekta objId. Vrednost iCache je ona na koju treba obratiti pažnju. Polje stranice koje prikazuje "(All)", ne filtrirajući ništa, čuva sentinel 0x7FFD umesto stvarnog indeksa stavke. Programski izgrađen pivot se otvara sa svakim poljem stranice podešenim na "(All)" dok pozivalac ne izabere stavku, pri čemu indeks keša te stavke zamenjuje sentinel i Excel se otvara sa već primenjenim filterom. Pored njih se nalaze prateći zapisi koji opisuju pojedinačna polja i njihovo formatiranje, SXVD i SXVDEx za definicije prikaza polja, SXIVD za liste indeksa polja koje uređuju svaku osu, i SXFormat za formatiranje brojeva, od kojih svaki indeksira nazad u isti keš koji referenciraju linije tela.
Dva upisivača u jednom: sirovi binarni objekti i tipizirani model
Postoji strukturalni razlog zašto HotXLS čuva dve potpuno odvojene putanje za upisivanje pivot tabele, i to dolazi direktno iz zahteva za vernošću. Kada se radna sveska čita sa diska, njeni pivot zapisi su upisani od strane Excel-a ili nekog drugog proizvođača, i mogu koristiti varijante zapisa, neobične redoslede ili zapise proširenja koje nijedan nezavisni upisivač u potpunosti ne modeluje. Jedina bezbedna stvar koju treba uraditi sa tim bajtovima jeste vratiti ih nepromenjene. Stoga, pivot tabela koja je došla iz datoteke ima zastavicu FromRawBlobs = True, a prilikom čuvanja upisivač reprodukuje sačuvane zapise sirovih binarnih objekata (blobs) doslovno. Ništa se ne regeneriše, ništa se ne interpretira ponovo, a ceo proces otvaranja i čuvanja (round-trip) je bajt-stabilan.
Pivot tabela koju je program izgradio je suprotan slučaj. Nema originalnih bajtova za čuvanje, već samo tipizirani model objekata: TXLSPivotCache sa svojim poljima i listama stavki, i TXLSPivotTable sa svojim dodelama osa. Ta tabela ima zastavicu FromRawBlobs = False, i upisivač je serijalizuje na teži način, emitujući svež BOF = 0x0006 podtok keša, pakujući SXDBB indeksnu tabelu iz indeksa stavki koje tipizirani model drži, i raspoređujući SXLI i SXPI zapise iz konfiguracije osa. Zastavica je ono što omogućava da obe vrste koegzistiraju u jednoj radnoj svesci. Bez nje, jedan upisivač bi morao ili da odbaci vernost pročitanih tabela ili da odbije generisanje novih. Bilo koji zapisi proširenja specifični za proizvođača koje je pročitana tabela nosila čuvaju se kao dopunski zapisi, dostupni kroz listu SupplementalRecords tabele, tako da tabela pregledana kroz tipizirani model ne gubi delove koje model ne opisuje.
Izgradnja pivot tabele u kodu
Sva gore navedena mašinerija nalazi se iza jednog poziva. AddPivotTable uzima izvorni opseg u A1 notaciji, ciljnu ćeliju gde se sidri gornji levi ugao tabele, i naziv. Analizira opseg, skenira ga da bi zaključio tipove polja i izgradio keš (ponovo koristeći postojeći keš ako se druga tabela već povezuje na isti opseg), i vraća tipiziranu TXLSPivotTable sa jednim poljem po izvornoj koloni, pri čemu je svako polje u početku van ose. Zatim postavljate polja na ose i birate agregaciju. Potpis je tačno ovakav, a keš, SXDBB pakovanje i zapisi prikaza se proizvode za vas u trenutku čuvanja.
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;Prvi red izvornog opsega se čita kao zaglavlje koje imenuje polja keša, tako da AddRowField('Region') pronalazi kolonu po tekstu njenog zaglavlja, a ne po poziciji. Pošto je vraćena tabela tipizirani model sa FromRawBlobs = False, upisivač ide putanjom od nule: gradi samostalni keš koji ne zavisi od toga da li je izvorni opseg i dalje prisutan u trenutku osvežavanja, što je upravo svojstvo koje želite kada se pivot šalje primaocu koji može premestiti ili obrisati osnovne podatke.
Čitanje i usklađivanje pivot i keš zapisa datoteke koju niste sami proizveli, uključujući putanju očuvanja sirovih binarnih objekata, pokriveni su u vodiču kroz reviziju radne sveske i radni sto za konverziju. Kada izvorni opseg ide do desetina hiljada redova, a SXDBB tok obuhvata mnogo nastavaka zapisa, tehnike u beleškama o performansama velikih radnih svezaka sprečavaju da izgradnja keša dominira vremenom izvršavanja. Oba se povezuju sa upisivačem pivot tabela koji se isporučuje u HotXLS komponenti za tabele za Delphi i C++Builder zajedno sa API-jima za ćelije, formule, grafikone i formatiranje koji su pokriveni na drugim mestima na ovom blogu.