Si SUBTOTAL(109, ...) et SUBTOTAL(9, ...) retournent le même nombre sur un classeur contenant des lignes masquées, l'un des deux est faux. HotXLS, le composant de feuille de calcul Excel natif pour Delphi et C++Builder, se comportait exactement ainsi jusqu'à la version 2.197.0, car son moteur de calcul n'avait aucun moyen de demander à une feuille de calcul si une ligne donnée était masquée
Le symptôme arrive rarement sous forme de rapport de bogue concernant des codes de formule. Il arrive sous forme d'une incohérence : un traitement par lots sur le serveur calcule un total, un utilisateur ouvre le même fichier dans Excel avec un filtre appliqué, et les deux nombres diffèrent de la somme des lignes filtrées. Personne ne soupçonne la fonction d'agrégation, car la chaîne de formule dans la cellule est identique aux deux endroits. La différence réside entièrement dans ce que l'évaluateur était autorisé à voir
Pourquoi SUBTOTAL 109 inclut-il les lignes masquées ?
Parce que dans la plupart des conceptions de moteur, la couche qui évalue une formule n'apprend jamais rien sur la visibilité des lignes. HotXLS était un cas d'école : le moteur de calcul dans lxCalc.pas atteignait les valeurs de cellule via un unique callback TXLSGetValue qui répond avec une valeur pour un triplet (feuille, ligne, colonne) et rien d'autre. La visibilité est un attribut de présentation stocké sur l'enregistrement de ligne, et aucune partie de cet enregistrement ne voyageait le long de la chaîne d'appel. Le moteur n'avait donc qu'un seul chemin d'agrégation, et les deux moitiés de la table des numéros de fonction de SUBTOTAL s'y résolvaient toutes deux. Ce n'est pas une classe de défaut d'erreur d'arrondi : c'est la raison même pour laquelle la seconde moitié de la table existe. ECMA-376 Part 1, publiée sous ISO/IEC 29500-1, définit SUBTOTAL dans ses définitions de fonctions de formule (§18.17.7) avec un premier argument qui sélectionne à la fois l'agrégation interne et la politique de lignes masquées. Les codes 1 à 11 correspondent à AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR et VARP tout en incluant les valeurs sur les lignes masquées manuellement. Les codes 101 à 111 sélectionnent les onze mêmes agrégations et les excluent. Un utilisateur qui tape 109 au lieu de 9 fait une déclaration délibérée sur les données masquées, et un moteur qui efface cette distinction annule silencieusement cette déclaration
Vers quoi les numéros de fonction pointent-ils à l'intérieur du moteur
HotXLS résout le premier argument de SUBTOTAL dans CalcSubtotalFunc, qui normalise les codes 101 à 111 vers les mêmes identifiants de fonction interne que les codes 1 à 11, puis distribue sur l'agrégation elle-même. La majeure partie de la famille passe par l'accumulateur incrémental ExcelSum, celui qui gère SUM, COUNT, COUNTA, MIN, MAX et AVERAGE. Cinq d'entre elles ne le peuvent pas : STDEV, VAR, STDEVP, VARP et PRODUCT nécessitent une passe sous forme close sur les données, si bien que CalcSubtotalFunc route les codes internes 12, 46, 193, 194 et 183 vers un réducteur séparé, SubtotalReduceVariance. Cette scission est la première chose à cartographier avant de toucher à quoi que ce soit, car deux chemins d'agrégation indépendants signifient deux boucles de parcours de cellules indépendantes, et une correction appliquée à une seule des deux produit le pire résultat possible : SUBTOTAL(109, ...) respecte le filtre tandis que SUBTOTAL(107, ...) sur la même plage ne le fait pas. Compter les boucles dans HotXLS en a révélé six une fois AGGREGATE inclus, réparties entre l'évaluation de plage, la collecte de plage simple, et trois réducteurs séparés
Pourquoi un champ de travail plutôt que six nouvelles signatures ?
Parce que faire transiter un nouveau paramètre à travers six fonctions de parcours de cellules, plus tout ce qui les appelle, est un changement étendu sur un chemin de code critique pour le bénéfice d'un simple booléen. HotXLS disposait déjà d'un précédent pour l'alternative : un champ transitoire sur le calculateur, dans le même esprit que le champ de travail que GetRangeInfo utilise pour enregistrer quand une référence 3D se résolvait dans un classeur externe. La version 2.197.0 en a ajouté un second. Le moteur a acquis un type de callback, TXLSIsRowHidden, déclaré comme une fonction de (SheetIndex, row) retournant un Boolean, stocké dans FIsRowHidden, plus un indicateur transitoire FIgnoreHiddenRows. L'indicateur est armé à l'entrée de CalcSubtotalFunc quand le code de fonction se situe entre 101 et 111, et à l'entrée de CalcAggregateFunc pour les codes d'option AGGREGATE qui sélectionnent l'exclusion des lignes masquées. Chaque boucle de parcours de cellules l'inspecte alors et saute une ligne quand il est positionné, en ajoutant une seule ligne de code à chaque fois
// The shape repeated in all six cell-walk loops
for sh := s1 to s2 do
for rr := r1 to r2 do
begin
if FIgnoreHiddenRows and Assigned(FIsRowHidden) and FIsRowHidden(sh, rr) then
Continue;
for cc := c1 to c2 do
begin
// ... fold Cells[rr, cc] into the accumulator ...
end;
end;Deux détails dans le code d'armement portent la correction de tout le schéma. L'indicateur est sauvegardé puis restauré plutôt que simplement positionné puis effacé, car un argument de SUBTOTAL peut contenir une expression qui exécute sa propre évaluation pendant que l'agrégation externe est encore sur la pile, et ce travail imbriqué ne doit ni hériter ni détruire la porte externe. Et la restauration se trouve dans un bloc finally, car CalcSubtotalFunc a plusieurs sorties anticipées pour des codes d'erreur ; un indicateur laissé armé après un retour d'erreur corromprait silencieusement la prochaine formule sans rapport dans l'ordre de recalcul
prevIgnoreHidden := FIgnoreHiddenRows;
if (fnCode >= 101) and (fnCode <= 111) and Assigned(FIsRowHidden) then
FIgnoreHiddenRows := True;
try
// aggregate over Item.Child[2] .. Item.Child[ChildCount]
// every Exit path below is covered by the finally
finally
FIgnoreHiddenRows := prevIgnoreHidden;
end;Le test Assigned est ce qui garde le changement compatible. HotXLS a étendu le constructeur du calculateur avec un troisième paramètre valant nil par défaut, si bien que tout code qui construit un TXLSCalculator avec l'ancien appel à deux arguments compile toujours et conserve toujours le comportement historique d'inclusion des lignes masquées. Rien dans la forme de l'API existante n'a changé
D'où vient réellement le bit de ligne masquée ?
De la feuille de calcul, via deux sources différentes, car HotXLS porte deux moteurs de classeur. Le côté BIFF historique répond depuis TXLSRowInfoList.GetHidden, atteint via TXLSWorkbook.GetRowHidden. Le côté OOXML répond depuis TXLSXWorksheet.GetRowHidden, atteint via TXLSXWorkbook.GetCalcRowHidden. Les deux sont câblés dans le calculateur au moment de la construction, aux côtés du callback de valeur de cellule qu'ils reflètent. Les conventions de numérotation de ligne sont l'endroit où ce genre de pont se trompe habituellement, donc elles méritent d'être énoncées explicitement. Le calculateur transmet au callback une ligne base 0, correspondant aux coordonnées que TXLSGetValue utilise déjà. La feuille XLSX indexe sa carte de lignes masquées par numéro de ligne base 1, exactement comme Excel numérote les lignes, ce qui est aussi ce qu'expose la propriété publique RowHidden[ARow]. Le pont XLSX ajoute donc un avant la recherche, et le pont BIFF ne le fait pas, car TXLSRowInfoList est déjà en base 0. Les deux ponts traitent un index de feuille ou une ligne hors de la plage valide comme visible, si bien qu'une requête hors limites se dégrade vers l'ancienne réponse d'inclusion des masquées plutôt que de faire perdre des données
Ce qui change pour les classeurs filtrés
C'est le cas qui génère les tickets de support. Appliquer un AutoFilter dans HotXLS via ApplyAutoFilter évalue les critères de colonne et masque chaque ligne de données qui ne correspond pas, ce qui est précisément ce que fait Excel quand un utilisateur clique sur un menu déroulant de filtre. Avant la v2.197.0, ces lignes masquées étaient invisibles à l'utilisateur et pleinement visibles au moteur de calcul, si bien qu'un SUBTOTAL(109, ...) côté serveur rapportait le total non filtré. Désormais, le même appel rapporte le total filtré
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
VisibleRows: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('orders.xlsx');
Sheet := Book.Sheets[0];
Sheet.SetAutoFilter('A1:E500');
Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');
VisibleRows := Sheet.ApplyAutoFilter; // hides the non-matching rows
Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
Book.Recalculate;
// The cell value now agrees with what Excel shows for the same filter,
// and VisibleRows tells you how many rows fed into it
Book.SaveAs('orders-filtered.xlsx');
finally
Book.Free;
end;
end;Le masquage manuel fonctionne de la même façon, puisque RowHidden[ARow] := True est le même état que celui écrit par le filtre. Cette équivalence est délibérée dans Excel et tient désormais aussi dans HotXLS. Une conséquence mérite une note dans toute documentation livrée avec vos classeurs générés : un total calculé avec le code 109 est un nombre dépendant de la vue, si bien qu'un destinataire qui efface le filtre le change. Quand un rapport doit énoncer un chiffre fixe quoi que le lecteur fasse à la vue, le code 9 est le bon choix et l'a toujours été. Les filtres, la validation et les tableaux sont couverts ensemble dans l'article sur la validation de données, l'AutoFilter et les tableaux. Comme masquer des lignes ne touche à aucune formule, cela ne salit pas non plus le graphe de dépendances de lui-même, ce qui mérite d'être su si vous vous appuyez sur le recalcul incrémental sur le sous-graphe sale pour garder les grands classeurs réactifs
Codes d'option AGGREGATE et une limite encore ouverte
AGGREGATE est SUBTOTAL avec un second argument de politique, et HotXLS le gère dans CalcAggregateFunc. L'argument d'option encode des commutateurs indépendants : si les appels SUBTOTAL et AGGREGATE imbriqués à l'intérieur de la plage sont ignorés, si les valeurs sur les lignes masquées sont ignorées, et si les valeurs d'erreur sont supprimées plutôt que propagées. HotXLS arme la porte partagée des lignes masquées pour les codes d'option 2, 3, 6 et 7, et supprime les valeurs d'erreur pour les codes d'option 4 à 7. L'argument de numéro de fonction sélectionne ensuite l'agrégation exactement comme SUBTOTAL le fait, y compris le routage de la variance, de l'écart type et du produit vers leurs propres réducteurs. Une lacune documentée demeure, et il vaut mieux l'énoncer ici que de la découvrir en production : la sémantique d'ignorance des SUBTOTAL imbriqués associée aux codes d'option bas n'est pas implémentée dans HotXLS. Détecter un SUBTOTAL imbriqué à l'intérieur d'une plage référencée exige de marquer l'état de récursion de l'évaluateur afin qu'une agrégation interne puisse s'annoncer à l'externe, ce qui est un changement plus vaste que la porte des lignes masquées. En pratique, l'exposition est faible, car les classeurs réels placent presque toujours les formules SUBTOTAL en dehors des plages sur lesquelles d'autres formules SUBTOTAL agrègent. Si votre générateur construit effectivement des plages d'agrégation qui se chevauchent, ne comptez pas sur les codes d'option bas pour les dédupliquer
Le garde-fou d'arité livré en même temps
La version 2.197.0 a aussi comblé une lacune de validation dans le même répartiteur, et la raison de conception est la même que celle qui a motivé le champ de travail : placer la vérification là où elle peut être écrite une seule fois. Environ 280 corps de fonctions intégrées vérifiaient chacun leur propre nombre d'arguments contre Item.ChildCount, ce qui ne laissait aucune limite cohérente pour le cas de trop d'arguments. Un appel comme =SIN(1,2) atteignait un corps de fonction qui examinait son premier argument, ignorait le surplus, et retournait un nombre plausible là où Excel retourne #VALUE!. HotXLS stockait déjà l'arité déclarée de chaque fonction intégrée dans son registre de fonctions, exposée comme THashFunc.ArgsCnt, avec -1 marquant une fonction variadique telle que SUM, IF ou CONCAT. La version 2.197.0 a transmis cela via une nouvelle propriété TXLSFormula.FuncArgsCntByPtg et a ajouté une porte en tête de GetValueItemFunc, le répartiteur principal
lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
lProvidedArgs := Item.ChildCount - 1; // Child[0] is the function node
if lProvidedArgs > lDeclaredArgs then
begin
Result := lxErrorValue; // =SIN(1,2) now yields #VALUE!
Exit;
end;
end;Le garde-fou rejette trop d'arguments et ne dit délibérément rien sur trop peu d'arguments. Omettre un argument optionnel de fin est légal dans Excel pour VLOOKUP, SUBSTITUTE et une longue liste d'autres fonctions, si bien qu'une vérification symétrique aurait cassé des formules correctes pour attraper des formules incorrectes. Les identifiants inconnus se déclarent variadiques et sautent entièrement la porte, ce qui laisse les fonctions définies par l'utilisateur hors de son chemin ; si vous enregistrez vos propres fonctions, le comportement décrit dans le guide sur le moteur de formules et les fonctions personnalisées n'est pas affecté. Centraliser le cas de trop peu d'arguments est un travail séparé, car chacun de ces 280 corps a sa propre sémantique de code d'erreur, et ils doivent être revus un par un plutôt que d'être supposés
Le moteur de calcul décrit ici, les deux façades de classeur, et les API d'AutoFilter et de visibilité de ligne qui l'alimentent font partie du composant de feuille de calcul HotXLS pour Delphi, livré avec le code source complet pour Delphi et C++Builder et ne nécessitant aucune installation d'Excel sur la machine qui l'exécute