HotXLS Delphi Component: Office-free workbook automation in Delphi

Se l'unico compito di un server è emettere file Excel, non ha alcuna ragione di eseguire Excel. Installare Office su un build agent o un servizio di reportistica per pilotarlo tramite automazione COM è la progettazione sbagliata, e lo è stata da quando questa pratica esiste. Lo dice Microsoft stessa, in linee guida che non si sono ammorbidite in vent'anni: Office non è costruito né concesso in licenza per essere automatizzato da un processo lato server incustodito. La risposta giusta è scrivere direttamente i byte BIFF e OOXML, senza alcun Excel nel quadro. Questa è l'intera premessa di HotXLS, una libreria Object Pascal nativa che legge e scrive essa stessa i formati per fogli di calcolo, così non c'è alcuna applicazione desktop da far bloccare, che perde memoria, o per cui pagare per postazione

Perché pilotare EXCEL.EXE da un servizio fallisce

L'automazione COM controlla da remoto un programma desktop, e un programma desktop presume silenziosamente tre cose che un servizio Windows non può fornirgli: un profilo utente caricato, una window station interattiva e un essere umano che guarda lo schermo. Togli queste cose e i fallimenti arrivano in una forma che nessuna macchina di sviluppo riproduce mai. Un prompt di ripristino file, un errore di un add-in, o una finestra di dialogo di attivazione della licenza si apre su un desktop che nessuno può vedere, e la chiamata di automazione che l'ha innescata non ritorna mai. Il chiamante alla fine va in timeout e muore; l'istanza di Excel spesso no, sopravvivendo come orfana che tiene lock sui file e avvelena l'esecuzione successiva. Chiunque abbia visto undici processi EXCEL.EXE randagi accumularsi sotto un account di servizio conosce il resto di questa storia

La storia della scalabilità non è migliore nemmeno quando nulla va in crash. Un'istanza di Excel è una pipeline a singola cartella di lavoro, ogni accesso a proprietà paga il costo del marshaling COM tra processi, e la macchina che esegue il codice porta una licenza Office i cui termini escludono esattamente questo uso. La maggior parte dei team incontra questi limiti un'interruzione alla volta, il che è più o meno come "ritirare il layer COM" finisce su una roadmap

Prima che quella riscrittura inizi, risolvi una questione di ambito, perché decide quanto del lavoro sia reale. Il codice COM quasi mai si limita a impostare valori di cella. Chiama Workbook.SaveAs con costanti di formato, forza il ricalcolo, imposta la configurazione di stampa, a volte ricorre agli appunti. Percorri il vecchio codice e annota quali di questi comportamenti finiscano davvero nell'output, dato che ciascuno atterra in un angolo diverso di una libreria nativa, e un paio di essi (l'interoperabilità con gli appunti è quello ovvio) non hanno alcun significato lato server e andrebbero eliminati invece che portati

Due motori nativi, due modelli di proprietà

HotXLS sostituisce il processo Excel con due implementazioni dirette del formato. Un motore a flusso di record BIFF8 (TXLSWorkbook, unit lxHandle) gestisce .xls. Uno scrittore di pacchetti OOXML (TXLSXWorkbook, unit lxHandleX) produce .xlsx conforme a ECMA-376 / ISO/IEC 29500. Non c'è nulla da registrare e nulla da installare sul server, e puoi tenere aperte tante cartelle di lavoro contemporaneamente quante ne permette la memoria

Ciò che inganna presto le persone è che le due facciate possiedono la loro memoria in modo diverso, e la differenza è silenziosa finché non va in crash:

var
  Book: IXLSWorkbook;          // riferimento a interfaccia: rilasciato automaticamente
  Sheet: IXLSWorksheet;
  BookX: TXLSXWorkbook;        // oggetto semplice: lo liberi tu
  SheetX: TXLSXWorksheet;
begin
  // output BIFF8 .xls - nessun Free; il refcount dell'interfaccia lo possiede
  Book := TXLSWorkbook.Create;
  Sheet := Book.Sheets.Add;
  Sheet.Name := 'Report';
  Sheet.Cells.Item[1, 1].Value := 'Generated without Excel';
  Book.SaveAs('report.xls');

  // output OOXML .xlsx - ciclo di vita esplicito
  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;

La facciata XLS è a conteggio di riferimenti tramite l'interfaccia IXLSWorkbook. Dichiara la variabile come tipo interfaccia e non chiamare mai Free su di essa; tieni lo stesso oggetto in una variabile oggetto semplice e liberalo tu stesso, e il refcount lo libera una seconda volta. La facciata XLSX è un oggetto ordinario che vuole un ordinario try..finally. L'indirizzamento delle celle è a base 1 su entrambi i lati, l'unico punto su cui i due concordano. Le collezioni di fogli no: Entries sul lato XLS è a base 1, l'indicizzatore Items di XLSX è a base 0, e quell'off-by-one compila senza problemi in qualunque modo tu lo sbagli e si manifesta solo a runtime

Scrivere una cartella di lavoro direttamente in una risposta HTTP

Un'esportazione lato server di solito non ha motivo di toccare il disco. I file temporanei richiedono una politica di pulizia, collidono sotto richieste concorrenti, e lasciano dati cliente su volumi che nessuno ha pensato di auditare. Entrambe le facciate accettano un TStream tramite i loro overload di SaveAs, così la cartella di lavoro può finire direttamente nella risposta:

Mem := TMemoryStream.Create;
Book := TXLSXWorkbook.Create;
try
  Sheet := Book.Sheets.Add('Data');
  Sheet.Cells[1, 1].Value := 'Generated ' + DateTimeToStr(Now);
  Book.SaveAs(Mem);          // scrive dalla posizione ATTUALE dello stream
  Mem.Position := 0;         // riavvolgi prima di consegnare lo stream
  Response.ContentType :=
    'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
  Response.ContentStream := Mem;   // ora il framework possiede Mem
finally
  Book.Free;
end;

Il riavvolgimento è la riga che si guadagna il suo commento. SaveAs(Stream) scrive dalla posizione corrente dello stream e non torna mai a zero dopo. Dimentica Mem.Position := 0 e il client ottiene un download di zero byte, oppure Excel dichiara il file corrotto. Questo è il bug più comune nel codice per cartelle di lavoro rivolto al web, ed è il più crudele, perché passa indenne qualsiasi unit test che si limiti ad asserire che lo stream ha lunghezza diversa da zero

Un'unica routine di costruzione della cartella di lavoro raggiunge ogni altro formato di consegna senza ristrutturazioni. SaveAsCSV risponde alla richiesta "dammi solo i dati grezzi", SaveAsHTML gestisce "mettilo in una pagina del portale," SaveAsRTF alimenta pipeline documentali, e SaveAsODS copre un mandato OpenDocument, tutti con overload sia per file sia per stream. Un'unica routine di esportazione più un parametro di formato sostituisce quelle che tendevano a essere quattro macro COM separate. TXLSXHtmlExportOptions dell'esportatore HTML porta titolo, classe CSS e un interruttore frammento-o-documento-completo, il che tiene il caso del portale fuori dal business di modificare con regex il markup esportato

Valori di formula senza un processo Excel che li calcoli

Sotto l'automazione COM, Excel ricalcolava tutto gratuitamente, e abbandonare COM revoca silenziosamente questo. SaveAs memorizza le formule come testo senza valutarle; i numeri compaiono solo quando Excel apre il file e ricalcola, un comportamento che la facciata XLS ti lascia regolare tramite RecalcOnSave e CalculationMode. Per un file destinato a una persona questo è esattamente corretto. È sbagliato per un servizio che deve confermare un totale prima di spedirlo, ed è sbagliato per l'esportazione CSV, che scrive il testo della formula invece del suo risultato. In entrambi i casi bisogna valutare sul server con il motore integrato:

SheetX.Cells[1, 1].Value := 1200;
SheetX.Cells[2, 1].Value := 950;
SheetX.Cells[3, 1].Formula := 'SUM(A1:A2)';   // facciata XLSX: nessun prefisso '='
Total := BookX.Calculate('SUM(A1:A2)');       // valuta sul server, ora
if Total <> 2150 then
  raise Exception.Create('reconciliation failed before delivery');

La convenzione delle facciate morde di nuovo qui. Il lato XLSX assegna le espressioni tramite Cell.Formula senza segno di uguale; il lato XLS le scrive tramite Cell.Value con un '=' iniziale. Porta il codice da uno all'altro senza modifiche e la convenzione sbagliata memorizza una stringa di testo che assomiglia solo a una formula, senza alcun errore a segnalarlo. Quando le formule di una cartella di lavoro devono raggiungere la tua logica di business, il callback OnUserFunction lascia che il motore passi i nomi di funzione sconosciuti al codice Delphi al momento della valutazione. Questo è il sostituto nativo per gli add-in UDF che tendono a nascondersi proprio dentro i fogli di calcolo attorno a cui è cresciuto un sistema di automazione COM

Spigoli di distribuzione che emergono solo sul server

Alcuni dettagli decidono se il rollout è pulito o rompicapo, e il primo è il grafo delle unit. L'esportatore di dataset drag-and-drop TDataToXLS importa Forms, Controls e Dialogs della VCL. Innocuo in uno strumento desktop; in un servizio console trascina dietro di sé l'intera VCL. Le unit principali lxHandle e lxHandleX ricorrono solo a Windows, Classes, SysUtils e Variants, quindi un servizio puro fa meglio a scrivere il proprio ciclo sul dataset contro l'API principale piuttosto che importare il componente per comodità

Poi c'è il threading. Le istanze di cartella di lavoro non sono thread-safe, ma non condividono nemmeno stato globale, quindi il pattern che scala è il più semplice: un oggetto cartella di lavoro per job, oppure per worker thread. Questo compra la generazione parallela di report, cosa che una singola istanza Excel condivisa non può mai fare. Un gestore di richieste che crea, riempie, salva e libera la propria cartella di lavoro non ha bisogno di alcun lock, e il raggio di danno di un fallimento collassa da "l'istanza Excel condivisa è bloccata per tutti" a "questa singola richiesta ha sollevato un'eccezione," cosa che la tua gestione degli errori esistente già sa come trattare

L'ultimo di questi è il targeting del formato. TXLSWorkbook.SaveAs scrive BIFF (xlExcel97) per default, e spingere contenuto XLS in .xlsx passa per il ponte SaveXLSWorkbookAsXLSX a fedeltà ridotta. Scegli la facciata in base al formato che intendi spedire, in fase di progettazione, invece di costruire in una e convertire alla fine della pipeline

Per la metà di caricamento dati di un tipico progetto di sostituzione, i pattern di esportazione da database a cartella di lavoro coprono sia il componente sia il ciclo scritto a mano, e una volta che il conteggio delle righe raggiunge le sei cifre le tecniche di prestazione per cartelle di lavoro di grandi dimensioni diventano la differenza tra minuti e secondi. I report costruiti a partire da layout mantenuti da un designer sono trattati nella guida alla generazione di report da template

HotXLS è distribuito come codice sorgente Object Pascal per Delphi e C++Builder; edizioni, licenze e il riferimento API completo si trovano sulla pagina prodotto di HotXLS Delphi Component