Gotovo svaki dio naslije?enog Excel binarnog formata je jedan zapis s ?istim dvobajtnim tipom i dvobajtnom duljinom. ?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 ?alju?i ih na temelju rije?i tipa. PivotTable tablice prekidaju taj ritam. Jedna pivot tablica nije zapis, to je mali program sastavljen od desetaka suradni?kih zapisa raspore?enih na dva razli?ita mjesta u istom toku OLE slo?enog dokumenta, a odnosi izme?u njih su pozicijski, bit-pakirani 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 jest taj ?to su to zapravo dva artefakta spojena zajedno. Postoji pivot predmemorija, samostalna snimka izvornih podataka s vlastitim podtokom, i postoji prikaz tablice, izgled koji govori koja polja sjede na kojoj osi. Predmemorija i prikaz upu?uju jedan na drugi putem indeksa. Pogrije?ite li jedan indeks, datoteka se otvara uz pogre?ku osvje?avanja ili tiho praznu mre?u
Pivot predmemorija je zaseban podtok
Predmemorija ?ivi u toku globalnih varijabli radne knjige kao potpuni BIFF podtok, uokviren zapisom BOF ?iji je tip dokumenta 0x0006 (vrijednost koja ozna?ava pivot predmemoriju, nasuprot 0x0005 za radnu knjigu ili 0x0010 za radni list) i zatvoren odgovaraju?im EOF. Unutar tog okvira struktura je fiksna. Zapis SXDB je zaglavlje predmemorije. On nosi broj zapisa, broj polja predmemorije i identifikator toka koji ?e prikaz tablice citirati kako bi se povezao s ovom predmemorijom. Svaki izvorni stupac zatim doprinosi zapisom definicije polja SXFDB pra?enim tipom SXFDBType koji ga klasificira, a zatim jedinstvenim vrijednostima koje je taj stupac poprimio, emitiranim kao jedan tipizirani zapis stavke po jedinstvenoj vrijednosti
Zapisi stavki su mjesto gdje predmemorija opravdava svoj rad. Tekstualna vrijednost postaje SXSTRING, numeri?ka vrijednost SXNUM, logi?ka vrijednost SXBOOLEAN, a pogre?ka formule SXERR. Predmemorija ne pohranjuje izvornu mre?u, ve? pohranjuje jedinstvene vrijednosti 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 temelju vrijednosti koje sadr?i, ukloniti duplikate u tipizirani popis stavki i zabilje?iti svaki redak kao torku indeksa stavki. HotXLS radi upravo to: potpuno numeri?ki stupac emitira se sa stavkama SXNUM, stupac s mije?anim tekstom postaje stavka SXSTRING, a datumi se prenose kao serijske vrijednosti kroz istu numeri?ku putanju
SXDBB i pakiranje bitova koje ga ?ini zanimljivim
Tablica indeksa po zapisu tehni?ki je najzanimljiviji dio cijele strukture, a ?ivi u zapisu SXDBB. Naivno kodiranje bi pohranilo indeks stavke svakog polja kao 16-bitnu rije?. Excel to ne radi. On pakira indeks svakog polja u to?no onaj broj bitova koji je potreban za adresiranje stavki tog polja, i ni?ta vi?e. ?irina je ceil(log2(itemCount + 1)) bitova. Vrijednost + 1 je va?na: dodatna vrijednost je sentinel koji zna?i "prazno, nema vrijednosti za ovo polje u ovom zapisu", pa polje s tri jedinstvene stavke treba predstavljati ?etiri stanja i stoga uzima dva bita, a ne jedan bit koji bi same tri stavke sugerirale. Polje bez stavki uop?e pridonosi s nula bitova i u potpunosti se preska?e tijekom pakiranja
Bitovi za jedan zapis spajaju se kroz sva polja, a zatim sljede?i zapis po?inje na novoj granici bajta. Zapisi su poravnani po bajtovima, a ne pakirani po bitovima s kraja na kraj, ?to ?ini nasumi?ni pristup tablici izvedivim uz cijenu nekoliko bitova podstave po retku. Pakiranje unutar bajta ide od najmanje zna?ajnog bita prema naprijed. Jednom kada prihvatite ta dva pravila, enkoder je jednostavna pumpa bitova, a dekoder je njegovo zrcalo
// 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 aktivna pivot predmemorija s tisu?ama izvornih redaka preletjet ?e to davno prije nego ?to emitira svaki redak. Zato je tablica indeksa podijeljena. HotXLS ograni?ava jedno tijelo SXDBB na 8220 bajtova, ?to je limit zapisa od 8224 minus ?etverobajtno zaglavlje zapisa tipa i duljine, dijeli to sa ?irinom bajta jednog pakiranog zapisa kako bi saznao koliko cijelih redaka stane, a zatim emitira onoliko nastavaka zapisa SXDBB koliko to broj redaka zahtijeva. Svaki nastavak po?inje ?isto na granici zapisa, tako da nijedan redak nikada nije presje?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 neprekinutom nizu bitova
Izgled prikaza: SXLI za tijelo, SXPI za stranicu
S izgra?enom predmemorijom, prikaz tablice je druga polovica. Njezina sr? su stavke linije osi, redovi tijela pivota koji nabrajaju svaku kombinaciju vrijednosti polja redaka i polja stupaca 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 pohranjuje samo kako se razlikuje od linije iznad nje, izra?eno kao broj zajedni?kih prefiksa, tako da duboko ugnije??ena os ne ponavlja vrijednosti vanjskih polja u svakom retku. Linija sveukupnog zbroja i prva linija bilo kojeg zapisa uvijek vra?aju taj broj prefiksa na nulu, tako da ?ita? nikada ne mora gledati unatrag preko granice zapisa kako bi rekonstruirao liniju
Os stranice, padaju?i izbornici filtara 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 predmemorije iCache, rije? pozicije ipos i naslije?eni ID objekta objId. Vrijednost iCache je ona na koju treba paziti. Polje stranice koje prikazuje "(All)", ne filtriraju?i ni?ta, pohranjuje sentinel 0x7FFD umjesto stvarnog indeksa stavke. Programski izgra?en pivot otvara se sa svakim poljem stranice postavljenim na "(All)" dok pozivatelj unaprijed ne odabere stavku, na kojoj to?ki indeks predmemorije te stavke zamjenjuje sentinel i Excel se otvara s ve? primijenjenim filtrom. Uz njih stoje prate?i zapisi koji opisuju pojedina?na polja i njihovo oblikovanje, SXVD and SXVDEx za definicije prikaza polja, SXIVD za popise indeksa polja koji ure?uju svaku os i SXFormat za oblikovanje brojeva, od kojih svaki indeksira natrag u istu predmemoriju na koju se odnose linije tijela
Dva pisca u jednom: sirovi blobovi i tipizirani model
Postoji strukturni razlog za?to HotXLS ?uva dvije potpuno odvojene putanje za pisanje pivot tablice, a on dolazi izravno iz zahtjeva za vjerno??u. Kada se radna knjiga ?ita s diska, njezine pivot zapise napisao je Excel ili neki drugi proizvo?a?, i oni mogu koristiti varijante zapisa, neobi?nosti u redoslijedu ili zapise pro?irenja koje nijedan pisac tre?e strane ne modelira u potpunosti. Jedina sigurna stvar s tim bajtovima jest vratiti ih nepromijenjene. Stoga je pivot tablica koja je do?la iz datoteke ozna?ena s FromRawBlobs = True, a pri spremanju pisac doslovno reproducira sa?uvane blobove zapisa. Ni?ta se ne regenerira, ni?ta se ponovno ne tuma?i, a kru?no putovanje kroz otvaranje i spremanje 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 popisima stavki, te TXLSPivotTable sa svojim dodjelama osi. Ta je tablica ozna?ena s FromRawBlobs = False, a pisac je serijalizira na te?i na?in, emitiraju?i svje?i podtok predmemorije BOF = 0x0006, pakiraju?i indeksnu tablicu SXDBB iz indeksa stavki koje dr?i tipizirani model i raspore?uju?i zapise SXLI i SXPI iz konfiguracije osi. Zastavica je ono ?to omogu?uje objema vrstama da koegzistiraju u jednoj radnoj knjizi. Bez nje bi jedan pisac morao ili odbaciti vjernost u?itanih tablica ili odbiti generiranje novih. Svi zapisi pro?irenja specifi?ni za proizvo?a?a koje je u?itana tablica nosila ?uvaju se kao dopunski zapisi, dostupni kroz popis tablice SupplementalRecords list, tako da tablica pregledana kroz tipizirani model ne gubi dijelove koje model ne opisuje
Izgradnja pivot tablice u kodu
Sav gornji mehanizam nalazi se iza jednog poziva. AddPivotTable uzima izvorni raspon u bilje?enju A1, odredi?nu ?eliju na kojoj se sidri gornji lijevi kut tablice i naziv. On analizira raspon, skenira ga kako bi zaklju?io tipove polja i izgradio predmemoriju (ponovno koriste?i postoje?u predmemoriju ako se druga tablica ve? ve?e na isti raspon) te vra?a tipizirani TXLSPivotTable s jednim poljem po izvornom stupcu, pri ?emu je svako polje u po?etku izvan osi. Zatim postavljate polja na osi i birate agregaciju. Potpis je to?no ovakav, a predmemorija, pakiranje SXDBB i zapisi prikaza proizvode se za vas u trenutku spremanja
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 redak izvornog raspona ?ita se kao zaglavlje koje imenuje polja predmemorije, pa AddRowField('Region') odgovara stupcu prema tekstu zaglavlja, a ne prema poziciji. Budu?i da je vra?ena tablica tipizirani model s FromRawBlobs = False, pisac ide putem stvaranja ispo?etka: gradi samostalnu predmemoriju koja ne ovisi o tome da je izvorni raspon jo? uvijek prisutan u trenutku osvje?avanja, ?to je upravo svojstvo koje ?elite kada se pivot ?alje primatelju koji mo?e premjestiti ili izbrisati temeljne podatke
?itanje i uskla?ivanje pivot zapisa i zapisa predmemorije datoteke koju niste sami proizveli, uklju?uju?i putanju o?uvanja sirovih blobova, pokriveno je u vodi?u za reviziju radne knjige i radni stol za pretvorbu. Kada izvorni raspon dose?e desetke tisu?a redaka, a tok SXDBB obuhva?a mnogo nastavaka zapisa, tehnike u bilje?kama o performansama s velikim radnim knjigama sprje?avaju da izgradnja predmemorije dominira va?im vremenom izvo?enja. Obje se povezuju s pivot piscem koji se isporu?uje u softverskoj komponenti HotXLS spreadsheet component za Delphi i C++Builder, zajedno s API-jima za ?elije, formule, grafikone i oblikovanje koji su obra?eni drugdje na ovom blogu