Moteur de formules HotXLS et fonctions personnalisées dans Delphi

Un service de reporting écrit SUM(B2:B501) dans une cellule de total, enregistre le classeur, puis exporte la même feuille en CSV pour un importeur aval. Le .xlsx s'ouvre parfaitement dans Excel, totaux compris — pourtant le CSV contient le texte littéral =SUM(B2:B501) là où un nombre devrait se trouver. Rien n'est cassé. HotXLS, comme les formats de fichier eux-mêmes, traite une formule comme du texte stocké plus un résultat mis en cache optionnel : Excel évalue les formules quand il ouvre le fichier, mais un exporteur CSV reproduit le contenu des cellules tel qu'il existe, et personne dans une chaîne purement serveur n'a lancé le calcul. La correction est précisément le sujet de cet article — un moteur de formules intégré que vous invoquez explicitement et que vous pouvez étendre avec vos propres fonctions.

HotXLS est une bibliothèque native Object Pascal pour lire et écrire des fichiers XLS et XLSX depuis Delphi et C++Builder sans automatisation Excel, et son moteur de calcul fonctionne de la même manière dans les deux façades.

Les formules sont stockées, pas évaluées immédiatement

Écrire une formule dans une cellule ne calcule rien. À l'enregistrement, le classeur consigne le texte de formule et, côté XLS, des indicateurs gouvernés par RecalcOnSave, qui vaut True par défaut et demande à Excel de recalculer à l'ouverture. Ce modèle est correct pour les fichiers destinés à Excel, et faux pour les chaînes qui consomment directement les valeurs de cellules — export CSV, export HTML ou votre propre code qui relit les cellules. Pour ces cas, évaluez explicitement avec Calculate, disponible à quatre points d'entrée : TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook et TXLSXWorksheet exposent tous function Calculate(const Formula: WideString): Variant.

// evaluate in-process, then ship the value rather than the recipe
Total := Book.Calculate('SUM(Sales!B2:B501)');
Sheet.Cells[502, 2].Value := Total;
Book.SaveAsCSV('sales.csv', 0, ',');   // the CSV now carries the number

L'expression remise à Calculate est du texte de formule Excel ordinaire — références interfeuilles, noms définis et fonctions imbriquées se résolvent tous contre le classeur en mémoire. Cela la rend utile bien au-delà de la correction des exports CSV : c'est un mécanisme d'assertion. Un générateur qui vient d'écrire cinq cents lignes de détail peut demander au classeur son propre total général et le comparer au chiffre calculé indépendamment en Pascal, ce qui capture les erreurs de plage off-by-one avant l'auditeur d'un client.

Cela définit aussi la bonne stratégie de test pour les sorties riches en formules. Excel reste l'implémentation de référence du langage de formules ; pour les quelques formules qui portent des conséquences métier, conservez donc un fichier fixture approuvé dont les valeurs attendues ont été produites par Excel lui-même, et faites évaluer au pipeline de build les formules du classeur généré avec Calculate contre ces fixtures. Les différences ressortent alors comme des tests Delphi en échec plutôt que comme des écarts découverts par un client comparant deux rapports.

Ajouter des fonctions métier avec OnUserFunction

Lorsque le moteur rencontre un nom de fonction qu'il ne reconnaît pas, il déclenche un événement au lieu d'échouer immédiatement. Affectez OnUserFunction sur l'une ou l'autre classe de classeur et vous pouvez résoudre l'appel vous-même :

procedure TReportBuilder.HandleUserFunction(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'DISCOUNT') then
  begin
    Value := Args[0] * 0.9;   // Args arrives as a Variant array
    Handled := True;
  end;
end;

// wiring and use
Book.OnUserFunction := HandleUserFunction;
Sheet.Cells[1, 1].Value := 200;
Sheet.Cells[1, 2].Formula := 'DISCOUNT(A1)';
Net := Book.Calculate('DISCOUNT(A1) + SUM(A1:A1)');

Trois détails méritent l'attention. D'abord, définissez Handled := True seulement lorsque vous avez réellement reconnu le nom — le laisser à False permet au moteur de poursuivre son traitement normal des fonctions inconnues, si bien qu'un même handler peut servir plusieurs classeurs sans tout revendiquer. Ensuite, comparez les noms sans tenir compte de la casse (SameText), puisque les auteurs de formules saisissent discount( et DISCOUNT( indifféremment. Enfin, les arguments arrivent déjà évalués : DISCOUNT(A1) vous remet la valeur de A1, pas la référence ; une fonction ne peut donc pas savoir d'où viennent ses entrées, ce qui mène directement à la section suivante.

Traitez le corps du handler avec la même prudence que toute entrée externe. Le tableau Args reflète ce que l'auteur de formule a saisi ; validez donc le nombre et les types d'arguments avant d'y indexer, et décidez ce qu'un appel invalide renvoie — une valeur d'erreur Variant ou une exception levée. Une exception lancée dans le handler remonte par l'appel Calculate qui a déclenché l'évaluation, ce qui convient dans un générateur étroitement contrôlé mais devient brutal dans un service évaluant des classeurs écrits par des utilisateurs ; dans ce contexte, capturez dans le handler et renvoyez un marqueur que le flux environnant saura reconnaître et journaliser.

Les fonctions sensibles à la position ont besoin de la variante Ex

Certaines fonctions dépendent légitimement de l'endroit où elles sont évaluées — un taux qui diffère par feuille, une recherche relative à la ligne, un multiplicateur par région sur des feuilles régionales. L'événement simple ne peut pas exprimer cela ; le moteur propose donc OnUserFunctionEx, identique sauf pour un paramètre supplémentaire :

procedure TReportBuilder.HandleUserFunctionEx(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  const Context: TXLSUserFunctionContext;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'REGIONRATE') then
  begin
    // the same formula yields a different rate on each regional sheet
    Value := RateForSheet(Context.SheetIndex) * Args[0];
    Handled := True;
  end;
end;

TXLSUserFunctionContext porte le SheetIndex, la Row et la Col de la cellule en cours d'évaluation. Si le résultat d'une fonction dépend, même légèrement, de sa position, câblez l'événement Ex dès le départ ; rétrofiter le contexte dans un handler que trente formules appellent déjà est bien plus désordonné que choisir la bonne signature le premier jour.

Les fonctions personnalisées ne voyagent pas jusqu'à Excel

C'est la décision de conception qui sépare une démonstration d'un produit : une formule comme DISCOUNT(A1) n'a de sens que pendant que votre processus Delphi et son event handler sont vivants. Ouvrez le fichier enregistré dans Excel et DISCOUNT est un nom inconnu — la cellule affiche #NAME? sauf si une fonction VBA ou un add-in correspondant existe sur la machine de l'utilisateur.

Décidez donc, cellule par cellule, lequel des deux contrats vous livrez. Les cellules que l'utilisateur doit voir se recalculer dans Excel doivent se limiter au vocabulaire de fonctions intégré à Excel. Les cellules dont la logique est propriétaire doivent être évaluées dans le processus via Calculate et persistées comme valeurs simples — la fonction personnalisée agit alors comme une règle de calcul interne, pas comme du contenu de fichier. Mélanger les deux en persistant des formules de fonctions personnalisées est l'option qui génère le plus sûrement des tickets support.

Le contrat valeurs seulement a un avantage discret : il protège la propriété intellectuelle. Une règle de prix évaluée dans votre processus Delphi et livrée comme nombre ne peut pas être rétro-ingénierée depuis le classeur comme une formule visible, et elle ne peut pas être cassée par un utilisateur qui modifie une cellule intermédiaire. Les générateurs de factures, relevés de commission et grilles de tarifs appartiennent presque toujours à ce camp ; les modèles interactifs de simulation, où le client est censé modifier des entrées et regarder les totaux bouger, sont le cas qui a vraiment besoin de formules vivantes — construites avec le vocabulaire propre à Excel plus des noms définis.

Modes de calcul, itération et R1C1 : les réglages de la façade XLS

La façade XLS expose les paramètres de calcul de niveau BIFF que lit Excel dans le fichier. CalculationMode accepte xlCalcManual, xlCalcAutomatic (valeur par défaut) ou xlCalcAutomaticExceptTables, et détermine le comportement d'Excel après ouverture — un modèle avec des milliers de formules est souvent plus agréable livré en mode manuel. EnableIteration (par défaut False) avec MaxIterations (par défaut 100) et MaxIterationChange (par défaut 0.001) débloque les références circulaires délibérées de convergence itérative utilisées dans certains modèles financiers. ReferenceStyle bascule entre l'affichage A1 et R1C1, et UseFullPrecision reflète l'option Excel de précision telle qu'affichée.

Ces propriétés vivent sur la façade XLS parce qu'elles correspondent à des enregistrements BIFF ; lorsque vous générez du .xlsx, concevez les formules pour qu'elles ne dépendent pas de paramètres itératifs, ou calculez les valeurs convergées dans Delphi et écrivez les résultats.

Formules matricielles : le point d'entrée public est XLSX

Les formules matricielles legacy de style CSE sont créées via TXLSXRange.SetArrayFormula :

// one array formula spanning A2:A4
Sheet.RCRange[2, 1, 4, 1].SetArrayFormula('A1*{1;2;3}');

La méthode équivalente existe dans la hiérarchie de classes XLS mais se trouve dans une section privée ; il n'existe donc pas de moyen supporté d'écrire de nouvelles formules matricielles dans des fichiers .xls — celles déjà présentes dans les fichiers ouverts font l'aller-retour intactes, mais les cibles de génération doivent être .xlsx dès que la sémantique matricielle fait partie de l'exigence. Si un livrable .xls legacy a réellement besoin d'un comportement matriciel, la route pragmatique consiste à calculer le résultat du tableau dans Delphi et à écrire les valeurs individuelles.

Deux lectures liées sur ce site : noms définis et formules interfeuilles couvre la résolution de noms effectuée par le moteur, et l'article sur l'export CSV et TSV détaille le comportement d'export qui rend le calcul explicite nécessaire. La référence complète du moteur, y compris l'ensemble de fonctions supportées, est livrée avec HotXLS Component.