Streamování obrovských souborů XLSX v Delphi bez načítání do paměti

Tabulka s milionem řádků a tuctem sloupců je zcela běžný export z databázové reportovací úlohy. Otevřete-li ji běžným způsobem načtením celého sešitu do TXLSWorkbook, musí proces materializovat každou z těchto dvanácti milionů buněk jako živý objekt ještě předtím, než se vůbec spustí první řádek vaší obchodní logiky. Soubor na disku může mít šedesát megabajtů komprimovaného XML. Objektový strom, do kterého se rozvine, je několikanásobně větší a to vše musí být současně přítomno v paměti, protože model je ze své podstaty navržen pro náhodný přístup. U reportu, který hodláte pouze přečíst shora dolů a zahodit, je to zbytečně velké množství paměti vynaložené na strukturu, kterou jste vlastně nikdy nepotřebovali

Existuje i druhá cesta zpracování téhož souboru. Namísto budování modelu skenujete XML listu pouze směrem vpřed, jednu buňku po druhé, a jakmile si každou buňku prohlédnete, necháte ji plynout pryč. Nic se nehromadí. Využití paměti zůstává téměř konstantní, ať už má list tisíc řádků nebo deset milionů, protože čtečka nikdy nedrží více než právě analyzovanou část a několik malých vyhledávacích tabulek. Přesně takto funguje přímá čtečka v HotXLS a zbytek tohoto článku se věnuje tomu, proč si zachovává nízké paměťové nároky a co získáte výměnou za to

Proč se model v paměti špatně škáluje

Soubor XLSX je balíček ZIP obsahující části XML popsané normou ECMA-376. Každý pracovní list tvoří samostatnou část, xl/worksheets/sheetN.xml, a uvnitř ní je každý řádek reprezentován prvkem <row> obsahujícím prvky buněk <c>. Standardní způsob načítání přečte tuto část a zkonstruuje adresovatelný objekt pro každou buňku tak, abyste se později mohli dotázat na Cells[12345, 7] a získat odpověď v konstantním čase. Náhodný přístup je hlavním smyslem modelu sešitu a právě ten umožňuje pohodlné úpravy, vyhodnocování vzorců a formátování

Daní za to je, že náhodný přístup vyžaduje současnou přítomnost všeho v paměti. Nemůžete indexovat do struktury, kterou jste vybudovali pouze částečně. Špičková spotřeba paměti při plném načtení je tedy úměrná počtu buněk a u listu s miliony vyplněných buněk se tato spotřeba dostává do hodnot, kde by se vaše služba ocitla jen nerada, zejména pokud na sdíleném počítači běží několik takových úloh současně. Když je přístup, který skutečně potřebujete, pouze sekvenční, je platba za náhodný přístup de facto platbou za schopnost, kterou nevyužijete

SAX analýza směrem vpřed, která nebuduje strom

Přímá čtečka (direct reader) otevře ZIP balíček a prochází každou část listu pomocí pull parseru ve stylu SAX. SAX zde znamená, že analyzátor hlásí události analýzy podle toho, jak na ně narazí: počáteční element, textový úsek, koncový element, a poté pokračuje dál. Neuchovává za sebou žádný strom uzlů. Čtečka sleduje aktuální řádek a sloupec z atributů r, shromažďuje typ buňky, index stylu, hodnotu a text vzorce tak, jak události přicházejí. Jakmile zachytí uzavírací značku </c>, vypustí jednu buňku a zapomene na ni. Další buňka pak znovu využije stejnou hrstku lokálních proměnných

Protože se mezi buňkami nic neuchovává, nároky na paměť nerostou s počtem buněk. A právě této vlastnosti je třeba se držet. List se dvěma sty řádky a list s dvaceti miliony řádků stojí čtečku naprosto stejné množství rezidentní paměti a rozdíl spočívá pouze v tom, jak dlouho skenování běží. Vzdáte se náhodného přístupu, hlavní přednosti modelu, a výměnou získáte paměťový strop, který počet buněk nedokáže prolomit

Co zůstává v paměti a proč zrovna tyto dvě části

Skenování není zcela bezstavové a právě tyto výjimky jsou poučné. Dvě malé tabulky musí být po celou dobu uloženy v paměti, protože samotná buňka neobsahuje dostatek informací k tomu, aby se dala bez nich interpretovat

První z nich je tabulka sdílených řetězců. Ve SpreadsheetML neukládá textová buňka svůj vlastní text. Nese s sebou atribut t="s" a číselnou hodnotu, což je index do xl/sharedStrings.xml, jednoho deduplikovaného seznamu všech rozdílných řetězců v sešitu. Je to výhodný kompromis šetřící místo u souborů, kde se tisíce řádků opakují stejné štítky, ale to znamená, že čtečka musí tuto tabulku řetězců načíst předem a držet ji v paměti, protože kterákoli buňka v jakémkoli listu může odkazovat na kteroukoli její položku. Velikost tabulky je dána počtem odlišných řetězců, nikoli celkovým počtem buněk, takže zůstává poměrně malá i u obrovských listů

Druhou částí je mapování formátů čísel z části se styly. Číselná buňka a buňka s datem jsou z hlediska uložených bajtů totožné: obě představují obyčejné číslo, protože datum ve SpreadsheetML je pouze sériový počet dní. Jediná věc, která je odlišuje, je styl buňky, který ukazuje přes cellXfs v xl/styles.xml na ID formátu čísla. Aby bylo možné interpretovat datum jako datum, a ne jako surové sériové číslo, čtečka načte tuto tabulku přiřazující styly k formátům a udrží ji v paměti. Všechno ostatní v souboru, samotná data buněk, která tvoří většinu bajtů, pouze proteče pamětí bez toho, aby se uložilo

Každá buňka hlásí svůj druh a hodnotu

Každá generovaná buňka dorazí jako záznam typu TXLSDirectCell. Ten obsahuje index a název listu, řádek a sloupec s počátkem na pozici 1, sémantický typ Kind, hodnotu Value jako Variant, text vzorce Formula bez počátečního rovnítka a surový index stylu StyleIndex. Druhem buňky může být xdkNumber, xdkString, xdkBoolean, xdkDate nebo xdkError, takže můžete provádět větvení kódu přímo podle významu buňky místo toho, abyste jej složitě odvozovali z atributů. Buňka se vzorcem nahlásí druh svého uloženého výsledku spolu s textem vzorce, takže vypočítaný součet projde jako číslo, které vám zároveň poví, jak přesně vzniklo

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;

Rozlišení data od čísla

Otázka formátu data si zaslouží bližší pohled, protože právě zde dělá většina naivních parserů chybu. Číselná buňka žádný typ datum neobsahuje. Buňka obsahující sériovou hodnotu 46000 může být množství, cena nebo 17. únor 2025. Co to ve skutečnosti je, zjistíte pouze díky ID formátu čísla získanému prostřednictvím stylu buňky. Norma ECMA-376 rezervuje blok předdefinovaných ID formátů, jejichž význam je pevně daný u každého kompatibilního generátoru sešitu. ID označující datum leží ve dvou rozsazích: 14 až 22 pro standardní formáty data a času a 45 až 47 pro formáty uplynulého času, jako je [h]:mm:ss. Když je zapnutá volba DetectDates, což je ve výchozím nastavení, čtečka přeloží styl každé číselné buňky na její ID formátu. Buňka, jejíž ID spadá do těchto rezervovaných rozsahů, je pak nahlášena jako xdkDate s hodnotou Value již převedenou na typ TDateTime v Delphi. Vlastní formáty se kontrolují rovněž prostřednictvím prohledávání kódu formátu na přítomnost tokenů pro datum a čas, avšak rezervované rozsahy představují tu nejspolehlivější páteř rozpoznávání. Pokud volbu DetectDates vypnete, tabulka stylů se vůbec nenačte, každá číselná buňka projde jako xdkNumber a skenování tak bude zlomkově štíhlejší

Přeskakování listů a včasné ukončení

Sekvenční skenování má obrovskou výhodu, které se náhodný přístup nemůže rovnat: můžete se zastavit. Událost OnSheet se vyvolá před otevřením každého listu a nabídne vám dva přepínače. Nastavte SkipSheet a celá tato část listu se vůbec analyzovat nebude. Takto projdete pouze listy, které vás ve vícestránkovém sešitu zajímají, aniž byste platili výkonnostní daň za čtení zbytku. Nastavením Abort ukončíte celé skenování okamžitě. Událost OnCell přináší svůj vlastní Abort, takže můžete proces zastavit ve chvíli, kdy narazíte na to, co jste hledali, ať už jde o konkrétní řádek, signalizační hodnotu nebo konec bloku hlavičky, a to bez nutnosti načítat zbývající miliony buněk. Při skenování pouze směrem vpřed je zrušení skutečně zadarmo, protože práce, kterou přeskočíte, je prací, která se ještě vůbec nestala

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;

Počítání buněk bez zpracovatele události

Za zmínku stojí ještě jedno nedávné vylepšení, které převádí častou otázku na jediné nenáročné volání. Čtečka počítá každou vyplněnou buňku, kterou projde, a dělá to bez ohledu na to, zda je, či není připojen zpracovatel události OnCell. Dříve bez nastaveného obslužného programu vyšel počet vyplněných buněk jako nulový, protože počítání bylo vedlejším efektem emitování událostí. Nyní je počítání na generování událostí zcela nezávislé. To znamená, že můžete položit jedinou otázku, kolik vyplněných buněk vlastně tento sešit obsahuje, a získat odpověď za cenu jednoduchého proskenování bez volání callbacků. Funkce ReadFile a ReadStream vrací tento součet jako typ Int64 a stejné číslo je následně k dispozici také ve vlastnosti CellCount. Vrácená hodnota -1 signalizuje, že soubor se nepodařilo otevřít nebo že nejde o balíček OOXML

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;

Pro úplné proskenování připojíte obslužný program a zavoláte funkci ReadFile naprosto stejným způsobem. Kontrast oproti plnému načtení je tím hlavním důvodem: tam, kde by načtení quarterly_export.xlsx do sešitu expandovalo každou buňku na rezidentní objekt a drželo by je všechny, přímá čtečka udržuje pouze sdílené řetězce a tabulku stylů, zatímco dvanáct milionů buněk protéká událostí OnCell jedna za druhou. Logika spuštěná nad každou buňkou po sobě nezanechává nic, takže maximální spotřeba paměti je dána počtem unikátních řetězců v sešitu, nikoli celkovým počtem řádků

Přímá čtečka je správný nástroj pro jednorázové přečtení velkého sešitu, extrakci dat nebo tvorbu přehledů. Pokud naopak potřebujete náhodný přístup plného modelu, ale chcete zachovat dobrý výkon u rozměrných souborů, řešení nastiňuje vyladění v našich poznámkách k výkonu u velkých sešitů v Delphi. A pokud je směr opačný a velký výstup nekonzumujete, nýbrž produkujete, návod pro streamovaný zápis pro dávkové serverové úlohy uplatňuje stejnou disciplínu konstantní paměti pro zápis. Všechny tyto tři nástroje tvoří součást komponenty HotXLS pro Delphi a C++Builder spolu s rozhraními API pro čtení, zápis, výpočty a formátování zmíněnými jinde na tomto blogu