Výkon při zpracování velkých sešitů Excelu v Delphi s HotXLS

Když export s 300 000 řádky prorazí svůj paměťový rozpočet, obvykle se za to viní počet řádků. Počet řádků bývá obvykle nevinný. Nákladné části velkého sešitu jsou ty, které vznikají jako vedlejší efekt: pool stylů, který roste o jeden záznam na buňku, protože se formátování přidávalo uvnitř smyčky, XML listu sestavené při ukládání jako jeden obří řetězec, milion identických těl vzorců uložených jedno po druhém. HotXLS, nativní knihovna losLab v Delphi pro soubory XLS a XLSX, vám dává konkrétní páku pro každý z těchto nákladů. Žádná z nich není ve výchozím nastavení zapnutá, protože každá mění kompromis, takže vědět, která páka odpovídá kterému symptomu, je ta skutečná dovednost v oblasti výkonu

Kde velký sešit utrácí paměť

Existují dva odlišné paměťové režimy, se kterými je třeba počítat. Během generování roste model buněk v paměti s každou buňkou, které se dotknete: hodnoty, formáty a vzorce se všechny stávají objekty nebo záznamy v poolu. Během ukládání výchozí cesta XLSX navíc vykreslí XML každého listu do širokého řetězce, než jej zkomprimuje do kontejneru zip, takže špičkové využití je model plus serializovaná podoba největšího listu. Úloha, která přežije sestavovací smyčku a pak zemře uvnitř SaveAs, naráží na druhý režim, ne na první, a oprava jednoho nic nedělá pro ten druhý

Velikost souboru se řídí příbuzným pravidlem: buňky jsou jen jeden z přispěvatelů, vedle stylů, sdílených řetězců, vzorců, obrázků a komentářů. Auditní průchod pomocí ForEachCell a počtů kolekcí po listech vám před optimalizací prozradí, který zdroj v problematickém souboru skutečně dominuje, takže neoptimalizujete ten nesprávný. Jedna jemnost při měření: Sheet.Cells.Count na straně XLSX hlásí počet instanciovaných buněk v řídkém úložišti, ne plochu použitého rozsahu. List, jehož data zabírají obdélník 1000 na 50 s poloviční prázdných buněk, napočítá zhruba 25 000, ne 50 000. Tento rozdíl je důležitý, když porovnáváte zákazníkův „obří" soubor se svými testovacími fixture, protože plocha použitého rozsahu a skutečná obsazenost buněk se v řídkých finančních rozvrženích mohou lišit o řád

StreamingWrite opravuje cestu ukládání, ne cestu sestavování

Nastavení TXLSXWorkbook.StreamingWrite := True přepne SaveAs na streamovací serializátor, který zapisuje XML listu přímo do streamu zip a eliminuje mezikrok s řetězcem na list. Výchozí hodnota je False kvůli zachování chování, a zapnutí je změna na jeden řádek:

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;   // XML listu se streamuje přímo do kontejneru zip
  Book.SaveAs('bulk.xlsx');
finally
  Book.Free;
end;

Buďte přesní v tom, co to přináší: model buněk sestavený smyčkou zabírá přesně stejně tolik paměti jako předtím. StreamingWrite zploští špičku v čase ukládání, což je rozdíl mezi dávkovou úlohou, která doběhne do konce, a tou, která selže na 95 %. Pokud paměť vyčerpá už samotná sestavovací smyčka, potřebné páky jsou další dvě

Pooly stylů: přidejte jednou, index znovu použijte

Formátování XLSX je v HotXLS založené na poolech: Book.Fonts.Add(...), Fills.AddSolid(...) a Borders.Add(...) vracejí 0-based index poolu, na který se buňky odkazují. Volání Fonts.Add se shodnými parametry uvnitř smyčky se deduplikuje, takže plýtvá časem, ne prostorem. Alignments.Add se chová jinak: vrací při každém volání nový objekt, takže vytváření zarovnání pro každou buňku roste pool lineárně s počtem řádků. Jeden zvyk pokrývá oba případy. Vyřešte každý index poolu jednou, mimo smyčku, a uvnitř ní jen indexy přiřazujte

// vytáhněte vyhledávání v poolu ven z horké smyčky
HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);   // index poolu, 0-based
for C := 1 to 24 do
  Sheet.Cells[1, C].FontIndex := HeaderFont + 1;            // buňky ukládají 1-based; 0 = výchozí

+ 1 není překlep a zapomenutí na něj je tady klasická chyba, která generuje symptomy: pooly vydávají 0-based indexy, zatímco vlastnosti na straně buňky považují 0 za „výchozí", takže každý index poolu musí být při přiřazení posunutý o jedna. Uděláte-li to opomenutím špatně, vaše záhlaví se tiše vykreslí ve výchozím písmu sešitu, což je vada, kterou nikdo nezpozoruje až do kontroly brandingu

Nahraďte provoz Variant na úrovni jednotlivých buněk callbacky po řádcích

Každé Sheet.Cells[R, C].Value := X zahrnuje vyhledání nebo vytvoření buňky plus přiřazení Variant. U několika set tisíc buněk se tato režie na přístup stává měřitelnou v profilech. HotXLS poskytuje na obou fasádách hromadná callback API (ForEachCell a ForEachRow pro čtení, WriteCells a WriteRows pro zápis), která přesunou iteraci dovnitř enginu a vašemu kódu předávají celé řádky najednou:

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;     // zastaví celý zápis
    Exit;
  end;
  Values := VarArrayOf([FRows[Row - 1].Account,
                        FRows[Row - 1].PostedOn,
                        FRows[Row - 1].Amount]);
end;

// jedno volání enginu místo stovek tisíc přístupů k vlastnosti
Sheet.WriteRows(1, 1, FCount, 3, FillRow);

Příznak Skip u callbacku ponechá řádek nedotčený bez přerušení a Cancel operaci ukončí předčasně, což se hodí, když je zdrojem reader, jehož délku zjišťujete za pochodu. Spárujte WriteRows pro sestavení se StreamingWrite pro uložení, a cesta generování už nemá žádné zbývající horké místo na úrovni buňky

Páky na straně čtení ve fasádě XLS

Velké starší soubory .xls mají vlastní sadu nástrojů. _DisableGraphics := True před Open úplně přeskočí parsování kreslicí vrstvy, což zrychlí načítání sešitů, které nesou roky nahromaděných tvarů a vložených obrázků. Omezení je tvrdé: kreslicí vrstva pak v modelu chybí, takže uložení takového sešitu zapíše soubor bez jeho kreseb. Tento příznak si nechte pro čistě čtecí analytické úlohy. SetTempDir přesměruje dočasné soubory zapisovače BIFF, což hraje roli na serverech, kde má výchozí umístění dočasných souborů kvótu nebo leží na pomalém úložišti. UseSharedFormulas seskupí opakovaná těla vzorců do záznamů sdílených vzorců, čímž zmenší soubory tam, kde se sloupec vzorců opakuje přes šedesát tisíc řádků

Čtecí smyčky nad daty XLS mají past v indexování, na kterou stojí za to upozornit, protože při obranném zacházení zdvojí práci a při přehlédnutí poškodí výsledky: UsedRange hlásí své hranice FirstRow, LastRow, FirstCol a LastCol jako 0-based, zatímco Cells.Item[Row, Col] je 1-based. Skenování, které prochází použitý rozsah, musí při přístupu k buňce přičíst jedničku ke každé souřadnici, jako v Cells.Item[Row + 1, Col + 1], jinak čte mřížku posunutou diagonálně o jednu buňku, tiše vynechá poslední řádek a sloupec a zahrne fantomový první. Callback ForEachCell se tomuto nesouladu úplně vyhne, což je další důvod, proč jej upřednostnit u skenování celého listu

Prozkoumejte soubory dřív, než je načtete

Nejlevnější operace s velkým sešitem je ta, které se vyhnete. GetSheetNames na obou fasádách vypíše listy souboru, aniž by načetla data buněk. Implementace XLSX čte pouze manifest sešitu uvnitř zipu a explicitně ponechá instanci sešitu nenaplněnou, a fasáda XLS zastaví skenování na první hranici podproudu. Díky tomu je to správná předletová kontrola pro otázku „na který list by měla tato importní úloha mířit" a CanReadEncrypted zodpoví otázku „je toto šifrovaný kontejner" dřív, než dojde k odsouzenému pokusu o Open

Names := TStringList.Create;
Book := TXLSXWorkbook.Create;
try
  if Book.GetSheetNames('big-unknown.xlsx', Names) <= 0 then
    raise Exception.Create('cannot enumerate sheets');   // selhání seznam vyprázdní
  // vyberte cílový list a pak rozhodněte, jestli se vyplatí plný Open
finally
  Book.Free;
  Names.Free;
end;

Všimněte si konvence návratového kódu: tyto zkoumací funkce signalizují selhání hodnotami rovnými nule nebo nižšími a vyprázdní výstupní seznam, takže testujte <= 0 místo porovnávání s jednou konkrétní hodnotou úspěchu

Přizpůsobení přístupu úloze

Pro bezobslužné pipeline, které postupně generují mnoho velkých souborů, celkový obrázek doplňují ještě dva zvyky. Objekty sešitu nejsou bezpečné pro sdílení mezi vlákny, ale nic nebrání jednomu nezávislému sešitu na pracovní vlákno, což čistě zparalelizuje dávkovou konverzi. A když výstup míří do HTTP místo na disk, přetížení ukládání TStream se kombinují se StreamingWrite, takže se velká odpověď nikdy nezhmotní jako dočasný soubor. Platí jedna provozní poznámka: uložení do streamu zapisuje od aktuální pozice bez převinutí, takže před předáním streamu odpovídacímu frameworku nastavte Position := 0. Článek o streamovaném zápisu a dávkových úlohách na serveru tento vzor na straně serveru rozvíjí, a článek o exportu z databáze ukazuje, kam tyto páky zapadají do reportu řízeného datovou sadou

Nakonec si pro každou rodinu reportů uchovejte jednu fixture pro nejhorší případ a měřte její čas v CI. Regrese výkonu při generování dokumentů se málokdy samy ohlásí. Styl přidaný uvnitř smyčky nebo sonda nahrazená plným Open funkčně nic nemění, a noční dávka prostě trvá o čtyřicet minut déle. Časovaný test na reprezentativní fixture s půl milionem buněk promění tento posun v červený build místo provozního incidentu

Hodnoticí sestavení, ukázkové projekty s příkladem hromadného generování a kompletní referenci API najdete na stránce HotXLS Delphi Component