Gotovo svaki deo nasleđenog Excel binarnog formata je jedan zapis sa čistim dvobajtnim tipom i dvobajtnom dužinom. Ćelija je LABELSST ili NUMBER. Spojeno područje je MERGEDCELLS. Većinu radnog lista možete pročitati prolazeći kroz zapise jedan po jedan i usmeravajući ih na osnovu reči tipa. PivotTable tablice prekidaju taj ritam. Jedna pivot tablica nije zapis, to je mali program sastavljen od desetina saradničkih zapisa raspoređenih na dva različita mesta u istom toku OLE složenog dokumenta, a odnosi između njih su pozicijski, bit-pakovani i nemilosrdni. To je struktura koju većina čitača BIFF8 ili u potpunosti preskače ili čuva kao neprozirne bajtove, jer pisanje jedne ispočetka znači reproduciranje svake unakrsne reference koju sam Excel održava
Razlog zašto je pivot tablica teška jeste taj što su to zapravo dva artefakta spojena zajedno. Postoji pivot keš (pivot cache), samostalna snimka izvornih podataka sa sopstvenim podtokom, i postoji prikaz tablice, izgled koji govori koja polja sede na kojoj osi. Keš i prikaz upućuju jedan na drugi putem indeksa. Pogrešite li jedan indeks, datoteka se otvara uz grešku osvežavanja ili tiho praznu mrežu
Pivot keš je zaseban podtok
Keš živi u toku globalnih promenljivih radne sveske kao potpuni BIFF podtok, uokviren zapisom BOF č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. 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 tablice citirati kako bi se povezao sa ovim kešom. Svaka izvorna kolona zatim doprinosi zapisom definicije polja SXFDB praćenim tipom SXFDBType koji ga klasifikuje, a zatim jedinstvenim vrednostima koje je ta kolona poprimila, emitovanim kao jedan tipizirani zapis stavke po jedinstvenoj vrednosti
Zapisi stavki su mesto gde keš opravdava svoj rad. Tekstualna vrednost postaje SXSTRING, numerička vrednost SXNUM, logička vrednost SXBOOLEAN, a greška formule SXERR. Keš ne čuva izvornu mrežu, već čuva jedinstvene vrednosti po polju plus tablicu indeksa koja govori, za zapis n, koju je jedinstvenu stavku svako polje poprimilo. Zato izgradnja pivot tablice programski nije stvar kopiranja ćelija. Morate skenirati izvorni raspon, zaključiti tip svakog polja na osnovu vrednosti koje sadrži, ukloniti duplikate u tipizirani spisak stavki i zabeležiti svaki red kao torku indeksa stavki. HotXLS radi upravo to: potpuno numerička kolona emituje se sa stavkama SXNUM, kolona sa mešanim tekstom postaje stavka SXSTRING, a datumi se prenose kao serijske vrednosti kroz istu numeričku putanju
SXDBB i pakovanje bitova koje ga čini zanimljivim
Tablica indeksa po zapisu tehnički je najzanimljiviji deo cele strukture, a živi u zapisu SXDBB. Naivno kodiranje bi sačuvalo 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)) bitova. Vrednost + 1 je važna: dodatna vrednost je sentinel koji znači "prazno, nema vrednosti za ovo polje u ovom zapisu", pa polje sa tri jedinstvene stavke treba da predstavlja četiri stanja i stoga uzima dva bita, a ne jedan bit koji bi same tri stavke sugerisale. Polje bez stavki uopšte doprinosi sa nula bitova i u potpunosti se preskače tokom pakovanja
Bitovi za jedan zapis spajaju se kroz sva polja, a zatim sledeći zapis počinje na novoj granici bajta. Zapisi su poravnani po bajtovima, a ne pakovani po bitovima s kraja na kraj, što čini nasumični pristup tablici izvodljivim uz cenu nekoliko bitova podloge po redu. Pakovanje unutar bajta ide od najmanje značajnog bita prema napred. Jednom kada prihvatite ta dva pravila, enkoder je jednostavna pumpa bitova, 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 zanemariti je gornja granica od 8224 bajta na jednom BIFF zapisu. Svaki zapis u formatu, uključujući pivot zapise, mora uklopiti svoj korisni teret u najviše 8224 bajta, a aktivni pivot keš sa hiljadama elektronskih redova preleteće to davno pre nego što emituje svaki red. Zato je tablica indeksa podeljena. HotXLS ograničava jedno telo SXDBB na 8220 bajtova, što je limit zapisa od 8224 minus četvorobajtno zaglavlje zapisa tipa i dužine, deli to sa širinom bajta jednog pakovanog zapisa kako bi saznao koliko celih redova stane, a zatim emituje onoliko nastavaka zapisa SXDBB koliko to broj redova zahteva. Svaki nastavak počinje čisto na granici zapisa, tako da nijedan red nikada nije presečen na dva zapisa. Čitač koji zna širinu bita po zapisu može proći kroz svaki SXDBB redom kao da se radi o jednom neprekidnom nizu bitova
Izgled prikaza: SXLI za telo, SXPI za stranicu
Sa izgrađenim kešom, prikaz tablice je druga polovina. Njena srž su stavke linije ose, redovi tela pivota koji nabrajaju svaku kombinaciju vrednosti polja redova i polja kolona koje tablica iscrtava. Oni se prenose u zapisima SXLI (tip zapisa 0x00B5, opisan u [MS-XLS] §2.4.275). Jedan SXLI drži mnogo linija, opet dok limit od 8224 bajta ne nametne novi zapis, i koristi mali trik kompresije: svaka linija čuva samo kako se razlikuje od linije iznad nje, izraženo kao broj zajedničkih prefiksa, tako da duboko ugnježdena osa ne ponavlja vrednosti spoljnih polja u svakom redu. Linija sveukupnog zbira i prva linija bilo kog zapisa uvek vraćaju taj broj prefiksa na nulu, tako da čitač nikada ne mora gledati unazad preko granice zapisa kako bi rekonstruisao liniju
Osa stranice, padajući meniji filtera koji stoje iznad pivot tablice, zaseban je zapis. SXPI (tip zapisa 0x00B6, [MS-XLS] §2.4.276) nosi jedan deseterobajtni unos po polju stranice: indeks pivot polja isxvd, odabranu stavku keša iCache, reč pozicije ipos i nasleđeni ID objekta objId. Vrednost iCache je ona na koju treba paziti. Polje stranice koje prikazuje "(All)", ne filtrirajući ništa, čuva sentinel 0x7FFD umesto stvarnog indeksa stavke. Programski izgrađen pivot otvara se sa svakim poljem stranice postavljenim na "(All)" dok pozivalac unapred ne odabere stavku, na kojoj tački indeks keša te stavke zamenjuje sentinel i Excel se otvara sa već primenjenim filterom. Uz njih stoje prateći zapisi koji opisuju pojedinačna polja i njihovo oblikovanje, SXVD i SXVDEx za definicije prikaza polja, SXIVD za spiskove indeksa polja koji uređuju svaku osu i SXFormat za oblikovanje brojeva, od kojih svaki indeksira nazad u isti keš na koji se odnose linije tela
Dva pisca u jednom: sirovi blobovi i tipizirani model
Postoji strukturni razlog zašto HotXLS čuva dve potpuno odvojene putanje za pisanje pivot tablice, a on dolazi direktno iz zahteva za vernošću. Kada se radna sveska čita sa diska, njene pivot zapise napisao je Excel ili neki drugi proizvođač, i oni mogu koristiti varijante zapisa, neobičnosti u redosledu ili zapise proširenja koje nijedan pisac treće strane ne modelira u potpunosti. Jedina sigurna stvar sa tim bajtovima jeste vratiti ih nepromenjene. Stoga je pivot tablica koja je došla iz datoteke označena sa FromRawBlobs = True, a pri čuvanju pisac doslovno reprodukuje sačuvane blobove zapisa. Ništa se ne regeneriše, ništa se ponovo ne tumači, a kružno putovanje kroz otvaranje i čuvanje je bajtovno stabilno
Pivot tablica koju je program izgradio je suprotan slučaj. Nema originalnih bajtova za čuvanje, samo tipizirani objektni model: TXLSPivotCache sa svojim poljima i spiskovima stavki, te TXLSPivotTable sa svojim dodelama osa. Ta tabela je označena sa FromRawBlobs = False, a pisac je serijalizuje na teži način, emitujući sveži podtok keša BOF = 0x0006, pakujući indeksnu tablicu SXDBB iz indeksa stavki koje drži tipizirani model i raspoređujući zapise SXLI i SXPI iz konfiguracije osa. Zastavica je ono što omogućuje obema vrstama da koegzistiraju u jednoj radnoj svesci. Bez nje bi jedan pisac morao ili odbaciti vernost učitanih tablica ili odbiti generisanje novih. Svi zapisi proširenja specifični za proizvođača koje je učitana tabela nosila čuvaju se kao dopunski zapisi, dostupni kroz spisak tabele SupplementalRecords, tako da tabela pregledana kroz tipizirani model ne gubi delove koje model ne opisuje
Izgradnja pivot tablice u kodu
Sav gornji mehanizam nalazi se iza jednog poziva. AddPivotTable uzima izvorni raspon u A1 notaciji, odredišnu ćeliju na kojoj se sidri gornji levi ugao tablice i naziv. On analizira raspon, skenira ga kako bi zaključio tipove polja i izgradio keš (ponovo koristeći postojeći keš ako se druga tabela već veže na isti raspon) te vraća tipizirani 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š, pakovanje SXDBB i zapisi prikaza proizvode se 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 raspona čita se kao zaglavlje koje imenuje polja keša, pa AddRowField('Region') odgovara koloni prema tekstu zaglavlja, a ne prema poziciji. Budući da je vraćena tabela tipizirani model sa FromRawBlobs = False, pisac ide putem stvaranja ispočetka: gradi samostalan keš koji ne zavisi od toga da je izvorne raspon još uvek prisutan u trenutku osvežavanja, što je upravo svojstvo koje želite kada se pivot šalje primaocu koji može premestiti ili izbrisati osnovne podatke
Čitanje i usklađivanje pivot zapisa i zapisa keša datoteke koju niste sami proizveli, uključujući putanju očuvanja sirovih blobova, pokriveno je u vodiču za reviziju radne sveske i radni sto za pretvaranje. Kada izvorni raspon doseže desetine hiljada redova, a tok SXDBB obuhvata mnogo nastavaka zapisa, tehnike u beleškama o performansama sa velikim radnim sveskama sprečavaju da izgradnja keša dominira vašim vremenom izvršavanja. Obe se povezuju sa pivot piscem koji se isporučuje u softverskoj komponenti HotXLS spreadsheet component za Delphi i C++Builder, zajedno sa API-jima za ćelije, formule, grafikone i oblikovanje koji su obrađeni drugde na ovom blogu