Melkein jokainen osa vanhaa Excelin binaarimuotoa on yksittäinen tietue, jolla on selkeä kaksitavuinen tyyppi ja kaksitavuinen pituus. Solu on LABELSST tai NUMBER. Yhdistetty alue on MERGEDCELLS. Voit lukea suurimman osan laskentataulukosta käymällä tietueita läpi yksi kerrallaan ja reagoimalla tyyppisanaan. Pivot-taulukot rikkovat tämän rytmin. Yksittäinen pivot-taulukko ei ole tietue, se on pieni ohjelma, joka koostuu kymmenistä yhteistyössä toimivista tietueista jaettuna kahteen eri paikkaan samassa OLE-yhdistelmädokumenttivirrassa (OLE compound document stream), ja niiden väliset suhteet ovat paikkaan sidottuja, bittipakattuja ja anteeksiantamattomia. Tämä on rakenne, jonka useimmat BIFF8-lukijat joko ohittavat kokonaan tai säilyttävät läpinäkymättöminä tavuina, koska sellaisen kirjoittaminen tyhjästä tarkoittaa jokaisen ristiinviittauksen uusimista, joita Excel itse ylläpitää.
Syy siihen, miksi pivot-taulukko on vaikea, on se, että se on todellisuudessa kaksi yhteen hitsattua tuotetta. Siinä on pivot-välimuisti (pivot cache) - itsenäinen tilannevedos lähdetiedoista omalla alavirrallaan - ja taulukkonäkymä (table view) eli asettelu, joka kertoo mitkä kentät sijaitsevat milläkin akselilla. Välimuisti ja näkymä viittaavat toisiinsa indeksin kautta. Tee yksi indeksivirhe, ja tiedosto avautuu päivitysvirheeseen tai hiljaisesti tyhjään ruudukkoon.
Pivot-välimuisti on oma alavirta
Välimuisti elää työkirjan globaalivirrassa (workbook globals stream) täydellisenä BIFF-alavirtana, jota kehystää BOF-tietue, jonka dokumenttityyppi on 0x0006 (arvo, joka merkitsee pivot-välimuistin, toisin kuin 0x0005 työkirjalle tai 0x0010 laskentataulukolle), ja jonka sulkee vastaava EOF-tietue. Kyseisen kehyksen sisällä rakenne on kiinteä. SXDB-tietue on välimuistin otsikko. Se kantaa tietueiden määrää, välimuistikenttien määrää ja virran tunnistetta, johon taulukkonäkymä viittaa sitoutuakseen tähän välimuistiin. Kukin lähdesarake tuottaa sitten SXFDB-kentänmääritystietueen, jota seuraa sen luokitteleva SXFDBType, ja sen jälkeen kyseisen sarakkeen saamat uniikit arvot, jotka tuotetaan yhtenä tyypitettynä alkiotietueena (item record) kunkin erillisen arvon kohdalla.
Alkiotietueet ovat se kohta, jossa välimuisti ansaitsee paikkansa. Tekstiarvosta tulee SXSTRING, numeerisesta arvosta SXNUM, loogisesta arvosta SXBOOLEAN ja kaavavirheestä SXERR. Välimuisti ei tallenna lähderuudukkoa, vaan se tallentaa erilliset (distinct) arvot kenttää kohden sekä indeksitaulukon, joka kertoo tietueelle n, minkä erillisen alkion kukin kenttä otti. Siksi pivot-taulukon rakentaminen ohjelmallisesti ei ole solujen kopioimista. Sinun on skannattava lähdealue, pääteltävä kunkin kentän tyyppi sen sisältämistä arvoista, poistettava duplikaatit tyypitetyksi alkioluetteloksi ja kirjattava jokainen rivi alkioindeksien tuplana. HotXLS tekee juuri tämän: kokonaan numeerinen sarake tuotetaan SXNUM-alkioilla, sekatekstisarake muodostuu SXSTRING-alkioista ja päivämäärät kuljetetaan sarja-arvoina saman numeerisen polun kautta.
SXDBB ja bittipakkaus, joka tekee siitä mielenkiintoisen
Tietuekohtainen indeksitaulukko on koko rakenteen teknisesti erikoisin osa, ja se elää SXDBB-tietueessa. Yksinkertainen koodaus tallentaisi kunkin kentän alkioindeksin 16-bittisenä sanana. Excel ei tee niin. Se pakkaa kunkin kentän indeksin täsmälleen siihen bittimäärään, joka vaaditaan kyseisen kentän alkioiden osoittamiseen, eikä yhtään enempää. Leveys on ceil(log2(itemCount + 1)) bittiä. + 1 on merkitsevä: lisäarvo on vartija (sentinel), joka tarkoittaa "tyhjä, ei arvoa tälle kentälle tässä tietueessa", joten kenttä, jossa on kolme erillistä alkiota, vaatii neljän tilan esittämistä ja vie siten kaksi bittiä, ei sitä yhtä bittiä, jota pelkät kolme alkiota ehdottaisivat. Kenttä, jossa ei ole lainkaan alkioita, tuottaa nolla bittiä ja se ohitetaan kokonaan pakkauksen aikana.
Yhden tietueen bitit ketjutetaan kaikkien kenttien yli, ja sitten seuraava tietue alkaa puhtaalta tavurajalta. Tietueet ovat tavukohdistettuja, eivät bittipakattuja päästä päähän, mikä tekee satunnaishakutoiminnosta taulukkoon hallittavan muutaman täytebitin (padding bit) kustannuksella riviä kohden. Pakkaus tavun sisällä on vähiten merkitsevä bitti ensin. Kun hyväksyt nämä kaksi sääntöä, kooderi on suoraviivainen bittipumppu ja dekooderi on sen peili.
// 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;Syy siihen, miksi tätä yksityiskohtaa ei voida ohittaa, on yksittäisen BIFF-tietueen 8224 tavun katto. Jokaisen formaatin tietueen, pivot-tietueet mukaan lukien, on mahdutettava hyötykuormansa enintään 8224 tavuun, ja vilkas pivot-välimuisti tuhansilla lähderiveillä ylittää tämän kauan ennen kuin se on tuottanut jokaisen rivin. Joten indeksitaulukko on jaettu. HotXLS rajoittaa yksittäisen SXDBB-rungon 8220 tavuun (mikä on 8224 tietueraja miinus tyypin ja pituuden nelitavuinen tietueen otsikko), jakaa sen yhden pakatun tietueen tavuleveydellä selvittääkseen kuinka monta kokonaista riviä mahtuu, ja tuottaa niin monta jatkettua SXDBB-tietuetta kuin rivimäärä vaatii. Jokainen jatko-osa käynnistyy puhtaasti tietuerajalla, joten mitään riviä ei koskaan leikata kahden tietueen yli. Lukija, joka tietää tietuekohtaisen bittleveyden, voi edetä jokaisen peräkkäisen SXDBB-tietueen läpi kuin ne olisivat yksi yhtenäinen bittitaulukko.
Näkymän asettelu: SXLI rungolle, SXPI sivulle
Kun välimuisti on rakennettu, taulukkonäkymä on toinen puolisko. Sen ydin on akselirivi-alkiot (axis line items), eli pivot-rungon rivit, jotka luettelevat jokaisen rivikentän ja sarakekentän arvojen yhdistelmän, jonka taulukko piirtää. Nämä kuljetetaan SXLI-tietueissa (tietuetyyppi 0x00B5, kuvattu dokumentissa [MS-XLS] §2.4.275). Yksi SXLI sisältää monta viivaa, jälleen kunnes 8224 tavun raja pakottaa uuden tietueen, ja se käyttää pientä pakkaustemppua: kukin viiva tallentaa vain sen, miten se eroaa yläpuolella olevasta viivasta, ilmaistuna yhteisen etuliitteen määränä (common-prefix count), joten syvälle sisäkkäinen akseli ei toista ulompia kenttäarvoja jokaisella rivillä. Kokonaissumma-rivi ja minkä tahansa tietueen ensimmäinen rivi nollaavat aina tämän etuliitemäärän, joten lukijan ei tarvitse koskaan katsoa taaksepäin tietuerajan yli viivan rekonstruoimiseksi.
Sivuakseli eli suodattimen pudotusvalikot, jotka sijaitsevat pivot-taulukon yläpuorella, on erillinen tietue. SXPI (tietuetyyppi 0x00B6, [MS-XLS] §2.4.276) kantaa yhtä kymmentavuista merkintää sivukenttää kohden: pivot-kentän indeksi isxvd, valittu välimuistialkio iCache, asemasana ipos ja perinteinen objekti-id objId. iCache-arvo on se, jota on seurattava. Sivukenttä, joka näyttää arvon "(All)" suodattamatta mitään, tallentaa vartijan 0x7FFD todellisen alkioindeksin sijaan. Ohjelmallisesti rakennettu pivot avautuu jokaisen sivukentän ollessa asettuna arvoon "(All)", kunnes kutsuja esivalitsee alkion, jolloin kyseisen alkion välimuisti-indeksi korvaa vartijan ja Excel avautuu suodattimen ollessa jo käytössä. Näiden rinnalla sijaitsevat tukitietueet, jotka kuvaavat yksittäisiä kenttiä ja niiden muotoilua: SXVD ja SXVDEx kenttänäkymän määrityksille, SXIVD kenttäindeksiluetteloille, jotka järjestävät kunkin akselin, ja SXFormat numeromuotoilulle, joista kukin osoittaa takaisin samaan välimuistiin, johon rungon rivit viittaavat.
Kaksi kirjoittajaa yhdessä: raa'at blokit ja tyypitetty malli
On olemassa rakenteellinen syy, miksi HotXLS pitää kaksi täysin erillistä polkua pivot-taulukon kirjoittamiseen, ja se johtuu suoraan uskollisuuden (fidelity) vaatimuksista. Kun työkirja luetaan levyltä, sen pivot-tietueet ovat Excelin tai jonkin muun tuottajan kirjoittamia, ja ne voivat käyttää tietuevariantteja, järjestysomituisuuksia tai laajennustietueita, joita mikään kolmannen osapuolen kirjoittaja ei täysin mallinna. Ainoa turvallinen tapa käsitellä näitä tavuja on antaa ne takaisin muuttumattomina. Joten tiedostosta tuotu pivot-taulukko liputetaan arvolla FromRawBlobs = True, ja tallennettaessa kirjoittaja toistaa säilytetyt tietueblokit (record blobs) sanatarkasti. Mitään ei luoda uudelleen, mitään ei tulkita uudelleen, ja round-trip avaamisen ja tallentamisen kautta on tavustabiili.
Ohjelman rakentama pivot-taulukko on päinvastainen tapaus. Alkuperäisiä tavuja ei ole säilytettäväksi, on vain tyypitetty oliomalli: TXLSPivotCache kenttineen ja alkioluetteloineen, ja TXLSPivotTable akselimäärityksineen. Kyseinen taulukko liputetaan arvolla FromRawBlobs = False, ja kirjoittaja serialisoi sen vaikeamman kautta: se tuottaa tuoreen BOF = 0x0006 -välimuistialavirran, pakkaa SXDBB-indeksitaulukon tyypitetyn mallin sisältämistä alkioindekseistä ja asettelee SXLI- ja SXPI-tietueet akselikonfiguraation perusteella. Lippu on se, joka sallii molempien tyyppien rinnakkaiselon yhdessä työkirjassa. Ilman sitä yksittäisen kirjoittajan pitäisi joko luopua luettujen taulukoiden uskollisuudesta tai kieltäytyä luomasta uusia. Kaikki luetun taulukon kantamat tuottajakohtaiset laajennustietueet säilytetään täydentävinä tietueina, jotka ovat saavutettavissa taulukon SupplementalRecords-luettelon kautta, joten tyypitetyn mallin kautta tarkastettu taulukko ei menetä niitä osia, joita malli ei kuvaile.
Pivot-taulukon rakentaminen koodissa
Kaikki yllä oleva koneisto sijaitsee yhden kutsun takana. AddPivotTable ottaa lähtöalueen A1-notaatiossa, kohdesolun, johon taulukon vasen yläkulma ankkuroituu, ja nimen. Se jäsentää alueen, skannaa sen päätelläkseen kenttätyypit ja rakentaakseen välimuistin (käyttäen uudelleen olemassa olevaa välimuistia, jos toinen taulukko jo sitoutuu samaan alueeseen), ja palauttaa tyypitetyn TXLSPivotTable-taulukon, jossa on yksi kenttä lähdesaraketta kohden, kunkin kentän ollessa aluksi akselin ulkopuolella. Sijoitat sitten kentät akseleille ja valitset aggregoinnin. Allekirjoitus on täsmälleen tämä, ja välimuisti, SXDBB-pakkaus ja näkymätietueet tuotetaan puolestasi tallennushetkellä.
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;Lähdealueen ensimmäinen rivi luetaan otsikkona, joka nimeää välimuistikentät, joten AddRowField('Region') vastaa saraketta sen otsikkotekstin eikä paikan perusteella. Koska palautettu taulukko on tyypitetty malli arvolla FromRawBlobs = False, kirjoittaja ottaa tyhjästä alkavan polun: se rakentaa itsenäisen välimuistin, joka ei riipu siitä, että lähdealue on edelleen paikalla päivityshetkellä, mikä on juuri se ominaisuus, jonka haluat silloin, kun pivot toimitetaan vastaanottajalle, joka saattaa siirtää tai poistaa taustalla olevat tiedot.
Sellaisen tiedoston pivot- ja välimuistitietueiden lukeminen ja täsmäyttäminen, jota et ole itse tuottanut - mukaan lukien raaka-blokkien säilytyspolku - käsitellään työkirjan auditoinnin ja muunnostyökalun läpikäynnissä. Kun lähdealue käsittää kymmeniä tuhansia rivejä ja SXDBB-virta ulottuu useisiin jatkettuihin tietueisiin, tekniikat suuren työkirjan suorituskykyä koskevissa huomautuksissa estävät välimuistin rakentamista hallitsemasta suoritusaikaasi. Molemmat pariutuvat pivot-kirjoittajan kanssa, joka toimitetaan Delphi- ja C++Builder-ohjelmille tarkoitetussa HotXLS spreadsheet component -taulukkolaskentakomponentissa yhdessä muiden tässä blogissa käsiteltyjen solu-, kaava-, kaavio- ja muotoilurajapintojen kanssa.