Streamovaný zápis HotXLS pre dávkové úlohy v Delphi

Predstavte si nočnú službu v Delphi, ktorá generuje jeden súbor XLSX na zákazníka, teda niekoľko stoviek súborov, pričom niektoré majú 400 000 riadkov. Keď ju zmeriate profilerom, prekvapením je len zriedka cyklus plniaci bunky. Je ním volanie SaveAs. Pri predvolenom zapisovači sa každý hárok serializuje do jediného reťazca XML v pamäti ešte pred tým, než sa tento reťazec skomprimuje do zip archívu OOXML, a pri širokom hárku môže dočasný reťazec mnohonásobne prevýšiť model buniek, z ktorého vznikol. Úloha, ktorá pohodlne zostaví svoje dáta a drží sa na 800 MB, tak počas ukladania prekročí limit kontajnera 2 GB a OOM killer podá hlásenie o chybe o tretej ráno, keď sa nikto nepozerá. HotXLS, natívna tabuľková knižnica od losLab pre Delphi a C++Builder, má vlastnosť mierenú presne na túto špičku: StreamingWrite. Okolo nej stoja ďalšie dve páky, ktoré rozhodujú o tom, či dávkový pracovník zostane vo svojom pamäťovom a časovom rozpočte, konkrétne spätné volania na úrovni riadkov a to, ako sa fond štýlov správa vnútri tesného cyklu

Čo predvolená cesta ukladania bufferuje a čo mení StreamingWrite

Predvolený zapisovač XLSX uprednostňuje jednoduchosť. Vykreslí XML hárka celé naraz a hotový reťazec potom odovzdá kompresoru zip. Pri drvivej väčšine zošitov, kde sa XML celého hárka zmestí do niekoľkých megabajtov, je to správny kompromis. Prestáva ním byť vo chvíli, keď serializovaná podoba jedného hárka narastie na stovky megabajtov. Tabuľkové XML je mnohovravné: každá číselná bunka stojí desiatky znakov značiek a reťazec, ktorý ich všetky drží, musí byť súvislý. Na grafe pamäte sa tento vzorec nedá prehliadnuť. Dlhá plochá plošina, kým sa plnia riadky, potom ostrá trojuholníková špička počas volania SaveAs a napokon prepad, len čo sa zip vyprázdni

Nastavenie Book.StreamingWrite := True prepne metódu SaveAs na zapisovač hárkov, ktorý XML hárka posiela priamo do prúdu zip tak, ako vzniká. Medziľahlý reťazec sa nikdy nealokuje a trojuholníková špička sa vyhladí do úrovne šumu

Buďte presní v tom, čo tým skutočne získate, pretože prehnané očakávania vedú k chybným kapacitným plánom. Príznak mení iba cestu ukladania. Zostavenie zošita naďalej alokuje kompletný model buniek v pamäti, takže plošina počas fázy plnenia je presne taká vysoká ako predtým. Zmizne len serializačná špička, ktorá sa nad túto plošinu pri ukladaní nabaľovala, a pri úlohe plniacej 400 tisíc riadkov býva práve táto špička celým rozdielom medzi dodržaním pamäťového rozpočtu a jeho prekročením. Vlastnosť je predvolene False, aby sa zachovalo historické správanie, takže jej zapnutie je jediný explicitný riadok, ktorý napíšete zámerne

Hromadný export so zapnutým príznakom

Book := TXLSXWorkbook.Create;
try
  BoldIdx := Book.Fonts.Add('Calibri', 11, True, False); // index fondu, od nuly
  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;
    if (R mod 1000) = 0 then
      Sheet.Cells[R, 2].FontIndex := BoldIdx + 1;        // v bunke od jednotky
  end;
  Book.StreamingWrite := True;   // XML hárka ide priamo do prúdu zip
  Book.SaveAs('bulk.xlsx');
finally
  Book.Free;
end;

Cells[R, C] vytvára bunky na požiadanie, čím zostáva telo cyklu prehľadné. Dva stropy mriežky si oplatí zapamätať: 1 048 576 riadkov a 16 384 stĺpcov, sprístupnené ako XlsxMaxRow a XlsxMaxCol. Dátový zdroj, ktorý prekročí strop riadkov, musíte vo vlastnom kóde rozdeliť medzi viac hárkov. Nič ďalej v reťazci si prekročenie nevšimne ani ho za vás neopraví a súbor jednoducho skončí orezaný na limite

Plnenie riadkov bez réžie typu Variant na každú bunku

Každé priradenie Cells[R, C].Value platí za vyhľadanie bunky a konverziu na Variant. Pri desiatich tisícoch riadkov si to nikto nevšimne. Pri milióne riadkov po dvadsať stĺpcov sa táto réžia na volanie stáva dominantným nákladom fázy plnenia a profiler na ňu ukáže priamo. Dávkové rozhrania vám umožnia odovzdať zapisovaču celý riadok naraz. Metóda WriteRows riadi spätné volanie, ktoré pri každom vyvolaní dodá jeden riadok:

procedure TBulkExporter.FillRow(Sender: TObject; SheetIndex, Row, FirstCol,
  LastCol: Integer; var Values: Variant; var Skip: Boolean;
  var Cancel: Boolean);
begin
  if not FReader.Next then
  begin
    Cancel := True;              // zdroj dát je vyčerpaný: čisto zastaviť
    Exit;
  end;
  Values := VarArrayCreate([FirstCol, LastCol], varVariant);
  Values[FirstCol]     := FReader.RecordId;
  Values[FirstCol + 1] := FReader.CustomerName;
  Values[FirstCol + 2] := FReader.Amount;
end;

// naplní riadky 2..100001, stĺpce A..C dátami z čítačky
Sheet.WriteRows(2, 1, 100001, 3, FillRow);

Príznak Cancel je to, čo mení pevný rozsah riadkov na „najviac N riadkov“, čo je prirodzený tvar vtedy, keď počet riadkov pochádza z dotazu, ktorý ste ešte nedokončili. Skip je jemnejší zásah: nechá jednotlivý riadok prázdny bez toho, aby zastavil beh. Okrem plnenia buniek sa spätné volanie ukazuje ako dobré miesto pre prevádzkové starosti, ktoré sa na plniaci cyklus inak nabaľujú nepohodlne. Počítadlo priebehu tikajúce každých tisíc riadkov, token zrušenia dopytovaný z plánovača úloh, obmedzovač rýchlosti čítania zo zdrojovej databázy: všetko žije na jednom mieste namiesto toho, aby sa preplietalo kódom zapisujúcim bunky. Na strane čítania zrkadlia rovnaký vzorec metódy ForEachRow a ForEachCell, čo je dôležité vtedy, keď dávková úloha veľké súbory zároveň konzumuje aj produkuje

Fondy štýlov odmeňujú vytiahnutie mimo cyklu

Model štýlovania v XLSX je sada zdieľaných fondov. Metódy Fonts.Add, Fills.AddSolid a Borders.Add vracajú index vo fonde od nuly a bunka sa na písmo odkazuje tak, že si tento index zvýšený o jedna uloží do vlastnosti FontIndex, kde nula je vyhradená pre predvolené nastavenie zošita. To +1 je priamo v hromadnom príklade vyššie. Ak naň zabudnete, bunka si potichu prevezme nesprávny štýl, pretože index fondu posunutý o jedna je stále platným indexom a nič nevyvolá výnimku

Disciplína, ktorá z toho vyplýva, znie: vytvorte každý objekt štýlu pred cyklom cez riadky a vnútri cyklu sa odkazujte na jeho index. Metóda Fonts.Add identické definície deduplikuje, takže jej volanie raz na riadok plytvá iba časom procesora. Pascou je Alignments.Add, lebo pri každom volaní vracia novú položku. Vnútri cyklu so 100 tisíc riadkami to pochová súbor styles.xml pod stotisíc duplicitných záznamov o zarovnaní, čo nafúkne súbor na disku a spomalí každé neskoršie otvorenie v Exceli, keď sa duplikáty znovu analyzujú. Postavte každý štýl raz mimo cyklu a potom sa na jeho index odkazujte, koľkokrát potrebujete

Prúdy, dočasné adresáre a dávkový cyklus okolo toho všetkého

Nič z toho nevyžaduje súborový systém. Obe fasády nesú naprieč svojím IO povrchom preťaženia pre TStream, medzi nimi Open, SaveAs, SaveAsCSV, SaveAsHTML aj SaveAsODS, takže dávkový pracovník môže vykresľovať priamo do objektu TMemoryStream mieriaceho do blob úložiska alebo do odpovede HTTP bez toho, aby sa vôbec dotkol disku. Jedna ostrá hrana stojí za zapamätanie. Volanie SaveAs(Stream) zapisuje od aktuálnej pozície prúdu a po sebe ju nevracia späť, takže Position := 0 nastavte sami skôr, než prúd odovzdáte tomu, kto ho doručí, inak konzument prečíta nula bajtov. Fasáda XLS pridáva dva vlastné gombíky. Metóda SetTempDir nasmeruje dočasné súbory zapisovača BIFF na zväzok, ktorý má na ich pohltenie miesto aj rezervu v IO, čo je dôležité na serveroch, kde predvolená dočasná cesta leží na stiesnenom systémovom disku. Vlastnosť UseSharedFormulas zbalí opakujúce sa telá vzorcov do zdieľaných skupín, čo je reálne zmenšenie pri klasickom tvare zostavy, kde je jeden vzorec skopírovaný cez celý stĺpec

Samotný dávkový cyklus zostáva zámerne nudný:

for FileName in SourceFiles do
begin
  Book := TXLSXWorkbook.Create;        // nová inštancia: žiadny presakujúci stav
  try
    Book.StreamingWrite := True;
    if Book.Open(FileName) <> 1 then
      Continue;                        // jeden zlý vstup nesmie zabiť celú dávku
    Book.SaveAsCSV(ChangeFileExt(FileName, '.csv'), 0, ',');
  finally
    Book.Free;
  end;
end;

Nová inštancia zošita na každý súbor stojí mikrosekundy a odstraňuje celú kategóriu chýb s krížovou kontamináciou medzi súbormi: štýly, definované názvy a vlastnosti dokumentu zo súboru 17 nemajú ako presiaknuť do súboru 18. Preskočenie a pokračovanie po neúspešnom Open si svoje miesto zaslúži rovnako, pretože jedno useknuté nahranie v dávke 600 súborov by vás malo stáť jediný riadok v logu, a nie zvyšok behu. Za zmienku stojí aj to, čo vetva CSV zámerne nerobí. Metóda SaveAsCSV zapisuje vzorce ako doslovný text a nikdy ich nevyhodnocuje, takže konverzná dávka, ktorej konzumenti očakávajú vypočítané čísla, musí najprv na príslušných bunkách spustiť Calculate, alebo vychádzať zo zošitov, ktoré už nesú uložené výsledky z predchádzajúceho výpočtu

Model súbežnosti: jeden zošit na vlákno

Objekty ani jednej fasády nie sú bezpečné pre vlákna a návrh nikdy netvrdil opak. Keďže medzi inštanciami neexistuje žiadny zdieľaný globálny stav, pravidlo škálovania znie jednoducho: jeden zošit na pracovné vlákno a žiadne zdieľanie zošita medzi vláknami. Fond N pracovníkov, z ktorých každý vlastní svoju inštanciu TXLSXWorkbook, škáluje takmer lineárne, kým sa stropom nestane pamäť, a tento strop viete vyčísliť: najväčší súbežný model buniek vynásobený počtom pracovníkov plus tá réžia pri ukladaní, ktorú StreamingWrite vyhladila. Keď je fronta hlboká, aplikujte protitlak na fronte úloh, nie vnútri zapisovača. Vyhladované vlákno, ktoré má zošit napísaný spolovice, nevyprodukovalo nič užitočné, kým úloha, ktorá počkala pár sekúnd na voľného pracovníka, sa dokončí neporušená

Pre širší obraz ladenia vrátane zdieľaných vzorcov, preskakovania grafiky na strane čítania a pák špecifických pre XLS si pozrite sprievodcu výkonom veľkých zošitov. Dávkovým úlohám, ktorých riadky prichádzajú priamo z dotazu, sa venujú samostatne vzory exportu z databázy pre zostavy v Delphi

HotXLS sa do vašej služby v Delphi alebo C++Builder kompiluje ako natívny Object Pascal bez externých závislostí; edície a licencovanie nájdete na produktovej stránke HotXLS Delphi Component