Exporter des jeux de données Delphi vers Excel avec HotXLS

Transformer le résultat d'une requête en rapport Excel, ce sont trois problèmes sous un même manteau. Chaque type de champ Delphi doit atterrir dans une cellule avec le bon type Excel, la ligne d'en-tête doit se lire comme un rapport et non comme un vidage de schéma, et les nombres, les dates et les montants doivent porter des formats qui survivent au trajet. Négligez-en un seul et le fichier s'ouvre quand même, paraît toujours plausible, et échoue à la seconde où un utilisateur de la finance sélectionne une colonne et attend une somme qui ne vient jamais. Les valeurs ont été écrites en texte, Excel les traite comme des libellés, et aucune exception n'a jamais été levée pour vous prévenir

HotXLS est une bibliothèque tableur native en Object Pascal qui écrit des fichiers XLS et XLSX directement depuis Delphi et C++Builder, sans aucune automation Excel. Elle offre deux voies pour aller d'un TDataset à un classeur : le composant clé en main TDataToXLS, et une boucle écrite à la main sur l'API du classeur. Elles ne sont pas interchangeables. Le composant est un citoyen de la VCL bâti sur la façade XLS, si bien que le bon choix dépend de l'endroit où le code s'exécute et du format de fichier attendu par le consommateur. Ce qui suit présente les deux voies, la limite où le composant cesse d'être le bon outil, et comment préserver les types de champs quelle que soit la voie choisie

Les types de champs sont le véritable contrat d'export

Avant tout appel d'API, décidez comment chaque type de champ Delphi atterrit dans une cellule. Une cellule qui reçoit une chaîne Delphi reste une chaîne. HotXLS ne devine pas que '1,234.50' était censé être un nombre, et il ne le doit pas, car la réanalyse dépendante de la locale est exactement ce qui transforme une virgule décimale allemande en séparateur de milliers sur un serveur anglais. Le motif fiable consiste à affecter par les accesseurs typés : AsFloat ou AsCurrency pour les champs numériques, AsDateTime pour les dates afin que la cellule contienne un véritable numéro de série de date Excel plutôt qu'une chaîne formatée, et AsString uniquement pour les champs qui sont réellement du texte

La gestion des valeurs nulles mérite une décision explicite plutôt qu'un comportement par défaut. Convertir la valeur d'un champ avec VarToStr transforme un NULL SQL en chaîne vide, c'est-à-dire en cellule de texte, alors que ne pas faire l'affectation laisse la cellule réellement vide, ce qu'attendent AVERAGE, COUNT et les consommateurs de tableaux croisés dynamiques. Pour les colonnes monétaires, décidez avant d'écrire la boucle si NULL signifie zéro ou inconnu. Les deux s'affichent à l'identique dès que quelqu'un met la colonne en forme, et la différence change tous les agrégats calculés en aval

La voie du composant : TDataToXLS dans les applications VCL

Pour une application VCL classique dont la requête est déjà câblée dans un data module, TDataToXLS est la voie en un seul appel. Il parcourt n'importe quel descendant de TDataset, qu'il s'agisse de FireDAC, ADO, IBX ou de tout autre composant qui implémente l'interface abstraite de dataset, et produit une feuille de calcul mise en forme avec des libellés d'en-tête, des polices, des bordures, des sous-totaux de groupe optionnels et un découpage automatique en feuilles pour les grands jeux de résultats

var
  Exporter: TDataToXLS;
begin
  Exporter := TDataToXLS.Create(nil);
  try
    Exporter.Dataset := OrdersQuery;          // tout descendant de TDataset
    Exporter.WorksheetName := 'Orders';
    Exporter.HeaderSource := hsDisplayLabel;  // les libellés, pas les noms de colonnes bruts
    Exporter.GroupFields.Add('CustomerID');   // un bloc de sous-total par client
    Exporter.RowsPerSheet := 50000;           // rester sous le plafond de lignes BIFF8
    Exporter.VisibleFieldsOnly := True;             // respecter Field.Visible
    Exporter.SaveDatasetAs('orders.xls');
  finally
    Exporter.Free;
  end;
end;

Deux propriétés portent ici l'essentiel du poids en production. HeaderSource := hsDisplayLabel écrit le DisplayLabel de chaque champ au lieu du nom de colonne SQL brut, si bien que le classeur affiche "Customer Name" plutôt que CUST_NM. RowsPerSheet existe parce que le composant écrit du BIFF8, dont la grille s'arrête à 65 536 lignes sur 256 colonnes ; le régler à 50 000 répartit un grand jeu de résultats sur plusieurs feuilles avant que le plafond du format ne le tronque. L'apparence est pilotée par les propriétés HeaderFont, DetailFont, GroupColor et les styles de bordure, et l'ensemble DisableFormat désactive des catégories entières de mise en forme quand le consommateur veut des cellules brutes. Pour tout traitement sur mesure, les événements AfterCell et AfterRow vous remettent la plage qui vient d'être écrite

Là où le composant s'arrête

Trois contraintes sont inscrites par conception dans TDataToXLS, et les connaître d'emblée évite une refonte pénible deux sprints plus tard

• C'est un composant VCL au sens plein. Son unité tire Forms, Controls et Dialogs, si bien que le lier dans un traitement console ou un service Windows entraîne la VCL dans le binaire. Les unités de classeur du noyau n'ont pas cette dépendance. Elles ne demandent que Windows, Classes, SysUtils et Variants, ce qui explique pourquoi le code côté serveur doit utiliser la boucle montrée plus bas
• Il est bâti sur la façade XLS. Le composant remplit un IXLSWorkbook et écrit du .xls (BIFF8). Aucune propriété ne le bascule vers une sortie OOXML
• Ses événements parlent le dialecte XLS. Le paramètre Cell: IXLSRange de AfterCell appartient au modèle objet XLS, donc la personnalisation cellule par cellule écrite là est du code de style XLS même si le fichier est converti en .xlsx ensuite

Produire du .xlsx à partir de la sortie du composant

Lorsque le consommateur exige du .xlsx alors que la logique d'export vit déjà dans TDataToXLS, la fonction passerelle de l'unité lxXlsxExport convertit le classeur rempli en un seul appel :

uses lxXlsxExport;

Exporter.SaveDatasetAs('orders.xls');
// le composant expose le IXLSWorkbook qu'il a rempli
SaveXLSWorkbookAsXLSX(Exporter.Workbook, 'orders.xlsx');

Traitez la passerelle comme un transporteur de données tabulaires, pas comme un convertisseur haute fidélité. Elle copie les valeurs, les formules, les formats de nombre, les couleurs de remplissage, les attributs de police, les largeurs de colonnes et les réglages de vue. Elle ne copie délibérément pas les bordures, les plages fusionnées, les commentaires, les graphiques ni les mises en forme conditionnelles. Pour une grille plate faite d'un en-tête et de lignes, c'est exactement suffisant. Pour un rapport mis en forme, non, et le correctif honnête consiste à générer le XLSX directement plutôt qu'à rafistoler le fichier converti

La boucle écrite à la main pour les services et les traitements par lots

Le code côté serveur doit viser directement TXLSXWorkbook. Notez la différence de durée de vie entre les deux façades avant de copier le moindre exemple. Côté XLS, TXLSWorkbook est détenu par une interface à comptage de références et ne doit pas être libéré manuellement, tandis que TXLSXWorkbook est une classe ordinaire qui exige un try..finally Free. Mélanger les deux conventions est un moyen fiable de fabriquer soit une fuite, soit une double libération

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;  // diffuser le XML de la feuille droit dans le zip
    Book.SaveAs(FileName);
  finally
    Book.Free;
  end;
end;

Les lignes qui comptent sont les affectations typées et la garde IsNull. Les dates arrivent sous forme de numéros de série, les montants sous forme de doubles, et les dates de commande NULL restent réellement vides au lieu de devenir des chaînes vides. StreamingWrite := True ne change que le chemin de sauvegarde : le XML de la feuille est diffusé droit dans le conteneur zip au lieu d'être assemblé d'abord en une seule grande chaîne, ce qui aplatit le pic de mémoire au moment du SaveAs pour des comptes de lignes à six chiffres. Chaque méthode de sauvegarde possède aussi une surcharge TStream, si bien que le classeur peut aller droit dans une réponse HTTP sans toucher le disque. L'article sur l'écriture en flux et les traitements par lots détaille ce modèle de déploiement, et l'article sur les performances des grands classeurs couvre la marche à suivre quand le nombre de lignes grimpe encore

Cette boucle est aussi la voie qui passe à l'échelle sur plusieurs threads. Les deux moteurs sont des écrivains natifs en Object Pascal, flux d'enregistrements BIFF8 d'un côté, zip OOXML plus XML de l'autre, si bien qu'aucune partie d'un export ne touche à l'automation COM ni n'exige de licence Excel sur le serveur. Ce que cela vous apporte, c'est du parallélisme sans goulot d'étranglement à instance unique, à condition que chaque thread construise son propre classeur. Les objets classeur ne sont pas thread-safe pour un usage partagé, donc la règle est une instance par export, jamais une instance partagée protégée par un verrou

Une limite mérite d'être connue avant de concevoir autour d'elle. La grille XLSX s'arrête à 1 048 576 lignes sur 16 384 colonnes, si bien que le découpage en feuilles géré par RowsPerSheet côté XLS est rarement nécessaire ici. Un classeur d'un million de lignes est de toute façon rarement ce que veut un consommateur humain. Lorsque le jeu de résultats est réellement de cette taille, un fichier délimité constitue généralement le meilleur contrat, et l'article sur l'export CSV et TSV couvre les délimiteurs, le comportement du BOM et la réserve sur l'évaluation des formules qui s'y applique

Choisir un point de départ

Si l'export vit dans un outil bureautique VCL et que la sortie .xls est acceptable, commencez par TDataToXLS et sa prise en charge des groupes. C'est le moins de code, et la passerelle SaveXLSWorkbookAsXLSX est là quand quelqu'un réclamera plus tard du .xlsx, tant que vous acceptez les limites de fidélité déjà décrites. Si le code s'exécute sans surveillance, ou si le consommateur exige du .xlsx dès le départ, écrivez la boucle. Les deux voies sont livrées avec des projets de démonstration fonctionnels et font partie du paquet HotXLS Delphi Component