HotXLS, le composant Excel pour Delphi et C++Builder, scinde automatiquement une règle de mise en forme conditionnelle ou de validation de données en deux objets de règle séparés ou plus chaque fois qu'une insertion ou une suppression de ligne ou de colonne découpe la plage couverte par la règle en morceaux nécessitant des ancres de formule relatives différentes, puis réattribue à chaque règle de mise en forme conditionnelle un nouveau numéro de priorité unique. Ce comportement a été introduit dans la version 2.196 du moteur XLSX et s'exécute automatiquement, sans réglage permettant de le désactiver. Le déclencheur est étroit mais courant : une règle cellIs ou expression dont la formule lit une cellule relative à sa propre plage, sur une feuille de calcul où une ligne est ensuite insérée ou supprimée quelque part au milieu de cette plage exacte
La plupart des articles sur l'automatisation Excel s'arrêtent au problème du texte des formules : décaler les numéros de ligne et de colonne à l'intérieur de chaque SUM() et de chaque VLOOKUP() pour que les références continuent de pointer vers les bonnes cellules. Cette moitié de l'histoire est réelle, et elle est couverte dans l'article compagnon sur la manière dont HotXLS réécrit les références de formule lors du déplacement de lignes et de colonnes, mais une règle de mise en forme conditionnelle ou de validation de données n'est pas simplement une formule assise dans une cellule. Elle associe une formule à une plage, sqref selon la terminologie ECMA-376, et les deux doivent se déplacer ensemble. Lorsqu'une modification structurelle découpe cette plage en deux morceaux qui nécessiteraient deux décalages relatifs différents pour rester corrects, conserver un seul objet de règle avec une seule chaîne de formule cesse d'être une option, et prétendre le contraire est la façon dont une règle de surlignage se met silencieusement à comparer les mauvaises lignes
Pourquoi insérer une ligne scinde-t-il une règle de mise en forme conditionnelle au lieu de simplement la déplacer ?
Une règle de mise en forme conditionnelle ou de validation de données ne conserve qu'une seule formule pour toute sa plage, évaluée par rapport à une seule cellule d'ancrage, si bien que dès qu'une modification force deux parties de cette plage à nécessiter deux décalages relatifs différents, une seule formule ne peut plus décrire correctement les deux parties. ECMA-376 exprime la couverture d'une règle via l'attribut sqref sur l'élément conditionalFormatting ou dataValidation, et Excel évalue Formula1 et Formula2 comme si le texte avait été saisi dans la cellule supérieure gauche de ce sqref puis recopié sur le reste de la plage, de la même manière qu'une formule relative ordinaire se recopie le long d'une colonne. Imaginez un surlignage d'écart sur B2:B50 qui signale tout chiffre réel dépassant son budget, construit comme une règle cellIs dont Formula1 est le texte littéral C2, ce qui signifie comparer la cellule B de la ligne actuelle à la cellule C de cette même ligne
Idx := Sheet.AddConditionalFormat('B2:B50', xlsxCfOpGreaterThan, 'C2');
Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);
Sheet.InsertRows(25, 1); // one blank separator row, starting at old row 25Insérez cette unique ligne de séparation à l'ancienne ligne 25, et les lignes au-dessus du point d'insertion ne bougent pas, si bien que leur part de la règle continue de lire correctement Formula1 comme C2. Les lignes qui étaient autrefois 25 à 50 glissent vers 26 à 51, et pour elles C2 est désormais entièrement la mauvaise cellule, puisque la ligne 26 doit se comparer à C26, pas à un chiffre de budget situé deux douzaines de lignes plus haut
Comment HotXLS décide-t-il si une règle doit être scindée
HotXLS ne crée des objets de règle supplémentaires que lorsque la géométrie l'exige réellement : une routine interne, XlsxBuildShiftedRuleParts, parcourt chaque zone disjointe du sqref de la règle, détermine quelle était la cellule d'ancrage de cette zone avant la modification et ce qu'elle devient après, et vérifie si chaque morceau résultant nécessiterait la même correction de décalage relatif. Si tous les morceaux s'accordent, une seule règle survit, son sqref étant reconstruit comme l'union des morceaux décalés et sa formule rebasée une seule fois. Une véritable scission ne se produit que lorsque les morceaux sont en désaccord, exactement le cas B2:B50 ci-dessus, où le bloc supérieur conserve son ancrage d'origine tandis que le bloc inférieur en a besoin d'un nouveau
Rebaser la formule d'un morceau est une opération en deux étapes qui réutilise une mécanique que HotXLS possède déjà pour les groupes de formules partagées OOXML : la formule est d'abord traduite comme si elle avait été à l'origine ancrée à la cellule supérieure gauche propre à ce morceau, en utilisant les mêmes calculs de décalage relatif qui étendent une formule partagée sur sa plage, puis le résultat passe par le même scanner de décalage de lignes et de colonnes qui réécrit les formules ordinaires de la feuille de calcul. C'est ainsi que Formula1 passe de C2 à C26 en deux mouvements plutôt qu'un cas particulier écrit à la main : traduire C2 de 23 lignes vers l'avant pour obtenir C25, comme si la règle avait toujours commencé là, puis laisser le décalage ordinaire à la ligne 25 la pousser jusqu'à C26. Chaque autre propriété, couleur de remplissage, arrêt-si-vrai, l'opérateur lui-même, se transmet sans changement au nouvel objet de règle, si bien que les deux moitiés continuent de peindre les cellules de la couleur qu'elles ont toujours eue
// ConditionalFormats now holds two rules instead of one:
// B2:B25 Formula1 = 'C2' (rows above the insert)
// B26:B51 Formula1 = 'C26' (rows that shifted down)Les barres de données et les jeux d'icônes se scindent-ils de la même façon que les règles cellIs ?
Non : HotXLS ne partitionne que les types de règles dont la correction dépend réellement d'une formule relative par région, les comparaisons cellIs et les règles expression, et laisse chaque autre type de mise en forme conditionnelle comme un seul objet de règle dont le sqref croît simplement pour couvrir les morceaux décalés sous forme d'union multi-zone. En interne, la branche est une simple vérification de Kind, cf.Kind in [cfkCellIs, cfkExpression], rien de plus exotique que cela. Les barres de données, les échelles à deux et trois couleurs, les jeux d'icônes, les classements du haut et du bas, et les détecteurs de doublons, de cellules vides et d'erreurs portent une charge utile, une couleur de barre, un ensemble de points d'échelle, une famille d'icônes, qui décrit toute la plage couverte en une fois plutôt qu'une comparaison relative par cellule, si bien que les scinder en plusieurs objets de règle priorisés n'apporterait aucune correction supplémentaire et ne ferait qu'ajouter des règles à gérer. Lorsqu'une modification divise leur plage, HotXLS recombine les morceaux en une seule règle avec un sqref multi-zone et réancre la charge utile comme une unité unique plutôt que de cloner un nouvel objet de règle par morceau. La distinction s'aligne sur la taxonomie des types de règles présentée dans l'article sur les bases de la mise en forme conditionnelle et du texte enrichi : les barres de données, les échelles de couleurs et les jeux d'icônes se distinguent déjà des règles cellIs en ignorant entièrement la propriété Style, et il s'avère maintenant qu'ils se distinguent aussi du réancrage par région pour la même raison sous-jacente
Pourquoi les priorités des règles changent-elles après une modification structurelle ?
Les priorités changent parce que chaque clone commence par détenir exactement la même valeur de priorité que la règle dont il est issu, et HotXLS exécute ensuite une passe de normalisation qui résout les doublons résultants en un ordonnancement propre et sans lacune plutôt que de laisser deux règles à égalité sur le même rang. Une seconde routine interne, XlsxNormalizeConditionalFormatPriorities, prend la priorité actuelle de chaque mise en forme conditionnelle, retombe sur la position de cette règle dans la collection pour toute règle qui n'en avait jamais eu une définie explicitement, trie toute la liste de façon stable afin que les égalités conservent leur ordre relatif d'origine, et renumérote le résultat trié en une séquence dense 1, 2, 3 sans lacune ni répétition. HotXLS l'exécute une fois avant qu'un décalage ne commence, si bien que le clonage part d'une base propre, et de nouveau après chaque scission et chaque suppression d'une règle vidée, si bien que le fichier enregistré ne comporte jamais deux entrées de règle revendiquant la même priorité. Cela importe si vous avez suivi le conseil de l'article sur les bases de la mise en forme conditionnelle consistant à laisser des écarts entre les valeurs de priorité pour qu'une règle ultérieure puisse s'insérer sans renuméroter le reste : les écarts survivent jusqu'à ce que la prochaine modification de ligne ou de colonne touche cette feuille de calcul, puis s'effondrent, car la normalisation ne garantit que l'unicité et l'ordre stable, pas que votre schéma de numérotation d'origine revienne inchangé
Les règles de validation de données se scindent aussi, sans priorité à renuméroter
Les règles de validation de données passent par la même logique de partitionnement de plage que les mises en forme conditionnelles cellIs et expression, et contrairement à la mise en forme conditionnelle, chaque type de validation emprunte ce chemin de façon uniforme : HotXLS n'a pas de famille non-formule séparée pour la validation de données comme le sont les barres de données et les jeux d'icônes pour la mise en forme conditionnelle, si bien qu'une simple règle de liste ou de nombre entier est partitionnée par la routine identique qui gère une formule personnalisée relative. Ce qui diffère, c'est la priorité : ECMA-376 ne donne aucun attribut priority à l'élément dataValidation, si bien qu'il n'y a pas d'étape de renumérotation pour les validations comme il y en a pour les mises en forme conditionnelles. Imaginez une validation par formule personnalisée qui empêche le montant réel de chaque ligne de dépasser son propre budget dans la colonne voisine
Sheet.AddCustomValidation('D2:D400', 'D2<=C2');
Sheet.DeleteRows(150, 5); // remove five rows out of the validated range
// DataValidations now holds two rules instead of one:
// D2:D149 Formula1 = 'D2<=C2' (rows above the deletion)
// D150:D395 Formula1 = 'D150<=C150' (rows that shifted up)Cela importe pour la même raison que l'article sur les bases de la validation de données déconseille d'attacher une règle avant que le nombre de lignes ne soit définitif : une validation ne couvre que les cellules littérales que vous lui avez données, et une modification structurelle ultérieure peut laisser deux règles ou plus faire le travail qu'une seule faisait auparavant. Rien ne casse fonctionnellement : chaque cellule de la plage d'origine est toujours validée par quelque chose, mais le code qui suppose une seule entrée DataValidations par colonne commencera à mal indexer dès la première modification qui la touche. Il existe un plafond strict à ce que cela peut atteindre : si une scission devait faire dépasser à une feuille de calcul 65 534 règles de validation de données, HotXLS lève une exception plutôt que d'écrire un fichier qu'Excel rejetterait silencieusement, ce qui est la bibliothèque refusant de fabriquer un classeur corrompu plutôt qu'une limite qu'un usage ordinaire est susceptible d'atteindre
Que vérifier après une insertion ou une suppression en masse
Les deux choses qui méritent d'être vérifiées après qu'un script exécute un lot de modifications de lignes ou de colonnes sur une feuille pleine de mises en forme conditionnelles et de validations sont le nombre total de règles et l'ordre des priorités, car les deux peuvent dériver de façons faciles à manquer en revue de code et évidentes dès que quelqu'un ouvre Gérer les règles dans Excel. Une seule modification cause rarement beaucoup de dégâts : une insertion unique au milieu d'une règle cellIs produit au maximum deux objets de règle là où il y en avait un. Le risque se cumule lorsqu'une routine de génération de rapport insère des lignes une par une dans une boucle sur une feuille qui porte déjà plusieurs règles ancrées par formule : chaque passage peut re-scinder des règles qu'un passage précédent avait déjà scindées, et cinq règles cellIs d'origine peuvent finir par devenir plusieurs fois ce nombre de fragments de faible valeur couvrant des éclats de la plage d'origine. Regrouper les modifications structurelles, en insérant tout le nouveau bloc en un seul appel plutôt qu'une ligne à la fois, garde le nombre de règles lié au nombre d'ancres véritablement distinctes plutôt qu'au nombre de modifications effectuées
Le partitionnement de règles et la normalisation de priorité font partie du comportement standard du moteur XLSX dans le composant Excel Delphi HotXLS pour Delphi et C++Builder ; la page produit porte la référence complète de l'API d'édition de feuille de calcul, y compris les méthodes de mise en forme conditionnelle et de validation de données décrites ici