Úloha generující report běží celý rok naprosto bez problémů. Sestaví sešit, naplní list vším, co vrátí databázový dotaz, a uloží jej. Pak si ale zákazník s pětiletou historií vyžádá kompletní export, počet řádků překročí hranici milionu a proces skončí s chybou nedostatku paměti (out-of-memory) dlouho předtím, než se soubor vůbec dostane na disk. S vaším kódem nebylo nic v nepořádku. Pouze držel celý sešit v paměti RAM, aby jej na konci mohl serializovat, a využití paměti se tak zvyšovalo ruku v ruce s počtem řádků k zápisu
Nápravou není výkonnější server, ale odlišný model zápisu. Komponenta streamovacího přímého zápisu (streaming direct writer) v HotXLS vytváří balíček OOXML postupně s tím, jak řádky přicházejí, takže paměť, kterou spotřebovává, nezávisí na počtu zapsaných řádků. Je to na straně zápisu protějšek ke streamovací čtečce: zatímco čtečka prochází obrovský list, aniž by vytvářela strom buněk, nástroj pro zápis obrovský list vytvoří, aniž by takový strom budoval
Proč při standardním uložení paměť roste s daty
Standardní způsob práce s TXLSXWorkbook spočívá v tom, že se nejprve vytvoří plnohodnotný objektový model. Každá buňka se svou hodnotou, typem a odkazem na styl žije v paměti jako samostatný objekt, dokud nezavoláte uložení. V tomto bodě se pak celý strom serializuje do finálního balíčku. Tento model je správný ve chvíli, kdy chcete list přečíst, upravit, přepočítat a zapsat zpět, protože náhodný přístup ke kterékoli buňce je přesně tím, co tyto úpravy vyžadují. Je to ovšem špatný přístup ve chvíli, kdy plníte řádky jen v jednom směru a nikdy se k nim nevracíte, protože pak platíte za udržování každého řádku v paměti zcela bez užitku. Milion řádků objektů zůstává milionem řádků objektů, ať už s nimi dál pracujete, nebo ne
The streaming writer removes the tree. As soon as a cell is written it becomes bytes in the worksheet part, and those bytes are handed to the zip output. The worksheet stream is the only buffer that grows, and it grows on the output side, not as live Delphi objects on the heap. What stays resident is a fixed amount of bookkeeping: the sheet names, a few flags, the current row number, a cell counter. That set does not change between row one and row ten million
Tabulka sdílených řetězců je past a vložené řetězce jsou cestou ven
Většina nástrojů pro streamovaný zápis formátu XLSX funguje velmi dobře do chvíle, než narazí na text. Formát OOXML obvykle ukládá text do tabulky sdílených řetězců: každý jedinečný řetězec je zapsán právě jednou do oddělené části a každá buňka, která daný řetězec obsahuje, nese místo textu samotného pouze index do této tabulky. Jde o vynikající optimalizaci prostoru u souborů naplněných opakujícími se štítky a je to výchozí nastavení pro standardní metodu ukládání. Pro streamovací zápis se tu ale otevírá brutální problém. Kvůli deduplikaci musí celá tabulka zůstat v paměti od začátku až do konce, protože jakýkoli budoucí řádek může obsahovat řetězec z řádku, který již byl zapsán. Ke správnému přidělení indexu je zkrátka nezbytné udržovat celou mapu zjištěných řetězců neustále v operační paměti. Jediná struktura, kterou proto streamovací nástroj zkrátka nemůže streamovat, je paradoxně ta, která má zajistit malou velikost souboru. Data se spoustou textu tak zničí myšlenku streamování, kvůli kterému jste sem přišli
Přímý nástroj pro zápis (direct writer) se této tabulce zcela vyhýbá. Řetězce se zapisují přímo, tedy jako vložené buňky t="inlineStr", jejichž text se nachází přímo uvnitř elementu <is><t> v buňce. Neexistuje tu žádná tabulka, která by se plnila, ani žádná mapa spatřených řetězců, kterou by bylo třeba udržovat v paměti. Textové sloupce tak nestojí o nic více paměti než sloupce číselné. Kompromis je naprosto jasný a musíme si to říci otevřeně. Vložené řetězce zkrátka zopakují tentýž text tam, kde se objeví, takže soubor s mnoha identickými štítky bude na disku větší než srovnatelný soubor se sdílenými řetězci. Obětujete velikost souboru pro zajištění konstantní paměti. Pro jednoprůchodový export to bývá výhodný obchod a komprese zip sama o sobě velkou část opakování textu při vytváření souboru zredukuje
Tabulka stylů přichází až nakonec společně s jedním formátem data
Styly vyvolávají stejné napětí jako řetězce. Sešit odkazuje na své formátování skrz sekci stylů a streamovací zápis nedokáže udržet paletu stylů synchronizovanou s buňkami, které již byly zpracovány a vypuštěny. Nástroj pro přímý zápis to řeší tak, že udržuje tabulku stylů malou a pevnou a vypisuje ji až na úplném konci místo její deklarace na začátku souboru. Jeden výchozí formát pak pokryje obyčejné buňky. Jeden číselný formát data postačí pro všechna data, je zaregistrován pod kódem formátu yyyy-mm-dd a má známou pozici na seznamu formátů buněk
Právě tento formát data je důvodem, proč existuje samostatné volání WriteDateTime. Excel totiž žádný nativní typ pro datum nezná. Datum je pro něj pouze číslo zabalené do formátu data. Z tohoto důvodu zapíše WriteDateTime hodnotu jako obyčejné sériové číslo a buňku pouze označí vybraným formátem data, aby ji tabulkový procesor dokázal vykreslit jako datum a ne jako pěticiferné celé číslo. Sériové číslo, které metoda zapisuje, pak hraje roli v obousměrné kompatibilitě. Zapisuje totiž hodnotu TDateTime přímo v systému data roku 1900, tedy pod stejnou konvencí, jakou využívá pro uložení klasický postup metodou TXLSXWorkbook. Protože se obě metody na zápisu data shodují, soubor, který vznikne pomocí streamovacího nástroje, lze přečíst přes čtečku z balíku komponent HotXLS a při otevření v Excelu budou data přesně taková, jaká jste zamýšleli, a nedočkáte se u nich nečekaného posunu o jeden den nebo odlišné výchozí epochy
Na pořadí záleží, protože odeslané bajty už nevrátíte
K využití vynikajícího paměťového profilu při streamování musíte dodržet jediné pravidlo. Výstup se postupně emituje a nelze se k němu vrátit, proto se musí zapisovat přesně v tom pořadí, v jakém je prezentován v souboru. Uvnitř řádku se postupuje s buňkami vzestupně po sloupcích. Uvnitř listu jdou po sobě vzestupně řádky. Nenajdete zde žádnou vyrovnávací paměť (buffer), v níž by si nástroj seřadil buňky, které jste mu předali na přeskáčku, protože vámi uzavřený řádek se v mžiku přeměnil na odeslané bajty v datovém toku formátu zip a nelze ho dál nijak ovlivnit. Zadejte mu sloupec 5 a po něm sloupec 2 ve stejném řádku a celá struktura souboru se zbortí, protože nástroj slepě odhazuje vstupy přesně v takové sekvenci, v jaké je od vás obdrží
Rozhraní API pro řádky obsahuje jedno drobné vylepšení pro usnadnění běžných scénářů. Funkce AddRow přebírá index řádku s počátkem na pozici 1. Pokud jí předáte 0, pochopí to jako povel k výběru bezprostředně následujícího řádku a při sekvenčním zápisu se už nikdy nemusíte starat o aktualizaci postupně se zvyšujícího inkrementálního počítadla. Každé spuštění metody AddRow se postará o ukončení předcházejícího řádku a volání metody AddSheet zase uzavře předešlý list, čímž vás zbavuje povinnosti explicitně ukončovat řádky nebo listy. Nástroj za vás stávající strukturu v klidu finalizuje ve chvíli, kdy přistoupíte ke spuštění té následující
O escapování se stará místo, kde do XML vstupuje text
Veškerý vámi zapsaný text se dříve či později přemění na součást dokumentu formátu XML, proto je nutné vždy patřičně zabezpečit oněch pět předdefinovaných XML entit formou escapování. V opačném případě bude stačit přítomnost jediného znaku ampersand nebo jedné úhlové závorky ve vaší hodnotě ke kompletnímu zneplatnění balíčku. Nástroj tento překlad v podobě znaků &, <, >, " a ' vyřeší za vás, a to v prostoru vloženého textu a v prostoru pro zadávání vzorců, tedy ve dvou oblastech, v nichž volající umisťují vlastní znaky mezi strukturu značek. Zadáte mu surovou proměnnou WideString a zbytek obstará sám. Název vašeho firemního produktu, jakým může být Smith & Co <Ltd>, nebo běžný vzorec propočítávající vybrané buňky projdou na výstup jako formálně bezchybný XML kód bez nutnosti manuálních úprav na straně uživatele
Životní cyklus a proč metoda Destroy stále vše uzavírá
Právě fáze kompletování balíčku je obdobím, kdy probíhá zápis sešitu jako celku, zápis stylů, všech typů obsahu, souvisejících prvků vztahů a konečně zápis hlavního adresáře ve formátu zip. O tohle vše se pochopitelně stará volání Close. Pokud se stane, že tento proces neskončí korektním zavřením balíčku zip, odnesete si poloviční paskvil, který následně nerozlouskne žádný tabulkový procesor. Metoda pro uzavření tak není pouhou zálohou volitelného závěrečného pročištění, ale skutečným a klíčovým bodem zajišťujícím spolehlivou validaci vašeho souboru. Aby systém v podobných krizových momentech chránil balíčky zip před neúplností, implementuje do metody Destroy pro jistotu ještě jedno dodatečné provedení závěrečného zavření. Uživatelé se tím pádem zbaví obav z prosáknutí skrytých vad otevřeného archivu zip ve chvíli výskytu mimořádných chyb obcházejících standardní strukturu metod. Nejvyšším stupněm záruky přesto zůstává používání starého dobrého přístupu známého z Delphi: pište dovnitř bloku try, zavolejte si metodu Close a proces nezapomeňte uvolnit v klauzuli finally
Kompletní streamování rozsáhlého listu
Architektura pracovního úkolu vypadá následovně: odstartujte, vložte si pracovní list, nasypte do něj vaše řádky a nezapomeňte vše správně uzavřít. První příklad dole vykreslí řádek s hlavičkou, po kterém zařadí soustavný průběh řádků s psanými daty s libovolně promíchanými řetězci, sadami čísel, jediným datem a vzorcem postrádajícím uložený výsledek v mezipaměti. Operační paměť bude pokaždé čerpat naprosto stejné hodnoty, ať už jí zkompilujete tucet, nebo třeba rovnou dvanáct milionů řádků, jelikož všechny zpracované buňky poputují po svém zapsání do datového proudu v balíčku formátu zip
uses
lxDirectWrite;
procedure StreamReport(const Path: string; RowCount: Integer);
var
W: TXLSDirectWriter;
I: Integer;
begin
W := TXLSDirectWriter.Create;
try
W.BeginFile(Path);
W.AddSheet('Sales');
// Header row, written in ascending column order
W.AddRow(1);
W.WriteString(1, 'Item');
W.WriteString(2, 'Qty');
W.WriteString(3, 'Price');
W.WriteString(4, 'Total');
W.WriteString(5, 'Date');
// Data rows; pass 0 to AddRow to take the next row automatically
for I := 1 to RowCount do
begin
W.AddRow(0);
W.WriteString(1, 'Item ' + IntToStr(I));
W.WriteNumber(2, I);
W.WriteNumber(3, 1.5 + (I mod 10));
W.WriteFormula(4, Format('B%d*C%d', [I + 1, I + 1]));
W.WriteDateTime(5, EncodeDate(2026, 1, 1) + I);
end;
W.Close; // finalises the package
finally
W.Free;
end;
end;Druhý pracovní list pro vás představuje pouhé přidání další metody AddSheet bezprostředně před pokračováním. Nástroj si tak sám elegantně ukončí ten předchozí dříve, než stačí zahájit obsluhu toho nového. Logické příznaky typu boolean sázejí na přímé zadávání skrze funkci WriteBoolean starající se o precizní zápis konkrétní datové buňky logického typu mnohem lépe než fádní vypisování prostého řetězce o podobě s textem „True“. V případě, že vyžadujete ověření integrity hotového souboru po jeho úspěšném zpracování oběma procesy, poslouží vám k tomu skvěle hodnota funkce CellCount deklarující konečné množství doopravdy zapsaných políček, s čímž se v kontextu kontroly při dalším čtení výsledku s využitím přímého načítacího nástroje nabízí jednoznačná očekávaná komparace počtů s identickými celkovými objemy buňek
// A second sheet of typed flags after the data sheet above
W.AddSheet('Flags');
W.AddRow(1);
W.WriteString(1, 'Name');
W.WriteString(2, 'Active');
W.AddRow(0);
W.WriteString(1, 'alpha');
W.WriteBoolean(2, True);
WriteLn(Format('wrote %d cells', [W.CellCount]));Zápis do streamu namísto do souboru vyžaduje úplně totožný kus kódu s jediným drobným rozdílem spočívajícím v prohození obslužné rutiny BeginFile za variantu BeginStream, takže dovolíte vašemu serveru komfortní odesílání sešitů v podobě HTTP odpovědi nebo plnohodnotného paměťového streamu bez rizika odkládání jakýchkoliv prozatímních balastních souborů na pevném disku. Tento přímý zapisovací objekt pochopitelně nijak neovládá datové zdroje, které mu tímto zprostředkujete k odeslání, čímž si můžete být naprosto jisti svou jednoznačnou kontrolou zachovávanou z vaší strany po celou dobu jeho životnosti
V případě budování specifického serverového rozhraní navrženého pro tvorbu excelových sešitů výlučně na vyžádání jednotlivých klientů určitě neminete cenné příklady odhalující nejlepší postupy obsažené v článku věnovaném problematice streamování na serverech v kombinaci s řízením úloh s dávkovým zpracováním. Ty názorně ukazují dokonalé propojení takových modulů na stranu nástrojů s cílem plynulé integrace do správce klientských dotazů i rutinně naplánovaných jednorázových reportů s fixním termínem generování. Máte-li další dotazy vztahující se čistě k výpočtům hardwarových nákladů u masivních souborů na vstupu a výstupu celého systému, narazíte na jejich srozumitelnou interpretaci přímo ve vymezeném bloku příručky studující detailní výkonnost ohromných sešitů vyvolaných programem v Delphi, na jejímž počátku pochopíte zákonitosti toku propáleného času v synergii se žroutem RAM komponent ve spuštěných úkolech. Přímý modul se streamovacím zápisem je od začátku pevnou součástí balíku komponent HotXLS cíleného na moderní platformy Delphi i C++Builder, kam spadá stejným dílem jako zbytek výbavy z bohatého rozhraní API určeného pro plnohodnotné skenování, editování či závěrečnou katalogizaci dat, na které narážíme ve všech částech na tomto firemním blogu