Miljoonan rivin ja tusinan sarakkeen laskentataulukko on täysin tavallinen vienti tietokannan raportointityöstä. Jos avaat sen tavalliseen tapaan lataamalla koko työkirjan TXLSWorkbook-objektiin, prosessin on luotava jokainen noista kahdestatoista miljoonasta solusta elävänä objektina ennen kuin ensimmäinenkään rivi liiketoimintalogiikkaasi suoritetaan. Levyllä oleva tiedosto saattaa olla kuusikymmentä megatavua pakattua XML:ää. Objektipuu, joksi se laajenee, on moninkertainen tähän nähden, ja sen kaiken on oltava muistissa samanaikaisesti, koska malli on suunniteltu satunnaissaantiin (random-access). Raportille, jonka aiot lukea ylhäältä alas ja heittää pois, se on valtava määrä muistia käytettynä rakenteeseen, jota et koskaan tarvinnut
Saman tiedoston läpi on olemassa toinenkin reitti. Mallin rakentamisen sijaan skannaat laskentataulukon XML:n vain eteenpäin, yksi solu kerrallaan, ja annat jokaisen solun virrata ohi sen jälkeen, kun olet tarkastellut sitä. Mitään ei kerry. Muistinkäyttö pysyy lähes vakiona riippumatta siitä, onko taulukossa tuhat vai kymmenen miljoonaa riviä, koska lukija ei koskaan pidä hallussaan enempää kuin parhaillaan jäsentämäänsä osaa ja muutamaa pientä hakutaulukkoa. Tätä HotXLS:n suoralukija (direct reader) tekee, ja loppuosa tästä artikkelista käsittelee sitä, miksi se pysyy pienenä ja mitä se tarjoaa vastineeksi
Miksi muistissa oleva malli ei skaalaudu
XLSX-tiedosto on ECMA-376:n kuvaama ZIP-paketti XML-osista. Jokainen laskentataulukko on oma osansa, xl/worksheets/sheetN.xml, ja sen sisällä jokainen rivi on <row>-elementti, joka sisältää <c>-soluelementtejä. Tavallinen latauspolku lukee kyseisen osan ja muodostaa osoitettavan objektin jokaiselle solulle, jotta voit myöhemmin pyytää Cells[12345, 7] ja saada vastauksen vakioajassa. Satunnaissaanti on työkirjamallin koko ydin, ja se on juuri se asia, mikä tekee muokkaamisesta, kaavojen arvioinnista ja muotoilusta kätevää
Kustannuksena on, että satunnaissaanti vaatii kaiken olevan läsnä samanaikaisesti. Et voi indeksoida rakennetta, jonka olet rakentanut vasta osittain. Niinpä täyden latauksen huippumuisti on solumäärän funktio, ja taulukossa, jossa on miljoonia täytettyjä soluja, kyseinen funktio päätyy lukemiin, joissa palvelusi ei halua olla, varsinkin jos jaetulla koneella on käynnissä useita tällaisia töitä samanaikaisesti. Kun todellisuudessa tarvitsemasi saantimalli on peräkkäinen, satunnaissaannista maksaminen on maksamista ominaisuudesta, jota et tule käyttämään
Vain eteenpäin etenevä SAX-skannaus, joka ei rakenna puuta
Suoralukija avaa ZIP-paketin ja käy läpi jokaisen laskentataulukon osan SAX-tyyppisellä vetojäsentimellä (pull parser). SAX tarkoittaa tässä sitä, että jäsennin raportoi jäsennystapahtumista sitä mukaa kun se kohtaa niitä, aloituselementin, tekstijakson, lopetuselementin, ja jatkaa sitten eteenpäin. Se ei pidä takanaan solmupuuta. Lukija seuraa nykyistä riviä ja saraketta r-attribuuttien perusteella, kerää solun tyypin, tyyli-indeksin, arvon ja kaavatekstin tapahtumien saapuessa, ja kun sulkeva </c>-tunniste nähdään, se lähettää yhden solun ja unohtaa sen. Seuraava solu käyttää uudelleen samaa kourallista paikallisia muuttujia
Koska mitään ei säilytetä solujen välillä, muistijalanjälki ei kasva solujen määrän mukaan. Tämä on se ominaisuus, josta kannattaa pitää kiinni. Kahdensadan rivin taulukko ja kahdenkymmenen miljoonan rivin taulukko kuluttavat lukijalta saman verran pysyvää muistia, ja ero niiden välillä on vain skannauksen kestossa. Luovut satunnaissaannista, mallin pääominaisuudesta, ja saat vastineeksi muistikaton, jota solujen lukumäärä ei voi ylittää
Mikä pysyy muistissa, ja miksi nuo kaksi osaa
Skannaus ei ole täysin tilaton, ja poikkeukset ovat opettavaisia. Kaksi pientä taulukkoa on pidettävä muistissa koko toimituksen ajan, koska solu yksinään ei sisällä riittävästi tietoa tulkittavaksi ilman niitä
Ensimmäinen on jaettu merkkijonotaulukko (shared string table). SpreadsheetML:ssä tekstisolu ei tallenna omaa tekstiään. Se sisältää t="s" ja numeerisen hyötykuorman, joka on indeksi tiedostoon xl/sharedStrings.xml, työkirjan jokaista erillistä merkkijonoa sisältävään yksittäiseen deduplikoituun luetteloon. Tämä on hyvä tilasäästö tiedostoille, joissa samat otsikot toistuvat tuhansilla riveillä, mutta se tarkoittaa, että lukijan on ladattava kyseinen merkkijonotaulukko etukäteen ja pidettävä se muistissa, koska mikä tahansa solu missä tahansa taulukossa voi viitata mihin tahansa siinä olevaan merkintään. Taulukon koko määräytyy erilaisten merkkijonojen määrän, ei solumäärän, perusteella, joten se pysyy vaatimattomana valtavillakin taulukoilla
Toinen on numeromuotojen (number-format) kuviointi tyyliosiosta. Numeerinen solu ja päivämääräsolu ovat tavultaan identtiset lankatasolla: molemmat ovat tavallisia lukuja, koska SpreadsheetML:ssä päivämäärä on vain sarjallinen päivämäärälaskuri. Ainoa asia, joka erottaa ne toisistaan, on solun tyyli, joka osoittaa cellXfs:n kautta tiedostossa xl/styles.xml numeromuototunnukseen. Raportoidakseen päivämäärän päivämääränä raa'an sarjanumeron sijaan, lukija lataa tämän tyylistä muotoon -taulukon ja pitää sen muistissa. Kaikki muu tiedostossa, varsinainen soludata, joka muodostaa suurimman osan tavuista, virtaa ohi tallentumatta
Jokainen solu raportoi tyypin ja arvon
Jokainen lähetetty solu saapuu TXLSDirectCell-tietueena. Se kantaa mukanaan taulukon indeksin ja nimen, 1-pohjaisen rivin ja sarakkeen, semanttisen tyypin (Kind), arvon (Value) varianttina (Variant), kaavan tekstin (Formula) ilman alussa olevaa yhtäsuuruusmerkkiä ja raa'an tyyli-indeksin (StyleIndex). Tyyppi on jokin seuraavista: xdkNumber, xdkString, xdkBoolean, xdkDate tai xdkError, joten voit haaroittaa logiikan sen perusteella, mitä solu tarkoittaa, sen sijaan, että johtaisit sen uudelleen attribuuteista. Kaavasolu raportoi välimuistissa olevan tuloksensa tyypin kaavatekstin rinnalla, joten laskettu kokonaissumma näkyy numerona, joka kertoo myös, miten se on tuotettu
type
TReportScan = class
procedure OnCell(Sender: TObject; const Cell: TXLSDirectCell;
var Abort: Boolean);
end;
procedure TReportScan.OnCell(Sender: TObject; const Cell: TXLSDirectCell;
var Abort: Boolean);
begin
case Cell.Kind of
xdkString: AccumulateLabel(Cell.Row, Cell.Col, VarToStr(Cell.Value));
xdkNumber: AddToTotals(Cell.Col, Double(Cell.Value));
xdkDate: NoteWhen(Cell.Row, VarToDateTime(Cell.Value));
xdkBoolean: FlagRow(Cell.Row, Boolean(Cell.Value));
xdkError: LogBadCell(Cell.Row, Cell.Col, VarToStr(Cell.Value));
end;
end;Päivämäärän erottaminen luvusta
Päivämääräkysymys ansaitsee tarkemman katsauksen, koska siinä useimmat naiivit skannerit menevät vikaan. Numeerisella solulla ei ole päivämäärätyyppiä. Solu, joka sisältää sarja-arvon 46000, voi olla määrä, hinta tai 17. helmikuuta 2025, ja tiedosto kertoo kumpi se on vain numeromuototunnuksen (number-format id) avulla, joka saavutetaan solun tyylin kautta. ECMA-376 varaa lohkon sisäänrakennettuja muototunnuksia, joiden merkitys on kiinteä kaikilla vaatimukset täyttävillä tuottajilla, ja päivämäärän sisältävät tunnukset sijaitsevat kahdella alueella: 14–22 vakiopäivämäärä- ja aikamuodoille sekä 45–47 kuluneen ajan muodoille, kuten [h]:mm:ss. Kun DetectDates on päällä, kuten oletuksena on, lukija ratkaisee jokaisen numeerisen solun tyylin sen muototunnukseksi, ja solu, jonka tunnus osuu näille varatuille alueille, raportoidaan tyyppinä xdkDate ja sen arvo (Value) on jo muunnettu Delphin TDateTime-muotoon. Myös mukautetut muodot tarkistetaan tutkimalla muotokoodia päivämäärä- ja aikatietueiden varalta, mutta varatut alueet ovat luotettava selkäranka. Jos DetectDates kytketään pois päältä, tyylitaulukkoa ei edes ladata, jokainen numeerinen solu tulee läpi xdkNumber-tyyppinä, ja skannaus on hieman kevyempi
Ohita taulukot ja keskeytä varhain
Peräkkäisellä skannauksella on hiljainen etu, johon satunnaissaanti ei yllä: voit lopettaa sen. OnSheet-tapahtuma laukeaa ennen kunkin laskentataulukon avaamista, ja se antaa sinulle kaksi kytkintä. Aseta SkipSheet, ja koko kyseistä osaa ei koskaan jäsennetä. Näin skannaat vain sinua kiinnostavat taulukot monisivuisessa työkirjassa maksamatta muiden lukemisesta. Aseta Abort, ja koko skannaus päättyy välittömästi. OnCell-tapahtumalla on oma Abort-kytkimensä, joten voit pysäyttää toiminnon heti, kun olet löytänyt etsimäsi, tietyn rivin, vartija-arvon (sentinel value), otsikkolohkon lopun, lukematta jäljellä olevia miljoonia soluja. Vain eteenpäin etenevässä skannauksessa keskeytys on aidosti ilmainen, koska ohittamasi työ on työtä, jota ei ollut vielä tapahtunut
procedure TReportScan.OnSheet(Sender: TObject; SheetIndex: Integer;
const SheetName: WideString; var SkipSheet: Boolean; var Abort: Boolean);
begin
// Scan only the "Data" sheet; leave the rest unread
SkipSheet := SheetName <> 'Data';
end;Solujen laskeminen ilman käsittelijää
Yksi viimeaikanen parannus on syytä mainita, koska se muuttaa yleisen kysymyksen yhdeksi halvaksi kutsuksi. Lukija laskee jokaisen ohittamansa täytetyn solun, ja se tekee tämän riippumatta siitä, onko OnCell-käsittelijää liitetty vai ei. Aiemmin, kun käsittelijää ei ollut asetettu, täytettyjen solujen määrä palasi nollana, koska laskenta oli lähettämisen sivuvaikutus. Nyt laskenta on riippumaton lähettämisestä. Se tarkoittaa, että voit kysyä yhden kysymyksen, kuinka monta täytettyä solua tämä työkirja todella sisältää, ja saada vastauksen pelkän skannauksen hinnalla ilman takaisinkutsuja. Sekä ReadFile että ReadStream palauttavat tämän yhteissumman muodossa Int64, ja sama luku on jälkikäteen saatavilla ominaisuutena CellCount. Paluuarvo -1 viestii, että tiedostoa ei voitu avata tai se ei ole OOXML-paketti
var
Reader: TXLSDirectReader;
Populated: Int64;
begin
Reader := TXLSDirectReader.Create;
try
// No OnCell handler: a pure populated-cell census, still near-constant memory
Populated := Reader.ReadFile('quarterly_export.xlsx');
if Populated < 0 then
raise Exception.Create('Not a readable XLSX package')
else
Writeln(Format('%d populated cells (CellCount = %d)',
[Populated, Reader.CellCount]));
finally
Reader.Free;
end;
end;Täyttä skannausta varten liität käsittelijän ja kutsut ReadFile aivan samalla tavalla. Kontrasti täyteen lataukseen on koko asian ydin: siinä missä quarterly_export.xlsx:n lataaminen työkirjaan laajentaisi jokaisen solun muistissa olevaksi objektiksi ja pitäisi kaiken siellä, suoralukija pitää vain jaetut merkkijonot ja tyylitaulukon, kun taas kaksitoista miljoonaa solua virtaa OnCell-käsittelijäsi läpi yksi kerrallaan. Jokaisen solun kohdalla suoritettu laskutoimitus ei jätä mitään taakseen, joten huippumuistin määrittää työkirjan erillisten merkkijonojen määrä, ei sen rivimäärä
Suoralukija on oikea työkalu, kun tehtävänä on lukea suuri työkirja kerran ja poimia tietoja tai tiivistää se. Kun tarvitset sen sijaan koko mallin satunnaissaantia, mutta haluat sen toimivan hyvin suurilla tiedostoilla, artikkelin huomiomme suurten työkirjojen suorituskyvystä Delphissä viritys kattaa kyseisen polun. Ja kun suunta on päinvastainen ja kyse on suuren tuotoksen tuottamisesta eikä sen kuluttamisesta, palvelimen erätöiden striimaavan kirjoituksen ohje soveltaa samaa vakiomuistikuria kirjoittamiseen. Kaikki kolme toimitetaan osana HotXLS-komponenttia Delphille ja C++Builderille, yhdessä luku-, kirjoitus-, kaava- ja muotoiluohjelmointirajapintojen kanssa, joita käsitellään muualla tässä blogissa