Mise en forme conditionnelle et texte enrichi HotXLS en Delphi

La règle était bien là. Excel l’affichait dans le gestionnaire des règles, la plage était correcte, la formule aussi — et pourtant aucune cellule n’était mise en évidence. C’est le premier contact le plus fréquent avec la mise en forme conditionnelle programmée : une règle cellIs décrit une condition, mais la mise en évidence vient d’un enregistrement de style différentiel séparé ; une règle écrite sans ce style s’évalue volontiers à "vrai, ne rien modifier". Avec HotXLS depuis Delphi, où vous assemblez ces éléments vous-même au lieu de passer par la boîte de dialogue d’Excel, cette séparation entre condition et conséquence est la première chose à intégrer.

HotXLS écrit la mise en forme conditionnelle nativement dans les fichiers BIFF8 .xls comme dans les fichiers OOXML .xlsx, aux côtés des segments de texte enrichi et d’un modèle de styles mutualisés. Les trois fonctionnalités interagissent plus que la surface API ne le laisse penser, et cet article cartographie les endroits où la sortie diverge de l’intention.

Une condition a besoin d’une conséquence : le style dxf

Sur la feuille XLSX, les règles de comparaison sont créées avec AddConditionalFormat, qui renvoie l’index de la nouvelle règle dans la collection ConditionalFormats de la feuille. L’objet règle renvoyé porte une propriété Style — le format différentiel (dxf en terminologie OOXML, selon ECMA-376) qu’Excel applique lorsque la condition est satisfaite. L’omettre produit la règle invisible du paragraphe d’ouverture.

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Idx: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('kpi.xlsx');
    Sheet := Book.Sheets[0];

    // Negative variance: light red fill
    Idx := Sheet.AddConditionalFormat('D2:D200', xlsxCfOpLessThan, '0');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    // Duplicate order IDs get flagged the same way
    Idx := Sheet.AddCondFormatDuplicateValues('A2:A200');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFEB9C);

    // Custom formula rule: highlight rows where actual misses 90% of target
    Idx := Sheet.AddCondFormatExpression('B2:B200', '$C2<$B2*0.9');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    Book.SaveAs('kpi-flagged.xlsx');
  finally
    Book.Free;
  end;
end;

Les couleurs sont ici des valeurs ARGB 32 bits ; $FFFFC7CE correspond donc au "rouge clair" familier d’Excel avec un octet alpha totalement opaque devant. La famille des règles textuelles — AddCondFormatContainsText, AddCondFormatBeginsWith, AddCondFormatEndsWith et leurs voisines — suit le même motif créer-puis-styliser, tout comme AddCondFormatTop10, AddCondFormatAboveAverage et les détecteurs de cellules vides ou en erreur.

Barres de données, échelles de couleurs et jeux d’icônes se peignent eux-mêmes

Les types de règles visuelles sont le miroir inverse : ils portent leur apparence dans la définition de la règle elle-même et ignorent entièrement la propriété Style. Assigner un remplissage à une règle de barre de données ne fait rien, ce qui ressemble à un bogue tant que l’on ne connaît pas la taxonomie des règles. AddCondFormatDataBar reçoit directement la couleur de la barre ; les échelles de couleurs à deux ou trois points reçoivent leurs couleurs d’extrémité ; AddCondFormatIconSet choisit l’un des 26 types de jeux d’icônes, comme icsTrafficLights3.

Les paramètres intéressants de ces appels sont les ancres de valeur, typées TXLSCfValueKind : l’extrémité d’une barre ou d’une échelle peut être placée au minimum ou au maximum de la plage, sur un nombre littéral, un pourcentage, un percentile ou le résultat d’une formule. Les ancres par défaut minimum-de-plage et maximum-de-plage paraissent correctes sur des données de démonstration, puis induisent en erreur sur des données de production avec valeurs aberrantes — une valeur extrême aplatit toutes les autres barres. Pour les tableaux de bord comparés d’une période à l’autre, fixez les extrémités sur des nombres ou des percentiles afin qu’une "demi-barre" signifie la même chose en mars et en avril.

Le writer hérité est un sous-ensemble, pas un portage

La façade XLS peut créer exactement quatre formes de règles conditionnelles : barres de données, échelles à deux ou trois couleurs, et jeux d’icônes, écrits comme enregistrements CF12 dans le flux BIFF8. Elle ne fournit pas d’API de création pour les règles cellIs, d’expression ou de texte. Les règles de ces types déjà présentes dans un fichier ouvert sont conservées et réécrites telles quelles ; le round-trip d’un .xls client n’abîme donc pas sa mise en forme. Mais un générateur qui doit mettre en évidence des seuils en sortie .xls doit l’imiter en appliquant depuis le code des remplissages de cellule ordinaires, ou déplacer le livrable vers .xlsx où toute la famille de règles est disponible.

Cette asymétrie mérite d’être exposée tôt dans un projet, car elle change le choix de format de fichier pour tout ce qui ressemble à un tableau de bord. Une équipe standardisée sur .xls pour raisons de compatibilité découvre généralement cette contrainte en troisième semaine, une fois la couche de données déjà construite.

Empilement des règles, priorité et plages qui se chevauchent

Les vrais tableaux de bord ont rarement une seule règle par plage. Une colonne d’écart peut porter une barre de données pour l’amplitude, plus une règle cellIs pour le seuil dur, et une règle d’expression au niveau de la ligne pour les escalades. Chaque TXLSXConditionalFormat expose une valeur Priority, et Excel évalue les règles concurrentes dans l’ordre de priorité — lorsqu’une même cellule peut être peinte par deux règles, le résultat est donc défini par des nombres que vous contrôlez, pas par l’ordre dans lequel un auditeur les lit dans la boîte de dialogue.

L’approche maintenable consiste à traiter les priorités comme le z-order d’un outil de dessin : attribuez-les délibérément dès que deux règles peuvent toucher les mêmes cellules, et laissez un écart entre les valeurs afin de pouvoir insérer une règle plus tard sans tout renuméroter. Lorsque les règles n’interagissent vraiment pas — une barre de données en colonne E, une règle de texte en colonne G — laissez l’ordre de création faire son travail et consacrez plutôt l’effort de revue aux limites de plages. Les bogues coûteux dans cette zone sont presque jamais des inversions de priorité ; ce sont des plages comme B2:B200 sur un rapport qui compte désormais 350 lignes, où la fin non couverte ressemble exactement à une ligne saine. Dérivez chaque plage de règle de la variable de nombre final de lignes, avec la même discipline que pour les séries de graphiques et les plages de validation ailleurs dans la bibliothèque.

Une habitude de vérification se rembourse vite : après génération, ouvrez le fichier dans Excel, sélectionnez la plage de règles et parcourez le gestionnaire des règles à chaque changement de modèle. La mise en forme conditionnelle fait partie des rares fonctionnalités dont le seul moteur de rendu faisant autorité est l’application consommatrice, et un contrôle visuel de soixante secondes détecte ce qu’aucun test unitaire du XML ne verra.

Texte enrichi : plusieurs formats dans une même cellule

Dans le modèle XLSX, le texte enrichi est une liste de segments, chacun avec ses attributs de police, assemblée à part puis attachée à une cellule. La règle de possession est ce qui mord : assigner à Cell.RichText transfère la possession de l’objet TXLSXRichText à la cellule, qui le libérera quand elle sera détruite. Le libérer vous-même ensuite crée un double-free qui peut ne planter que bien plus tard, dans du code sans rapport.

var
  Rich: TXLSXRichText;
  Run: TXLSXRichTextRun;
begin
  Rich := TXLSXRichText.Create;
  Rich.AddRunText('Status: ');
  Run := Rich.AddRunText('OVERDUE');
  Run.Bold := True;
  Run.Color := $FFC00000;
  Run.ColorIsAuto := False;
  Run := Rich.AddRunText(' — escalated to regional manager');
  Run.Italic := True;
  Sheet.Cells[2, 7].RichText := Rich;   // ownership moves to the cell: do not Free
end;

Notez le ColorIsAuto := False explicite : une affectation de couleur de segment ne prend effet qu’une fois l’indicateur de couleur automatique désactivé, et l’oublier donne un segment en gras mais obstinément noir. Les segments gèrent aussi le barré, les variantes de soulignement et l’alignement vertical pour exposant et indice, tandis que PlainText aplatit toute la liste en chaîne pour l’export ou la comparaison.

La façade XLS n’a pas d’API publique pour écrire du texte enrichi au niveau cellule ; les segments y sont disponibles sur les commentaires et les zones de texte via TextRuns, et les chaînes enrichies lues depuis un fichier existant survivent intactes à un round-trip. Comme l’écart de mise en forme conditionnelle, cela oriente les rapports à formats mixtes vers le writer XLSX.

Le pool de styles et le décalage de un qui part en production

Le style de cellule ordinaire dans le modèle XLSX passe par des collections mutualisées sur le classeur : Fonts.Add, Fills.AddSolid, Borders.Add renvoient chacune un index dans leur pool. Ces index renvoyés sont basés sur 0 ; les propriétés côté cellule comme FontIndex traitent 0 comme "par défaut", donc l’index du pool doit être incrémenté de un lors de l’affectation :

HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);  // pool index, 0-based
for Col := 1 to 6 do
  Sheet.Cells[1, Col].FontIndex := HeaderFont + 1;          // cell index, 1-based

Oubliez le + 1 et chaque en-tête s’affiche silencieusement avec la police par défaut — pas d’exception, pas d’avertissement, seulement un classeur qui semble non stylé. La faute de second ordre consiste à appeler Fonts.Add dans la boucle de lignes : les définitions de police identiques sont dédupliquées, mais créer des entrées de pool par ligne gaspille du travail, et certains pools renvoient un nouvel objet à chaque appel. Construisez une fois les quelques styles avant la boucle et réutilisez les index ; sur des rapports de centaines de milliers de lignes, c’est l’une des différences détaillées dans le réglage des performances des grands classeurs avec HotXLS. Pour des apparences sémantiques ponctuelles, les deux façades offrent aussi ApplyBuiltinStyle sur les plages, correspondant aux styles Excel intégrés Good, Bad, Neutral et aux styles d’accent, sans manipuler directement les pools.

La mise en forme conditionnelle et le texte enrichi sont généralement le dernier kilomètre d’un pipeline de rapport — le modèle de données et les décisions de mise en page qui les précèdent sont couverts dans la génération de rapports à base de modèles avec HotXLS. La référence complète des règles, segments et styles se trouve sur la page produit HotXLS Component.