Formula References When Inserting Rows in HotXLS

HotXLS ajuste automatiquement les références de formules lorsque vous insérez ou supprimez des lignes ou des colonnes dans une feuille de calcul XLSX. Les méthodes InsertRows, DeleteRows, InsertCols et DeleteCols réécrivent chaque formule restante pour que ses références A1 — relatives, absolues et de plage — continuent de pointer vers les mêmes données après la modification de structure, et les références pointant vers un bloc supprimé deviennent #REF!, reproduisant le comportement d'Excel

Le bug ainsi évité est l'un des plus sournois de la génération de rapports. Un générateur écrit des données quotidiennes dans C2:C9 avec =SUM(C2:C9) en dessous, puis une étape ultérieure insère une ligne d'en-tête en haut. Si le moteur ne fait que déplacer les valeurs de cellule et laisse le texte de la formule inchangé, la formule SUM continuera de lire C2:C9 alors que les données résident désormais dans C3:C10 — de sorte que le total omettra silencieusement le dernier jour et comptera deux fois l'en-tête. Rien ne lève d'exception, le fichier s'ouvre correctement, et le nombre obtenu est simplement erroné. Avant la version 2.160, le moteur XLSX de HotXLS laissait les formules inchangées lors des modifications de structure. Depuis la version 2.160, la réécriture est automatique et ne requiert aucune configuration

Qu'advient-il des formules lorsque vous insérez une ligne dans Excel ?

La règle d'Excel est que les références suivent les données, et non les adresses. Lorsqu'une ligne est insérée, chaque référence dont l'index de ligne se trouve au niveau ou en dessous du point d'insertion est décalée vers le bas du nombre de lignes insérées ; les références situées entièrement au-dessus du point d'insertion restent inchangées. La suppression de lignes applique la même règle en sens inverse : les références situées sous le bloc supprimé glissent vers le haut, et les références pointant vers le bloc supprimé lui-même deviennent #REF! car les cellules qu'elles désignaient n'existent plus. Les colonnes se comportent de la même manière sur l'autre axe. Une bibliothèque de feuilles de calcul dont la sortie doit être conforme aux attentes des utilisateurs d'Excel doit reproduire ces mécanismes à l'identique, car les utilisateurs conçoivent leurs formules ainsi sans même y penser

Ce que le développeur a tendance à oublier, c'est que les références absolues se déplacent également. Les ancres $ dans $B$2 contrôlent ce qui se passe lorsqu'une formule est *copiée ou étirée* vers une autre cellule — elles n'ont aucun effet lors des modifications structurelles. Insérez une ligne au-dessus de la ligne 2 et Excel réécrit $B$2 en $B$3, symboles dollar conservés, car la valeur dont dépend la formule a physiquement été déplacée à la ligne 3. Un moteur qui ne décalerait que les références relatives corromprait précisément les formules que les gens ancrent le plus délibérément. HotXLS décale les deux formes et préserve les symboles $ dans le texte réécrit

Comment HotXLS décale-t-il automatiquement les références de formule ?

Les quatre méthodes d'édition structurelle de TXLSXWorksheet délèguent à un moteur géométrique unique : ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). InsertRows(BeforeRow, Count) l'appelle avec un décalage de ligne positif, DeleteRows(StartRow, Count) avec un décalage négatif, et les méthodes de colonnes font de même sur l'axe des colonnes. La routine déplace d'abord les cellules elles-mêmes — en supprimant toute cellule qui tombe dans un bloc supprimé — puis parcourt chaque cellule de formule restante et passe son texte à XlsxAdjustFormulaRowColRefs, un analyseur qui recherche les références de type A1 et réécrit leurs composants de ligne et de colonne en fonction du décalage. La même opération déplace les plages fusionnées, les hyperliens, les commentaires, les images, les graphiques, les mises en forme conditionnelles, les validations de données et les plages de tableaux, déplaçant ainsi toute la feuille comme une seule unité

// Layout before the edit:
//   C2..C9  daily figures
//   C10     =SUM(C2:C9)
Sheet.InsertRows(2, 1);      // one blank row before row 2
// Layout after the call:
//   C3..C10 daily figures
//   C11     =SUM(C3:C10)    -- the range moved with the data

Quelques détails de l'analyseur méritent d'être connus. XlsxAdjustFormulaRowColRefs reconnaît les références de cellule unique sous ses quatre formes d'ancrage (A1, $A1, A$1, $A$1) and les plages à deux angles comme A1:B3, en ajustant chaque extrémité indépendamment. Les formules dont le texte commence déjà par # — un marqueur d'erreur d'une modification antérieure — sont ignorées au lieu d'être analysées à nouveau. De plus, InsertCols ajoute une subtilité propre à Excel : les colonnes nouvellement insérées héritent de la largeur de leur voisine de gauche, ce qui correspond à la commande d'insertion de colonnes d'Excel

Quand une référence supprimée devient-elle #REF! ?

La règle de décalage pour un index de ligne ou de colonne unique produit trois résultats. Un index situé avant le point de modification reste inchangé. Un index situé au niveau ou après le point de modification se déplace selon le décalage. Et lors d'une suppression, un index qui se trouve à l'intérieur du bloc supprimé n'a plus de nouvelle valeur de destination — la cellule ayant disparu — l'analyseur réécrit donc toute la référence sous la forme #REF!. Pour une référence de plage, les deux extrémités sont soumises à la même règle, et si l'une des extrémités se trouve dans le bloc supprimé, la référence entière est réécrite sous la forme #REF! au lieu de rester partiellement valide

// A12 holds  =A4+A6+A10
Sheet.DeleteRows(5, 3);      // delete rows 5..7
// The formula, now in A9, reads  =A4+#REF!+A7
//   A4  : above the deleted block, unchanged
//   A6  : inside rows 5..7, gone      -> #REF!
//   A10 : below the block, slides up  -> A7

Produire une erreur #REF! explicite au lieu de rediriger silencieusement constitue le bon choix, et c'est celui que fait Excel. Une formule pointant vers une cellule voisine après la suppression de sa véritable entrée renverrait un nombre plausible ; #REF! se propage dans les formules dépendantes et apparaît dès le premier test de validation. La même conversion s'applique sur l'axe des colonnes

// E1 holds  =B1*$C$1
Sheet.DeleteCols(3, 1);      // remove column C
// The formula, now in D1, reads  =B1*#REF!
// The absolute anchor did not protect $C$1 -- the cell itself is gone

Quelles formes de référence ne sont pas réécrites ?

L'analyseur cible les références A1 sur la même feuille ayant explicitement la forme lettre-de-colonne-plus-numéro-de-ligne, et il convient de préciser ce qui en est exclu. Les références de colonne entière comme A:A et de ligne entière comme 1:1 manquent de l'un des deux composants, l'analyseur les laisse donc telles quelles. Les références de tableau structuré (Table1[Amount]) sont également laissées inchangées. De plus, la réécriture s'applique strictement à la notation A1 — si votre code construit des formules en style R1C1, convertissez-les en A1 avant une édition structurelle, comme décrit dans l'article complémentaire sur la notation de formule R1C1 dans Delphi

Les structures croisées entre feuilles et au niveau du classeur sont gérées par des passes distinctes plutôt que par l'analyseur de texte de cellule. Après avoir ajusté la feuille modifiée, ShiftSheetGeometry propage la modification géométrique aux formules des autres feuilles faisant référence à la feuille modifiée, aux plages de séries de graphiques, aux cibles d'hyperliens internes et aux noms définis. Les noms définis bénéficient d'une protection supplémentaire au niveau du cycle de vie de la feuille : depuis la version 2.150, la suppression d'une feuille de calcul réécrit chaque qualificateur SheetN! à l'intérieur de la formule d'un nom défini en #REF!, et renommer une feuille réécrit le qualificateur avec le nouveau nom. Ainsi, les noms ne pointent jamais vers une feuille inexistante. L'intégration des noms et des formules croisées est traitée dans l'article sur les noms définis et les formules croisées

Recalculer après le décalage

L'ajustement des références réécrit le *texte* de la formule ; il ne recalcule pas les résultats. Après une édition structurelle, les valeurs en cache stockées avec les formules décrivent l'ancienne géométrie. La séquence recommandée consiste à effectuer d'abord toutes les insertions et suppressions, puis à déclencher le recalcul une fois, et enfin à enregistrer. Exécuter le décalage avant le recalcul maintient également la cohérence des informations de dépendance — chaque référence réécrite désigne son véritable antécédent, ce qui correspond exactement aux besoins de moteur de recalcul incrémentiel et son graphe de dépendance pour recalculer l'ensemble minimal de cellules affectées. Si une formule réécrite contient désormais #REF!, le recalcul fait immédiatement remonter l'erreur au lieu de laisser un nombre obsolète dans le fichier

L'ajustement des références de formule est proposé comme comportement standard du moteur XLSX dans HotXLS Delphi Excel Component pour Delphi et C++Builder ; la page du produit contient la référence complète de l'API d'édition de feuille de calcul, y compris les méthodes d'insertion et de suppression présentées ici