Výkon veľkých zošitov Excelu v Delphi pomocou HotXLS

Keď export s 300 000 riadkami prekročí pamäťový limit, zvyčajne sa obviňuje počet riadkov. Ten je však väčšinou nevinný. Skutočne náročnými časťami veľkého zošita sú tie, ktoré vznikajú ako vedľajší efekt: fond štýlov (style pool) rastúci o jeden záznam na bunku kvôli formátovaniu pridanému vnútri cyklu, XML kód hárka zostavený pri ukladaní ako jeden obrovský reťazec alebo milión identických tiel vzorcov uložených po jednom. HotXLS, natívna knižnica pre Delphi od losLab určená pre súbory XLS and XLSX, poskytuje konkrétny nástroj na optimalizáciu každého z týchto nákladov. Žiadny z nich nie je predvolene zapnutý, pretože každý mení určitý kompromis, takže vedieť, ktorý nástroj zodpovedá ktorému príznaku, je skutočným umením optimalizácie výkonu.

Kde veľký zošit spotrebováva pamäť

Je potrebné rozlišovať dva pamäťové režimy. Počas generovania rastie pamäťový model buniek v pamäti s každou bunkou, ktorej sa dotknete: hodnoty, formáty a vzorce sa stávajú objektmi alebo záznamami vo fonde. Počas ukladania predvolený prístup XLSX dodatočne vykreslí XML kód každého hárka do dlhého reťazca pred jeho kompresiou do archívu zip, takže špičková spotreba je daná modelom plus serializovanou formou najväčšieho hárka. Úloha, ktorá úspešne prebehne cyklom zostavenia a potom zlyhá vnútri metódy SaveAs, naráža na druhý menovaný režim, nie na prvý, a oprava jedného nerieši druhý.

Veľkosť súboru sa riadi podobným pravidlom: bunky sú len jedným z prispievateľov, popri štýloch, zdieľaných reťazcoch, vzorcoch, obrázkoch a komentároch. Analýza pomocou ForEachCell a počty prvkov v kolekciách pre jednotlivé hárky vám napovedia, ktorý prostriedok skutočne dominuje v problematickom súbore ešte pred tým, než začnete optimalizovať nesprávnu vec. Jedna jemnosť merania: vlastnosť Sheet.Cells.Count na strane XLSX vracia počet inštancovaných buniek v riedkom úložisku (sparse store), nie plochu použitého rozsahu. Hárok, ktorého dáta zaberajú oblasť 1000 x 50 riadkov a stĺpcov, pričom polovica buniek je prázdna, vykazuje počet približne 25 000, nie 50 000. Tento rozdiel je dôležitý, keď porovnávate „obrovský“ súbor od zákazníka s vašimi testovacími súbormi, pretože plocha použitého rozsahu a skutočný počet buniek sa v riedkych finančných rozloženiach môžu líšiť o poriadok.

StreamingWrite rieši cestu ukladania, nie zostavenia

Nastavenie vlastnosti TXLSXWorkbook.StreamingWrite := True prepne metódu SaveAs na streamovací serializátor, ktorý zapisuje XML kód hárka priamo do prúdu zip, čím eliminuje dočasný medzistupeň v podobe reťazca pre každý hárok. Z dôvodu spätnej kompatibility je predvolene nastavená hodnota False a jej zapnutie je záležitosťou jedného riadku:

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;   // sheet XML streams into the zip container
  Book.SaveAs('bulk.xlsx');
finally
  Book.Free;
end;

Buďte presní v tom, čo tým získate: model buniek vytvorený cyklom zaberá v pamäti presne toľko isto miesta ako predtým. Vlastnosť StreamingWrite vyhladzuje špičku pri ukladaní, čo je rozdiel medzi dávkovou úlohou, ktorá sa úspešne dokončí, a tou, ktorá zlyhá pri 95 % priebehu. Ak samotný cyklus vytvárania vyčerpá pamäť, nástroje, ktoré potrebujete, sú popísané v nasledujúcich dvoch častiach.

Fondy štýlov: pridajte raz, používajte index

XLSX formátovanie v HotXLS je založené na fondoch: metódy Book.Fonts.Add(...), Fills.AddSolid(...) a Borders.Add(...) vracajú index vo fonde od nuly, na ktorý sa bunky odkazujú. Volanie Fonts.Add s identickými parametrami vnútri cyklu je deduplikované, takže skôr plytvá časom než pamäťou. Vlastnosť Alignments.Add sa však správa inak: vracia nový objekt pri každom volaní, takže vytváranie zarovnania pre každú bunku zväčšuje fond lineárne s počtom riadkov. Jeden prístup pokrýva oba prípady: zistite každý index fondu iba raz pred cyklom a priraďujte indexy vnútri neho.

// hoist pool lookups out of the hot loop
HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);   // 0-based pool index
for C := 1 to 24 do
  Sheet.Cells[1, C].FontIndex := HeaderFont + 1;            // cells store 1-based; 0 = default

Zápis + 1 nie je preklep a jeho vynechanie je klasickou chybou spôsobujúcou problémy: fondy poskytujú indexy začínajúce od nuly, zatiaľ čo vlastnosti na strane bunky považujú 0 za „predvolenú“, takže každý index fondu sa musí pri priradení posunúť o jedna. Ak to vynecháte, vaše hlavičky sa v zošite ticho vykreslia predvoleným písmom, čo je chyba, ktorú si nikto nevšimne až do kontroly dizajnu.

Nahraďte prístup k jednotlivým bunkám spätnými volaniami riadkov

Každý zápis Sheet.Cells[R, C].Value := X zahŕňa vyhľadanie alebo vytvorenie bunky a priradenie typu Variant. Pri niekoľkých stovkách tisíc buniek sa táto réžia na prístup stáva merateľnou pri analýze výkonu. HotXLS poskytuje hromadné rozhrania API pre spätné volania na oboch fasádach (metódy ForEachCell a ForEachRow na čítanie, WriteCells a WriteRows na zápis), ktoré presúvajú iteráciu vnútri enginu a odovzdávajú vášmu kódu celé riadky naraz:

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;     // stop the whole write
    Exit;
  end;
  Values := VarArrayOf([FRows[Row - 1].Account,
                        FRows[Row - 1].PostedOn,
                        FRows[Row - 1].Amount]);
end;

// one engine call instead of hundreds of thousands of property hits
Sheet.WriteRows(1, 1, FCount, 3, FillRow);

Príznaky spätnej väzby Skip ponechá riadok nedotknutý bez prerušenia zápisu a príznak Cancel ukončí operáciu predčasne, čo je užitočné, keď je zdrojom čítačka, ktorej dĺžku zisťujete priebežne. Skombinujte metódu WriteRows pre zostavenie s vlastnosťou StreamingWrite pre ukladanie a cesta generovania už nebude obsahovať žiadne úzke hrdlá na úrovni jednotlivých buniek.

Nástroje pre stranu čítania v rozhraní XLS

Veľké staršie súbory .xls majú vlastnú sadu nástrojov. Nastavenie _DisableGraphics := True pred zavolaním Open úplne preskočí analýzu vrstvy kreslenia, čo urýchľuje načítanie zošitov, ktoré obsahujú roky nahromadených tvarov a vložených obrázkov. Obmedzenie je však prísne: vrstva kreslenia potom v modeli chýba, takže uloženie takéhoto zošita vytvorí súbor bez obrázkov a kresieb. Tento príznak si vyhraďte len pre analytické úlohy na čítanie. Metóda SetTempDir presmeruje dočasné súbory zapisovača BIFF, čo je dôležité na serveroch, kde má predvolené dočasné umiestnenie obmedzenú kvótu alebo sa nachádza na pomalom úložisku. Vlastnosť UseSharedFormulas zoskupuje opakujúce sa telá vzorcov do zdieľaných záznamov, čím zmenšuje súbory, v ktorých sa stĺpec so vzorcom opakuje na šesťdesiatich tisíckach riadkov.

Cykly čítania nad dátami XLS skrývajú pascu s indexovaním, ktorú stojí za to spomenúť, pretože pri defenzívnom prístupe zdvojuje prácu a pri prehliadnutí poškodzuje výsledky: vlastnosť UsedRange uvádza svoje hranice FirstRow, LastRow, FirstCol a LastCol indexované od nuly, zatiaľ čo prístup Cells.Item[Row, Col] indexuje od 1. Skenovanie prechádzajúce použitým rozsahom musí pridať jednotku ku každej súradnici pri prístupe k bunke, napríklad Cells.Item[Row + 1, Col + 1], inak číta mriežku posunutú diagonálne o jednu bunku, čím ticho zahodí posledný riadok a stĺpec a zahrnie fiktívny prvý riadok a stĺpec. Spätné volanie ForEachCell sa tomuto nesúladu úplne vyhýba, čo je ďalší dôvod, prečo ho uprednostniť pre prehľadávanie celých hárkov.

Preskúmajte súbory pred ich načítaním

Najlacnejšia operácia s veľkým zošitom je tá, ktorej sa vyhnete. Metóda GetSheetNames na oboch rozhraniach vypíše názvy hárkov súboru bez načítania dát buniek. Implementácia XLSX číta iba manifest zošita vnútri zip archívu a explicitne ponecháva inštanciu zošita neobsadenú, zatiaľ čo rozhranie XLS zastaví skenovanie na hranici prvého čiastkového prúdu. To z nej robí správnu kontrolu pred spustením typu „na ktorý hárok by mala táto importná úloha cieliť“ a metóda CanReadEncrypted odpovie na otázku „je tento kontajner šifrovaný“ ešte pred neúspešným pokusom o Open.

Names := TStringList.Create;
Book := TXLSXWorkbook.Create;
try
  if Book.GetSheetNames('big-unknown.xlsx', Names) <= 0 then
    raise Exception.Create('cannot enumerate sheets');   // failure clears the list
  // pick the target sheet, then decide whether a full Open is worth it
finally
  Book.Free;
  Names.Free;
end;

Upozorňujeme na konvenciu návratových kódov: tieto detekčné funkcie signalizujú zlyhanie hodnotami rovnými alebo menšími ako nula a vyprázdnia výstupný zoznam, preto testujte podmienku <= 0 a neporovnávajte ju len s jednou konkrétnou úspešnou hodnotou.

Prispôsobenie prístupu danej úlohe

Pre automatizované procesy spracovania, ktoré generujú mnoho veľkých súborov za sebou, dopĺňajú celkový obraz ďalšie dva návyky. Objekty zošitov nie sú vláknovo bezpečné pre zdieľanie, ale nič nebráni spusteniu jedného nezávislého zošita na jedno pracovné vlákno, čo čisto paralelizuje dávkovou konverziu. A keď výstup smeruje do HTTP namiesto na disk, preťaženia metódy na ukladanie do TStream sa spoja s vlastnosťou StreamingWrite, takže veľká odpoveď sa nikdy nematerializuje ako dočasný súbor. Platí tu jedna prevádzková poznámka: ukladanie do prúdu zapisuje od aktuálnej pozície bez posunu späť, preto pred odovzdaním prúdu webovému frameworku nastavte vlastnosť Position := 0. Článok o streamovanom zápise a dávkových úlohách rozvíja tento vzor na strane servera a článok o exporte databáz ukazuje, kam tieto nástroje zapadajú v reporte riadenom datasetom.

Na záver udržiavajte jeden testovací súbor pre najhorší možný scenár pre každú skupinu reportov a merajte jeho čas v rámci CI. Regresie výkonu pri generovaní dokumentov sa zriedka ohlásia samy. Štýl pridaný vnútri cyklu alebo nahradenie detekcie plným otvorením pomocou Open nič nemení na funkčnosti a nočná dávka len trvá o štyridsať minút dlhšie. Časovo meraný test na reprezentatívnej vzorke s pol miliónom buniek premení tento posun na červené zostavenie v CI namiesto incidentu v ostrej prevádzke.

Skúšobné verzie, ukážkové projekty s príkladom hromadného generovania a kompletná referenčná príručka k rozhraniu API sú k dispozícii na stránke komponentu HotXLS Component.