HotXLS est un composant tableur natif pour Delphi et C++Builder, et depuis la version 2.209.0 il peut répondre à la question qu Excel garde normalement pour lui-même : pour cette cellule précise, quelles règles de mise en forme conditionnelle se déclenchent, et vers quel remplissage, police, barre de données ou icône se résolvent-elles. Cette réponse est ce dont vous avez besoin dès que votre sortie est un rapport HTML, un PDF, ou une grille que vous peignez vous-même
C est un problème différent de la création de règles. Deux notes précédentes couvrent le côté rédaction : la mise en forme conditionnelle et les styles de texte enrichi traite de l attachement de règles et de formats différentiels à une plage, et le partitionnement des formats conditionnels ancrés traite de ce qui arrive à la plage d une règle quand des lignes et des colonnes sont insérées ou supprimées. Les deux sont structurelles. Celle-ci concerne la sémantique : étant donné un classeur qui porte déjà des règles, calculer la surbrillance
Pourquoi le format de fichier ne vous dit-il pas quelles cellules s allument
La réponse courte est qu ECMA-376 et ISO 29500-1 définissent le stockage, pas l évaluation. Un élément conditionalFormatting (§18.3.1.18) porte un sqref et une liste d enfants cfRule (§18.3.1.10), et chaque règle porte un type, un operator optionnel, une priority, un indicateur stopIfTrue, un ou deux enfants formula, et pour les familles visuelles un ensemble de seuils cfvo. Chacun d eux décrit fidèlement ce que l utilisateur a configuré, et aucun n est un algorithme. Pour la moitié des types de règle, cet écart n a pas d importance : cellIs avec operator="greaterThan" signifie supérieur à, et containsText signifie que la sous-chaîne est présente. L écart s ouvre sur les familles agrégées. Une règle top10 avec rank="10" et percent="1" sur 27 cellules numériques peuplées met en surbrillance combien de cellules ? Deux virgule sept n est pas un nombre. Arrondir, tronquer vers le bas, ou vers le haut — la spécification est muette, et faire le mauvais choix signifie que votre PDF sera en désaccord avec le classeur que le client a ouvert à côté
Règles monocellulaires et où s arrête TCondFormatRule.Evaluate
HotXLS a d abord pris la moitié la moins chère. TCondFormatRule.Evaluate dans lxCondFormat.pas, ajouté en 2.199.0, répond si une règle se déclenche pour une cellule sans rien savoir du reste de la plage. Il gère les huit opérateurs de comparaison BIFF derrière 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 afin que les références relatives se rebasent correctement, les quatre prédicats de texte, et les prédicats de cellules vides et d erreurs. Les seuils proviennent de FFormula1 et FFormula2 résolus via TXLSCalculator.GetRangeValue à la position de la cellule, et les bornes inversées sont échangées plutôt que rejetées
var
I: Integer;
Rule: TCondFormatRule;
Value: Variant;
begin
Value := Sheet.Cells[Row, Col].Value;
for I := 0 to CondFormat.RuleCount - 1 do
begin
Rule := CondFormat.Rule(I);
// Single-cell verdict only. Aggregate and visual kinds answer False.
if Rule.Evaluate(Calculator, SheetIndex, Row, Col, Value) then
ApplyHighlight(Row, Col, Rule.Style);
end;
end;La partie honnête de cette méthode est ce qu elle refuse de deviner. top10, aboveAverage, belowAverage, duplicateValues et uniqueValues renvoient False, non parce qu elles sont difficiles mais parce qu elles sont indécidables depuis une seule cellule — chacune a besoin d une statistique sur tout le domaine. Les quatre familles visuelles, dataBar, colorScale2, colorScale3 et iconSet, renvoient False pour une raison différente : elles ne produisent jamais du tout un booléen, elles produisent une charge de rendu, et un type de retour booléen est la mauvaise forme pour elles
Comment un évaluateur au niveau feuille de calcul évite-t-il de rescanner la feuille ?
En calculant chaque quantité partagée une seule fois, à la construction, et plus jamais ensuite. TXLSXConditionalFormatEvaluator dans lxHandleX.pas est un instantané immuable pour une feuille de calcul, construit via TXLSXWorksheet.CreateConditionalFormatEvaluator, et toute sa conception est une défense contre l implémentation naïve où chaque cellule peinte déclenche un balayage complet de plage
Quatre choses se produisent dans le constructeur. Chaque sqref multi-zone distinct est analysé exactement une fois dans un TXlsxCfRangeSnapshot, de sorte que dix règles partageant une plage partagent une analyse et une passe de statistiques. Cette passe calcule en flux la moyenne, l écart-type de population, le minimum et le maximum sur les cellules peuplées en un seul parcours, et ne conserve un tableau numérique ordonné que quand une règle Top/Bottom ou de percentile en a réellement besoin pour les statistiques d ordre. Les clés de doublon et d unicité sont construites de façon sûre pour Unicode et triées par lot une fois plutôt que par recherche. Puis l axe des lignes est découpé en bandes à chaque limite de zone, de sorte que EvaluateCell effectue une recherche binaire dans une bande et ne visite que les règles dont les plages peuvent possiblement atteindre cette ligne
Le quatrième point est celui qui compte le plus à grande échelle. Une formule de règle relative telle que =A1>AVERAGE($A$1:$A$100) signifie quelque chose de différent dans chaque cellule du domaine, et l implémentation évidente compile un arbre syntaxique frais par cellule. TXlsxCfRulePlan le compile une fois et réévalue le même arbre à travers des décalages de coordonnées réversibles, ce qui préserve le comportement d ancrage d Excel sans allocation d arbre syntaxique par cellule. Les règles sont ensuite superposées par priority, et une correspondance sur une règle dont StopIfTrue est défini interrompt la boucle, exactement comme Excel court-circuite
var
Evaluator: TXLSXConditionalFormatEvaluator;
Res: TXLSXCfCellResult;
begin
Evaluator := Sheet.CreateConditionalFormatEvaluator;
try
if Evaluator.EvaluateCell(Row, Col, Res) then
begin
if Res.HasFillColor then
Canvas.Brush.Color := TColor(Res.FillColor);
if Res.HasIcon then
// IconIndex is zero-based inside Res.IconSetType
DrawIcon(Res.IconSetType, Res.IconIndex, Res.IconCount);
if Res.HasDataBar then
// DataBarAxis and DataBarEnd are normalised to 0..1
DrawBar(Res.DataBarAxis, Res.DataBarEnd, Res.DataBarColor);
if not Res.ShowCellValue then
Exit; // showValue="0" on the rule hides the number
end;
finally
Evaluator.Free;
end;
end;Comment Excel arrondit-il réellement une règle Top 10 pourcent ?
Il tronque vers le bas, avec un minimum de un, et il inclut les égalités au point de coupure. Cela n est écrit nulle part dans la norme ISO 29500-1 — cela a été fixé en sondant Excel 16 avec des classeurs construits à la main et en relisant quelles cellules l application mettait en surbrillance. HotXLS implémente exactement cela : le nombre de rang est Floor(Count * Min(Rank, 100) / 100), élevé à 1 quand il atterrit à zéro, plafonné au nombre peuplé, et la valeur de coupure est ensuite comparée avec >= de sorte que chaque cellule égale à la limite soit mise en surbrillance même quand cela dépasse le nombre demandé. Vingt-sept valeurs et une règle de 10 pourcent mettent en surbrillance deux cellules, plus toute cellule supplémentaire à égalité avec la seconde
Les règles au-dessus de la moyenne cachaient une seconde ambiguïté : aboveAverage avec stdDev="1" sélectionne les cellules à un écart-type au-dessus de la moyenne, mais les écarts-types d échantillon et de population diffèrent par la correction de Bessel et ils divergent visiblement sur de petites plages, ce qui est précisément là où la mise en forme conditionnelle est utilisée. Excel 16 utilise l écart-type de population, et HotXLS s y aligne, avec l indicateur equalAverage rendant la comparaison stricte inclusive uniquement quand aucune bande d écart n est en jeu. Les règles de doublons et d unicité activent l identité de clé à la place. Si une cellule contient le nombre 100 et une autre le texte « 100 », Excel les traite comme la même clé de doublon, donc HotXLS normalise le texte numérique dans l espace de clé numérique plutôt que de comparer des chaînes brutes. Les cellules vides sont le cas miroir : une véritable cellule vide participe au décompte de la plage mais n est pas elle-même stylée, donc les cellules vides d une colonne ne s allument pas toutes comme doublons les unes des autres
Échelles de couleur et jeux d icônes : interpolation et règles de limite
Les familles visuelles se résolvent en nombres prêts au rendu plutôt qu en booléens, et leur comportement de bord a été fixé de la même manière. Pour une échelle de couleur avec des seuils numériques explicites, HotXLS plafonne la fraction de position à l intervalle fermé zéro à un, puis interpole par canal avec troncature plutôt qu arrondi — une valeur inférieure à l arrêt minimum obtient la couleur minimale plutôt qu une couleur extrapolée, une échelle à trois arrêts choisit sa paire en comparant contre l arrêt médian, et une échelle dégénérée dont les deux extrémités portent le même seuil s effondre vers la couleur du haut au lieu de diviser par zéro. Les jeux d icônes ont eu besoin du soin opposé, car chaque cfvo après le premier porte sa propre rigueur de comparaison : HotXLS lit ThresholdEqualsInclude par seuil et applique >= ou > en conséquence, en remontant de sorte que le seuil satisfait le plus élevé gagne l index d icône. Un jeu inversé retourne l index résolu plutôt que les seuils, les surcharges par icône peuvent tirer un glyphe d une famille différente, et tout seuil invalide interrompt la règle au lieu de produire une icône fausse d apparence plausible
Alimenter une grille, un export HTML et un PDF depuis un seul résultat
Parce que EvaluateCell renvoie un TXLSXCfCellResult entièrement résolu — remplissage différentiel et couleur de police avec teinte de thème déjà appliquée, gras, italique, souligné, id de format numérique, étendues de barre positive et négative directionnelles, position d axe, famille et index d icône — chaque consommateur lit le même enregistrement et aucun n a besoin de comprendre les rouages internes des règles. HotXLS utilise ce chemin unique pour l export HTML, l export PDF et le visualiseur interactif, ce qui est la seule façon pratique d empêcher trois moteurs de rendu de diverger. La version 2.210.0 l a câblé dans TXLSWorkbookViewer, qui met en cache un évaluateur préparé par feuille de calcul active et le réutilise à travers le défilement, la sélection et le repeint, en le libérant quand le classeur ou la feuille change — reconstruire l instantané à chaque Paint anéantirait toute la conception au moment de la construction. Ce cache est aussi pourquoi TXLSWorkbookViewer.RefreshConditionalFormats existe : l instantané est immuable, donc si vous modifiez le classeur attaché sur place, les statistiques agrégées et les seuils résolus sont périmés jusqu à ce que vous l appeliez
// Editing behind a live viewer: the cached snapshot must be invalidated.
Sheet := Viewer.XlsxWorkbook.Sheets[1];
Sheet.Cells[5, 2].Value := 4200; // changes mean, min, max, ranking
Viewer.RefreshConditionalFormats; // drop evaluator, repaintCe que l évaluateur ne fera pas pour vous
Trois limites méritent d être énoncées clairement. Le TCondFormatRule.Evaluate classique monocellulaire et le TXLSXConditionalFormatEvaluator au niveau feuille de calcul sont des surfaces différentes avec des capacités différentes, et le premier décline délibérément les familles agrégées et visuelles plutôt que de les approximer — si vous avez besoin de Top/Bottom ou d une échelle de couleur, construisez l évaluateur. Les périodes de date relatives dépendent de l horloge de la machine au moment de l évaluation, donc une règle timePeriod se rend différemment dans un PDF généré aujourd hui et un généré la semaine prochaine, ce qui est un comportement correct et néanmoins un ticket de support en attente si votre archive doit être stable octet pour octet. La troisième est grammaticale plutôt que technique : la grammaire de formule de mise en forme conditionnelle interdit les références structurées de tableau, donc une règle ne peut pas adresser une colonne de tableau par nom comme le peut une formule de feuille de calcul, et c est une contrainte du format plutôt que de l implémentation
Si vous construisez une sortie de rapport, un pipeline d export ou une grille personnalisée qui doit s accorder avec Excel cellule par cellule, le même résultat résolu pilote aussi la grille tableur VCL personnalisée décrite ailleurs sur ce blog. La documentation complète de l API, le modèle de règles et les téléchargements d essai pour le composant tableur Delphi HotXLS sont disponibles sur la page produit