Formules de tableaux dynamiques en Delphi avec HotXLS

HotXLS évalue nativement les formules de tableaux dynamiques d'Excel 365 en Delphi et C++Builder : vous lui remettez un classeur dont les cellules portent XLOOKUP ou FILTER, et son moteur de formules calcule l'ensemble de résultats puis le déverse sur le rectangle de sortie ancré, exactement les valeurs qu'Excel produirait. Le composant Excel Delphi HotXLS modélise une formule déversée comme une cellule racine qui possède le calcul, plus un bloc de cellules dépendantes qui la relisent, et c'est tout le tableau dont vous avez besoin avant d'écrire une ligne de code

Ce modèle compte parce que la tâche quotidienne n'a rien d'abstrait. Un client envoie un .xlsx construit dans Excel 365, ses feuilles sont pleines de =FILTER(...) et de =XLOOKUP(...), et votre service doit reproduire exactement les mêmes nombres sans interface, sur une machine où Excel n'est pas installé, puis lire les valeurs déversées ou écrire lui-même une nouvelle région de déversement. Les tableaux dynamiques ont fait passer le contrat de calcul de « une formule, une cellule » à « une formule, un rectangle de cellules », et le moteur HotXLS suit ce contrat au lieu de le simuler avec une grille pré-développée

Comment évaluer XLOOKUP et FILTER en Delphi ?

HotXLS aiguille toutes les fonctions de tableaux dynamiques par un unique point d'entrée d'évaluation, CalcDynArrayFunc dans lxCalc.pas, si bien que toute la famille partage un seul chemin de code de déversement. L'ensemble pris en charge est XLOOKUP et FILTER (les deux originales), rejointes par XMATCH, SORT, UNIQUE et SEQUENCE. Chacune renvoie un tableau variant en deux dimensions plutôt qu'un scalaire : SEQUENCE(3;2;1;1) donne une grille de 3 sur 2, SORT réordonne les lignes selon n'importe quelle colonne clé, en ordre croissant ou décroissant, UNIQUE réduit les lignes en double en gardant la première occurrence, et XMATCH indique la position en base 1 d'une valeur selon les mêmes modes de correspondance exacte, à caractères génériques, valeur immédiatement inférieure et valeur immédiatement supérieure que XLOOKUP. Contrairement aux fonctions de distribution statistique scalaires, qui renvoient un nombre par appel, ces fonctions rendent une forme entière

Pour calculer un classeur qui porte déjà ces formules, ouvrez-le, appelez Recalculate une fois, et lisez les cellules où le déversement a atterri. TXLSXWorkbook.Recalculate parcourt le graphe de dépendances et évalue chaque formule salie en ordre topologique, si bien qu'une racine de déversement est calculée une seule fois et que ses éléments sont écrits directement dans les cellules membres

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  r, c: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('from-customer.xlsx');
    Book.Recalculate;                 // évaluer chaque formule, déversements compris
    Sheet := Book.Sheets[1];          // Sheets est en base 1
    // Relire le bloc où XLOOKUP / FILTER se sont déversés, disons D2:F9.
    for r := 2 to 9 do
      for c := 4 to 6 do
        Writeln(Sheet.Cells[r, c].Text);
  finally
    Book.Free;
  end;
end;

Si le classeur a besoin d'une fonction que le moteur ne livre pas, le même évaluateur vous laisse brancher votre propre logique par le point d'extension des fonctions de feuille personnalisées, et une fonction personnalisée est libre de renvoyer un tableau variant afin de se déverser exactement comme les fonctions intégrées

Ancrer la plage de déversement avec SetArrayFormula

HotXLS ne devine jamais la taille que devrait avoir un déversement : vous nommez le rectangle de sortie, et TXLSRange.SetArrayFormula y ancre la formule. La méthode compile la formule une fois, range l'arbre syntaxique sur la cellule racine en haut à gauche avec la plage ancrée, et donne à chaque autre cellule du rectangle une formule légère qui référence faiblement la racine. Au recalcul, la racine s'évalue une fois et les éléments de sa matrice sont diffusés directement sur chaque cellule membre, ce qui explique aussi pourquoi une racine de déversement apparaît comme un seul nœud dans le graphe de dépendances du recalcul incrémental. C'est la voie à emprunter quand le travail consiste à écrire une région de déversement dans le fichier plutôt qu'à en lire une

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('report.xlsx');
    Sheet := Book.Sheets[1];
    // Dimensionner le rectangle au résultat : une grille de 3 lignes sur 2 colonnes.
    if Sheet.Range['A1:B3'].SetArrayFormula('=SEQUENCE(3;2;1;1)') > 0 then
      Book.Recalculate;               // remplir A1:B3 avec 1..6 le long des lignes
    Book.SaveAs('report-out.xlsx');
  finally
    Book.Free;
  end;
end;

La conséquence de l'ancrage explicite est une limite qui mérite d'être énoncée sans détour : HotXLS ne redimensionne pas automatiquement un déversement comme le fait Excel en interactif. Excel agrandit et rétrécit la région déversée à mesure que ses entrées changent et lève #SPILL! quand les cellules cibles sont occupées. Dans HotXLS, le rectangle que vous passez à SetArrayFormula est le rectangle que vous obtenez : vous le dimensionnez donc au résultat attendu, et si une fonction produit plus de lignes que vous n'en avez ancrées, le surplus n'a nulle part où atterrir

Diffusion de tableaux élément par élément à travers les opérateurs

HotXLS applique les opérateurs arithmétiques élément par élément dès qu'un côté de l'opération est un tableau. La fonction utilitaire ApplyArrayBinaryOp couvre +, -, *, / et ^ sur des tableaux variants à une et deux dimensions : un opérande scalaire est diffusé à chaque élément, deux tableaux doivent avoir la même forme sous peine de rejet de l'opération, et la division par zéro se propage comme une erreur au lieu de faire planter le programme. Le chemin arithmétique scalaire reste intact, si bien que cela ne s'active que lorsqu'au moins un opérande est réellement un tableau, comme une plage déversée ou un résultat de fonction matricielle. Cela signifie qu'une formule comme =D2:D13*1.1 ancrée sur une colonne multiplie chaque élément à son tour, et que =D2:D13*E2:E13 multiplie deux colonnes de forme identique position par position

// Diffusion scalaire : chaque cellule de l'ancre reçoit D(n) * 1.1.
Sheet.Range['F2:F13'].SetArrayFormula('=D2:D13*1.1');
// Deux tableaux de forme identique se multiplient élément par élément.
Sheet.Range['G2:G13'].SetArrayFormula('=D2:D13*E2:E13');
Book.Recalculate;

Que fait l'opérateur @ face à un tableau CSE hérité ?

HotXLS lit un @ explicite comme l'opérateur d'intersection de références, et non comme l'intersection implicite par ligne de l'Excel actuel. Dans le moteur, A1:A3 @ B1:B3 renvoie l'unique cellule où les deux plages se croisent, et l'opérateur lie plus fort que ^ et moins fort que %. Une note de franchise accompagne cela : la forme d'intersection implicite séparée par une espace n'est pas encore reconnue par l'analyseur lexical, qui saute toujours les blancs, si bien que vous n'obtenez l'intersection que par le symbole @ explicite

La distinction plus profonde oppose une formule matricielle CSE héritée à un tableau dynamique moderne, et dans HotXLS les deux passent par la même machinerie d'ancrage. Une formule matricielle classique était le bloc {=...} saisi avec Ctrl+Maj+Entrée sur une sélection dimensionnée à l'avance, chaque cellule partageant une seule formule compilée. Un tableau dynamique est une formule unique dont le résultat détermine sa propre forme. HotXLS les unifie sous SetArrayFormula : vous nommez toujours le rectangle, la racine possède l'arbre compilé, et les membres la référencent, que la formule soit une expression matricielle à l'ancienne ou un SORT récent. Ce que vous n'obtenez jamais, c'est la propagation automatique d'Excel où la région se réétend silencieusement, et garder cette limite en tête empêche les deux modèles mentaux de se brouiller

Là où la prise en charge des tableaux dynamiques s'arrête

Connaître les bords épargne une après-midi de débogage. SORT respecte un sens par clé, croissant par défaut et décroissant quand l'argument d'ordre est négatif ; le sens de comparaison mérite que vous testiez vos propres colonnes clés face à Excel, puisqu'une comparaison inversée renvoie silencieusement les lignes dans l'ordre opposé au lieu d'échouer. UNIQUE garde la première occurrence de chaque ligne distincte, ce que le moteur confirme en parcourant les lignes précédentes plutôt qu'en se fiant à un décompte brut de doublons. La fonction TABLE (les tables de données de l'analyse de scénarios) est reconnue afin de faire l'aller-retour dans le fichier, mais elle s'évalue vers un espace réservé, car les vrais résultats de substitution sont les valeurs mises en cache qu'Excel a déjà rangées, et HotXLS ne rejoue pas la grille de scénarios

LET est la limite la plus nette à signaler. HotXLS enregistre LET et porte un chemin d'évaluation pour elle, mais l'analyseur rejette un nom nu qui n'est pas un nom déjà défini, si bien que =LET(x;10;x) échoue à l'analyse avant que l'évaluateur soit atteint. Considérez LET comme non pris en charge tant que l'analyseur n'aura pas conscience de la portée de ses noms liés. Une note de portabilité plus modeste : les chaînes de formules de ces exemples utilisent le séparateur de liste du moteur, le point-virgule, alors alignez-vous sur le séparateur qu'attend votre build quand vous assemblez du texte de formule. Les fonctions de tableaux dynamiques traitées ici, de XLOOKUP et FILTER à SORT, UNIQUE, SEQUENCE et XMATCH, sont livrées dans le HotXLS Delphi Excel Component, dont la référence des formules liste le catalogue complet des fonctions et les modes d'arguments pris en charge par chacune