HotXLS: database export to spreadsheet reports in Delphi

Trasformare il risultato di una query in un report Excel è in realtà tre problemi travestiti da uno solo. Ogni tipo di campo Delphi deve atterrare in una cella come il tipo Excel corretto, la riga di intestazione deve leggersi come un report e non come un dump dello schema, e numeri, date e importi devono portare con sé formati che sopravvivano al viaggio. Trascurane anche solo uno e il file continua ad aprirsi, continua a sembrare plausibile, e fallisce comunque nel momento in cui un utente finance seleziona una colonna e attende una somma che non compare mai. I valori sono stati scritti come testo, Excel li tratta come etichette, e nessuna eccezione è mai stata sollevata per avvisarti

HotXLS è una libreria Object Pascal nativa per fogli di calcolo che scrive file XLS e XLSX direttamente da Delphi e C++Builder, senza alcuna automazione di Excel. Offre due percorsi da un TDataset a una cartella di lavoro: il componente pronto all'uso TDataToXLS, e un ciclo scritto a mano contro l'API della cartella di lavoro. Non sono intercambiabili. Il componente è un cittadino VCL costruito sulla facciata XLS, quindi la scelta giusta dipende da dove gira il codice e quale formato di file si aspetta il consumatore. Quanto segue copre entrambi i percorsi, il punto in cui il componente smette di essere lo strumento giusto, e come mantenere intatti i tipi di campo qualunque sia la scelta

I tipi di campo sono il vero contratto di esportazione

Prima di qualsiasi chiamata API, decidi come ogni tipo di campo Delphi debba atterrare in una cella. Una cella che riceve una stringa Delphi resta una stringa. HotXLS non indovina che '1,234.50' fosse pensato come un numero, e non dovrebbe farlo, perché una reinterpretazione dipendente dalla locale è esattamente il modo in cui una virgola decimale tedesca si trasforma in un separatore delle migliaia su un server inglese. Lo schema affidabile è assegnare tramite gli accessor tipizzati: AsFloat o AsCurrency per i campi numerici, AsDateTime per le date così che la cella contenga un vero numero seriale di data Excel invece di una stringa formattata, e AsString solo per i campi che sono davvero testo

La gestione dei valori null merita una decisione esplicita, non un comportamento predefinito. Convertire un valore di campo con VarToStr trasforma un NULL SQL in una stringa vuota, che è una cella di testo, mentre saltare l'assegnazione lascia la cella davvero vuota, che è ciò che si aspettano AVERAGE, COUNT e i consumatori di tabelle pivot. Per le colonne monetarie, decidi prima di scrivere il ciclo se NULL significhi zero oppure sconosciuto. Le due situazioni appaiono identiche una volta che qualcuno formatta la colonna, e la differenza cambia ogni aggregato calcolato a valle

Il percorso del componente: TDataToXLS nelle applicazioni VCL

Per una classica applicazione VCL con una query già collegata a un data module, TDataToXLS è il percorso a chiamata singola. Percorre qualsiasi discendente di TDataset, che sia FireDAC, ADO, IBX o qualsiasi altra cosa implementi l'interfaccia astratta del dataset, e produce un foglio di lavoro stilizzato con didascalie di intestazione, font, bordi, subtotali di gruppo opzionali e suddivisione automatica in più fogli per i result set di grandi dimensioni

var
  Exporter: TDataToXLS;
begin
  Exporter := TDataToXLS.Create(nil);
  try
    Exporter.Dataset := OrdersQuery;          // qualsiasi discendente di TDataset
    Exporter.WorksheetName := 'Orders';
    Exporter.HeaderSource := hsDisplayLabel;  // didascalie, non nomi di colonna grezzi
    Exporter.GroupFields.Add('CustomerID');   // blocco di subtotale per cliente
    Exporter.RowsPerSheet := 50000;           // resta sotto il limite di righe BIFF8
    Exporter.VisibleFieldsOnly := True;             // rispetta Field.Visible
    Exporter.SaveDatasetAs('orders.xls');
  finally
    Exporter.Free;
  end;
end;

Due proprietà comportano la maggior parte del peso in produzione qui. HeaderSource := hsDisplayLabel scrive il DisplayLabel di ciascun campo invece del nome di colonna SQL grezzo, così la cartella di lavoro riporta "Customer Name" invece di CUST_NM. RowsPerSheet esiste perché il componente scrive BIFF8, la cui griglia si ferma a 65.536 righe per 256 colonne; impostarlo a 50.000 suddivide un result set di grandi dimensioni su più fogli prima che il limite del formato lo tronchi. L'aspetto è gestito dalle proprietà HeaderFont, DetailFont, GroupColor e dagli stili di bordo, e l'insieme DisableFormat disattiva intere categorie di formattazione quando il consumatore vuole celle semplici. Per qualsiasi personalizzazione, gli eventi AfterCell e AfterRow ti passano l'intervallo appena scritto per la post-elaborazione

Dove si ferma il componente

Tre vincoli sono integrati per progettazione in TDataToXLS, e conoscerli in anticipo evita una riprogettazione scomoda due sprint più tardi

• È un componente VCL nel senso pieno del termine. La sua unit importa Forms, Controls e Dialogs, quindi collegarlo in un job da console o in un servizio Windows trascina la VCL nell'eseguibile. Le unit principali della cartella di lavoro non hanno questa dipendenza. Necessitano solo di Windows, Classes, SysUtils e Variants, motivo per cui il codice lato server dovrebbe invece usare il ciclo mostrato di seguito
• È costruito sulla facciata XLS. Il componente popola una IXLSWorkbook e scrive .xls (BIFF8). Non esiste alcuna proprietà che lo commuti sull'output OOXML
• I suoi eventi parlano il dialetto XLS. Il parametro Cell: IXLSRange in AfterCell appartiene al modello a oggetti XLS, quindi la personalizzazione per cella scritta lì è codice in stile XLS anche se il file viene poi convertito in .xlsx

Produrre .xlsx a partire dall'output del componente

Quando il consumatore richiede .xlsx ma la logica di esportazione risiede già in TDataToXLS, la funzione ponte nella unit lxXlsxExport converte la cartella di lavoro popolata con una sola chiamata:

uses lxXlsxExport;

Exporter.SaveDatasetAs('orders.xls');
// il componente espone la IXLSWorkbook che ha popolato
SaveXLSWorkbookAsXLSX(Exporter.Workbook, 'orders.xlsx');

Considera il ponte come un veicolo per dati tabellari, non come un convertitore a piena fedeltà. Copia valori, formule, formati numerici, colori di riempimento, attributi dei font, larghezze di colonna e impostazioni di visualizzazione. Deliberatamente non copia bordi, intervalli uniti, commenti, grafici o formattazioni condizionali. Per una griglia piatta di intestazione più righe è esattamente sufficiente. Per un report stilizzato non lo è, e la soluzione onesta è generare direttamente l'XLSX invece di correggere il file convertito

Il ciclo scritto a mano per servizi e job batch

Il codice lato server dovrebbe puntare direttamente a TXLSXWorkbook. Nota la differenza di ciclo di vita tra le due facciate prima di copiare qualsiasi esempio. Il TXLSWorkbook lato XLS è gestito tramite un'interfaccia a conteggio di riferimenti e non va liberato manualmente, mentre TXLSXWorkbook è una classe semplice che richiede try..finally Free. Mescolare le due convenzioni è un modo affidabile per produrre una perdita di memoria oppure un double-free

procedure ExportOrders(Q: TDataSet; const FileName: string);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Orders');
    Sheet.Cells[1, 1].Value := 'Order No';
    Sheet.Cells[1, 2].Value := 'Customer';
    Sheet.Cells[1, 3].Value := 'Ordered';
    Sheet.Cells[1, 4].Value := 'Amount';

    Row := 2;
    Q.First;
    while not Q.Eof do
    begin
      Sheet.Cells[Row, 1].Value := Q.FieldByName('OrderNo').AsInteger;
      Sheet.Cells[Row, 2].Value := Q.FieldByName('Customer').AsString;
      if not Q.FieldByName('Ordered').IsNull then
        Sheet.Cells[Row, 3].Value := Q.FieldByName('Ordered').AsDateTime;
      Sheet.Cells[Row, 4].Value := Q.FieldByName('Amount').AsFloat;
      Inc(Row);
      Q.Next;
    end;

    Book.StreamingWrite := True;  // invia in streaming l'XML del foglio direttamente nello zip
    Book.SaveAs(FileName);
  finally
    Book.Free;
  end;
end;

Le righe che contano sono le assegnazioni tipizzate e la guardia IsNull. Le date arrivano come numeri seriali di data, gli importi arrivano come double, e le date d'ordine NULL restano davvero vuote invece di diventare stringhe vuote. StreamingWrite := True cambia solo il percorso di salvataggio: l'XML del foglio di lavoro viene inviato in streaming direttamente nel contenitore zip invece di essere prima assemblato come un'unica grande stringa, il che appiattisce il picco di memoria al momento di SaveAs per conteggi di righe a sei cifre. Ogni metodo di salvataggio ha anche un overload TStream, così la cartella di lavoro può finire direttamente in una risposta HTTP senza toccare il disco. L'articolo sulla scrittura in streaming e i job batch illustra quel pattern di distribuzione, e l'articolo sulle prestazioni delle cartelle di lavoro di grandi dimensioni tratta cosa fare quando il numero di righe cresce ulteriormente

Questo ciclo è anche il percorso che scala su più thread. Entrambi i motori sono writer Object Pascal nativi, flussi di record BIFF8 da un lato e zip OOXML più XML dall'altro, quindi nessuna parte di un'esportazione tocca l'automazione COM o richiede una licenza Excel sul server. Ciò che ottieni è il parallelismo senza un collo di bottiglia a istanza singola, purché ogni thread costruisca la propria cartella di lavoro. Gli oggetti cartella di lavoro non sono thread-safe per un uso condiviso, quindi la regola è un'istanza per ogni esportazione, mai una condivisa protetta da un lock

Vale la pena conoscere un limite prima di progettare attorno a esso. La griglia XLSX si ferma a 1.048.576 righe per 16.384 colonne, quindi la suddivisione in fogli che RowsPerSheet gestisce sul lato XLS qui è raramente necessaria. Anche una cartella di lavoro da un milione di righe è raramente ciò che vuole un consumatore umano. Quando il result set è davvero così grande, un file delimitato è di solito il contratto migliore, e l'articolo sull'esportazione CSV e TSV tratta i delimitatori, il comportamento del BOM e l'avvertenza sulla valutazione delle formule che si applica in quel caso

Scegliere un punto di partenza

Se l'esportazione vive in uno strumento desktop VCL e l'output .xls è accettabile, inizia con TDataToXLS e il suo supporto al raggruppamento. È la quantità minima di codice, e il ponte tramite SaveXLSWorkbookAsXLSX è lì per quando qualcuno chiederà .xlsx in seguito, purché tu accetti i limiti di fedeltà già descritti. Se il codice gira incustodito, o il consumatore richiede .xlsx fin dall'inizio, scrivi il ciclo. Entrambi i percorsi includono progetti demo funzionanti e fanno parte del pacchetto HotXLS Delphi Component