Generisanje Excel datoteka u Delphi-ju bez Office automatizacije

Ako je jedini posao servera da emituje Excel datoteke, onda nema razloga da pokreće Excel. Instaliranje Office-a na agentu za izgradnju (build agent) ili servisu za izveštaje radi upravljanja putem COM automatizacije jeste pogrešan dizajn, i to je pogrešan dizajn od kada takva praksa postoji. Sam Microsoft to kaže u smernicama koje nisu ublažene već dvadeset godina: Office niti je napravljen niti licenciran da se automatizuje iz neaktivnih procesa na strani servera (unattended process). Pravi odgovor je direktno pisanje BIFF i OOXML bajtova, bez ikakvog Excel-a u slici. To je cela premisa HotXLS-a, izvorne Object Pascal biblioteke koja sama čita i upisuje formate radnih tabela, tako da nema desktop aplikacije koja bi se blokirala, curela memoriju ili zahtevala plaćanje licenci po radnom mestu

Zašto upravljanje programom EXCEL.EXE iz servisa ne uspeva

COM automatizacija daljinski kontroliše desktop program, a desktop program prećutno pretpostavlja tri stvari koje Windows servis ne može da mu pruži: učitan korisnički profil, interaktivnu prozorsku stanicu i čoveka koji gleda u ekran. Oduzmite to i neuspesi stižu u obliku koji nijedna programerska mašina nikada ne reprodukuje. Dijalog za oporavak datoteke, greška dodatka (add-in) ili prozor za aktivaciju licence otvaraju se na desktopu koji niko ne vidi, a poziv automatizacije koji ih je pokrenuo nikada se ne vraća. Pozivalac na kraju istekne (times out) i prekine se; instanca Excel-a često preživi, ostajući kao siroče koje drži zaključane datoteke i truje sledeće pokretanje. Svako ko je gledao jedanaest zalutalih EXCEL.EXE procesa kako se gomilaju pod servisnim nalogom zna ostatak te priče

Priča o skaliranju nije ništa bolja čak i kada ništa ne pukne. Instanca Excel-a je proces za jednu radnu svesku, svaki pristup svojstvu plaća cenu međugraničnog COM maršalizovanja (COM marshaling), a mašina koja izvršava kod nosi licencu za Office čiji uslovi isključuju upravo ovu upotrebu. Većina timova se susreće sa ovim ograničenjima kroz uzastopne ispade u radu, što je otprilike način na koji zadatak "penzionisanje COM sloja" završi na mapi puta razvoja

Pre nego što to ponovno pisanje počne, rešite jedno pitanje obima posla, jer ono odlučuje o tome koliki je deo posla stvaran. COM kod skoro nikada samo ne postavlja vrednosti ćelija. On poziva Workbook.SaveAs sa konstantama formata, forsira preračunavanje, podešava postavke štampanja, a ponekad se obraća i clipboard-u (ostavi za isečke). Prođite kroz stari kod i zapišite koja se od tih ponašanja zapravo isporučuju u izlazu, pošto svako od njih sleće u drugi ugao izvorne biblioteke, a nekoliko njih (interoperabilnost sa clipboard-om je očigledan primer) nema nikakvo značenje na strani servera i treba ih odbaciti umesto portovati

Dva izvorna mehanizma, dva modela vlasništva

HotXLS zamenjuje Excel proces sa dve direktne implementacije formata. Mehanizam binarnog toka BIFF8 zapisa (TXLSWorkbook, unit lxHandle) rukuje formatom .xls. Pisac OOXML paketa (TXLSXWorkbook, unit lxHandleX) proizvodi .xlsx koji je usaglašen sa standardima ECMA-376 / ISO/IEC 29500. Nema šta da se registruje niti instalira na serveru, a u memoriji možete držati otvorenim onoliko radnih svezaka odjednom koliko to memorija dozvoljava

Ono što ljudima rano stvara probleme jeste to što dve fasade poseduju svoju memoriju na različite načine, a ta razlika je tiha sve dok ne dođe do pada programa:

var
  Book: IXLSWorkbook;          // interface reference: released automatically
  Sheet: IXLSWorksheet;
  BookX: TXLSXWorkbook;        // plain object: you free it
  SheetX: TXLSXWorksheet;
begin
  // BIFF8 .xls output - no Free; the interface refcount owns it
  Book := TXLSWorkbook.Create;
  Sheet := Book.Sheets.Add;
  Sheet.Name := 'Report';
  Sheet.Cells.Item[1, 1].Value := 'Generated without Excel';
  Book.SaveAs('report.xls');

  // OOXML .xlsx output - explicit lifetime
  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;

XLS fasada se upravlja brojanjem referenci (reference-counted) kroz interfejs IXLSWorkbook. Deklarišite promenljivu kao tip interfejsa i nikada ne pozivajte Free nad njom; ako držite isti objekat u običnoj objektnoj promenljivoj i sami ga oslobodite, brojač referenci će ga osloboditi drugi put i srušiti program. XLSX fasada je običan objekat koji zahteva uobičajenu try..finally strukturu. Adresiranje ćelija počinje od 1 (1-based) na obe strane, što je jedino mesto gde se slažu. Kolekcije listova se ne slažu: Entries na strani XLS-a je zasnovan na 1, dok je XLSX Items indeksator zasnovan na 0, i taj pomeraj za jedan se kompajlira bez greške na koji god način da pogrešite, a prikazuje se tek u vreme izvršavanja

Upisivanje radne sveske direktno u HTTP odgovor

Izvoz na strani servera obično nema razloga da koristi disk. Privremene datoteke zahtevaju politiku čišćenja, sudaraju se pod konkurentnim zahtevima i ostavljaju podatke klijenata na diskovima koje niko nije planirao da revidira. Obe fasade prihvataju TStream kroz svoja preopterećenja metode SaveAs, tako da radna sveska može ići direktno u odgovor:

Mem := TMemoryStream.Create;
Book := TXLSXWorkbook.Create;
try
  Sheet := Book.Sheets.Add('Data');
  Sheet.Cells[1, 1].Value := 'Generated ' + DateTimeToStr(Now);
  Book.SaveAs(Mem);          // writes from the CURRENT stream position
  Mem.Position := 0;         // rewind before handing the stream over
  Response.ContentType :=
    'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
  Response.ContentStream := Mem;   // the framework now owns Mem
finally
  Book.Free;
end;

Premotavanje je linija koja zaslužuje svoj komentar. SaveAs(Stream) piše od trenutne pozicije toka i nikada ne premotava unazad na nulu nakon toga. Zaboravite Mem.Position := 0 i klijent dobija preuzimanje od nula bajtova, ili Excel prijavljuje da je datoteka oštećena. Ovo je najčešći bag u kodu za radne sveske okrenutom vebu, i najokrutniji, jer prolazi pored svakog jediničnog testa koji samo proverava da li tok ima dužinu veću od nule

Jedna rutina za izgradnju radne sveske dostiže svaki drugi format isporuke bez restrukturiranja. SaveAsCSV odgovara na zahtev "samo mi daj sirove podatke", SaveAsHTML rukuje ubacivanjem na portalnu stranicu, SaveAsRTF hrani procese za dokumente, a SaveAsODS pokriva obavezni OpenDocument format, sve to sa preopterećenjima i za datoteku i za tok. Jedna izvozna rutina plus parametar formata zamenjuje ono što su obično bila četiri odvojena COM makroa. Opcije HTML izvoza (TXLSXHtmlExportOptions) nose naslov, CSS klasu i prekidač za fragment ili ceo dokument, što sprečava da se u slučaju portala bavite regex izmenama izvezenog markup-a

Vrednosti formula bez Excel procesa koji bi ih izračunao

Pod COM automatizacijom Excel je besplatno preračunavao sve, a odbacivanje COM-a to tiho ukida. SaveAs čuva formule kao tekst bez njihove procene; brojevi se pojavljuju tek kada Excel otvori datoteku i preračuna ih, što je ponašanje koje na XLS fasadi možete podesiti preko RecalcOnSave i CalculationMode. Za datoteku namenjenu čoveku, to je potpuno ispravno. Pogrešno je za servis koji mora da potvrdi ukupni zbir pre slanja, i pogrešno za izvoz u CSV koji upisuje tekst formule umesto njenog rezultata. U oba slučaja proračun se mora obaviti na serveru pomoću ugrađenog mehanizma:

SheetX.Cells[1, 1].Value := 1200;
SheetX.Cells[2, 1].Value := 950;
SheetX.Cells[3, 1].Formula := 'SUM(A1:A2)';   // XLSX facade: no '=' prefix
Total := BookX.Calculate('SUM(A1:A2)');       // evaluate on the server, now
if Total <> 2150 then
  raise Exception.Create('reconciliation failed before delivery');

Konvencija fasada ponovo pravi problem ovde. XLSX strana dodeljuje izraze preko Cell.Formula bez znaka jednakosti; XLS strana ih upisuje preko Cell.Value sa vodećim '='. Prenesite kod sa jedne strane na drugu nepromenjen i pogrešna konvencija će sačuvati tekstualni string koji samo liči na formulu, bez ikakve greške koja bi to signalizirala. Kada formule radne sveske treba da dosegnu vašu sopstvenu poslovnu logiku, povratni poziv OnUserFunction omogućava mehanizmu da preda nepoznate nazive funkcija Delphi kodu u vreme procene. To je izvorna zamena za UDF dodatke (add-ins) koji se obično kriju unutar radnih tabela oko kojih je sistem sa COM automatizacijom i izgrađen

Detalji raspoređivanja koji se pojavljuju samo na serveru

Nekoliko detalja odlučuje o tome da li je uvođenje čisto ili problematično, a prvi je graf zavisnosti jedinica (unit graph). Drag-and-drop eksporter skupova podataka TDataToXLS povlači za sobom VCL Forms, Controls i Dialogs. Bezopasno u desktop alatu; u konzolnom servisu to povlači ceo VCL sa sobom. Jezgro jedinice lxHandle i lxHandleX obraćaju se samo modulima Windows, Classes, SysUtils i Variants, tako da je za čist servis bolje da napiše sopstvenu petlju skupa podataka prema API-ju jezgra, nego da uvozi komponentu radi pogodnosti

Zatim je tu višenitnost (threading). Instance radne sveske nisu bezbedne za niti (thread-safe), ali ne dele ni bilo kakvo globalno stanje, tako da je šablon koji se skalira najjednostavniji: jedan objekat radne sveske po poslu ili po radnoj niti (worker thread). To donosi paralelno generisanje izveštaja, što jedna deljena instanca Excel-a nikada ne može. Rukovodilac zahteva koji kreira, popunjava, čuva i oslobađa sopstvenu radnu svesku ne zahteva nikakve lock-ove (katance), a radijus eksplozije kvara se smanjuje sa "deljena instanca Excel-a je blokirana za sve" na "ovaj jedan zahtev je pokrenuo izuzetak", sa čime vaše postojeće rukovanje greškama već zna šta da radi

Ciljanje formata je poslednji od njih. TXLSWorkbook.SaveAs podrazumevano piše BIFF (xlExcel97), a prebacivanje XLS sadržaja u .xlsx prolazi kroz SaveXLSWorkbookAsXLSX most sa smanjenom vernošću. Izaberite fasadu prema formatu koji nameravate da isporučite, u vreme projektovanja, umesto da gradite u jednom i konvertujete na kraju procesa obrade

Za polovinu koja se bavi učitavanjem podataka u tipičnom projektu zamene, šabloni izvoza baze podataka u radnu svesku pokrivaju i komponentu i ručno napisanu petlju, a kada broj redova dostigne šestocifrene vrednosti, tehnike performansi velikih radnih svezaka čine razliku između minuta i sekundi. Izveštaji izgrađeni na osnovu rasporeda koje održava dizajner pokriveni su vodičem za generisanje izveštaja na osnovu šablona

HotXLS se isporučuje kao Object Pascal izvorni kod za Delphi i C++Builder; izdanja, licenciranje i kompletna referenca API-ja nalaze se na stranici proizvoda HotXLS komponente