Export sešitů Excelu do CSV, TSV, HTML a RTF z Delphi s HotXLS

Představte si noční úlohu, která v kódu sestaví sešit s fakturami a zapíše jej jako soubor CSV, aby jej mohl importovat navazující systém. V Excelu čísla vypadají správně. Soubor CSV se v textovém editoru otevře bez chyb. Poté se však importér zasekne na sloupci s celkovými součty, protože pole částky na řádku 42 obsahuje =SUM(D2:D41), tedy vzorec jako doslovný text, nikoli výsledné číslo. Nic není rozbité. Jedná se o zdokumentované chování a je to první věc, kterou je třeba o exportu z HotXLS pochopit: zapisovač serializuje model buněk přesně v tom stavu, v jakém se nachází, a buňka se vzorcem, jejíž hodnota nebyla nikdy vypočtena, má k předání pouze text svého vzorce

Proč váš soubor CSV obsahuje vzorce namísto čísel

Knihovna HotXLS ukládá text vzorce a vypočtenou hodnotu jako dvě samostatné věci. Metoda SaveAsCSV ze své podstaty nespouští při výstupu výpočetní jádro: export by neměl sešit měnit ani riskovat zaseknutí na složitém řetězci vzorců. Soubory uložené samotným Excelem nesou mezipaměťové výsledky hned vedle vzorců, takže jejich opětovný export funguje podle očekávání. Toto úskalí se týká výhradně sešitů generovaných vaším vlastním kódem, kde byly vzorce zapsány, ale nikdy nebyly vyhodnoceny. Nápravou je zajistit existenci hodnot před samotným exportem pomocí stejného jádra Calculate, které vyhodnocuje odkazy napříč listy a vlastní funkce:

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, co ten cyklus ve skutečnosti dělá: přepisuje buňky se vzorcem jejich vypočtenými hodnotami. To je naprosto správné pro jednorázový export, ale chybné, pokud chcete sešit poté znovu uložit jako .xlsx, protože jste právě nahradili živé vzorce statickými čísly. Exportujte z kopie sešitu nebo omezte zápis tak, aby se dotkl pouze exportního běhu. Výpočetní jádro na pozadí Calculate toho umí mnohem více, včetně registrace vlastních funkcí, což je tématem článku o jádru vzorců HotXLS a vlastních funkcích

Co zaručuje oddělovací zapisovač

Export do CSV produkuje kódování UTF-8 s označením pořadí bajtů (BOM), konce řádků CRLF a uvozování podle specifikace RFC 4180. Jakékoli pole obsahující oddělovač, uvozovky nebo zalomení řádku je obaleno uvozovkami a vnitřní uvozovky jsou zdvojeny. Kalendářní data se vykreslují ve formátu yyyy-mm-dd hh:nn:ss bez ohledu na formát zobrazení buňky. To je správná volba pro strojové zpracování, i když to může překvapit každého, kdo očekával přenos vizuálního formátování z obrazovky. Formátovaný text (rich text) v buňkách se sloučí do jednoho řetězce

Tato výchozí nastavení vyřeší většinu konfliktů s importérem ještě předtím, než začnou, ale dvě z nich přesto patří do vašeho integračního rozhraní. Prvním je BOM. Ten umožňuje Excelu otevřít soubor se správně zobrazenou diakritikou, avšak několik přísných parserů považuje tyto tři bajty za data; pokud je to váš případ, odstraňte je při předávání. Druhým je TSV. Nejde o samostatnou funkci, ale o stejný zapisovač volaný se střídou #9 (tabulátor) jako oddělovačem, takže vše výše popsané pro něj platí beze změny. List k exportu se volí pomocí indexu (od nuly) v přetížené metodě s více parametry, zatímco zkrácené volání SaveAsCSV(FileName) s jedním parametrem exportuje aktivní list

HTML export je a snapshot, not an interchange format

Zatímco CSV zahazuje vše kromě hodnot, metoda SaveAsHTML se snaží zachovat vzhled: jednu tabulku <table> na list, sloučené oblasti vyjádřené pomocí colspan a rowspan a základní stylování buněk vložené přímo jako CSS. Barvy závislé na šablonách motivů se spíše přeskakují, než vyhodnocují, takže šablona opírající se o motivy může vypadat prostěji než v Excelu. Nastavte explicitní barvy RGB na cokoli, co musí převod přežít. Možnosti exportu řídí konfigurační objekt:

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 této ukázce stojí za pozornost. Nastavte WriteDocument na False a výstupem se stane pouze čistý fragment tabulky namísto celé stránky, což je užitečné při vkládání náhledu do stávajícího rozvržení stránky: nastavte TableClass a nechte stylování na hostitelském CSS. Návratová konvence je také opačná než u většiny volání v HotXLS. Metoda SaveAsHTML vrací 0 při úspěchu a -1 při nesprávném indexu listu, takže kontrola na hodnotu = 1 ze zvyku nahlásí každý úspěšný export jako selhání. Pokud potřebujete pouze oblast namísto celého listu, například pro odeslání e-mailem nebo vložení samostatného bloku, metoda TXLSXRange.SaveAsHTML exportuje jakýkoli obdélníkový rozsah pod stejnými pravidly vykreslování

Výstup do RTF a kde má stále své místo

Čtvrtým formátem jsou tabulky RTF 1.6, zapisované po jednotlivých listech voláním SaveAsRTF. Šířky sloupců jsou aproximovány na zhruba 96 twipů na jeden znak šířky sloupce. Strukturálním omezením, které je třeba znát, je, že sloučené buňky se ve výstupu neroztáhnou: pouze kotevní buňka nese svůj obsah a zakryté buňky se vygenerují jako prázdné. To vylučuje RTF pro složitě strukturované šablony. Své místo si však stále obhajuje jako cesta nejmenšího odporu pro vkládání tabulkových výsledků do textových procesorů nebo do starších systémů správy dokumentů, které nepodporují HTML

Převod tam i zpět: import CSV je záměrně destruktivní

Zpětné načtení CSV má svá vlastní pravidla. Metoda OpenCSV vymaže celý sešit a znovu jej sestaví jako jediný list pojmenovaný Sheet1. Z hlediska logiky jde o konstruktor, nikoli o sloučení, takže ji nikdy nevolejte nad sešitem, který obsahuje neuložená data. Předání znaku #0 jako oddělovače spustí automatickou detekci oddělovače. Příznak ADetectTypes řídí převod typů: pokud je zapnutý, číselné řetězce se změní na čísla, řetězce ISO-8601 na data a hodnoty true/false na logické typy. Vypněte jej, pokud datový zdroj obsahuje identifikátory s počátečními nulami, PSČ nebo kódy produktů. Automatický převod by je tiše zkrátil na čísla (počáteční nula zmizí, jakmile se z 00123 stane 123). Obě rozhraní vystavují stejný import. V kombinaci s výše uvedeným exportem získáte formátový most, který nevyžaduje instalovaný Excel na žádném místě zpracování, což je scénář popsaný v článku o generování reportů z databáze do Excelu s HotXLS

Export přímo do streamu

Každý zde popsaný zapisovač má vedle verze s názvem souboru také přetížení pro stream: pro CSV, HTML, RTF i samotné formáty sešitů. V serverovém kódu jsou tato přetížení tou správnou volbou. Webový endpoint poskytující stažení CSV může zapisovat do TMemoryStream a ten předat přímo objektu odpovědi. Odpadá tak potřeba dočasných souborů, jejich mazání i riziko kolize mezi dvěma požadavky, které by náhodou zvolily stejný název souboru. Totéž platí pro odesílání exportů do cloudových úložišť (blob storage) nebo jejich připojování k odchozím e-mailům. Souborový systém z celého procesu zcela mizí

Tento vzor ladí se způsobem nasazení knihovny. Obě rozhraní jsou nativními čtečkami a zapisovači v Object Pascalu, takže není potřeba instalace Excelu, automatizace COM ani nevzniká úzké hrdlo při serializaci požadavků na serveru. Každý požadavek může vlastnit svůj objekt sešitu, spustit výpočetní přepis z první části a streamovat svůj export paralelně s ostatními. Paměť je jediným zdrojem, na který je třeba dát pozor. Model sešitu žije po dobu exportu v paměti RAM, takže služba, která otevírá velmi velké soubory pouze pro jejich převod do CSV, by měla omezit počet souběžných úloh nebo ty nadměrné zařadit do fronty, namísto aby o velikosti pracovní sady rozhodovala špička v návštěvnosti

Jedna drobná možnost: nastavte vlastnost IncludeBOM v nastavení HTML, pokud bude fragment uložen jako samostatný soubor, u kterého následný nástroj detekuje kódování. Pokud poskytujete HTML přímo přes HTTP, ponechte deklaraci znakové sady raději na hlavičkách odpovědi serveru

Když zapsané bajty přesto vykazují chybu

Nejčastější dotaz na podporu ohledně exportu do CSV je problém s otevřením v jiném kabátě: Excel zobrazuje rozsypaný čaj (mojibake) namísto znaků s diakritikou. Prvním instinktem je vinit zapisovač, ten však právě z tohoto důvodu generuje UTF-8 BOM a soubor je při opuštění vašeho kódu téměř vždy správný. Značku BOM odstranilo něco na cestě mezi kódem a Excelem. Přenos přes FTP v textovém režimu, kopírování streamu vynechávající první tři bajty, proxy re-kódující data za běhu: cokoli z toho odstraní značku a nechá Excel hádat kódování, což mu příliš nejde. Diagnostikujte tento problém na rozhraní a ne v samotném volání exportu. Otevřete doručený soubor v hexadecimálním prohlížeči a ověřte, zda jsou na jeho začátku stále bajty EF BB BF

To je společná linka pro všechny čtyři formáty. Volání exportu je ta snadná část a HotXLS volí optimální řešení pro každé rozhodnutí, kterému zapisovač čelí. Tyto chyby žijí na rozhraních — tam, kde se text vzorce setkává s parserem vyžadujícím číslo, kde se BOM potkává s přenosem, který jej nezachová, nebo kde se sloučená buňka potkává s plochým tabulkovým modelem RTF. Každá z těchto skutečností by měla být zanesena do integrační dohody mezi vaším exportérem a tím, co data zpracovává, protože příjemce nedokáže vyčíst vaše záměry ze samotných bajtů. Kompletní seznam metod pro obě rozhraní sešitů naleznete na produktové stránce HotXLS Component