Beveik kiekviena senojo dvejetainio Excel formato dalis yra vienas įrašas su aiškiu dviejų baitų tipu ir dviejų baitų ilgiu. Langelis yra LABELSST arba NUMBER. Sujungtas regionas yra MERGEDCELLS. Didžiąją dalį darbalapio galima perskaityti peržiūrint įrašus po vieną ir reaguojant į tipo žodį. Suvestinės lentelės (PivotTables) pažeidžia šį ritmą. Viena suvestinė lentelė nėra paprastas įrašas – tai nedidelė programa, sudaryta iš dešimčių bendradarbiaujančių įrašų, išsibarsčiusių dviejose skirtingose to paties OLE sudėtinio dokumento srauto vietose, o ryšiai tarp jų yra poziciniai, supakuoti bitais ir negailestingi. Tai yra struktūra, kurią dauguma BIFF8 skaitytuvų arba visiškai praleidžia, arba išsaugo kaip neaiškius baitus, nes jos rašymas nuo nulio reikalauja atkurti kiekvieną kryžminę nuorodą, kurią palaiko pats Excel.
Suvestinė lentelė yra sudėtinga todėl, kad ją sudaro du sujungti elementai. Yra suvestinės talpykla (pivot cache) – atskira šaltinio duomenų momentinė nuotrauka su savo srautu – ir yra lentelės vaizdas (table view), kuris nustato, kurie laukai atvaizduojami kurioje ašyje. Talpykla ir vaizdas nurodo vienas kitą pagal indeksą. Suklyskite bent su vienu indeksu, ir failas atsidarys su atnaujinimo klaida arba tyliai tuščiu tinkleliu.
Suvestinės talpykla yra atskiras srautas
Talpykla saugoma darbaknygės globaliame sraute kaip pilnas BIFF srautas, kurį įrėmina BOF įrašas, kurio dokumento tipas yra 0x0006 (reikšmė, žyminti suvestinės talpyklą, priešingai nei 0x0005 darbaknygei arba 0x0010 darbalapiui), ir uždaro atitinkamas EOF. Šiame rėme struktūra yra fiksuota. SXDB įrašas yra talpyklos antraštė. Jame nurodomas įrašų skaičius, talpyklos laukų skaičius ir srauto identifikatorius, kurį lentelės vaizdas nurodys, kad susisietų su šia talpykla. Kiekvienas šaltinio stulpelis pateikia SXFDB lauko apibrėžimo įrašą, po kurio seka jį klasifikuojantis SXFDBType, o tada – unikalios to stulpelio reikšmės, pateikiamos po vieną įrašą kiekvienai skirtingai reikšmei.
Reikšmų įrašai yra tai, kur talpykla atlieka savo pagrindinį darbą. Tekstinė reikšmė tampa SXSTRING, skaitinė – SXNUM, loginė – SXBOOLEAN, o formulės klaida – SXERR. Talpykla saugo ne patį šaltinio tinklelį, o unikalias lauko reikšmes ir indeksų lentelę, kuri nurodo, kokią unikalią reikšmę kiekvienas laukas turėjo įraše n. Todėl suvestinės lentelės kūrimas programiškai nėra tiesiog langelių kopijavimas. Turite nuskaityti šaltinio diapazoną, nustatyti kiekvieno lauko tipą pagal jo reikšmes, pašalinti dublikatus sukuriant tipizuotą elementų sąrašą ir įrašyti kiekvieną eilutę kaip elementų indeksų rinkinį. HotXLS daro būtent tai: skaitinis stulpelis išvedamas su SXNUM elementais, mišrus tekstinis stulpelis tampa SXSTRING elementais, o datos perduodamos kaip skaitinės sekos reikšmės per tą patį kelią.
SXDBB ir bitų pakavimas, kuris daro jį įdomų
Kiekvieno įrašo indeksų lentelė yra techniškai įdomiausia visos struktūros dalis, esanti SXDBB įraše. Paprastas kodavimo būdas išsaugotų kiekvieno lauko elemento indeksą kaip 16 bitų žodį. Excel to nedaro. Jis supakuoja kiekvieno lauko indeksą į tikslų bitų skaičių, reikalingą to lauko reikšmėms adresuoti, ir ne daugiau. Plotis yra ceil(log2(itemCount + 1)) bitų. Šis + 1 yra svarbus: papildoma reikšmė yra sargybinis, reiškiantis „tuščia, šiame įraše laukas neturi reikšmės“, todėl laukui su trimis skirtingais elementais reikia atvaizduoti keturias būsenas, kas užima du bitus, o ne vieną, kurio pakaktų tik trims elementams. Laukas be elementų užima nulį bitų ir pakavimo metu yra visiškai praleidžiamas.
Vieno įrašo bitai sujungiami per visus laukus, o kitas įrašas prasideda ties nauja baito riba. Įrašai yra lygiačiuojami pagal baitus, o ne supakuoti bitais vienas po kito, todėl atsitiktinė prieiga prie lentelės yra įmanoma nedidelių užpildymo bitų (padding bits) eilutėje sąskaita. Pakavimas baite vyksta pradedant nuo mažiausiai reikšmingo bito (least-significant-bit first). Priėmus šias dvi taisykles, kodavimo įrenginys veikia kaip paprastas bitų siurblys, o dekoderis yra jo veidrodis.
// 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;Šios detalės negalima ignoruoti dėl 8224 baitų ribos vienam BIFF įrašui. Kiekvienas šio formato įrašas, įskaitant suvestinių lentelių įrašus, turi tilpti į daugiausiai 8224 baitus, o aktyvi suvestinės talpykla su tūkstančiais šaltinio eilučių viršys šią ribą gerokai prieš išvedant visas eilutes. Todėl indeksų lentelė yra padalijama. HotXLS riboja vieną SXDBB turinį iki 8220 baitų (tai yra 8224 įrašo riba minus keturių baitų įrašo antraštė tipui ir ilgiui), padalija tai iš vieno supakuoto įrašo pločio baitais, kad sužinotų, kiek eilučių telpa, ir tada išveda tiek tęsiamų SXDBB įrašų, kiek reikalauja eilučių skaičius. Kiekvienas tęsinys prasideda iš naujo ties įrašo riba, todėl jokia eilutė nėra perkertama per du įrašus. Skaitytuvas, žinantis vieno įrašo bitų plotį, gali eiti per kiekvieną SXDBB iš eilės tarsi tai būtų vienas ištisinis bitų masyvas.
Vaizdo išdėstymas: SXLI kūnui, SXPI puslapiui
Sukūrus talpyklą, lentelės vaizdas yra antroji pusė. Jo pagrindas yra ašių linijų elementai – suvestinės lentelės kūno eilutės, kurios išvardija kiekvieną eilučių ir stulpelių lauko reikšmių derinį, kurį lentelė nubraižo. Šie duomenys perduodami SXLI įrašuose (įrašo tipas 0x00B5, aprašytas [MS-XLS] §2.4.275). Vienas SXLI saugo daug eilučių, kol 8224 baitų riba priverčia sukurti naują įrašą, ir naudoja nedidelį suspaudimo triuką: kiekviena eilutė saugo tik tai, kuo ji skiriasi nuo virš jos esančios eilutės (bendro prefikso skaičių), todėl giliai lizdinė ašis nekartoja išorinio lauko reikšmių kiekvienoje eilutėje. Bendros sumos (grand-total) eilutė ir pirmoji bet kurio įrašo eilutė visada atstato šį prefikso skaičių į nulį, todėl skaitytuvui nereikia ieškoti duomenų už įrašo ribų eilutės atkūrimui.
Puslapio ašis (filtro išplečiamieji sąrašai virš suvestinės lentelės) yra atskiras įrašas. SXPI (įrašo tipas 0x00B6, [MS-XLS] §2.4.276) perneša po vieną dešimties baitų įrašą kiekvienam puslapio laukui: suvestinės lauko indeksą isxvd, pasirinktą talpyklos elementą iCache, pozicijos žodį ipos ir seną objekto ID objId. Svarbi yra iCache reikšmė. Puslapio laukas, rodantis „(All)“ (nieko nefiltruojantis), saugo sargybinio reikšmę 0x7FFD, o ne tikrąjį elemento indeksą. Programiškai sukurta lentelė atsidaro su visais puslapio laukais, nustatytais į „(All)“, kol iškvietėjas iš anksto nepasirenka elemento – tada to elemento talpyklos indeksas pakeičia sargybinį, ir Excel atsidaro su uždarytu filtru. Šalia jų yra pagalbiniai įrašai, aprašantys laukus bei jų formatavimą: SXVD ir SXVDEx laukų vaizdo apibrėžimams, SXIVD laukų indeksų sąrašams, nustatantiems kiekvienos ašies tvarką, bei SXFormat skaičių formatavimui, kur kiekvienas iš jų nurodo tą pačią talpyklą, kurią naudoja kūno eilutės.
Du rašymo būdai viename: neapdoroti duomenys ir tipizuotas modelis
Yra struktūrinė priežastis, kodėl HotXLS naudoja du visiškai atskirus kelius suvestinės lentelės įrašymui – tai kyla iš suderinamumo reikalavimų. Kai darbaknygė nuskaitoma iš disko, jos suvestinės įrašai buvo įrašyti Excel arba kito kūrėjo, ir jie gali naudoti įrašų variantus, tvarkos keistumus ar plėtinių įrašus, kurių joks trečiųjų šalių įrankis pilnai nemodeliuoja. Vienintelė saugi funkcija su šiais baitais yra grąžinti juos nepakeistus. Todėl suvestinė lentelė, gauta iš failo, pažymima savybe FromRawBlobs = True, o išsaugojimo metu rašymo programa atkartoja išsaugotus įrašų blokus pažodžiui. Niekas nėra generuojama iš naujo ar interpretuojama, o failo atidarymas bei išsaugojimas išlieka baitiškai stabilus.
Suvestinė lentelė, kurią sukūrė pati programa, yra priešingas atvejis. Nėra jokių originalių baitų išsaugojimui – tik tipizuotas objekto modelis: TXLSPivotCache su savo laukais bei elementų sąrašais ir TXLSPivotTable su ašių priskyrimais. Ši lentelė pažymima kaip FromRawBlobs = False, o rašymo programa ją serializuoja sudėtingu būdu: išveda naują BOF = 0x0006 talpyklos srautą, supakuoja SXDBB indeksų lentelę iš modelio turimų indeksų bei išdėsto SXLI ir SXPI įrašus iš ašių konfigūracijos. Ši žymė leidžia abiem tipams egzistuoti vienoje darbaknygėje. Be jos rašymo programa turėtų arba atmesti nuskaitytų lentelių suderinamumą, arba atsisakyti kurti naujas. Bet kokie specifiniai plėtinių įrašai, kuriuos turėjo nuskaityta lentelė, išlaikomi kaip papildomi įrašai, pasiekiami per lentelės SupplementalRecords sąrašą, todėl modelis nepraranda dalių, kurių pats neaprašo.
Suvestinės lentelės kūrimas kode
Visa ši sistema slepiasi po vienu iškvietimu. AddPivotTable priima šaltinio diapazoną A1 žymėjimu, tikslo langelį, kuriame bus lentelės viršutinis kairysis kampas, ir pavadinimą. Metodas išanalizuoja diapazoną, nuskaito jį laukų tipų nustatymui bei talpyklos suformavimui (pakartotinai naudodamas esamą talpyklą, jei kita lentelė jau susieta su tuo pačiu diapazonu) ir grąžina tipizuotą TXLSPivotTable su vienu lauku kiekvienam šaltinio stulpeliui. Tada galite išdėstyti laukus ašyse ir pasirinkti agregavimą. Parašas yra būtent toks, o talpykla, SXDBB pakavimas bei vaizdo įrašai sugeneruojami išsaugojimo metu.
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;Pirmoji šaltinio diapazono eilutė nuskaitoma kaip antraštė, kuri pavadina talpyklos laukus, todėl AddRowField('Region') suranda stulpelį pagal jo antraštės tekstą, o ne pagal poziciją. Kadangi grąžinta lentelė yra tipizuotas modelis su FromRawBlobs = False, rašymo programa naudoja kūrimo nuo nulio kelią: ji suformuoja atskirą talpyklą, kuri nepriklauso nuo to, ar šaltinio diapazonas vis dar egzistuoja duomenų atnaujinimo metu – tai yra būtent tai, ko reikia, kai suvestinė lentelė siunčiama gavėjui, kuris gali perkelti arba ištrinti pagrindinius duomenis.
Reading and reconciling the pivot and cache records of a file you did not produce, including the raw-blob preservation path, is covered in the workbook audit and conversion workbench walkthrough. When the source range runs to tens of thousands of rows and the SXDBB stream spans many continued records, the techniques in the large-workbook performance notes keep the cache build from dominating your runtime. Both pair with the pivot writer that ships in the HotXLS spreadsheet component for Delphi and C++Builder, alongside the cell, formula, chart, and formatting APIs covered elsewhere on this blog.