Formats conditionnels Excel en Delphi avec HotXLS

Une règle Top 10 pourcent posée sur une colonne de 27 chiffres de ventes, et Excel peint trois cellules. Dix pour cent de 27 font 2,7, donc un moteur de rendu qui arrondit obtient trois, un moteur qui prend le plafond obtient trois, et un moteur qui tronque obtient deux. Deux des trois suppositions tombent juste sur cette colonne, et les deux sont fausses : Excel tronque le compte à deux puis peint aussi la troisième cellule parce qu'elle égale la seconde à 88. Changez une valeur et les suppositions chanceuses cessent de tomber juste

Toute la difficulté de dessiner la mise en forme conditionnelle hors d'Excel est là. Les règles sont dans le fichier ; la réponse à « quelles cellules s'allument, et en quelle couleur » ne l'est pas. Si votre sortie est un rapport HTML, un PDF ou une grille que vous peignez vous-même, il faut la calculer, et les familles de règles d'agrégats sont là où l'arithmétique se cache. La création de règles est un sujet à part, traité dans la mise en forme conditionnelle et les styles de texte enrichi, et ce qui arrive à la plage d'une règle quand les lignes bougent est traité dans le partitionnement des formats conditionnels ancrés. Cette note parle d'évaluation

Ce que l'élément conditionalFormatting dit et ne dit pas

ECMA-376 et ISO 29500-1 définissent le stockage, et ils s'arrêtent là. Un élément conditionalFormatting (§18.3.1.18) porte un sqref et une liste d'enfants cfRule (§18.3.1.10). Chaque règle a un type, un operator optionnel, une priority, un drapeau stopIfTrue, un ou deux enfants formula et, pour les familles visuelles, un ensemble de seuils cfvo. Tout cela enregistre fidèlement ce que l'utilisateur a cliqué dans la boîte de dialogue. Rien de tout cela n'est un algorithme

Pour beaucoup de types de règles, l'écart est sans conséquence. cellIs avec operator="greaterThan" signifie supérieur à, et containsText signifie que la sous-chaîne est présente. L'ennui commence avec les règles dont le verdict dépend du reste de la plage : top10 avec percent="1" ne dit pas comment un compte fractionnaire s'arrondit, aboveAverage avec stdDev="1" ne dit pas si l'écart est d'échantillon ou de population, et aucun des deux ne dit si une valeur égale au seuil qualifie. Se tromper, et votre PDF est en désaccord avec le classeur que le client a ouvert dans la fenêtre d'à côté

TCondFormatRule.Evaluate : ce qu'une cellule peut décider seule

HotXLS a deux surfaces d'évaluation, et elles diffèrent par ce qu'elles acceptent de répondre. Le moteur classique (.xls) expose TCondFormatRule.Evaluate dans lxCondFormat.pas, qui prend un calculateur, un index de feuille, une position de cellule et la valeur de la cellule, et retourne un Boolean pour une règle. Il couvre les huit opérateurs cellIs (entre, hors de, égal, différent, supérieur, inférieur, supérieur ou égal, inférieur ou égal), les règles expression libres évaluées à la cellule pour que les références relatives se rebasent correctement, les quatre prédicats de texte, et les prédicats de vide et d'erreur. Les formules de seuils sont résolues à la position de la cellule, et une règle between dont les bornes arrivent inversées les échange plutôt que de la rejeter. Les comparaisons de texte sont insensibles à la casse, comme dans Excel

// Moteur classique : une règle, une cellule, un Boolean
function AnyRuleMatches(Calculator: TXLSCalculator; CondFormat: TCondFormat;
  SheetIndex, Row, Col: Integer; const Value: Variant): Boolean;
var
  I: Integer;
begin
  Result := False;
  for I := 0 to CondFormat.RuleCount - 1 do
    // top10, moyennes, doublons, périodes de dates et types visuels
    // répondent False ici ; ils ont besoin de toute la plage
    if CondFormat.Rule(I).Evaluate(Calculator, SheetIndex, Row, Col, Value) then
      Exit(True);
end;

La méthode retourne False pour top10, aboveAverage, belowAverage, duplicateValues et uniqueValues. Une cellule ne peut pas savoir si elle est dans les dix pour cent du haut sans voir les autres valeurs, donc répondre reviendrait à deviner. Les règles de dates relatives (timePeriod) répondent aussi False sur ce chemin. Les quatre familles visuelles, dataBar, colorScale2, colorScale3 et iconSet, répondent False pour une autre raison : elles ne produisent jamais un oui ou un non, elles produisent une couleur, une longueur de barre ou un index d'icône, et un Boolean n'a nulle part où les mettre. Un False de cet appel signifie donc « pas décidé ici » aussi souvent que « ne correspond pas », ce qui convient pour un contrôle rapide sur des règles de comparaison et reste risqué pour tout ce que vous expédiez

Comment un évaluateur de feuille évite-t-il de rescanner la feuille pour chaque cellule ?

Il fait tout le travail à l'échelle de la plage une seule fois, à sa création. TXLSXConditionalFormatEvaluator dans lxHandleX.pas est un instantané immuable des mises en forme conditionnelles d'une feuille, obtenu via TXLSXWorksheet.CreateConditionalFormatEvaluator ; l'appelant le possède et le libère. L'alternative naïve, où chaque cellule peinte déclenche un balayage de sa plage de règles pour recalculer une moyenne ou un rang, transforme un export de 10 000 cellules en 10 000 balayages de plage par règle

À la construction, l'évaluateur analyse chaque sqref distinct une fois, si bien que dix règles sur la même plage partagent une analyse et une passe sur ses cellules peuplées. Cette passe collecte moyenne, écart-type de population, minimum et maximum en un seul parcours, et ne garde une copie triée des nombres que quand une règle Top/Bottom ou un seuil de percentile a réellement besoin de statistiques d'ordre. Les règles de doublons et d'unicité font construire et trier leurs clés de valeur une fois, pour que chaque recherche ensuite soit une recherche binaire. Les formules des règles sont compilées une fois et réévaluées par cellule avec le décalage de la cellule appliqué, de sorte que =A1>AVERAGE($A$1:$A$100) garde ses parties relative et absolue exactement comme elles se comporteraient dans Excel sans être réanalysée à chaque ligne. Les lignes sont groupées pour que EvaluateCell ne visite que les règles dont les plages peuvent atteindre la ligne demandée

Les règles sont ensuite appliquées dans l'ordre des priority, du plus petit nombre d'abord, les égalités départagées par la position dans le fichier. Chaque propriété de mise en forme est prise à la première règle correspondante qui la définit, et les règles de priorité inférieure ne remplissent que les propriétés encore vides : une règle de priorité 1 qui met en gras et une règle de priorité 2 qui pose un remplissage rouge s'affichent toutes les deux, et la cellule sort en gras sur rouge. Une correspondance sur une règle avec StopIfTrue termine le parcours pour cette cellule

procedure PaintConditional(Sheet: TXLSXWorksheet; Canvas: TCanvas;
  FirstRow, LastRow, FirstCol, LastCol: Integer);
var
  Evaluator: TXLSXConditionalFormatEvaluator;
  Res: TXLSXCfCellResult;
  Row, Col: Integer;
begin
  // Un instantané par passe de rendu ; lignes et colonnes commencent à 1
  Evaluator := Sheet.CreateConditionalFormatEvaluator;
  try
    for Row := FirstRow to LastRow do
      for Col := FirstCol to LastCol do
      begin
        if not Evaluator.EvaluateCell(Row, Col, Res) then
          Continue;
        if Res.HasFillColor then
          Canvas.Brush.Color := TColor(Res.FillColor and $00FFFFFF);  // BGR, même disposition que TColor
        if Res.HasDataBar then  // DrawBar est votre propre routine
          DrawBar(Row, Col, Res.DataBarAxis, Res.DataBarEnd, Res.DataBarColor);
        if Res.HasIcon then     // DrawIcon est votre propre routine
          DrawIcon(Row, Col, Res.IconSetType, Res.IconIndex);
        if Res.ShowCellValue and Res.HasNumberFormat and
           Sheet.Cells.HasCell(Row, Col) then  // DrawCellText est votre propre routine
          DrawCellText(Row, Col,
            Evaluator.FormatValue(Sheet.Cells[Row, Col].Value, Res.NumberFormatId));
      end;
  finally
    Evaluator.Free;
  end;
end;

L'instantané est construit une fois par passe volontairement. Le créer dans la boucle interne recompile et re-balance tout pour chaque cellule, ce qui est précisément le coût que la conception existe pour éviter. ShowCellValue revient False quand une règle de barre de données a showValue="0" ou qu'un jeu d'icônes est réglé pour ne montrer que l'icône, si bien que le moteur dessine la barre ou le glyphe et laisse le nombre de côté

Combien de cellules une règle Top 10 pourcent surligne-t-elle vraiment ?

Le compte est tronqué, jamais autorisé sous un, et toute valeur à égalité avec le seuil est incluse. La spécification ne le dit pas ; c'est ce que fait Excel, et vous le confirmez en une minute en tapant les 27 valeurs de la figure dans une colonne, en appliquant Top 10% et en comptant les cellules peintes. TXLSXConditionalFormatEvaluator calcule le compte de rang comme Floor(Count * Min(Rank, 100) / 100), le monte à 1 quand il tombe à zéro, le plafonne au nombre de cellules numériques peuplées, lit le seuil dans les valeurs triées, puis compare chaque cellule avec >=. Pour la colonne de 27 valeurs, cela donne un compte de deux, un seuil de 88 et trois cellules peintes parce que deux d'entre elles portent 88. Les règles Bottom-N font le miroir avec <=

Les règles au-dessus de la moyenne portent la seconde ambiguïté. Avec stdDev="1", la règle sélectionne les cellules à au moins un écart-type au-dessus de la moyenne, et l'écart d'échantillon et l'écart de population diffèrent de la correction de Bessel, ce qui compte le plus sur les courtes plages où les gens utilisent réellement cette règle. Sur cinq valeurs, les deux diffèrent d'environ 12 pour cent. HotXLS utilise l'écart-type de population, le même chiffre que retourne STDEV.P, si bien qu'un moyen rapide de vérifier n'importe quel moteur est de poser =AVERAGE(r)+STDEV.P(r) à côté de la plage et de voir quelles cellules le dépassent. Les comparaisons contre une bande d'écart sont inclusives ; la comparaison simple au-dessus ou en dessous est stricte sauf si la règle porte equalAverage="1"

Les règles de doublons et d'unicité dépendent de ce qui compte comme « la même valeur ». Le nombre 100 et le texte « 100 » partagent une clé, parce que le texte numérique est normalisé dans l'espace des clés numériques ; les clés de texte se comparent sans casse ; TRUE et 1 restent distincts, tout comme les valeurs d'erreur de différentes sortes. Le texte contenant * ou ? est apparié comme motif à jokers, à la COUNTIF, si bien qu'une cellule portant Q* compte contre chaque valeur qui commence par Q. Les cellules vides sont le piège inverse : une cellule réellement vide dans la plage n'est jamais peinte comme doublon, si bien qu'une colonne inachevée ne s'allume pas de haut en bas, tandis qu'une cellule qui stocke une chaîne vide est comptée avec les vides

Échelles de couleur, barres de données et jeux d'icônes reviennent en nombres

Les familles visuelles se résolvent en valeurs prêtes au rendu, et leurs bords sont là où les moteurs écrits à la main dérivent. Pour une échelle de couleur, EvaluateCell borne la position à l'intervalle 0 à 1 et interpole chaque canal RGB avec troncature, si bien qu'une valeur sous l'arrêt minimum obtient la couleur minimum plutôt qu'une couleur extrapolée. Une échelle à trois arrêts utilise la paire milieu-max pour les valeurs au-dessus du seuil médian et la paire min-milieu sinon. Une échelle dont le premier et le dernier seuil se résolvent au même nombre retourne la dernière couleur au lieu de diviser par zéro. Les seuils de percentile sont interpolés linéairement entre valeurs triées voisines, la définition PERCENTILE.INC

Les barres de données sont normalisées à la largeur de la cellule. Pour une plage toute positive, DataBarAxis vaut 0 et DataBarEnd court entre la longueur de barre minimum et maximum de la règle, par défaut 10 et 90 pour cent, si bien que la plus petite valeur reçoit quand même une petite barre et que la plus grande ne remplit jamais tout à fait la cellule. Quand la plage enjambre zéro, l'axe se place à sa position proportionnelle et DataBarEnd marque où la barre s'arrête, du côté opposé de l'axe pour les valeurs négatives. Direction, dégradé, bordure et couleurs de barre négative sont remplis sur le même enregistrement, avec les couleurs de thème et les teintes déjà résolues

Les jeux d'icônes demandent du soin sur la rigueur de chaque seuil, car chaque cfvo après le premier porte son propre drapeau gte. L'évaluateur remonte les seuils, appliquant >= ou > par seuil comme le dit ThresholdEqualsInclude, et le seuil le plus haut que la valeur franchit gagne. Un jeu inversé retourne l'index résolu, les surcharges par icône peuvent substituer un glyphe d'une autre famille via IconSetType, et un seuil qui ne se résout pas arrête la règle pour cette cellule, si bien que la cellule n'a pas d'icône du tout plutôt qu'une fausse plausible

Un résultat pour la grille, l'export HTML et le PDF

TXLSXCfCellResult porte tout ce dont un moteur a besoin : remplissage et couleur de police avec teinte de thème appliquée, gras, italique, souligné, barré, un id de format numérique, la géométrie et les couleurs de barre de données, et la famille et l'index d'icône. HotXLS alimente le même enregistrement à son export HTML, son export PDF et TXLSWorkbookViewer, ce qui garde les trois sorties d'accord cellule pour cellule. Le visualiseur crée un évaluateur pour la feuille active la première fois qu'il peint et le réutilise à travers défilement, sélection et repeint, et le libère quand vous changez de feuille ou affectez un autre classeur

// Édition derrière un visualiseur en direct : l'instantané en cache ne voit pas le changement
Sheet := Viewer.XlsxWorkbook.Sheets[1];   // index de feuille à base 1
Sheet.Cells[5, 2].Value := 4200;          // déplace la moyenne, le max et le classement
Viewer.RefreshConditionalFormats;         // jette l'instantané et repeint

L'instantané étant immuable, une édition sur place laisse moyennes, rangs et extrémités d'échelle de couleur périmés jusqu'à ce que RefreshConditionalFormats s'exécute. La même chose vaut pour votre propre code : un évaluateur que vous gardez à travers les éditions doit être libéré et recréé, car CreateConditionalFormatEvaluator capture les valeurs au moment de son appel

Les règles de dates relatives méritent une décision dès le départ. L'évaluateur de feuille compare les règles timePeriod à la date courante de la machine, si bien qu'une règle « cette semaine » se rend d'une façon dans un PDF produit aujourd'hui et d'une autre quand le même classeur est exporté mardi prochain. C'est un comportement correct et c'est pourtant un ticket de support assuré si votre archive est censée être reproductible. Pour les rapports archivés, remplacez les règles timePeriod par une règle expression qui compare à une date de rapport stockée dans une cellule, comme =AND(A2>=$Z$1-6,A2<=$Z$1) pour une fenêtre de sept jours ; la sortie dépend alors du classeur et de rien d'autre. Excel refuse aussi les références structurées de tableau dans une formule de mise en forme conditionnelle, donc pointez la règle vers la plage de cellules de la colonne à la place

Si la cible est un écran plutôt qu'un fichier, le même enregistrement de résultat pilote aussi la grille tableur VCL personnalisée décrite ailleurs sur ce blog. Le modèle de règles, la référence de l'API d'évaluation et les builds d'essai sont sur la page du composant tableur Delphi HotXLS