Otevírání a ukládání tabulek ODS v Delphi s HotXLS

Reportovací backend v Delphi, který léta vydával .xlsx, dostane nový požadavek: pravidla pro zadávání veřejných zakázek u zákazníka z veřejného sektoru vyžadují výstup OpenDocument Spreadsheet, a analytici na tomto účtu posílají své úpravy zpět jako soubory .ods uložené z LibreOffice. Stejný kód teď tedy musí ODS zapisovat i číst. HotXLS, nativní knihovna losLab pro tabulkové procesory v Object Pascalu pro Delphi a C++Builder, zvládá oba směry, aniž by kdekoli byl nainstalovaný Excel nebo LibreOffice. Co nedělá, je udělat oba směry symetrickými. Export nese mnohem víc, než dokáže import obnovit, a tým, který předpokládá opak, uvidí, jak vzorce a formátování mizí někde mezi zákazníkovou revizí a dalším reportem, bez jediné chyby, ke které by se to dalo dopátrat

Podpora ODS žije na fasádě XLSX, ne na fasádě XLS

HotXLS dodává v jediném balíčku dvě nezávislé hierarchie tříd: TXLSWorkbook v jednotce lxHandle pro binární soubory BIFF8 .xls a TXLSXWorkbook v jednotce lxHandleX pro balíčky OOXML .xlsx. Každý vstupní bod pro OpenDocument, OpenODS, SaveAsODS, GetODSSheetNames, visí na TXLSXWorkbook. Toto umístění není libovolné. Balíček ODS, jak jej specifikuje OASIS ODF 1.3, je archiv zip nesoucí člen mimetype, manifest a tělo content.xml, což z něj dělá strukturálního bratrance zipu OOXML; BIFF8 je binární proud záznamů z devadesátých let, který s tím nemá nic společného

Toto umístění má praktický důsledek: starší sešit .xls se nemůže stát .ods jedním voláním. Nejprve přemostíte obsah BIFF do modelu XLSX pomocí SaveXLSWorkbookAsXLSX z jednotky lxXlsxExport, výsledek znovu otevřete přes TXLSXWorkbook a teprve odtamtud exportujete. Most není bezeztrátový a vyplatí se znát mezery dřív, než na něm stavíte. Zkopíruje hodnoty, vzorce, formáty čísel, písma, výplně a šířky sloupců. Zahodí ohraničení, sloučené rozsahy, komentáře, grafy a podmíněné formátování. Zdroj .xls s hustým formátováním dorazí do ODS a bude vypadat prostěji, než z jakého vyšel, a to je vlastnost mostu, ne zapisovače ODS

Detekce na straně importu je automatická. Obyčejná metoda Open rozpozná balíček ODS podle jeho členu mimetype a v případě, že tento člen chybí, se vrátí ke kontrole content.xml na nejvyšší úrovni, takže obecná cesta kódu „otevři, cokoli uživatel nahrál" nepotřebuje vlastní čichání k příponám. Po otevření vlastnost SourceFormat hlásí, která větev se spustila

Export do ODS pomocí TODSExportOptions

Samotné volání exportu je jeden řádek; objekt s možnostmi kolem něj nese rozhodnutí, na která se recenzent bude později ptát:

var
  Book: TXLSXWorkbook;
  Opts: TODSExportOptions;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('quarterly-report.xlsx');
    Opts := TODSExportOptions.Create;        // volající vlastní tento objekt a uvolňuje jej
    try
      Opts.Generator := 'ReportService 4.2'; // přepis meta:generator
      Opts.IncludeCharts := True;
      Opts.IncludeImages := True;
      Book.SaveAsODS('quarterly-report.ods', Opts);
    finally
      Opts.Free;
    end;
  finally
    Book.Free;
  end;
end;

Objekt s možnostmi vlastní volající. HotXLS jej neuvolní, a proto tam vnitřní try..finally je a není volitelný. Dvě vlastnosti, které mění výstup, ne jen jej popisují, stojí za bližší pohled. Nastavení IncludeCharts := False dělá víc než jen skrývá grafy: vytáhne z balíčku poddokumenty grafů a jejich záznamy v manifestu, což je přesně to, co chcete, když je konzumentem datová pipeline, která by o ně zakopla. Generator přepisuje řetězec ODF meta:generator, který jinak zní HotXLS/<version>; přepište jej, když navazující nástroje otiskují producenty souborů kvůli směrování podpory. Pokud se na vás nic z toho nevztahuje, objekt s možnostmi úplně vynechte. Volání SaveAs(FileName, xlsxOpenDocumentSpreadsheet) je totéž jako SaveAsODS s výchozími hodnotami, a přetížení pro streamy u obou umožňují zapsat balíček přímo do odpovědi HTTP bez dočasného souboru

Co cesta importu čte, a co záměrně přeskočí

Tuto část si přečtěte pozorně, než někomu slíbíte věrnost cyklu tam a zpět. Import ODS je v HotXLS záměrně odlehčená cesta. Zachovává skalární hodnoty buněk a mezipaměťový výsledek, který každý vzorec nesl v okamžiku uložení, a rozbalí opakované řádky a sloupce do mřížky. Nepřenáší styly, výrazy vzorců ODS ani kresby

Rozhodnutí o vzorcích je to, které nejspíš kousne, a bylo učiněno záměrně. Buňka ODF ukládá dvě věci vedle sebe: výraz vzorce, zapsaný v dialektu OpenFormula definovaném v ODF 1.3 Part 4, a poslední hodnotu, kterou pro něj vypočítala produkující aplikace. Překlad OpenFormula do syntaxe vzorců Excelu je vlastní problém konverze dialektu, se skutečnými hraničními případy kolem slovníku funkcí, syntaxe odkazů a modelů chyb. Čtení mezipaměťové hodnoty místo toho obejde celou tuto třídu tichých chybných překladů, takže čísla, která importujete, jsou přesně ta čísla, která naposledy viděl odesílatel. Cena je, že dorazí jako čísla, ne jako živé vzorce, které je vytvořily

Selhání, kolem kterého je třeba navrhovat, plyne přímo odtud: tabulka, jejíž součty byly správné, když ji naposledy uložil LibreOffice, se importuje se správnými čísly, ale tato čísla jsou teď konstanty. Upravíte vstupní buňku, přepočítáte, a nic se nepohne - vzorec je pryč, zůstal jen jeho konečný výsledek. Pokud workflow po importu potřebuje živé vzorce, znovu je programově ustavte z vlastních obchodních pravidel přes Cell.Formula, který na fasádě XLSX přebírá výraz bez úvodního rovnítka

Návrh kolem asymetrického cyklu tam a zpět

Export vykresluje z plného modelu sešitu v paměti: hodnoty, styly a, pokud o ně požádáte, grafy a obrázky. Import vrací jen hodnoty. Takže úsek .xlsx do .ods má vysokou věrnost a úsek .ods do .xlsx přinese zpět hodnoty a mezipaměťové výsledky, ale žádné styly a žádné živé vzorce. Zřetězíte-li oba, asymetrie se sčítá. Celý cyklus .xlsx do .ods do .xlsx zapíše na cestě ven vše věrně a na cestě zpět ztratí styly a vzorce, i když v žádném z kroků nic neselhalo

Book := TXLSXWorkbook.Create;
try
  Book.Open('vendor-revision.ods');          // formát se detekuje automaticky
  if Book.SourceFormat = xlsxOpenDocumentSpreadsheet then
  begin
    // Po importu ODS jsou přítomné hodnoty a mezipaměťové výsledky
    // vzorců; styly a živé vzorce ne. Před uložením obnovte vše,
    // na čem navazující pipeline závisí.
    Book.Sheets[0].Cells[2, 5].Formula := 'SUM(B2:D2)';
    Book.SaveAs('vendor-revision.xlsx');
  end;
finally
  Book.Free;
end;

Architektonický vzor, který z toho plyne: se vstupními soubory .ods zacházejte jako s datovými kanály, ne jako s dokumenty k úpravě na místě. Kanonický sešit udržujte v .xlsx, hodnoty čtěte ze zákaznických revizí a čerstvé ODS vydávejte na vyžádání z kanonické kopie. Ověřování patří do obou táborů - exportované soubory otevřete v LibreOffice Calc, referenčním konzumentovi ODF, i v Excelu, který ODS čte už léta, ale na okrajích podpory grafů a stylů se s LibreOffice neshoduje. Počet listů, hrstka klíčových buněk a přítomnost grafů tvoří dostatečnou kouřovou kontrolu pro každý exportní profil

Roztřídění souboru ODS před závazkem k importu

Když endpoint přijímá nahrané soubory, výpis názvů listů je mnohem levnější než plné parsování a zachytí strukturální překvapení včas:

Names := TStringList.Create;
Book := TXLSXWorkbook.Create;
try
  if Book.GetODSSheetNames('incoming.ods', Names) <= 0 then
    raise Exception.Create('not a readable ODS package');
  if Names.IndexOf('Data') < 0 then
    raise Exception.Create('revision is missing the Data sheet');
finally
  Book.Free;
  Names.Free;
end;

Konvence návratové hodnoty lidem podráží nohy: volání HotXLS obecně vrací kladný počet nebo 1 při úspěchu a -1 při selhání, přičemž při selhání seznam vyprázdní, takže testujte <= 0 místo porovnávání s jednou konkrétní kladnou hodnotou. GetODSSheetNames instanci sešitu ani neresetuje, ani nenaplní, takže jediný zkoumací objekt může proklepnout celý adresář příchozích souborů. Strukturální kontroly jako tato zachytí nejběžnější selhání z reálného světa, kdy analytik před odesláním revize zpět přejmenuje nebo smaže list, hned u brány, kde chybová zpráva ještě dokáže pojmenovat soubor a chybějící list, místo aby se projevila jako nil reference o tři vrstvy hlouběji

Pokud kolem toho stavíte širší konverzní pipeline, vzor pracoviště pro audit a konverzi sešitů ukazuje, jak zinventarizovat funkce souboru dřív, než zvolíte cílový formát, a průvodce výkonem velkých sešitů udržuje dávkové exporty ve zdravých paměťových mezích

HotXLS je nativní knihovna tabulkových procesorů pro Delphi a C++Builder s kompletním zdrojovým kódem; úplný seznam funkcí a podrobnosti licencování najdete na stránce produktu HotXLS Delphi Component