Generarea fișierelor Excel în Delphi fără automatizare Office

Dacă singura sarcină a unui server este să emită fișiere Excel, acesta nu are niciun motiv să ruleze Excel. Instalarea Office pe un agent de build sau pe un serviciu de raportare pentru a-l controla prin automatizare COM este o proiectare greșită, și a fost o proiectare greșită de când există această practică. Microsoft afirmă chiar ea acest lucru, într-un ghid care nu s-a îmblânzit în douăzeci de ani: Office nu este nici construit, nici licențiat pentru a fi automatizat dintr-un proces server-side nesupravegheat. Răspunsul corect este să scrieți direct octeții BIFF și OOXML, fără Excel implicat deloc. Aceasta este întreaga premisă a HotXLS, o bibliotecă nativă Object Pascal care citește și scrie ea însăși formatele de foi de calcul, astfel încât nu există nicio aplicație desktop care să se blocheze, să aibă scurgeri de memorie sau pentru care să plătiți per licență

De ce eșuează controlul EXCEL.EXE dintr-un serviciu

Automatizarea COM controlează de la distanță un program desktop, iar un program desktop presupune tacit trei lucruri pe care un serviciu Windows nu i le poate oferi: un profil de utilizator încărcat, o stație de fereastră interactivă și un om care privește ecranul. Eliminați aceste lucruri și eșecurile apar într-o formă pe care nicio mașină de dezvoltare nu o reproduce vreodată. O solicitare de recuperare a fișierului, o eroare de add-in sau un dialog de activare a licenței se deschide pe un desktop pe care nimeni nu îl poate vedea, iar apelul de automatizare care l-a declanșat nu se mai întoarce niciodată. Apelantul intră în cele din urmă în timeout și moare; instanța Excel adesea nu, supraviețuind ca un proces orfan care ține blocate fișiere și otrăvește următoarea rulare. Oricine a văzut unsprezece procese EXCEL.EXE rătăcite acumulându-se sub un cont de serviciu cunoaște restul acestei povești

Povestea scalării nu este mai bună nici măcar atunci când nimic nu se blochează. O instanță Excel este un pipeline pentru un singur registru de lucru, fiecare acces la o proprietate plătește costul marshaling-ului COM între procese, iar mașina care rulează codul poartă o licență Office ale cărei condiții exclud exact această utilizare. Majoritatea echipelor întâlnesc aceste limite câte o întrerupere odată, ceea ce este, aproximativ, modul în care „retragerea stratului COM” ajunge pe o foaie de parcurs

Înainte de a începe rescrierea, tranșați o întrebare de domeniu, pentru că ea decide cât de mult din muncă este reală. Codul COM aproape niciodată nu doar setează valori de celule. Apelează Workbook.SaveAs cu constante de format, forțează recalcularea, împinge configurarea de tipărire, uneori apelează la clipboard. Parcurgeți codul vechi și notați care dintre aceste comportamente ajung de fapt în rezultat, deoarece fiecare aterizează într-un colț diferit al unei biblioteci native, iar câteva dintre ele (interopul cu clipboard-ul fiind cel evident) nu au niciun sens server-side și ar trebui eliminate, nu portate

Două motoare native, două modele de proprietate

HotXLS înlocuiește procesul Excel cu două implementări directe de format. Un motor de flux de înregistrări BIFF8 (TXLSWorkbook, unitatea lxHandle) gestionează .xls. Un scriitor de pachete OOXML (TXLSXWorkbook, unitatea lxHandleX) produce .xlsx conform cu ECMA-376 / ISO/IEC 29500. Nu este nimic de înregistrat și nimic de instalat pe server, iar dvs. puteți ține deschise câte registre de lucru permite memoria

Ce încurcă oamenii încă de la început este că cele două fațade își gestionează memoria diferit, iar diferența este tăcută până se prăbușește:

var
  Book: IXLSWorkbook;          // referință interfață: eliberată automat
  Sheet: IXLSWorksheet;
  BookX: TXLSXWorkbook;        // obiect simplu: îl eliberați dvs.
  SheetX: TXLSXWorksheet;
begin
  // ieșire BIFF8 .xls - fără Free; numărul de referințe al interfeței îl deține
  Book := TXLSWorkbook.Create;
  Sheet := Book.Sheets.Add;
  Sheet.Name := 'Report';
  Sheet.Cells.Item[1, 1].Value := 'Generated without Excel';
  Book.SaveAs('report.xls');

  // ieșire OOXML .xlsx - durată de viață explicită
  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;

Fațada XLS este numărată prin referințe prin interfața IXLSWorkbook. Declarați variabila ca tip interfață și nu apelați niciodată Free pe ea; țineți același obiect într-o variabilă obiect simplă și eliberați-l dvs. înșivă, iar numărul de referințe îl eliberează a doua oară. Fațada XLSX este un obiect obișnuit care dorește un try..finally obișnuit. Adresarea celulelor este bazată pe 1 pe ambele părți, singurul loc în care cele două sunt de acord. Colecțiile de foi nu sunt: Entries pe partea XLS este bazat pe 1, indexatorul Items din XLSX este bazat pe 0, iar această eroare off-by-one se compilează curat oricare ar fi felul în care greșiți și se arată abia la runtime

Scrierea unui registru de lucru direct într-un răspuns HTTP

Un export server-side de obicei nu are niciun motiv să atingă discul. Fișierele temporare cer o politică de curățare, se ciocnesc sub cereri concurente și lasă date ale clienților așezate pe volume pe care nimeni nu s-a gândit să le auditeze. Ambele fațade acceptă un TStream prin supraîncărcările lor SaveAs, astfel încât registrul de lucru poate merge direct în răspuns:

Mem := TMemoryStream.Create;
Book := TXLSXWorkbook.Create;
try
  Sheet := Book.Sheets.Add('Data');
  Sheet.Cells[1, 1].Value := 'Generated ' + DateTimeToStr(Now);
  Book.SaveAs(Mem);          // scrie de la poziția CURENTĂ a fluxului
  Mem.Position := 0;         // derulați înapoi înainte de a preda fluxul
  Response.ContentType :=
    'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
  Response.ContentStream := Mem;   // framework-ul deține acum Mem
finally
  Book.Free;
end;

Derularea înapoi este linia care își merită comentariul. SaveAs(Stream) scrie de la poziția curentă a fluxului și nu revine niciodată la zero după aceea. Uitați Mem.Position := 0 și clientul primește o descărcare de zero octeți, sau Excel declară fișierul corupt. Aceasta este cea mai comună eroare din codul de registre de lucru orientat spre web, și cea mai crudă, pentru că trece neobservată de orice test unitar care doar verifică faptul că fluxul are o lungime diferită de zero

O singură rutină de construire a registrului de lucru ajunge la orice alt format de livrare fără restructurare. SaveAsCSV răspunde cererii de tipul „dă-mi doar datele brute”, SaveAsHTML se ocupă de „pune-l într-o pagină de portal”, SaveAsRTF alimentează pipeline-uri de documente, iar SaveAsODS acoperă un mandat OpenDocument, toate cu supraîncărcări atât pentru fișier, cât și pentru flux. O singură rutină de export plus un parametru de format înlocuiește ceea ce tindea să fie patru macrocomenzi COM separate. TXLSXHtmlExportOptions al exportatorului HTML poartă titlul, clasa CSS și un comutator fragment-sau-document-complet, ceea ce ține cazul portalului departe de editarea cu regex a marcajului exportat

Valori de formule fără un proces Excel care să le calculeze

Sub automatizarea COM, Excel recalcula totul gratuit, iar renunțarea la COM anulează tacit acest lucru. SaveAs stochează formulele ca text fără să le evalueze; numerele apar doar după ce Excel deschide fișierul și recalculează, comportament pe care fațada XLS vă permite să îl reglați prin RecalcOnSave și CalculationMode. Pentru un fișier destinat unei persoane, acest lucru este exact corect. Este greșit pentru un serviciu care trebuie să confirme un total înainte de a-l livra, și greșit pentru exportul CSV, care scrie textul formulei, nu rezultatul ei. În ambele cazuri trebuie să se evalueze pe server cu motorul încorporat:

SheetX.Cells[1, 1].Value := 1200;
SheetX.Cells[2, 1].Value := 950;
SheetX.Cells[3, 1].Formula := 'SUM(A1:A2)';   // fațada XLSX: fără prefix '='
Total := BookX.Calculate('SUM(A1:A2)');       // evaluează pe server, acum
if Total <> 2150 then
  raise Exception.Create('reconciliation failed before delivery');

Convenția fațadelor mușcă din nou aici. Partea XLSX atribuie expresii prin Cell.Formula fără semnul egal; partea XLS le scrie prin Cell.Value cu un '=' la început. Duceți cod de la una la cealaltă neschimbat, și convenția greșită stochează un șir de text care doar seamănă cu o formulă, fără nicio eroare care să semnaleze acest lucru. Când formulele unui registru de lucru trebuie să ajungă în propria dvs. logică de business, callback-ul OnUserFunction permite motorului să predea nume de funcții necunoscute către codul Delphi în momentul evaluării. Acesta este înlocuitorul nativ pentru add-in-urile UDF care tind să se ascundă chiar în foile de calcul din jurul cărora a crescut un sistem de automatizare COM

Marginile de desfășurare care apar doar pe server

Câteva detalii decid dacă desfășurarea este curată sau derutantă, iar primul este graful de unități. Exportatorul de dataset drag-and-drop TDataToXLS aduce după sine Forms, Controls și Dialogs din VCL. Inofensiv într-un instrument desktop; într-un serviciu de consolă, tractează întregul VCL după el. Unitățile de bază lxHandle și lxHandleX apelează doar la Windows, Classes, SysUtils și Variants, așa că un serviciu pur este mai bine să își scrie propria buclă de dataset pe baza API-ului de bază decât să importe componenta din comoditate

Apoi este vorba de threading. Instanțele de registru de lucru nu sunt thread-safe, dar nici nu partajează vreo stare globală, așa că modelul care scalează este cel mai simplu: un obiect registru de lucru per job, sau per thread de worker. Asta cumpără generarea paralelă de rapoarte, ceva ce o singură instanță Excel partajată nu poate face niciodată. Un handler de cerere care își creează, umple, salvează și eliberează propriul registru de lucru nu are nevoie de niciun blocaj, iar raza de acțiune a unui eșec se restrânge de la „instanța Excel partajată este blocată pentru toată lumea” la „această singură cerere a ridicat o excepție”, cu care gestionarea existentă a erorilor deja știe ce să facă

Țintirea formatului este ultimul dintre ele. TXLSWorkbook.SaveAs scrie BIFF (xlExcel97) implicit, iar împingerea conținutului XLS în .xlsx trece prin puntea SaveXLSWorkbookAsXLSX, la fidelitate redusă. Alegeți fațada în funcție de formatul pe care intenționați să îl livrați, în faza de proiectare, mai degrabă decât să construiți într-unul și să convertiți la finalul pipeline-ului

Pentru jumătatea de încărcare a datelor dintr-un proiect tipic de înlocuire, tiparele de export din baza de date către registrul de lucru acoperă atât componenta, cât și bucla scrisă manual, iar odată ce numărul de rânduri ajunge la șase cifre, tehnicile de performanță pentru registre de lucru mari devin diferența dintre minute și secunde. Rapoartele construite din machete întreținute în designer sunt acoperite în ghidul de generare a rapoartelor din șabloane

HotXLS este livrat ca sursă Object Pascal pentru Delphi și C++Builder; edițiile, licențierea și referința API completă se află pe pagina de produs HotXLS Delphi Component