Generování souborů Excelu v Delphi bez automatizace Office

Pokud je jediným úkolem serveru vydávat soubory Excelu, nemá co pohledávat spouštění Excelu. Instalovat Office na build agenta nebo reportovací službu, aby jej řídila přes automatizaci COM, je špatný návrh, a byl to špatný návrh po celou dobu, co tato praxe existuje. Říká to sám Microsoft, v pokynech, které se za dvacet let nezmírnily: Office není stavěný ani licencovaný k automatizaci z bezobslužného serverového procesu. Správná odpověď je zapisovat bajty BIFF a OOXML přímo, bez jakéhokoli Excelu v obraze. To je celá premisa HotXLS, nativní knihovny Object Pascal, která sama čte a zapisuje formáty tabulkových procesorů, takže tu není žádná desktopová aplikace, která by mohla zamrznout, unikat pamětí, nebo za kterou byste platili za licenci na pracovní stanici

Proč řízení EXCEL.EXE ze služby selhává

Automatizace COM vzdáleně ovládá desktopový program, a desktopový program tiše předpokládá tři věci, které mu služba Windows nemůže poskytnout: načtený uživatelský profil, interaktivní window station a člověka, který se dívá na obrazovku. Odeberte je a selhání přijdou v podobě, kterou žádný vývojářský počítač nikdy nezreprodukuje. Výzva k obnově souboru, chyba doplňku nebo dialog aktivace licence se otevře na desktopu, který nikdo nevidí, a automatizační volání, které jej spustilo, se nikdy nevrátí. Volající nakonec dostane timeout a zemře; instance Excelu často ne a přežije jako sirotek, který drží zámky souborů a otráví další běh. Kdokoli sledoval, jak se pod servisním účtem hromadí jedenáct bludných procesů EXCEL.EXE, zná zbytek tohoto příběhu

Příběh škálování není o nic lepší, ani když nic nespadne. Instance Excelu je pipeline pro jediný sešit, každý přístup k vlastnosti platí cenu meziprocesového marshalingu COM, a stroj, na kterém kód běží, nese licenci Office, jejíž podmínky přesně toto použití vylučují. Většina týmů na tato omezení narazí jeden výpadek po druhém, a zhruba takhle se „zrušit vrstvu COM" dostane na roadmapu

Než ten přepis začne, vyřešte jednu otázku rozsahu, protože rozhoduje o tom, kolik z té práce je reálné. Kód COM skoro nikdy jen nenastavuje hodnoty buněk. Volá Workbook.SaveAs s konstantami formátu, vynucuje přepočet, prosazuje nastavení tisku, občas sáhne po schránce. Projděte starý kód a zapište si, které z těchto chování se skutečně promítají do výstupu, protože každé z nich přistane v jiném koutě nativní knihovny, a pár z nich (interoperabilita se schránkou je ten očividný případ) nemá na straně serveru žádný smysl a mělo by se raději zahodit než portovat

Dva nativní enginy, dva modely vlastnictví

HotXLS vyměňuje proces Excelu za dvě přímé implementace formátů. Engine pro proud záznamů BIFF8 (TXLSWorkbook, jednotka lxHandle) zvládá .xls. Zapisovač balíčků OOXML (TXLSXWorkbook, jednotka lxHandleX) produkuje .xlsx odpovídající ECMA-376 / ISO/IEC 29500. Není tu nic k registraci a nic k instalaci na server, a otevřených sešitů najednou můžete mít tolik, kolik dovolí paměť

Co lidem hned na začátku podráží nohy, je fakt, že obě fasády vlastní svou paměť odlišně, a ten rozdíl mlčí, dokud to nespadne:

var
  Book: IXLSWorkbook;          // odkaz na rozhraní: uvolňuje se automaticky
  Sheet: IXLSWorksheet;
  BookX: TXLSXWorkbook;        // obyčejný objekt: uvolňujete jej vy
  SheetX: TXLSXWorksheet;
begin
  // výstup BIFF8 .xls - žádné Free; vlastní jej počet referencí rozhraní
  Book := TXLSWorkbook.Create;
  Sheet := Book.Sheets.Add;
  Sheet.Name := 'Report';
  Sheet.Cells.Item[1, 1].Value := 'Generated without Excel';
  Book.SaveAs('report.xls');

  // výstup OOXML .xlsx - explicitní životnost
  BookX := TXLSXWorkbook.Create;
  try
    SheetX := BookX.Sheets.Add('Report');
    SheetX.Cells[1, 1].Value := 'Generated without Excel';
    BookX.SaveAs('report.xlsx');
  finally
    BookX.Free;
  end;
end;

Fasáda XLS je počítaná referencemi přes rozhraní IXLSWorkbook. Deklarujte proměnnou jako typ rozhraní a nikdy na ni nevolejte Free; podržíte-li stejný objekt v obyčejné objektové proměnné a uvolníte jej sami, počet referencí jej uvolní podruhé. Fasáda XLSX je obyčejný objekt, který chce obyčejný try..finally. Adresování buněk je 1-based na obou stranách, což je to jediné místo, kde se obě shodnou. Kolekce listů ne: Entries na straně XLS je 1-based, indexer XLSX Items je 0-based, a tato chyba o jedna se čistě zkompiluje, ať už ji uděláte kterýmkoli způsobem, a projeví se až za běhu

Zápis sešitu přímo do odpovědi HTTP

Export na straně serveru obvykle nemá důvod sahat na disk. Dočasné soubory vyžadují politiku úklidu, kolidují při souběžných požadavcích a zanechávají zákaznická data ležet na svazcích, které nikoho nenapadlo auditovat. Obě fasády přebírají TStream přes svá přetížení SaveAs, takže sešit může jít přímo do odpovědi:

Mem := TMemoryStream.Create;
Book := TXLSXWorkbook.Create;
try
  Sheet := Book.Sheets.Add('Data');
  Sheet.Cells[1, 1].Value := 'Generated ' + DateTimeToStr(Now);
  Book.SaveAs(Mem);          // zapisuje od AKTUÁLNÍ pozice streamu
  Mem.Position := 0;         // před předáním streamu jej převiňte
  Response.ContentType :=
    'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
  Response.ContentStream := Mem;   // framework teď vlastní Mem
finally
  Book.Free;
end;

Převinutí je ten řádek, který si svůj komentář zaslouží. SaveAs(Stream) zapisuje od aktuální pozice streamu a nikdy se poté nevrátí zpět na nulu. Zapomenete-li na Mem.Position := 0, klient dostane stažení s nulovou velikostí, nebo Excel prohlásí soubor za poškozený. Toto je nejběžnější chyba v kódu sešitů obrácených k webu, a nejkrutější, protože proklouzne kolem jakéhokoli jednotkového testu, který jen tvrdí, že stream má nenulovou délku

Jedna rutina pro sestavení sešitu dosáhne na každý další doručovací formát bez přestavby. SaveAsCSV odpoví na požadavek „dej mi prostě syrová data", SaveAsHTML zvládne „vlož to do stránky portálu", SaveAsRTF krmí dokumentové pipeline a SaveAsODS pokrývá mandát OpenDocument, vše s přetíženími jak pro soubor, tak pro stream. Jediná exportní rutina plus parametr formátu nahrazuje to, co bývaly čtyři oddělená makra COM. TXLSXHtmlExportOptions exportéru HTML nese titulek, CSS třídu a přepínač fragment-nebo-plný-dokument, což drží případ portálu mimo záležitost úprav exportovaného markupu regulárními výrazy

Hodnoty vzorců bez procesu Excelu, který by je počítal

Pod automatizací COM Excel všechno přepočítával zadarmo, a odstranění COM to tiše odebere. SaveAs ukládá vzorce jako text, aniž by je vyhodnocoval; čísla se objeví až poté, co Excel soubor otevře a přepočítá, a toto chování vám fasáda XLS umožňuje ladit přes RecalcOnSave a CalculationMode. Pro soubor mířící k člověku je to přesně správně. Je to špatně pro službu, která musí potvrdit součet dřív, než jej odešle, a špatně pro export CSV, který zapíše text vzorce místo jeho výsledku. Oba případy musí vyhodnotit na serveru pomocí vestavěného enginu:

SheetX.Cells[1, 1].Value := 1200;
SheetX.Cells[2, 1].Value := 950;
SheetX.Cells[3, 1].Formula := 'SUM(A1:A2)';   // fasáda XLSX: bez prefixu '='
Total := BookX.Calculate('SUM(A1:A2)');       // vyhodnoť na serveru, teď
if Total <> 2150 then
  raise Exception.Create('reconciliation failed before delivery');

Konvence fasád tu kouše znovu. Strana XLSX přiřazuje výrazy přes Cell.Formula bez rovnítka; strana XLS je zapisuje přes Cell.Value s úvodním '='. Přenesete-li kód z jedné na druhou beze změny, špatná konvence uloží textový řetězec, který vzorci jen podobá, bez jakékoli chyby, která by na to upozornila. Když se vzorce sešitu potřebují dotknout vaší vlastní obchodní logiky, callback OnUserFunction umožňuje enginu předat neznámá jména funkcí kódu v Delphi v okamžiku vyhodnocení. To je nativní náhrada za doplňky UDF, které mívají tendenci se schovávat přímo uvnitř tabulek, kolem kterých systém s automatizací COM vyrostl

Hrany nasazení, které se projeví jen na serveru

Pár detailů rozhoduje o tom, jestli je nasazení hladké, nebo matoucí, a první z nich je graf jednotek. Exportér datasetů typu drag-and-drop TDataToXLS táhne s sebou VCL Forms, Controls a Dialogs. V desktopovém nástroji neškodné; v konzolové službě za sebou vleče celé VCL. Základní jednotky lxHandle a lxHandleX sahají jen po Windows, Classes, SysUtils a Variants, takže čistá služba je na tom lépe, když si napíše vlastní smyčku nad datasetem proti základnímu API, než aby kvůli pohodlí importovala komponentu

Pak je tu vláknění. Instance sešitu nejsou bezpečné pro vlákna, ale ani nesdílejí žádný globální stav, takže vzor, který škáluje, je ten nejjednodušší: jeden objekt sešitu na úlohu, nebo na pracovní vlákno. To přináší paralelní generování reportů, které jedna sdílená instance Excelu nikdy nedokáže. Obslužná rutina požadavku, která vytvoří, naplní, uloží a uvolní svůj vlastní sešit, nepotřebuje vůbec žádné zámky, a poloměr škody při selhání se zhroutí z „sdílená instance Excelu je zaseknutá pro všechny" na „tento jeden požadavek vyvolal výjimku", s čímž si vaše stávající zpracování chyb už ví rady

Poslední z nich je cílení formátu. TXLSWorkbook.SaveAs ve výchozím stavu zapisuje BIFF (xlExcel97), a protlačení obsahu XLS do .xlsx prochází mostem SaveXLSWorkbookAsXLSX se sníženou věrností. Vybírejte fasádu podle formátu, který máte v úmyslu dodat, už při návrhu, místo abyste stavěli v jedné a na konci pipeline konvertovali

Pro polovinu typického náhradního projektu věnovanou načítání dat pokrývají vzory exportu z databáze do sešitu jak komponentu, tak ručně psanou smyčku, a jakmile počty řádků dosáhnou šesti číslic, techniky výkonu velkých sešitů se stanou rozdílem mezi minutami a sekundami. Reporty postavené z rozvržení udržovaných návrhářem pokrývá průvodce generováním reportů ze šablon

HotXLS se dodává jako zdrojový kód v Object Pascalu pro Delphi a C++Builder; edice, licencování a kompletní referenci API najdete na stránce produktu HotXLS Delphi Component