Aproape fiecare parte a formatului binar Excel vechi este o singură înregistrare cu un tip clar de doi octeți și o lungime de doi octeți. O celulă este un LABELSST sau un NUMBER. O regiune îmbinată este un MERGEDCELLS. Puteți citi cea mai mare parte a unei foi de lucru parcurgând înregistrările pe rând și trimițându-le în funcție de cuvântul de tip. Tabelele pivot (PivotTables) întrerup acest ritm. Un singur tabel pivot nu este o înregistrare, ci este un mic program format din zeci de înregistrări cooperante răspândite în două locuri diferite din același flux de document compus OLE, iar relațiile dintre ele sunt poziționale, bit-packed și stricte. Aceasta este structura pe care majoritatea cititoarelor BIFF8 fie o omit complet, fie o păstrează ca octeți opaci, deoarece scrierea uneia de la zero înseamnă reproducerea fiecărei referințe încrucișate pe care Excel însuși o menține.
Motivul pentru care un tabel pivot este dificil este că reprezintă de fapt două artefacte sudate împreună. Există cache-ul pivot, o captură autonomă a datelor sursă cu propriul său sub-flux (substream), și există vizualizarea tabelului, aspectul care spune ce câmpuri se află pe fiecare axă. Cache-ul și vizualizarea se referă reciproc prin index. Greșiți un singur index și fișierul se deschide cu o eroare de actualizare sau cu o grilă goală în mod silențios.
Cache-ul pivot este un sub-flux de sine stătător
Cache-ul trăiește în fluxul global al registrului de lucru ca un sub-flux BIFF complet, încadrat de o înregistrare BOF al cărei tip de document este 0x0006 (valoarea care marchează un cache pivot, spre deosebire de 0x0005 pentru registrul de lucru sau 0x0010 pentru o foaie de lucru) și închis de înregistrarea EOF corespunzătoare. În interiorul acelui cadru, structura este fixă. O înregistrare SXDB este antetul cache-ului. Acesta conține numărul de înregistrări, numărul de câmpuri din cache și identificatorul de flux pe care vizualizarea tabelului îl va cita pentru a se conecta la acest cache. Fiecare coloană sursă contribuie apoi cu o înregistrare de definire a câmpului SXFDB urmată de un SXFDBType care o clasifică, și apoi valorile unice pe care le-a luat acea coloană, emise ca o înregistrare de element tipizat per valoare distinctă.
Înregistrările de elemente sunt locul în care cache-ul își dovedește utilitatea. O valoare de text devine un SXSTRING, o valoare numerică un SXNUM, o valoare logică un SXBOOLEAN, iar o eroare de formulă un SXERR. Cache-ul nu stochează grila sursă, ci stochează valorile distincte per câmp plus un tabel de indici care spune, pentru înregistrarea n, ce element distinct a luat fiecare câmp. De aceea, construirea programatică a unui tabel pivot nu este o chestiune de copiere a celulelor. Trebuie să scanați intervalul sursă, să deduceți tipul fiecărui câmp din valorile pe care le conține, să le deduplicați într-o listă de elemente tipizate și să înregistrați fiecare rând ca un tuplu de indici de elemente. HotXLS face exact acest lucru: o coloană complet numerică este emisă cu elemente SXNUM, o coloană cu text mixt devine elemente SXSTRING, iar datele sunt transportate ca valori seriale prin aceeași cale numerică.
SXDBB și împachetarea biților care îl face interesant
Tabelul de indici per înregistrare este cea mai curioasă parte din punct de vedere tehnic a întregii structuri și trăiește în înregistrarea SXDBB. Codificarea simplă ar stoca indexul fiecărui element ca un cuvânt pe 16 biți. Excel nu face asta. Împachetează indexul fiecărui câmp în numărul exact de biți necesari pentru a adresa elementele acelui câmp și nimic mai mult. Lățimea este de ceil(log2(itemCount + 1)) biți. Valoarea + 1 contează: valoarea suplimentară este o santinelă care înseamnă „gol, nicio valoare pentru acest câmp în această înregistrare”, astfel încât un câmp cu trei elemente distincte trebuie să reprezinte patru stări și, prin urmare, necesită doi biți, nu singurul bit pe care l-ar sugera trei elemente. Un câmp fără niciun element nu contribuie cu niciun bit și este omis complet în timpul împachetării.
Biții pentru o înregistrare sunt concatenați pe toate câmpurile, apoi următoarea înregistrare începe pe o graniță de octet nouă. Înregistrările sunt aliniate la nivel de octet, nu împachetate la nivel de bit cap la cap, ceea ce face ca accesul aleatoriu în tabel să fie realizabil cu costul câtorva biți de umplutură (padding) per rând. Împachetarea în cadrul unui octet este least-significant-bit first (cel mai puțin semnificativ bit primul). Odată ce acceptați aceste două reguli, codificatorul este o simplă pompă de biți, iar decodificatorul este oglinda sa.
// 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;Motivul pentru care acest detaliu nu poate fi ignorat este plafonul de 8224 de octeți pentru o singură înregistrare BIFF. Fiecare înregistrare din format, inclusiv înregistrările pivot, trebuie să își încadreze sarcina utilă în cel mult 8224 de octeți, iar un cache pivot încărcat cu mii de rânduri sursă va depăși această limită cu mult înainte de a emite fiecare rând. Astfel, tabelul de indici este împărțit. HotXLS limitează un singur corp SXDBB la 8220 de octeți, care este limita de înregistrare de 8224 minus antetul înregistrării de patru octeți pentru tip și lungime, împarte această valoare la lățimea în octeți a unei înregistrări împachetate pentru a afla câte rânduri întregi se potrivesc, apoi emite atâtea înregistrări SXDBB continuate câte cere numărul de rânduri. Fiecare continuare repornește curat pe o graniță de înregistrare, astfel încât niciun rând nu este vreodată tăiat pe parcursul a două înregistrări. Un cititor care cunoaște lățimea de biți per înregistrare poate parcurge fiecare SXDBB în secvență ca și cum ar fi o singură matrice de biți contiguă.
Aspectul vizualizării: SXLI pentru corp, SXPI pentru pagină
Cu cache-ul construit, vizualizarea tabelului este a doua jumătate. Nucleul său îl reprezintă elementele de linie ale axei, rândurile corpului pivotului care enumeră fiecare combinație de valori de câmp de rând și câmp de coloană pe care le desenează tabelul. Acestea sunt transportate în înregistrări SXLI (tip de înregistrare 0x00B5, descris în [MS-XLS] §2.4.275). O singură înregistrare SXLI deține multe linii, din nou până când limita de 8224 de octeți forțează o nouă înregistrare, și folosește un mic truc de compresie: fiecare linie stochează doar modul în care diferă de linia de deasupra ei, exprimat ca un număr de prefix comun, astfel încât o axă profund imbricată nu repetă valorile câmpului exterior pe fiecare rând. Linia totalului general și prima linie a oricărei înregistrări resetează întohdeauna acel număr de prefix la zero, astfel încât un cititor nu trebuie să se uite niciodată înapoi peste o graniță de înregistrare pentru a reconstrui o linie.
Axa paginii, listele derulante de filtrare care se află deasupra unui tabel pivot, reprezintă o înregistrare separată. SXPI (tip de înregistrare 0x00B6, [MS-XLS] §2.4.276) transportă o intrare de zece octeți per câmp de pagină: indexul câmpului pivot isxvd, elementul cache selectat iCache, un cuvânt de poziție ipos și un ID de obiect vechi objId. Valoarea iCache este cea de urmărit. Un câmp de pagină care afișează „(All)”, nefiltrând nimic, stochează valoarea santinelă 0x7FFD în loc de un index de element real. Un pivot construit programatic se deschide cu fiecare câmp de pagină setat la „(All)” până când apelantul pre-selectează un element, moment în care indexul cache al acelui element înlocuiește santinela, iar Excel se deschide cu filtrul deja aplicat. Alături de acestea se află înregistrările de asistență care descriu câmpurile individuale și formatarea lor, SXVD și SXVDEx pentru definițiile vizualizării câmpurilor, SXIVD pentru listele de indici de câmpuri care ordonează fiecare axă, și SXFormat pentru formatarea numerelor, fiecare indexând înapoi în același cache pe care îl referă liniile corpului.
Două mecanisme de scriere într-unul singur: blob-uri brute și modelul tipizat
Există un motiv structural pentru care HotXLS menține două căi complet separate pentru scrierea unui tabel pivot, iar acesta provine direct din cerințele de fidelitate. Când un registru de lucru este citit de pe disc, înregistrările sale pivot au fost scrise de Excel sau de un alt producător și pot folosi variante de înregistrare, particularități de ordonare sau înregistrări de extensie pe care niciun scriitor terț nu le modelează complet. Singurul lucru sigur de făcut cu acești octeți este returnarea lor neschimbată. Astfel, un tabel pivot care provine dintr-un fișier este marcat cu FromRawBlobs = True, iar la salvare scriitorul redă blocurile de înregistrare păstrate cuvânt cu cuvânt (verbatim). Nimic nu este regenerat, nimic nu este reinterpretat, iar un proces complet de deschidere și salvare este stabil la nivel de octet.
Un tabel pivot pe care l-a construit programul reprezintă cazul opus. Nu există octeți originali de păstrat, ci doar modelul de obiecte tipizat: un TXLSPivotCache cu câmpurile și listele sale de elemente și un TXLSPivotTable cu alocările sale de axe. Acel tabel este marcat cu FromRawBlobs = False, iar scriitorul îl serializează pe calea dificilă, emițând un nou sub-flux de cache BOF = 0x0006, împachetând tabelul de indici SXDBB din indicii de elemente pe care îi deține modelul tipizat și așezând înregistrările SXLI și SXPI din configurația axei. Indicatorul este cel care permite ambelor tipuri să coexiste într-un singur registru de lucru. Fără el, un singur scriitor ar trebui fie să renunțe la fidelitatea tabelelor citite, fie să refuze să genereze altele noi. Orice înregistrări de extensie specifice producătorului pe care le conținea un tabel citit sunt păstrate ca înregistrări suplimentare, accesibile prin lista SupplementalRecords a tabelului, astfel încât un tabel inspectat prin modelul tipizat nu pierde părțile pe care modelul nu le descrie.
Construirea unui tabel pivot în cod
Întregul mecanism de mai sus se află în spatele unui singur apel. AddPivotTable preia intervalul sursă în notație A1, celula de destinație unde se ancorează colțul din stânga-sus al tabelului și un nume. Acesta analizează intervalul, îl scanează pentru a deduce tipurile de câmpuri și a construi cache-ul (reutilizând un cache existent dacă un alt tabel se conectează deja la același interval) și returnează un obiect tipizat TXLSPivotTable cu un singur câmp per coloană sursă, fiecare câmp fiind inițial în afara axelor. Apoi plasați câmpurile pe axe și alegeți o agregare. Semnătura este exact aceasta, iar cache-ul, împachetarea SXDBB și înregistrările de vizualizare sunt toate produse în locul dvs. la salvare.
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;Primul rând din intervalul sursă este citit ca antetul care numește câmpurile din cache, astfel încât AddRowField('Region') se potrivește cu o coloană după textul antetului său și nu după poziție. Deoarece tabelul returnat este un model tipizat cu FromRawBlobs = False, scriitorul alege calea de la zero: construiește un cache autonom care nu depinde de prezența intervalului sursă la momentul actualizării, ceea ce reprezintă exact proprietatea pe care o doriți când tabelul pivot va fi trimis unui destinatar care poate muta sau șterge datele de bază.
Citirea și reconcilierea înregistrărilor de pivot și de cache ale unui fișier pe care nu l-ați produs, inclusiv calea de păstrare a blob-urilor brute, sunt acoperite în ghidul despre auditul registrului de lucru și bancul de lucru pentru conversie. Când intervalul sursă ajunge la zeci de mii de rânduri, iar fluxul SXDBB se extinde pe multe înregistrări continuate, tehnicile din notele de performanță pentru registre de lucru mari previn ca generarea cache-ului să vă domine timpul de rulare. Ambele se corelează cu scriitorul pivot care este livrat în HotXLS spreadsheet component pentru Delphi și C++Builder, alături de API-urile pentru celule, formule, diagrame și formatare acoperite în alte părți ale acestui blog.