Skoraj vsak del dednega dvojiškega formata Excel je en sam zapis s čistim dvobajtnim tipom in dvobajtno dolžino. Celica je LABELSST ali NUMBER. Združeno območje je MERGEDCELLS. Večino delovnega lista lahko preberete tako, da se sprehodite skozi zapise enega za drugim in jih usmerite glede na vrsto. Pivot tabele (PivotTables) pa ta ritem prekinejo. Ena sama pivot tabela ni zapis, temveč majhen program, sestavljen iz desetine sodelujočih zapisov, porazdeljenih na dveh različnih mestih v istem toku sestavljenega dokumenta OLE, odnosi med njimi pa so pozicijski, bitno stisnjeni in neprizanesljivi. To je struktura, ki jo večina bralnikov BIFF8 bodisi popolnoma preskoči ali pa jo ohrani kot neprosojne bajte, saj pisanje od začetka pomeni poustvarjanje vsake navzkrižne reference, ki jo vzdržuje Excel sam.
Razlog, zakaj je pivot tabela težka, je v tem, da gre dejansko za dva zvarjena artefakta. Na voljo je predpomnilnik pivot tabele (pivot cache), ki je samozadosten posnetek izvornih podatkov z lastnim podtokom, in pogled tabele, to je postavitev, ki določa, katera polja sedijo na kateri osi. Predpomnilnik in pogled se sklicujeta drug na drugaga prek indeksa. Če se zmotite pri enem indeksu, se datoteka odpre z napako pri osveževanju ali s tiho prazno mrežo.
Predpomnilnik pivot tabele je lasten podtok
Predpomnilnik živi v toku globalnih nastavitev delovnega zvezka kot celoten podtok BIFF, ki ga uokvirja zapis BOF, katerega vrsta dokumenta je 0x0006 (vrednost, ki označuje predpomnilnik pivot tabele, v nasprotju z 0x0005 za delovni zvezek ali 0x0010 za delovni list), zapre pa ga ustrezen EOF. Znotraj tega okvirja je struktura fiksna. Zapis SXDB je glava predpomnilnika. Prenaša število zapisov, število polj predpomnilnika in identifikator toka, ki ga bo pogled tabele navedel za povezavo s tem predpomnilnikom. Vsak izvorni stolpec nato prispeva zapis definicije polja SXFDB, ki mu sledi SXFDBType, ki ga klasificira, in nato edinstvene vrednosti, ki jih je ta stolpec prevzel, oddane kot en zapisan zapis predmeta na ločeno vrednost.
Zapisi predmetov (item records) so tisti del, kjer predpomnilnik upraviči svoj obstoj. Besedilna vrednost postane SXSTRING, številčna vrednost SXNUM, logična vrednost SXBOOLEAN in napaka formule SXERR. Predpomnilnik ne shranjuje izvorne mreže, temveč shranjuje edinstvene vrednosti na polje in indeksno tabelo, ki določa, kateri edinstven predmet je posamezno polje prevzelo za zapis n. Zato programsko grajenje pivot tabele ni le stvar kopiranja celic. Pregledati morate izvorni obseg, sklepati o vrsti vsakega polja iz vrednosti, ki jih vsebuje, jih očistiti dvojnikov v tipiziran seznam predmetov in zabeležiti vsako vrstico kot trojico ali nabor indeksov predmetov. HotXLS stori natanko to: povsem številski stolpec se odda s predmeti SXNUM, stolpec z mešanim besedilom postanejo predmeti SXSTRING, datumi pa se prenašajo kot zaporedne vrednosti skozi isto številsko pot.
SXDBB in bitno stiskanje, ki ga dela zanimivega
Indeksna tabela za posamezen zapis je tehnično najbolj nenavaden del celotne strukture in živi v zapisu SXDBB. Preprosto kodiranje bi indeks predmeta vsakega polja shranilo kot 16-bitno besedo. Excel tega ne počne. Indeks vsakega polja stisne v natanko tisto število bitov, ki je potrebno za naslovitev predmetov tega polja, in nič več. Širina znaša ceil(log2(itemCount + 1)) bitov. Vrednost + 1 je pomembna: dodatna vrednost je oznaka, ki pomeni "prazno, ni vrednosti za to polje v tem zapisu", zato mora polje s tremi edinstvenimi predmeti predstavljati štiri stanja in zato porabi dva bita, ne pa enega bita, kot bi nakazovali trije predmeti sami po sebi. Polje brez kakršnih koli predmetov prispeva nič bitov in se med stiskanjem popolnoma preskoči.
Biti za en zapis so stisnjeni čez vsa polja, nato pa se naslednji zapis začne na novi bajtni meji. Zapisi so bajtno poravnani, ne pa bitno stisnjeni od konca do konca, kar dela naključen dostop do tabele obvladljiv na račun nekaj polnilnih bitov (padding bits) na vrstico. Pakiranje znotraj bajta poteka po pravilu najmanj pomembnega bita naprej (least-significant-bit first). Ko sprejmete ti dve pravili, je kodirnik preprosta črpalka bitov, dekodirnik pa 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, zakaj te podrobnosti ne moremo prezreti, je zgornja meja 8224 bajtov za en zapis BIFF. Vsak zapis v formatu, vključno s pivot zapisi, mora svojo vsebino spraviti v največ 8224 bajtov, obremenjen predpomnilnik pivot tabele s tisoči izvornih vrstic pa bo to mejo presegel veliko preden bo oddal vsako vrstico. Zato je indeksna tabela razdeljena. HotXLS omejuje eno telo SXDBB na 8220 bajtov, kar je meja 8224 bajtov minus štiribajtna glava zapisa za vrsto in dolžino, to deli z bajtno širino enega stisnjenega zapisa, da ugotovi, koliko celih vrstic se prilega, in nato odda toliko nadaljevanih zapisov SXDBB, kolikor zahteva število vrstic. Vsako nadaljevanje se čisto začne na meji zapisa, zato nobena vrstica ni nikoli razdeljena čez dva zapisa. Bralnik, ki pozna bitno širino na zapis, lahko koraka skozi vsak SXDBB zaporedoma, kot da bi šlo za eno neprekinjeno bitno polje.
Postavitev pogleda: SXLI za telo, SXPI za stran
Ko je predpomnilnik zgrajen, je pogled tabele druga polovica. Njegovo jedro so vrstični elementi osi, to so vrstice telesa pivot tabele, ki navajajo vsako kombinacijo vrednosti vrstičnih in stolpčnih polj, ki jih tabela izrisuje. Ti se prenašajo v zapisih SXLI (vrsta zapisa 0x00B5, opisana v [MS-XLS] §2.4.275). En SXLI vsebuje veliko vrstic, spet dokler meja 8224 bajtov ne prisili novega zapisa, in uporablja majhen trik stiskanja: vsaka vrstica shranjuje le to, kako se razlikuje od vrstice nad njo, izraženo kot število skupnih predpon, tako da globoko ugnezdena os ne ponavlja vrednosti zunanjih polj v vsaki vrstici. Vrstica s skupno vsoto (grand-total) in prva vrstica katerega koli zapisa vedno ponastavita to število predpon na nič, tako da bralniku za rekonstrukcijo vrstice nikoli ni treba gledati nazaj čez mejo zapisa.
Os strani, to so spustni seznami filtrov, ki se nahajajo nad pivot tabelo, je ločen zapis. Zapis SXPI (vrsta zapisa 0x00B6, [MS-XLS] §2.4.276) prenaša en desetbajtni vnos na polje strani: indeks pivot polja isxvd, izbrani predmet predpomnilnika iCache, besedo položaja ipos in dedni id objekta objId. Vrednost iCache je tista, na katero moramo biti pozorni. Polje strani, ki prikazuje "(Vse)" in ne filtrira ničesar, shrani oznako 0x7FFD namesto dejanskega indeksa predmeta. Programsko zgrajena pivot tabela se odpre z vsemi polji strani nastavljenimi na "(Vse)", dokler klicatelj ne izbere predmeta. Na tej točki indeks predpomnilnika tega predmeta zamenja oznako in Excel se odpre z že uporabljenim filtrom. Poleg teh se nahajajo podporni zapisi, ki opisujejo posamezna polja in njihovo oblikovanje: SXVD in SXVDEx za definicije pogledov polj, 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 pisca v enem: surovi podatki (blobs) in tipiziran model
Obstaja strukturni razlog, zakaj HotXLS ohranja dve popolnoma ločeni poti za zapisovanje pivot tabele, to pa izhaja neposredno iz zahtev po zvestobi podatkov. Ko se delovni zvezek prebere z diska, so bili njegovi pivot zapisi zapisani s strani Excela ali kakšnega drugega avtorja in lahko uporabljajo različice zapisov, posebnosti razvrščanja ali razširitvene zapise, ki jih noben drug pisec v celoti ne modelira. Edina varna stvar z temi bajti je, da jih vrnemo nespremenjene. Zato je pivot tabela, ki je prišla iz datoteke, označena z FromRawBlobs = True, ob shranjevanju pa pisec dobesedno ponovi ohranjene zapise (blobs). Nič se ne regenerira, nič se ne interpretira znova in krožna pot skozi odpiranje in shranjevanje je bajtno stabilna.
Pivot tabela, ki jo je zgradil program, je nasproten primer. Ni izvirnih bajtov za ohranitev, temveč le tipiziran objektni model: TXLSPivotCache s svojimi polji in seznami predmetov ter TXLSPivotTable s svojimi dodelitvami osi. Ta tabela je označena z FromRawBlobs = False, pisec pa jo serializira na zahtevnejši način: odda nov podtok predpomnilnika BOF = 0x0006, stisne indeksno tabelo SXDBB iz indeksov predmetov, ki jih drži tipiziran model, in razporedi zapise SXLI in SXPI iz konfiguracije osi. Oznaka je tisto, kar omogoča obema vrstama soobstoj v enem delovnem zvezku. Brez nje bi moral en sam pisec bodisi zavreči zvestobo prebranih tabel ali pa zavrniti ustvarjanje novih. Any producer-specific extension records a read-in table carried are kept as supplemental records, reachable through the table's SupplementalRecords list, so a table inspected through the typed model does not lose the parts the model does not describe.
Gradnja pivot tabele v kodi
Vse zgoraj opisano orodje se nahaja za enim samim klicem. Metoda AddPivotTable sprejme izvorni obseg v zapisu A1, ciljno celico, kjer se zasidra zgornji levi kot tabele, in ime. Razčleni obseg, ga pregleda, da ugotovi vrste polj in zgradi predpomnilnik (pri čemer ponovno uporabi obstoječi predpomnilnik, če se druga tabela že povezuje na isti obseg), ter vrne tipizirano TXLSPivotTable z enim poljem na izvorni stolpec, pri čemer je vsako polje na začetku zunaj osi. Nato postavite polja na osi in izberete agregacijo. Signatura je natanko takšna, predpomnilnik, stiskanje SXDBB in zapisi pogleda pa se ustvarijo namesto vas ob shranjevanju.
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;Prva vrstica izvornega obsega se prebere kot glava, ki poimenuje polja predpomnilnika, zato AddRowField('Region') poišče stolpec po besedilu glave in ne po položaju. Ker je vrnjena tabela tipiziran model z FromRawBlobs = False, pisec izbere pot od začetka: zgradi samozadosten predpomnilnik, ki ni odvisen od tega, ali je izvorni obseg ob času osvežitve še vedno prisoten, kar je natanko lastnost, ki jo želite, ko se bo pivot tabela poslala prejemniku, ki lahko premakne ali izbriše osnovne podatke.
Branje in usklajevanje zapisov pivot tabel in predpomnilnikov datoteke, ki je niste ustvarili sami, vključno s potjo ohranjanja surovih zapisov (raw-blob), je obravnavano v vodniku za revizijo delovnih zvezkov in konverzijsko delovno mizo. Ko izvorni obseg obsega več deset tisoč vrstic in tok SXDBB obsega veliko nadaljevanih zapisov, tehnike v zapisih o zmogljivosti velikih delovnih zvezkov preprečujejo, da bi gradnja predpomnilnika prevladovala v vašem izvajanju. Oboje se ujema s piscem pivot tabel, ki se dostavlja v HotXLS spreadsheet component za Delphi in C++Builder, skupaj z vmesniki API za celice, formule, grafikone in oblikovanje, ki so obravnavani drugje na tem blogu.