Nagy Excel munkafüzetek teljesítményhangolása Delphi-ben HotXLS-szel

Amikor egy 300 000 soros export átszakítja a memóriakeretét, rendszerint a sorok számát okolják. A sorok száma rendszerint ártatlan. Egy nagy workbook drága részei azok, amelyek mellékhatásként jönnek létre: a stíluskészlet, amely cellánként egy bejegyzéssel nő, mert a formázás a cikluson belülre került; a munkalap XML-je, amelyet mentéskor egyetlen óriási sztringként állítunk össze; egymillió azonos képlettörzs, egyesével eltárolva. A HotXLS, a losLab natív Delphi-könyvtára XLS- és XLSX-fájlokhoz, mindegyik költséghez ad egy-egy konkrét kart. Egyik sincs alapból bekapcsolva, mert mindegyik megváltoztat egy kompromisszumot, így annak tudása, melyik kar melyik tünethez tartozik, maga a valódi teljesítménytudás

Hol fogy a memória egy nagy workbookban

Két külön memóriaszakaszról érdemes gondolkodni. A generálás alatt a memóriabeli cellamodell minden megérintett cellával nő: az értékek, a formátumok és a képletek mind objektummá vagy készletbejegyzéssé válnak. A mentés alatt az alapértelmezett XLSX-útvonal ráadásul minden munkalap XML-jét széles sztringgé rajzolja, mielőtt betömörítené a zip-tárolóba, így a csúcshasználat a modell plusz a legnagyobb munkalap sorosított alakja. Az a feladat, amely túléli az építőciklust, majd a SaveAs hívásban hal meg, a második szakaszba ütközik, nem az elsőbe, és az egyikre való javítás a másikon semmit nem tesz

A fájlméret rokon szabályt követ: a cellák csak az egyik hozzájárulók, a stílusok, az osztott sztringek, a képletek, a képek és a megjegyzések mellett. Egy ForEachCell hívással és a munkalaponkénti gyűjteményszámokkal végzett auditmenet megmondja, melyik erőforrás uralja valójában a problémás fájlt, mielőtt rossz dolgot optimalizálna. Egy mérési finomság: a Sheet.Cells.Count az XLSX oldalon a ritka tárolóban példányosított cellák számát jelenti, nem a használt tartomány területét. Az a munkalap, amelynek adata egy 1000-szer 50-es téglalapot foglal el, és amelynek fele üres, nagyjából 25 000-et számol, nem 50 000-et. Ez a különbség akkor számít, amikor egy ügyfél „hatalmas” fájlját hasonlítja a saját tesztadataihoz, mert ritka pénzügyi elrendezésekben a használt tartomány területe és a tényleges cellaszám nagyságrenddel is eltérhet

A StreamingWrite a mentési, nem az építési útvonalat javítja

A TXLSXWorkbook.StreamingWrite := True beállítás átkapcsolja a SaveAs hívást egy streamelő sorosítóra, amely a munkalap XML-jét közvetlenül a zip-adatfolyamba írja, és ezzel megszünteti a munkalaponkénti köztes sztringet. Alapértéke viselkedési kompatibilitásból False, a bekapcsolása pedig egysoros változtatás:

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;   // a munkalap XML-je a zip-tárolóba streamel
  Book.SaveAs('bulk.xlsx');
finally
  Book.Free;
end;

Legyen pontos abban, mit vesz ezzel: a ciklus által felépített cellamodell pontosan annyi memóriát foglal, mint korábban. A StreamingWrite a mentéskori csúcsot lapítja el, és ez a különbség egy befejeződő és egy 95 százaléknál elbukó kötegelt feladat között. Ha maga az építőciklus meríti ki a memóriát, akkor a következő két kar az, amelyre szüksége van

Stíluskészletek: add hozzá egyszer, az indexet használd újra

Az XLSX-formázás a HotXLS-ben készletalapú: a Book.Fonts.Add(...), a Fills.AddSolid(...) és a Borders.Add(...) nullától induló készletindexet ad vissza, amelyre a cellák hivatkoznak. A Fonts.Add azonos paraméterekkel, cikluson belüli hívása deduplikálódik, tehát időt pazarol, nem helyet. Az Alignments.Add másképp viselkedik: hívásonként friss objektumot ad vissza, így a cellánkénti igazításlétrehozás lineárisan növeli a készletet a sorok számával. Egyetlen szokás mindkét esetet lefedi. Oldjon fel minden készletindexet egyszer, a cikluson kívül, és a cikluson belül csak az indexeket ossza ki

// emeld ki a készletkereséseket a forró ciklusból
HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);   // nullától induló készletindex
for C := 1 to 24 do
  Sheet.Cells[1, C].FontIndex := HeaderFont + 1;            // a cella egytől tárol; 0 = alapértelmezett

A + 1 nem elgépelés, és az elhagyása itt a klasszikus, tünetgyártó hiba: a készletek nullától induló indexeket osztanak, a cellaoldali tulajdonságok viszont a 0 értéket „alapértelmezettként” kezelik, tehát minden készletindexet eggyel el kell tolni az értékadáskor. Rontsa el mulasztásból, és a fejlécei csendben a workbook alapértelmezett betűkészletével rajzolódnak ki – olyan hiba, amelyet a márkabírálatig senki nem vesz észre

Cellánkénti Variant-forgalom helyett sor-visszahívások

Minden Sheet.Cells[R, C].Value := X értékadás egy cellakeresést vagy -létrehozást és egy Variant-értékadást jelent. Néhány százezer cellánál ez a hozzáférésenkénti többlet mérhetővé válik a profilokban. A HotXLS mindkét felületen ad tömeges visszahívásos API-kat (olvasásra a ForEachCell és a ForEachRow, írásra a WriteCells és a WriteRows), amelyek a bejárást a motor belsejébe viszik, és egész sorokat adnak át a kódjának:

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;     // állítsd le a teljes írást
    Exit;
  end;
  Values := VarArrayOf([FRows[Row - 1].Account,
                        FRows[Row - 1].PostedOn,
                        FRows[Row - 1].Amount]);
end;

// egyetlen motorhívás több százezer tulajdonságérintés helyett
Sheet.WriteRows(1, 1, FCount, 3, FillRow);

A visszahívás Skip jelzője megszakítás nélkül hagyja érintetlenül az adott sort, a Cancel pedig korán befejezi a műveletet, ami akkor hasznos, amikor a forrás olyan olvasó, amelynek a hosszát menet közben deríti ki. Párosítsa a WriteRows hívást az építéshez a StreamingWrite beállítással a mentéshez, és a generálási útvonalon nem marad cellánkénti forró pont

Olvasásoldali karok az XLS felületen

A nagy, örökölt .xls fájloknak saját eszköztáruk van. A _DisableGraphics := True az Open előtt teljesen kihagyja a rajzréteg elemzését, ami felgyorsítja azoknak a workbookoknak a betöltését, amelyek évek alatt felhalmozott alakzatokat és beágyazott képeket cipelnek. A megkötés kemény: a rajzréteg ezután hiányzik a modellből, tehát egy ilyen workbook mentése a rajzai nélküli fájlt ír ki. Tartsa fenn ezt a jelzőt csak olvasható elemzési feladatokra. A SetTempDir átirányítja a BIFF-író ideiglenes fájljait, ami olyan kiszolgálókon számít, ahol az alapértelmezett ideiglenes helynek kvótája van, vagy lassú tárolón ül. A UseSharedFormulas az ismétlődő képlettörzseket osztottképlet-rekordokba csoportosítja, és ezzel zsugorítja azokat a fájlokat, ahol egy képletoszlop hatvanezer soron át ismétlődik

Az XLS-adaton futó olvasóciklusoknak van egy indexelési csapdájuk, amelyet érdemes jelezni, mert óvatos kezelés mellett megduplázza a munkát, elmulasztva pedig elrontja az eredményt: az UsedRange a FirstRow, LastRow, FirstCol és LastCol határait nullától indexelve jelenti, míg a Cells.Item[Row, Col] egytől indexel. Az a bejárás, amely végigmegy a használt tartományon, a cellahozzáférésnél minden koordinátához hozzá kell adjon egyet – így: Cells.Item[Row + 1, Col + 1] –, különben egy átlósan egy cellával eltolt rácsot olvas, csendben elhagyja az utolsó sort és oszlopot, és bevesz egy nem létező elsőt. A ForEachCell visszahívás teljesen megkerüli ezt az eltérést, és ez még egy ok arra, hogy teljes munkalapok bejárásához azt részesítse előnyben

Vizsgálja meg a fájlokat, mielőtt betöltené őket

A legolcsóbb művelet egy nagy workbookon az, amelyet elkerül. A GetSheetNames mindkét felületen felsorolja egy fájl munkalapjait a cellaadatok betöltése nélkül. Az XLSX-megvalósítás csak a zip-en belüli workbook-manifesztet olvassa, és a workbook-példányt kifejezetten feltöltetlenül hagyja, az XLS-felület pedig az első alfolyam-határnál abbahagyja a pásztázást. Ettől ez a helyes indulás előtti ellenőrzés arra a kérdésre, hogy „melyik munkalapot célozza ez az importálás”, a CanReadEncrypted pedig arra válaszol, hogy „titkosított tároló-e ez”, még egy kudarcra ítélt Open kísérlet előtt

Names := TStringList.Create;
Book := TXLSXWorkbook.Create;
try
  if Book.GetSheetNames('big-unknown.xlsx', Names) <= 0 then
    raise Exception.Create('cannot enumerate sheets');   // hiba esetén kiürül a lista
  // válaszd ki a célmunkalapot, aztán döntsd el, megéri-e a teljes Open
finally
  Book.Free;
  Names.Free;
end;

Figyeljen a visszatérési kód konvenciójára: ezek a vizsgáló függvények nullával vagy annál kisebb értékkel jelzik a hibát, és kiürítik a kimeneti listát, tehát a <= 0 feltételt tesztelje, ne egyetlen konkrét sikerértékkel hasonlítson

A megközelítés a feladathoz méretezve

A felügyelet nélküli feldolgozósorokhoz, amelyek egymás után sok nagy fájlt generálnak, még két szokás kerekíti ki a képet. A workbook-objektumok megosztásra nem szálbiztosak, de semmi nem akadályozza, hogy minden munkaszálnak saját, független workbookja legyen, ami tisztán párhuzamosítja a kötegelt átalakítást. És amikor a kimenet lemez helyett HTTP-re megy, a TStream mentési túlterhelései a StreamingWrite beállítással együtt gondoskodnak arról, hogy egy nagy válasz soha ne öltsön testet ideiglenes fájlként. Egy üzemeltetési lábjegyzet ide tartozik: az adatfolyamba mentés az aktuális pozíciótól ír, visszatekerés nélkül, ezért állítsa be a Position := 0 értéket, mielőtt átadná az adatfolyamot a válaszkeretrendszernek. A streamelő írásról és a kötegelt feladatokról szóló cikk ezt a kiszolgálóoldali mintát bontja ki, az adatbázis-export cikk pedig megmutatja, hova illeszkednek ezek a karok egy adatvezérelt jelentésben

Végül tartson meg jelentéscsaládonként egy legrosszabb esetet leíró tesztfájlt, és mérje az idejét a CI-ben. A dokumentumgenerálás teljesítményregressziói ritkán jelentik be magukat. Egy cikluson belülre került stílus vagy egy teljes Open hívásra cserélt vizsgálat funkcionálisan semmit nem változtat, az éjszakai köteg pedig egyszerűen negyven perccel tovább tart. Egy félmillió cellás, jellemző mintán futó időmért teszt ezt az elsodródást piros buildté teszi üzemeltetési incidens helyett

Kiértékelési buildek, tömeges generálási példát tartalmazó demóprojektek és a teljes API-referencia a HotXLS Delphi Component oldalon érhető el