Predstavte si nočnú dávkovú úlohu, ktorá v kóde vygeneruje zošit s faktúrami a zapíše ho ako CSV pre nadväzujúci systém. Čísla vyzerajú v Exceli správne. CSV sa v textovom editore otvorí bez problémov. Potom sa však importér zasekne na stĺpci s celkovými súčtami, pretože pole sumy pre riadok 42 obsahuje hodnotu =SUM(D2:D41), čiže text vzorca, a nie číslo, ktoré by sa malo vypočítať. Nič nie je pokazené. Ide o zdokumentované správanie a je to prvá vec, ktorú treba pochopiť o exportovaní z HotXLS: zapisovač serializuje model bunky presne tak, ako existuje, a bunka so vzorcom, ktorej hodnota nebola nikdy vypočítaná, má k dispozícii iba text vzorca
Prečo vaše CSV obsahuje vzorce namiesto čísel
HotXLS ukladá text vzorca a vypočítanú hodnotu ako dve samostatné veci. Metóda SaveAsCSV zámerne nespúšťa výpočtový engine pri exporte: export by nemal meniť zošit a nemal by riskovať zaseknutie na zložitej reťazi vzorcov. Súbory, ktoré uložil samotný Excel, nesú vedľa vzorcov nacacheované výsledky, takže ich opätovný export sa správa podľa očakávania. Pasca sa týka konkrétne zošitov vygenerovaných vaším vlastným kódom, kde boli vzorce zapísané, ale nikdy sa nevyhodnotili. Riešením je zabezpečiť existenciu hodnôt pred exportom pomocou rovnakého enginu Calculate, ktorý rieši odkazy medzi hárkami a vlastné funkcie:
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
R: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('invoice-run.xlsx');
Sheet := Book.Sheets[0];
// Materialize formula results so the CSV carries numbers, not '=...' text
for R := 2 to 41 do
if Sheet.Cells[R, 4].Formula <> '' then
Sheet.Cells[R, 4].Value := Book.Calculate(Sheet.Cells[R, 4].Formula);
Book.SaveAsCSV('feed.csv', 0, ','); // sheet 0, comma
Book.SaveAsCSV('feed.tsv', 0, #9); // same sheet as TSV
finally
Book.Free;
end;
end;Sledujte, čo tento cyklus v skutočnosti robí: prepisuje bunky so vzorcami ich vypočítanými hodnotami. To je úplne v poriadku pre jednorazový export, ale nesprávne, ak chcete zošit neskôr znova uložiť ako .xlsx, pretože ste práve nahradili živé vzorce statickými číslami. Exportujte z kópie zošita alebo obmedzte zápis tak, aby sa dotkol iba exportnej fázy. Engine na pozadí metódy Calculate dokáže viac, vrátane registrácie vašich vlastných funkcií, čo je témou článku o engine vzorcov HotXLS a vlastných funkciách
Čo garantuje zapisovač s oddelenými hodnotami
Export do CSV produkuje kódovanie UTF-8 so značkou poradia bajtov BOM, koncami riadkov CRLF a úvodzovkami podľa štandardu RFC 4180. Akékoľvek pole obsahujúce oddeľovač, úvodzovku alebo zlom riadka je zabelené do úvodzoviek a vnútorné úvodzovky sú zdvojené. Dátumy sa zobrazujú vo formáte yyyy-mm-dd hh:nn:ss bez ohľadu na formát zobrazenia bunky. To je správne rozhodnutie pre strojové spracovanie, hoci to môže prekvapiť každého, kto očakával prenos formátovania z obrazovky. Bunky s formátovaným textom (rich text) sa sploštia spojením ich jednotlivých častí
Tieto predvolené hodnoty vyriešia väčšinu sporov s importérom skôr, než vôbec začnú, no dve z nich by predsa mali byť súčasťou vašich integračných pravidiel. Prvou je značka BOM. Práve tá umožňuje Excelu otvoriť súbor so zachovaním diakritiky, no niektoré prísne parsery považujú tieto tri bajty za dáta; ak je to váš prípad, pred odovzdaním ich odstráňte. Druhou je formát TSV. Nejde o samostatnú funkciu, ale o ten istý zapisovač volaný s oddeľovačom #9 (tabulátor), takže všetko vyššie uvedené platí bez zmien aj preň. Hárok na export sa vyberá pomocou indexu začínajúceho od 0 v preťaženej metóde s viacerými argumentmi, zatiaľ čo skrátené volanie SaveAsCSV(FileName) s jedným argumentom exportuje aktívny hárok
HTML export je snímka, nie výmenný formát
Zatiaľ čo CSV zahadzuje všetko okrem hodnôt, metóda SaveAsHTML sa snaží zachovať vzhľad: jedna tabuľka <table> na každý hárok, zlúčené oblasti vyjadrené pomocou colspan a rowspan a základné štýlovanie inlinované ako CSS. Farby závislé od motívu (themes) sa skôr preskakujú, než prepočítavajú, takže šablóna využívajúca farebné motívy vyzerá po exporte jednoduchšie ako v Exceli. Na všetko, čo musí prežiť export, nastavte explicitné RGB farby. Objekt volieb riadi výsledný formát:
var
Opts: TXLSXHtmlExportOptions;
begin
Opts := TXLSXHtmlExportOptions.Create;
try
Opts.Title := 'Weekly settlement';
Opts.TableClass := 'report-grid'; // hook for the host page stylesheet
Opts.WriteDocument := True; // full page, not a fragment
if Book.SaveAsHTML('settlement.html', 0, Opts) <> 0 then
raise Exception.Create('Sheet index out of range');
finally
Opts.Free;
end;
end;Dva detaily v tomto fragmente kódu si zaslúžia pozornosť. Nastavte vlastnosť WriteDocument na False a výstupom bude iba čistý fragment tabuľky namiesto celej stránky. To je presne to, čo chcete pri vkladaní náhľadu do existujúceho rozvrhu stránky: nastavte vlastnosť TableClass a nechajte štýlovanie na CSS šablónu hostiteľskej stránky. Návratová hodnota je tiež opačná ako pri väčšine volaní v HotXLS. SaveAsHTML vracia 0 pri úspechu a -1 pri nesprávnom indexe hárka, takže kontrola na hodnotu 1 zo zvyku nahlási každý úspešný export ako zlyhanie. Keď musíte exportovať iba oblasť namiesto celého hárka (napríklad na odoslanie e-mailom alebo vloženie bloku), metóda TXLSXRange.SaveAsHTML exportuje akýkoľvek obdĺžnikový rozsah podľa rovnakých pravidiel vykresľovania
RTF výstup a kde si stále nachádza svoje miesto
Štvrtý cieľ zapisuje tabuľky formátu RTF 1.6, jeden hárok na jedno volanie cez SaveAsRTF. Šírka stĺpcov sa odhaduje približne na 96 twipov na jeden znak šírky stĺpca. Štrukturálnym obmedzením, o ktorom musíte vedieť, je, že zlúčené bunky sa vo výstupe nerozširujú: iba hlavná (ukotvená) bunka nesie svoj obsah a zakryté bunky sa exportujú ako prázdne. To vylučuje použitie RTF pre šablóny s náročným rozvrhnutím. Stále si však nachádza svoje miesto ako cesta najmenšieho odporu pre vkladanie tabuľkových výsledkov do textových procesorov alebo do starších systémov na správu dokumentov, ktoré nepodporujú HTML
Cyklus prevodu: import CSV je zámerne deštruktívny
Čítanie CSV spätne má svoje vlastné pravidlá. Metóda OpenCSV vymaže celý zošit a znova ho zostaví ako jeden hárok s názvom Sheet1. Ide v podstate o konštruktor, nie o zlúčenie, preto ju nikdy nevolajte na zošite, ktorý stále obsahuje neuložený obsah. Odovzdanie hodnoty #0 ako oddeľovača spustí automatickú detekciu oddeľovača. Príznak ADetectTypes riadi konverziu typov: pri jeho zapnutí sa číselné reťazce zmenia na čísla, reťazce ISO-8601 na dátumy a hodnoty true/false na boolean. Vypnite ho, keď importované dáta obsahujú identifikátory s poprednými nulami, PSČ alebo kódy produktov, pretože automatická konverzia by ich potichu zmenila na čísla (popredná nula zmizne v momente, keď sa z 00123 stane 123). Obe rozhrania ponúkajú rovnaký import. Spojte ho s vyššie uvedenými volaniami exportu a získate premostenie formátov, ktoré nevyžaduje inštaláciu Excelu na serveri, čo je scenár popísaný v článku o generovaní správ z databázy do Excelu s HotXLS
Exportovanie priamo do streamu
Každý tu spomenutý zapisovač má popri verzii so súbormi k dispozícii aj preťaženie so streamom: pre CSV, HTML, RTF aj samotné formáty zošitov. V serverovom kóde by ste mali siahnuť práve po týchto preťaženiach. Webové rozhranie, ktoré poskytuje sťahovanie CSV, môže zapisovať do TMemoryStream a odovzdať ho priamo objektu odpovede (response). Nevyžaduje sa žiadny dočasný súbor, žiadne čistenie pamäte a nedochádza k žiadnemu konfliktu medzi požiadavkami, ktoré by si náhodne vybrali rovnaký názov súboru. To isté platí pre ukladanie exportov do cloudových úložísk (blob storage) alebo ich pripájanie k odchádzajúcim e-mailom. Súborový systém tak úplne vypadáva z hry
Tento vzor sa navyše spája so spôsobom nasadenia knižnice. Obe rozhrania sú natívnymi čítačkami a zapisovačmi v Object Pascale, takže netreba inštalovať Excel, nepoužíva sa COM automatizácia a na serveri nevzniká úzke hrdlo pri serializácii požiadaviek na úrovni procesov. Každá požiadavka môže vlastniť svoj objekt zošita, spustiť výpočet hodnôt podľa prvej sekcie a streamovať svoj export paralelne s ostatnými. Jediným zdrojom, na ktorý si treba dávať pozor, je pamäť. Model zošita zostáva v pamäti RAM počas celého exportu, takže služba, ktorá otvára veľmi veľké súbory len preto, aby ich exportovala do CSV, by mala obmedziť počet súbežných úloh alebo radiť nadmerné požiadavky do frontu, namiesto toho, aby o využití pamäte rozhodovala nárazová prevádzka
Menší detail: nastavte vlastnosť IncludeBOM vo voľbách HTML exportu, ak sa fragment bude ukladať ako samostatný súbor, pri ktorom nejaký nadväzujúci nástroj zisťuje kódovanie. Keď poskytujete HTML priamo cez HTTP, nechajte deklaráciu znakovej sady radšej na hlavičky odpovede
Keď bajty stále vychádzajú nesprávne
Najčastejšia otázka podpory ohľadom exportu do CSV je v podstate ten istý problém s otváraním v inom šate: Excel zobrazuje skomolené znaky (mojibake) namiesto diakritiky. Inštinkt velí viniť zapisovač, ale ten vygeneruje UTF-8 BOM presne z tohto dôvodu a súbor je pri opustení vášho kódu takmer vždy správny. BOM však mohol zmiznúť niekde na ceste do Excelu. Prenos cez FTP v textovom režime, kopírovanie streamu, ktoré preskočí prvé tri bajty, alebo proxy server, ktorý prekoduje dáta počas prenosu: čokoľvek z toho môže odstrániť značku a nechať Excel hádať kódovanie, čo robí veľmi zle. Diagnostikujte to na rozhraní, nie priamo vo volaní exportu. Otvorte doručený súbor v hexadecimálnom prehliadači a overte si, či sú bajty EF BB BF stále na samom začiatku
To je spoločná črta pre všetky štyri formáty. Samotné volanie exportu je jednoduchá časť a HotXLS robí rozumné rozhodnutia pri každom kroku zápisu. Zlyhania nastávajú na prechodoch: kde sa text vzorca stretáva s parserom očakávajúcim číslo, kde sa značka BOM stretáva s prenosom, ktorý ju nezachová, a kde sa zlúčená bunka stretáva s plochým modelom tabuľky v RTF. Každá z týchto skutočností by mala byť zakotvená v integračných pravidlách medzi vaším exportérom a systémom, ktorý dáta spracováva, pretože prijímač nedokáže vyčítať vaše zámery priamo z bajtov. Pre kompletný zoznam metód pre obe rozhrania zošitov nájdete referenciu na produktovej stránke HotXLS Component