Mise en forme conditionnelle et texte enrichi HotXLS en Delphi

Une règle de mise en forme conditionnelle dans OOXML est deux choses distinctes portant le même nom. La condition (une comparaison, une formule, une correspondance de texte) décide quelles cellules sont concernées. L'apparence (un enregistrement de format différentiel, dxf selon la terminologie ECMA-376) décide à quoi ressemblent ces cellules. La boîte de dialogue d'Excel cache la couture en vous faisant remplir les deux à la fois. HotXLS, lui, ne le fait pas. Créez une règle cellIs depuis Delphi sans définir le style, et la règle est valide, la plage est correcte, la formule s'évalue à vrai sur exactement les bonnes cellules, et rien ne change de couleur, car l'instruction de la règle était « vrai, ne peins rien ». Ce décalage entre condition et conséquence est la première chose à maîtriser, et il explique la plupart des règles qui semblent correctes dans le gestionnaire des règles mais ne surlignent rien.

HotXLS écrit la mise en forme conditionnelle nativement dans les fichiers BIFF8 .xls et OOXML .xlsx, et fait de même pour les segments de texte enrichi et un modèle de style de cellule mutualisé. Les trois fonctionnalités partagent plus de mécanismes internes que la surface plate de l'API ne le suggère, et les endroits où la sortie dévie de l'intention sont généralement les jonctions entre elles.

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 prend une plage, un opérateur issu de TXLSXCfOperator, et une formule ou un littéral, puis retourne l'index de la nouvelle règle dans la collection ConditionalFormats de la feuille. L'objet règle à cet index expose une propriété Style, et c'est là que vit le surlignage. Définissez un remplissage dessus et les cellules qualifiantes prennent ce remplissage. Laissez-le intact et vous avez construit la règle invisible décrite ci-dessus.

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 ici sont des valeurs ARGB 32 bits, donc $FFFFC7CE est le « rouge clair » d'Excel que vous connaissez de la boîte de dialogue, avec un octet alpha totalement opaque devant le RVB. Chaque type de règle qui se déclenche sur une condition par cellule suit la même forme créer-puis-styliser. Les correspondances textuelles (AddCondFormatContainsText, AddCondFormatBeginsWith, AddCondFormatEndsWith) retournent un index que vous stylisez ensuite, tout comme AddCondFormatTop10, AddCondFormatAboveAverage, et les détecteurs de cellules vides et d'erreurs. Apprenez le modèle une fois et toute la famille texte-et-comparaison se comporte de la même façon.

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

Les types de règles visuelles fonctionnent à l'inverse. Ils portent leur apparence à l'intérieur de la définition de la règle et ignorent complètement la propriété Style. Assigner un remplissage à une règle de barre de données ne change rien, ce qui ressemble à un bogue jusqu'à ce que la taxonomie soit claire : AddCondFormatDataBar prend la couleur de la barre comme argument direct, les échelles de couleurs à deux et trois points prennent leurs couleurs d'extrémité de la même façon, et AddCondFormatIconSet sélectionne l'un des 26 types de jeux d'icônes tels que icsTrafficLights3. Il n'y a pas d'enregistrement de style séparé à oublier ici, car il n'y en a tout simplement pas.

Les paramètres sur lesquels réfléchir dans ces appels sont les ancres de valeur, typées TXLSCfValueKind. Une extrémité de barre ou d'échelle peut se placer au minimum ou maximum de la plage, à un nombre littéral, à un pourcentage ou percentile, ou au résultat d'une formule. Les valeurs par défaut, min-de-plage et max-de-plage, se comportent correctement sur des données de démonstration soignées, puis vous trahissent sur des données réelles avec des valeurs aberrantes : une valeur extrême étire l'échelle et aplatit chaque autre barre en un moignon. Lorsqu'un tableau de bord doit être lisible sur plusieurs périodes, ancrez les extrémités à des nombres fixes ou des percentiles, afin qu'une demi-barre en mars signifie la même quantité qu'une demi-barre en avril. Une barre auto-mise à l'échelle n'est comparable qu'à elle-même.

Le writer XLS couvre quatre types de règles, pas plus

La façade BIFF8 héritée n'est pas un miroir réduit de la façade XLSX ; c'est un sous-ensemble délibéré. La façade XLS peut créer exactement quatre formes de règles conditionnelles, les barres de données, les échelles à deux couleurs, les échelles à trois couleurs, et les jeux d'icônes, émises comme enregistrements CF12 dans le flux. Elle n'a pas d'API de création pour les règles cellIs, d'expression ou de texte. Les règles de ces types qui existent déjà dans un fichier ouvert sont lues, conservées, et réécrites inchangées, donc ouvrir et réenregistrer un .xls client n'endommage jamais sa mise en forme. Ce que vous ne pouvez pas faire, c'est générer un surlignage par seuil de zéro dans un .xls. Les choix là-dedans sont de le simuler avec des remplissages de cellules ordinaires calculés dans le code, ou de faire que le livrable soit un .xlsx, où la famille complète des règles est disponible.

C'est une contrainte à régler avant que la couche de données existe, pas après, car elle change la décision de format de fichier pour tout ce qui ressemble à un tableau de bord. Une équipe qui a choisi .xls pour la compatibilité puis spécifie un rapport KPI avec des seuils cellIs a choisi deux choses qui ne vont pas ensemble, et le moment le moins coûteux pour s'en apercevoir est lors de la décision de format plutôt que trois semaines après le début du développement.

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

Les vrais tableaux de bord font rarement tourner une seule règle par plage. Une colonne d'écart peut porter une barre de données pour l'amplitude, une règle cellIs pour le seuil dur, et une règle d'expression au niveau ligne au-dessus des deux pour les escalades. Chaque TXLSXConditionalFormat expose une valeur Priority, et Excel résout les règles concurrentes dans l'ordre de priorité. Quand deux règles veulent peindre la même cellule, le gagnant est déterminé par un nombre que vous définissez, pas par l'ordre dans lequel un réviseur fait défiler le gestionnaire des règles.

Traitez la priorité comme un logiciel de dessin traite l'ordre Z. Assignez-la délibérément chaque fois que deux règles peuvent atteindre les mêmes cellules, et laissez des écarts entre les valeurs pour qu'une règle ultérieure puisse s'insérer sans renuméroter le reste. Là où les règles ne peuvent pas entrer en collision, disons une barre de données confinée à la colonne E et une règle de texte confinée à la colonne G, l'ordre de création suffit et la priorité ne vaut pas l'attention. Consacrez cette attention aux limites des plages à la place, car les bugs coûteux ici ne sont presque jamais des inversions de priorité. Ce sont des plages comme B2:B200 sur un rapport qui a grandi jusqu'à 350 lignes, où la queue non couverte ressemble exactement à des données saines. Dérivez chaque plage de règle de la même valeur de nombre-de-lignes-final qui pilote les séries de graphiques et les plages de validation ailleurs dans le classeur, et la queue arrête de tomber.

Une habitude de vérification se justifie. Après la génération, ouvrez le fichier dans Excel, sélectionnez la plage mise en forme, et parcourez le gestionnaire des règles une fois pour chaque changement de modèle. La mise en forme conditionnelle est l'une des rares zones où le seul moteur de rendu faisant autorité est l'application qui consomme le fichier, donc un test unitaire sur le XML prouve que la règle a été écrite, pas qu'Excel la peint comme vous le souhaitiez. Une minute d'observation visuelle comble cet écart.

Texte enrichi : plusieurs formats dans une même cellule

Dans le modèle XLSX, une cellule de texte enrichi contient une liste de segments, où chaque segment est une portion de texte plus ses propres attributs de police. Vous construisez la liste à part en tant qu'objet TXLSXRichText, vous lui ajoutez des segments, puis vous attachez le tout à une cellule. La règle de possession est la partie qui mord. Assigner à Cell.RichText transfère la possession de cet objet à la cellule, et la cellule le libère lors de sa propre destruction. Le libérer vous-même également produit un double-free, le genre qui reste silencieux lors de l'exécution qui l'a causé et se manifeste comme un plantage quelque part sans rapport bien plus tard.

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;

Le ColorIsAuto := False explicite n'est pas une décoration optionnelle. Un segment porte un indicateur de couleur automatique, et une affectation de couleur n'est honorée qu'une fois cet indicateur effacé. Définissez Color et oubliez ColorIsAuto et le segment ressort en gras mais obstinément noir, sans erreur pointant vers la cause. 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 une seule chaîne quand vous avez besoin d'exporter ou de comparer le contenu textuel.

Le texte enrichi au niveau cellule est réservé à XLSX. La façade XLS n'a pas d'API publique pour l'écrire, bien que les segments soient disponibles là sur les commentaires et les zones de texte via TextRuns, et que les chaînes enrichies lues depuis un .xls existant survivent intactes à un aller-retour. L'attraction est la même que pour la mise en forme conditionnelle : tout ce qui mélange des formats à l'intérieur d'une cellule appartient au 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, et Borders.Add enregistrent chacun une définition et retournent son index dans le pool. Ces index sont en base 0. Les propriétés côté cellule qui les consomment, comme FontIndex, réservent 0 pour « par défaut », donc la valeur que vous assignez à une cellule est l'index du pool plus un :

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 revient à la police par défaut. Il n'y a pas d'exception et pas d'avertissement, seulement un classeur qui semble que personne ne l'a stylé. L'erreur de second ordre se cache dans la boucle : appeler Fonts.Add une fois par ligne. Les définitions de police identiques sont dédupliquées, donc le fichier n'est pas corrompu, mais le travail est gaspillé, et le pool d'alignements en particulier renvoie un nouvel objet à chaque appel plutôt que de replier les doublons. Construisez la poignée de styles une fois avant la boucle et réutilisez leurs index. Sur des rapports de centaines de milliers de lignes, ce seul changement est l'un des leviers couverts dans le réglage des performances des grands classeurs pour HotXLS. Quand vous avez seulement besoin d'une apparence sémantique standard, les deux façades exposent ApplyBuiltinStyle sur les plages, qui mappe vers les styles Excel intégrés Bon, Mauvais, Neutre et les styles accent sans que vous touchiez les pools.

La mise en forme conditionnelle, le texte enrichi et les styles mutualisés sont la dernière étape d'un rapport, appliqués après que le modèle de données et la mise en page sont fixés, et ces étapes antérieures sont le sujet de 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.