Générer des rapports Excel par modèle en Delphi

La façon fiable de produire un rapport Excel mis en forme depuis Delphi consiste à partir d'un classeur qu'un concepteur a déjà bâti. Quelqu'un à la finance dessine la facture dans Excel : le logo, les en-têtes de colonnes, les bordures du bandeau de détail, la ligne de totaux en gras, les formats monétaires. Votre code ouvre ce fichier, dépose des données vivantes dans les cellules que le concepteur leur a réservées, et enregistre le résultat. L'apparence est la sienne ; les nombres sont les vôtres. HotXLS, bibliothèque native pour Delphi et C++Builder qui lit et écrit des classeurs XLS et XLSX sans piloter Excel, vous donne les trois opérations dont cette approche a besoin : chercher une cellule par son texte, copier une plage avec ses styles et ses formules intacts, et insérer des lignes pour que tout ce qui se trouve dessous descende avec les données

La règle unique qui sépare un générateur survivant aux retouches de modèle de celui qui casse à la première est de ne jamais adresser les cellules par des numéros de ligne et de colonne littéraux. Un modèle est un document que d'autres personnes modifient. L'équipe financière ajoute une ligne de taxe, augmente la hauteur de la ligne du logo, réordonne le bloc d'adresse, et le format de fichier ne vous aide en rien : une sauvegarde BIFF ou OOXML réussit que la ligne 10 signifie encore ou non ce qu'elle signifiait au trimestre dernier. Un générateur qui écrit la première ligne de détail dans une ligne 10 codée en dur ira, la première fois que quelqu'un insère un bloc au-dessus de la section de détail, estamper des articles par-dessus les mauvaises cellules et totaliser une plage de SUM qui ne couvre plus les données. Rien ne lève d'exception, chaque sauvegarde renvoie un succès, et le seul signal est un client qui remarque une facture fausse

Ancrer chaque coordonnée sur un marqueur

Le remède consiste à faire porter au modèle ses propres coordonnées. Le concepteur écrit des marqueurs tels que {{CUSTOMER}}, {{DATE}} et {{DETAIL_START}} dans les cellules que le générateur doit toucher, et le générateur calcule chaque position à l'exécution à partir de l'endroit où il trouve ces marqueurs. Les retouches de mise en page ne comptent plus, car le marqueur se déplace avec la cellule qui le porte. La seconde moitié du contrat est la règle d'échec : si un marqueur obligatoire manque, le travail s'arrête avant qu'aucune donnée client n'atteigne le fichier. Un modèle qui a dérivé doit produire un ticket de travail en échec, pas un document livré

Trouver les marqueurs : FindText et ReplaceText

Les deux familles de classes de HotXLS exposent une recherche au niveau de la feuille. FindText renvoie la ligne et la colonne de la première cellule dont le texte correspond, avec une surcharge qui ajoute la sensibilité à la casse. ReplaceText échange chaque occurrence et renvoie le nombre de remplacements. Les deux couvrent les deux sortes de marqueurs que l'on a en général. Une ancre unique comme le nom du client, que vous localisez une fois et à côté de laquelle vous écrivez ; un marqueur censé apparaître exactement une fois, comme la date du rapport, que vous remplacez en contrôlant le compte. Côté XLSX, un remplissage qui s'ancre ainsi ressemble à ceci :

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  R, C: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open('invoice-template.xlsx') <> 1 then
      raise Exception.Create('Cannot open invoice template');
    Sheet := Book.Sheets[0];               // TXLSXSheets.Items est en base 0

    if not Sheet.FindText('{{CUSTOMER}}', R, C) then
      raise Exception.Create('Template drift: {{CUSTOMER}} anchor missing');
    Sheet.Cells[R, C].Value := 'ACME Corp';

    if Sheet.ReplaceText('{{DATE}}',
         FormatDateTime('yyyy-mm-dd', Date)) = 0 then
      raise Exception.Create('Template drift: {{DATE}} token missing');
    // l'expansion du détail et la sauvegarde suivent plus bas
  finally
    Book.Free;
  end;
end;

Deux détails comptent. D'abord, FindText et ReplaceText comparent la valeur texte d'une cellule ; un marqueur enfoui dans une chaîne de formule leur est invisible, si bien que les marqueurs ont leur place dans des cellules ordinaires, jamais à l'intérieur de formules. Ensuite, le nombre de remplacements est votre détecteur de dérive. Un modèle censé contenir exactement un marqueur {{DATE}} mais qui rapporte zéro remplacement a été modifié, et lever une exception à cet instant est précisément ce qui transforme une dérive de mise en page silencieuse en un échec visible

Cloner la ligne de détail sans perdre styles ni formules

La section de détail d'une facture grandit avec les données. Écrire des valeurs directement dans les lignes vides sous la ligne d'exemple jette tout ce que le concepteur avait préparé : les bordures, les formats de nombre, les formules ligne par ligne. Le motif qui conserve tout cela consiste à laisser dans le modèle une ligne d'exemple entièrement mise en forme et à la cloner pour chaque article. CopyRange duplique styles et formules en un seul appel, après quoi le générateur n'écrase que les cellules de valeurs

const
  DetailRow = 10;            // la ligne d'exemple mise en forme dans le modèle
var
  I: Integer;
begin
  // Ouvrir d'abord l'espace avant le bloc de totaux, pour que la plage SUM
  // sous le bandeau de détail s'étire en même temps que les données.
  if Length(Items) > 1 then
    Sheet.InsertRows(DetailRow + 1, Length(Items) - 1);

  for I := 0 to High(Items) do
  begin
    if I > 0 then              // cloner styles et formules depuis la ligne d'exemple
      Sheet.CopyRange(DetailRow, 1, DetailRow, 5, DetailRow + I, 1);
    Sheet.Cells[DetailRow + I, 1].Value := Items[I].Name;
    Sheet.Cells[DetailRow + I, 2].Value := Items[I].Qty;
    Sheet.Cells[DetailRow + I, 3].Value := Items[I].UnitPrice;
    Sheet.Cells[DetailRow + I, 4].Formula :=
      Format('B%d*C%d', [DetailRow + I, DetailRow + I]);  // pas de préfixe '='
  end;
end;

Observez de près l'affectation de formule. La propriété XLSX Formula prend l'expression sans signe égal initial, tandis que la façade XLS attend '=B10*C10' affecté par Value. Mélanger les deux conventions est l'erreur de portage la plus courante entre les familles de classes, et elle échoue sans se plaindre : la cellule contient simplement une chaîne littérale qu'Excel affiche comme du texte. Si le modèle décore le bandeau de détail avec des lignes de titre fusionnées, rappelez-vous que seule la cellule en haut à gauche d'une zone fusionnée porte une valeur. Les règles de mise en page exposées dans l'article compagnon sur les cellules fusionnées dans les modèles de rapport expliquent pourquoi les régions fusionnées ont leur place entièrement hors du bandeau de données

Ce que InsertRows déplace, et ce qu'il laisse derrière

Insérer des lignes devant le bloc de totaux est ce qui fait s'étirer une plage SUM à mesure que la section de détail grandit. Côté XLSX, InsertRows entraîne vers le bas une longue liste de structures dépendantes en même temps que les cellules : plages fusionnées, hauteurs de lignes, liens hypertexte, commentaires, volets figés, plages de filtre automatique, mises en forme conditionnelles, validations de données, tableaux, noms définis, ancrages d'images et de graphiques. Il y a dans cette liste une limite qui mérite d'être mémorisée. La réécriture des formules n'atteint que les références internes à la même feuille. Une formule d'une feuille de synthèse qui pointe dans la région déplacée garde ses anciennes coordonnées et lit discrètement les mauvaises cellules, ce qui explique pourquoi les totaux tirés d'une feuille à l'autre sont plus sûrs exprimés par des noms au niveau du classeur. L'article compagnon sur les noms définis et les formules inter-feuilles déroule ce motif

Le format XLS hérité trace la limite à un endroit plus dur. HotXLS garde les tableaux croisés dynamiques, les tables de requête et les connexions de données externes des fichiers BIFF sous forme de blocs d'octets bruts. Ils survivent inchangés à l'ouverture et à la sauvegarde, mais ils ne sont pas modélisés, si bien que l'insertion de lignes ne les touche jamais. Un modèle qui gare un tableau croisé sous un bloc de détail extensible s'enregistre sans le moindre avertissement pendant que le rectangle source du tableau croisé dérive hors des données. La sortie est structurelle, pas défensive : gardez le contenu de tableaux croisés et de requêtes sur des feuilles où le générateur n'insère jamais, et la péremption ne peut pas survenir

Recalculer avant la remise, ou savoir pourquoi vous vous en passez

HotXLS n'évalue pas les formules pendant SaveAs. Quand une personne ouvre le fichier, Excel recalcule tout (la façade XLS expose CalculationMode et RecalcOnSave si vous devez piloter cela), si bien qu'un rapport destiné à une boîte de réception humaine n'attend rien de plus de vous. Le tableau change dès que le classeur alimente un autre programme. L'export CSV écrit les formules sous forme de texte littéral et ne les calcule jamais, et tout analyseur en aval qui fait confiance aux valeurs mises en cache lira des nombres périmés ou des vides. Pour ces chemins, calculez sur le serveur avec Calculate, qui évalue une expression arbitraire sur le classeur chargé et vous rend le résultat :

var
  Total: Variant;
  LastDetail: Integer;
begin
  LastDetail := DetailRow + Length(Items) - 1;
  Total := Book.Calculate(Format('SUM(Invoice!D%d:D%d)',
    [DetailRow, LastDetail]));
  if (not VarIsNumeric(Total)) or
     (Abs(Total - ExpectedTotal) > 0.005) then
    raise Exception.Create('Invoice total does not match the order record');

  if Book.SaveAs('invoice-2026-0611.xlsx') <> 1 then
    raise Exception.Create('Save failed: check output path and permissions');
end;

Contrôler le total calculé face à l'enregistrement de commande avant la sauvegarde est une assurance bon marché au bon rendement. Elle transforme une facture fausse en travail en échec. Un opérateur peut relancer un travail en échec en quelques secondes ; une facture fausse déjà dans la boîte d'un client coûte à un chargé de compte des excuses et une correction

Deux familles de classes, un seul algorithme

La même logique se porte d'un format à l'autre, mais pas le même code. TXLSWorkbook pour le .xls hérité repose sur des interfaces à comptage de références, avec une indexation des feuilles en base 1, et vous ne le libérez jamais à la main. TXLSXWorkbook pour le .xlsx est un objet ordinaire que vous devez libérer dans un try..finally, avec une indexation des feuilles en base 0 et la convention de formule montrée plus haut. FindText, ReplaceText, CopyRange et InsertRows vivent des deux côtés, si bien que la forme ancrer-cloner-recalculer se transpose proprement. Le conseil pratique est de s'engager sur un format par pipeline, ou de cacher les deux cycles de vie d'objets derrière un mince adaptateur à vous plutôt que d'éparpiller la différence dans tout le générateur

La taille compte rarement pour le genre de rapport que produit ce motif. Cloner quelques milliers de fois une ligne mise en forme n'est rien pour le matériel actuel. Le chemin de sauvegarde ne devient le goulot d'étranglement que lorsqu'un bandeau de détail atteint six chiffres de lignes, et à ce moment-là poser StreamingWrite envoie le XML de la feuille droit dans le paquet de sortie au lieu de le mettre en tampon ; l'article sur les écritures en flux pour les traitements par lots serveur explique quand ce compromis en vaut la peine. Les graphiques se comportent comme le reste de la mise en page : côté XLSX, l'ancrage du graphique comme les références de ses séries se déplacent quand InsertRows agit au-dessus d'eux, si bien qu'un graphique sous la ligne de totaux reste lié aux bonnes données, tandis que côté XLS les graphiques siègent sur leurs propres feuilles de graphique et, comme les tableaux croisés, ne bougent jamais. Un argument de plus pour garder les feuilles de présentation à l'écart de la feuille que le générateur étend

Cette approche ancrer-cloner-recalculer laisse un concepteur maître de l'apparence d'un classeur tandis que votre code reste maître de ce qu'il dit, ce qui est généralement ce qui rend une sortie Excel générée digne d'être maintenue. Les appels de recherche, de copie et d'insertion montrés ici, ainsi que le moteur de formules utilisé pour le contrôle du total avant remise, sont livrés avec le HotXLS Delphi Component pour Delphi et C++Builder