Suurten Excel-työkirjojen suorituskyky Delphissä HotXLS-Delphi-komponentilla

Kun 300 000 rivin vienti ylittää muistibudjetin, syyksi syytetään yleensä rivimäärää. Rivimäärä on tavallisesti syytön. Suuren työkirjan kalliit osat syntyvät sivuvaikutuksina: tyylivarasto, johon kasvaa yksi merkintä solua kohden, koska muotoilu lisättiin silmukan sisällä, laskentataulukon XML, joka kootaan tallennushetkellä yhdeksi valtavaksi merkkijonoksi, tai miljoona samanlaista kaavarunkoa, jotka tallennetaan yksitellen. HotXLS, losLabin natiivi Delphi-kirjasto XLS- ja XLSX-tiedostoille, antaa sinulle täsmällisen säätimen jokaiseen näistä kustannuksista. Mikään niistä ei ole oletuksena käytössä, koska jokainen muuttaa kompromissia, joten varsinainen suorituskykytaito on tietää, mikä säädin vastaa mitäkin oiretta

Mihin suuri työkirja käyttää muistia

On kaksi erillistä muistijärjestelmää, joita on syytä tarkastella. Luonnin aikana muistissa oleva solumalli kasvaa jokaisesta koskettamastasi solusta: arvot, muotoilut ja kaavat muuttuvat kaikki olioiksi tai varastomerkinnöiksi. Tallennuksen aikana oletusarvoinen XLSX-polku lisäksi muodostaa jokaisen laskentataulukon XML:n laajaksi merkkijonoksi ennen sen pakkaamista zip-säiliöön, joten huippukulutus on malli plus suurimman taulukon sarjallistettu muoto. Työ, joka selviää rakennussilmukasta mutta kuolee SaveAs-kutsun sisällä, osuu toiseen järjestelmään eikä ensimmäiseen, eikä toisen korjaus tee mitään toiselle

Tiedostokoko noudattaa samankaltaista sääntöä: solut ovat vain yksi tekijä tyylien, jaettujen merkkijonojen, kaavojen, kuvien ja kommenttien rinnalla. Tarkastuskierros, jossa käytetään ForEachCell-kutsua ja taulukkokohtaisia kokoelmamääriä, kertoo, mikä resurssi todella hallitsee ongelmallista tiedostoa ennen kuin optimoit väärän kohteen. Mittauksessa on yksi hienovaraisuus: XLSX-puolen Sheet.Cells.Count ilmoittaa harvaan säilöön luotujen solujen määrän, ei käytetyn alueen pinta-alaa. Taulukko, jonka tiedot ovat 1000 kertaa 50 -suorakulmiossa ja jonka soluista puolet on tyhjiä, sisältää noin 25 000 solua, ei 50 000:ta. Ero on tärkeä, kun vertaat asiakkaan "valtavaa" tiedostoa testiaineistoihisi, sillä käytetyn alueen pinta-ala ja todellinen solupopulaatio voivat poiketa harvoissa talousasetteluissa kertaluokalla

StreamingWrite korjaa tallennuspolun, ei rakennuspolkua

Asettamalla TXLSXWorkbook.StreamingWrite := True vaihdat SaveAs-kutsun suoratoistavaan sarjallistajaan, joka kirjoittaa laskentataulukon XML:n suoraan zip-virtaan ja poistaa taulukkokohtaisen merkkijonovälivaiheen. Oletusarvo on yhteensopivuussyistä False, ja sen ottaminen käyttöön on yhden rivin muutos:

Book := TXLSXWorkbook.Create;
try
  Sheet := Book.Sheets.Add('Bulk');
  for R := 1 to 100000 do
  begin
    Sheet.Cells[R, 1].Value := R;
    Sheet.Cells[R, 2].Value := 'Row ' + IntToStr(R);
    Sheet.Cells[R, 3].Value := R * 1.5;
  end;
  Book.StreamingWrite := True;   // arkin XML virtaa zip-muistiosaan
  Book.SaveAs('bulk.xlsx');
finally
  Book.Free;
end;

Ole tarkka siitä, mitä tällä saat: silmukan rakentama solumalli vie täsmälleen yhtä paljon muistia kuin aiemmin. StreamingWrite tasoittaa tallennusajan piikin, mikä ratkaisee, valmistuuko eräajo vai epäonnistuuko se 95 prosentin kohdalla. Jos rakennussilmukka itse kuluttaa muistin loppuun, tarvitset seuraavat kaksi säädintä

Tyylivarastot: lisää kerran, käytä indeksiä uudelleen

HotXLS:n XLSX-muotoilu perustuu varastoihin: Book.Fonts.Add(...), Fills.AddSolid(...) ja Borders.Add(...) palauttavat 0-pohjaisen varastoindeksin, johon solut viittaavat. Identtisillä parametreilla silmukan sisällä kutsuttu Fonts.Add poistaa kaksoiskappaleet, joten se tuhlaa aikaa eikä tilaa. Alignments.Add käyttäytyy eri tavoin: se palauttaa jokaisella kutsulla uuden olion, joten solukohtainen kohdistuksen luonti kasvattaa varastoa lineaarisesti rivimäärän mukana. Yksi tapa kattaa molemmat tilanteet: ratkaise jokainen varastoindeksi kerran silmukan ulkopuolella ja määritä indeksit sen sisällä

// nosta pool-haut pois kuumasta silmukasta
HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);   // 0-pohjainen pool-indeksi
for C := 1 to 24 do
  Sheet.Cells[1, C].FontIndex := HeaderFont + 1;            // solut tallentavat 1-pohjaisena; 0 = oletus

+ 1 ei ole kirjoitusvirhe, ja sen unohtaminen on tässä se klassinen oireita synnyttävä virhe: varastot jakavat 0-pohjaisia indeksejä, kun taas solupuolen ominaisuudet käsittelevät arvoa 0 oletuksena, joten jokaista varastoindeksiä on siirrettävä yhdellä määrityksessä. Jos jätät siirron pois, otsikot hahmontuvat hiljaa työkirjan oletusfontilla. Kukaan ei huomaa virhettä ennen bränditarkastusta

Korvaa solukohtainen Variant-liikenne rivikutsupalautteilla

Jokainen Sheet.Cells[R, C].Value := X sisältää solun haku- tai luontitoiminnon sekä Variant-määrityksen. Muutamassa sadassatuhannessa solussa tämä solukohtainen kuormitus alkaa näkyä profiloinneissa. HotXLS tarjoaa kummassakin julkisivussa joukkokutsurajapinnat (ForEachCell ja ForEachRow lukemiseen, WriteCells ja WriteRows kirjoittamiseen), jotka siirtävät iteroinnin moottorin sisälle ja luovuttavat koodillesi kokonaisia rivejä kerrallaan:

procedure TLedgerExport.FillRow(Sender: TObject;
  SheetIndex, Row, FirstCol, LastCol: Integer;
  var Values: Variant; var Skip: Boolean; var Cancel: Boolean);
begin
  if Row > FCount then
  begin
    Cancel := True;     // lopeta koko kirjoitus
    Exit;
  end;
  Values := VarArrayOf([FRows[Row - 1].Account,
                        FRows[Row - 1].PostedOn,
                        FRows[Row - 1].Amount]);
end;

// yksi moottorikutsu satojentuhansien ominaisuusosumien sijaan
Sheet.WriteRows(1, 1, FCount, 3, FillRow);

Kutsupalautteen Skip-lippu jättää rivin koskematta keskeyttämättä toimintoa, ja Cancel lopettaa toiminnon varhain. Tämä on hyödyllistä, kun lähde on lukija, jonka pituus selviää vasta edetessä. Kun yhdistät rakennuksessa WriteRows-kutsun tallennuksen StreamingWrite-asetukseen, luontipolulle ei jää solukohtaista kuumaa kohtaa

XLS-julkisivun lukupuolen säätimet

Suurilla vanhoilla .xls-tiedostoilla on oma työkalupakkinsa. _DisableGraphics := True ennen Open-kutsua ohittaa piirrostason jäsentämisen kokonaan, mikä nopeuttaa sellaisten työkirjojen latausta, joihin on kertynyt vuosien varrella muotoja ja upotettuja kuvia. Rajoitus on ehdoton: piirrostaso puuttuu silloin mallista, joten tällaisen työkirjan tallentaminen kirjoittaa tiedoston ilman sen piirroksia. Varaa lippu vain luku -analyysitöille. SetTempDir ohjaa BIFF-kirjoittajan väliaikaistiedostot toiseen paikkaan, mikä on tärkeää palvelimilla, joiden oletusväliaikaissijainnilla on kiintiö tai hidas tallennusväline. UseSharedFormulas ryhmittelee toistuvat kaavarungot jaettujen kaavojen tietueiksi, mikä pienentää tiedostoja, joissa kaavasarake toistuu kuudenkymmenentuhannen rivin pituudelta

XLS-tietojen lukusilmukoissa on indeksointiansa, joka kannattaa nostaa esiin, koska puolustavasti käsiteltynä se kaksinkertaistaa työn ja huomaamatta jätettynä vioittaa tuloksia: UsedRange ilmoittaa rajansa FirstRow, LastRow, FirstCol ja LastCol 0-pohjaisina, kun taas Cells.Item[Row, Col] on 1-pohjainen. Käytetyn alueen läpikäyvän skannauksen on lisättävä yksi jokaiseen koordinaattiin solua käytettäessä, kuten muodossa Cells.Item[Row + 1, Col + 1], tai se lukee ruudukon yhden solun verran vinosti siirtyneenä, pudottaa hiljaa viimeisen rivin ja sarakkeen ja sisällyttää olemattoman ensimmäisen. ForEachCell-kutsupalautus kiertää ristiriidan kokonaan, mikä on yksi lisäsyy suosia sitä kokonaisten taulukoiden läpikäyntiin

Tunnustele tiedostot ennen niiden lataamista

Halvin suuren työkirjan toiminto on se, jonka jätät tekemättä. Molempien julkisivujen GetSheetNames luettelee tiedoston laskentataulukot lataamatta solutietoja. XLSX-toteutus lukee vain zip-tiedoston sisällä olevan työkirjaluettelon ja jättää työkirjaolion nimenomaisesti täyttämättä, kun taas XLS-julkisivu lopettaa skannauksen ensimmäiseen alivirtarajaan. Se on oikea ennakkotarkistus kysymykseen "mihin taulukkoon tämän tuontityön pitäisi kohdistua", ja CanReadEncrypted vastaa kysymykseen "onko tämä salattu säiliö" ennen toivotonta Open-yritystä

Names := TStringList.Create;
Book := TXLSXWorkbook.Create;
try
  if Book.GetSheetNames('big-unknown.xlsx', Names) <= 0 then
    raise Exception.Create('cannot enumerate sheets');   // virhe tyhjentää listan
  // valitse kohdearkki ja päätä sitten, kannattaako täysi avaaminen
finally
  Book.Free;
  Names.Free;
end;

Huomaa paluuarvokäytäntö: nämä tunnistelutoiminnot ilmaisevat epäonnistumisen nollaa pienemmillä tai yhtä suurilla arvoilla ja tyhjentävät tulosluettelon, joten testaa <= 0 sen sijaan, että vertaisit yhteen tiettyyn onnistumisarvoon

Sovita lähestymistapa työhön

Valvomattomiin liukuhihnoihin, jotka luovat useita suuria tiedostoja peräkkäin, kokonaisuuden täydentää vielä kaksi tapaa. Työkirjaoliot eivät ole säieturvallisia jaettaviksi, mutta mikään ei estä käyttämästä yhtä itsenäistä työkirjaa kutakin työsäiettä kohti, mikä rinnakkaistaa erämuunnoksen siististi. Kun tuloste menee levyn sijasta HTTP:hen, TStream-tallennuksen ylikuormitukset yhdistyvät StreamingWrite-asetukseen niin, ettei suuri vastaus koskaan konkretisoidu väliaikaistiedostoksi. Yksi käytännön alaviite pätee: virtaan tallennus kirjoittaa nykyisestä sijainnista kelaamatta taaksepäin, joten aseta Position := 0 ennen kuin luovutat virran vastauskehykselle. Suoratoistokirjoitusta ja eräajoja käsittelevä artikkeli kehittää palvelinpuolen mallin, ja tietokantavientiä käsittelevä artikkeli näyttää, mihin nämä säätimet sijoittuvat tietojoukko-ohjatussa raportissa

Pidä lopuksi yksi pahimman tapauksen testiaineisto jokaista raporttiperhettä varten ja mittaa sen ajoaika CI:ssä. Asiakirjojen luonnin suorituskykytaantumat ilmoittavat itsestään harvoin. Silmukan sisälle lisätty tyyli tai täydellä Open-kutsulla korvattu tunnustelu ei muuta toiminnallisuutta, ja yöajo kestää vain neljäkymmentä minuuttia pidempään. Ajastettu testi edustavalla puolen miljoonan solun aineistolla muuttaa ajautumisen punaiseksi koontiversioksi tuotantohäiriön sijaan

Arviointiversiot, joukkoluontiesimerkin sisältävät demoprojektit ja täydellinen API-viite ovat saatavilla HotXLS Delphi Component -sivulla