HotXLS évalue les formules de propagation de tableaux dynamiques (spill formulas) d'Excel 365 de manière native dans Delphi et C++Builder : vous lui fournissez un classeur dont les cellules contiennent XLOOKUP ou FILTER, et son moteur de formules calcule l'ensemble des résultats et le propage sur le rectangle de sortie ancré, les mêmes valeurs que celles produites par Excel. Le composant Excel HotXLS pour Delphi modélise une formule de propagation sous la forme d'une cellule racine propriétaire du calcul, associée à un bloc de cellules dépendantes qui s'y réfèrent, ce qui représente l'ensemble du concept à appréhender avant d'écrire la moindre ligne de code
Ce modèle est important car la tâche quotidienne n'est pas abstraite. Un client envoie un fichier .xlsx créé dans Excel 365, ses feuilles regorgent de =FILTER(...) and =XLOOKUP(...), et votre service doit reproduire exactement les mêmes chiffres en mode sans tête (headless), sur une machine où Excel n'est pas installé, pour ensuite lire les valeurs propagées ou écrire une nouvelle zone de propagation. Les tableaux dynamiques ont modifié le contrat de calcul, passant de "une formule, une cellule" à "une formule, un rectangle de cellules", et le moteur HotXLS respecte ce contrat plutôt que de le simuler avec une grille préalablement étendue
Comment évaluer XLOOKUP et FILTER dans Delphi ?
HotXLS aiguille chaque fonction de tableau dynamique via un point d'entrée d'évaluateur unique, CalcDynArrayFunc dans lxCalc.pas, de sorte que toute cette famille partage un même chemin de code de propagation. Le jeu pris en charge comprend XLOOKUP et FILTER (les deux d'origine), complétés par XMATCH, SORT, UNIQUE et SEQUENCE. Chacune de ces fonctions renvoie un tableau de variantes 2D plutôt qu'une valeur scalaire : SEQUENCE(3;2;1;1) produit une grille de 3 par 2, SORT réordonne les lignes selon n'importe quelle colonne clé par ordre croissant ou décroissant, UNIQUE élimine les lignes en double en conservant la première occurrence, et XMATCH indique la position basée sur 1 d'une valeur selon les mêmes modes de correspondance exacte, par caractère générique, valeur immédiatement inférieure ou immédiatement supérieure que XLOOKUP. Contrairement aux fonctions de distribution statistique scalaires, qui renvoient un seul chiffre par appel, ces fonctions renvoient une forme géométrique complète
Pour calculer un classeur qui contient déjà ces formules, ouvrez-le, appelez une fois Recalculate, puis lisez les cellules dans lesquelles la propagation s'est effectuée. TXLSXWorkbook.Recalculate parcourt le graphe de dépendance et évalue chaque formule modifiée dans l'ordre topologique, de sorte qu'une racine de propagation ne soit calculée qu'une seule fois et que ses éléments soient écrits directement dans les cellules associées
Si le classeur nécessite une fonction que le moteur n'intègre pas, ce même évaluateur vous permet de greffer votre propre logique via le hook de fonctions de feuille de calcul personnalisées, une fonction personnalisée étant libre de renvoyer un tableau de variantes pour se propager exactement comme les fonctions intégrées
Ancrer la plage de propagation avec SetArrayFormula
HotXLS ne devine jamais la taille d'une propagation : vous spécifiez le rectangle de sortie, et TXLSRange.SetArrayFormula y ancre la formule. La méthode compile la formule une fois, stocke l'arbre syntaxique sur la cellule racine en haut à gauche avec la plage ancrée, et attribue à chaque autre cellule du rectangle une formule allégée qui fait référence à la racine. Lors du recalcul, la racine est évaluée une fois et ses éléments de matrice sont diffusés directement sur chaque cellule membre, ce qui correspond également à la façon dont une racine de propagation apparaît sous forme de nœud dans le graphe de dépendance de recalcul incrémentiel. C'est le chemin à suivre lorsque le but est d'écrire une zone de propagation dans le fichier plutôt que d'en lire une
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('report.xlsx');
Sheet := Book.Sheets[1];
// Size the rectangle to the result: a 3-row by 2-column grid.
if Sheet.Range['A1:B3'].SetArrayFormula('=SEQUENCE(3;2;1;1)') > 0 then
Book.Recalculate; // fill A1:B3 with 1..6 down the rows
Book.SaveAs('report-out.xlsx');
finally
Book.Free;
end;
end;L'ancrage explicite a pour conséquence une limite qu'il convient d'énoncer clairement : HotXLS ne redimensionne pas automatiquement une propagation comme le fait Excel en mode interactif. Excel agrandit et réduit la zone propagée à mesure que ses entrées changent et renvoie l'erreur #SPILL! lorsque les cellules cibles sont occupées. Dans HotXLS, le rectangle que vous transmettez à SetArrayFormula est le rectangle que vous obtenez, vous devez donc le dimensionner selon le résultat attendu, et si une fonction génère plus de lignes que ce que vous avez ancré, le surplus ne sera pas écrit
Diffusion de tableau par élément à travers les opérateurs
HotXLS applique les opérateurs arithmétiques élément par élément lorsque l'un des côtés de l'opération est un tableau. L'assistant ApplyArrayBinaryOp prend en charge +, -, *, / et ^ sur les tableaux de variantes 1D et 2D : un opérande scalaire est diffusé sur chaque élément, deux tableaux doivent avoir une forme identique sous peine de rejet de l'opération, et la division par zéro se propage sous forme d'erreur plutôt que de provoquer un plantage. Le chemin de l'arithmétique scalaire reste inchangé, de sorte que cette fonction ne s'active que si au moins un opérande est véritablement un tableau, comme une plage de propagation ou le résultat d'une fonction matricielle. Cela signifie qu'une formule telle que =D2:D13*1.1 ancrée sur une colonne multiplie chaque élément l'un après l'autre, et que =D2:D13*E2:E13 multiplie deux colonnes de forme identique position par position
// Scalar broadcast: every cell of the anchor gets D(n) * 1.1.
Sheet.Range['F2:F13'].SetArrayFormula('=D2:D13*1.1');
// Two arrays of identical shape multiply element by element.
Sheet.Range['G2:G13'].SetArrayFormula('=D2:D13*E2:E13');
Book.Recalculate;Que fait l'opérateur @ par rapport à un tableau CSE hérité ?
HotXLS lit un symbole @ explicite comme l'opérateur d'intersection de référence, et non comme l'intersection implicite de sélection de ligne du logiciel Excel actif. Dans le moteur, A1:A3 @ B1:B3 renvoie la cellule unique à l'intersection des deux plages, l'opérateur se liant plus fortement que ^ et moins fortement que %. Une précision s'impose toutefois : la forme d'intersection implicite séparée par un espace n'est pas encore découpée en jetons (tokenised), l'analyseur lexical ignorant toujours les espaces, vous n'obtenez donc l'intersection que via le symbole @ explicite
La distinction la plus profonde réside entre une formule matricielle CSE héritée et un tableau dynamique moderne, et dans HotXLS, les deux empruntent le même mécanisme d'ancrage. Une formule matricielle classique était le bloc {=...} saisi via Ctrl+Shift+Enter sur une sélection préalablement dimensionnée, chaque cellule partageant une unique formule compilée. Un tableau dynamique est une formule unique dont le résultat détermine sa propre forme. HotXLS les unifie sous la méthode SetArrayFormula : vous spécifiez toujours le rectangle, la racine possède l'arbre compilé et les membres y font référence, qu'il s'agisse d'une expression matricielle à l'ancienne ou d'un nouveau SORT. Ce que vous n'obtenez jamais, c'est l'extension automatique d'Excel où la zone s'agrandit d'elle-même, et garder cette limite à l'esprit évite de confondre ces deux modèles de pensée
Où s'arrête la prise en charge des tableaux dynamiques
Connaître les limites évite de longs moments de débogage. SORT respecte une direction par clé, croissante par défaut et décroissante lorsque l'argument d'ordre est négatif ; le sens de comparaison est un point qu'il convient de tester sur vos propres colonnes clés par rapport à Excel, car une comparaison inversée renvoie silencieusement les lignes dans l'ordre opposé plutôt que d'échouer. UNIQUE conserve la première occurrence de chaque ligne distincte, ce que le moteur confirme en balayant les lignes précédentes plutôt qu'en se fiant à un simple décompte de doublons. La fonction TABLE (tables de données d'analyse de scénarios) est reconnue de sorte qu'elle effectue un aller-retour dans le fichier, mais elle s'évalue sous forme d'espace réservé (placeholder) car les résultats de substitution réels correspondent aux valeurs de cache qu'Excel a déjà stockées, HotXLS ne recalculant pas la grille de scénarios
LET constitue la limite la plus nette à signaler. HotXLS enregistre LET et dispose d'un chemin d'évaluation pour cette fonction, mais l'analyseur rejette un nom simple qui n'est pas déjà défini, ainsi, =LET(x;10;x) échoue lors de l'analyse avant même que l'évaluateur ne soit sollicité. Considérez LET comme non prise en charge jusqu'à ce que l'analyseur gère la portée de ses noms liés. Une remarque mineure sur la portabilité : les chaînes de formules de ces exemples utilisent le séparateur de liste du moteur, le point-virgule, veillez donc à utiliser le séparateur attendu par votre build lorsque vous assemblez des formules. Les fonctions de tableaux dynamiques traitées ici, de XLOOKUP et FILTER à SORT, UNIQUE, SEQUENCE et XMATCH, sont incluses dans le composant HotXLS Delphi Excel, dont la référence de formule répertorie le catalogue complet des fonctions et les modes d'arguments pris en charge pour chacune