Excel-Bedingte-Formatierung auswerten in Delphi mit HotXLS

Eine Top-10-Prozent-Regel liegt auf einer Spalte mit 27 Vertriebszahlen, und Excel färbt drei Zellen. Zehn Prozent von 27 sind 2,7, also bekommt ein Renderer, der rundet, drei, ein Renderer, der aufrundet, drei, und ein Renderer, der abrundet, zwei. Zwei dieser drei Vermutungen passen bei dieser Spalte zufällig zu Excel, und beide sind falsch: Excel rundet die Anzahl auf zwei ab und färbt dann doch die dritte Zelle mit, weil sie mit der zweiten bei 88 gleichzieht. Ändert man einen Wert, passen die glücklichen Vermutungen nicht mehr

Das ist die ganze Schwierigkeit, bedingte Formatierung außerhalb von Excel zu zeichnen. Die Regeln stehen in der Datei; die Antwort auf die Frage „welche Zellen leuchten auf, und in welcher Farbe“ steht dort nicht. Ist die eigene Ausgabe ein HTML-Report, eine PDF oder ein selbst gemaltes Grid, muss man sie berechnen, und die Aggregatfamilien sind der Ort, an dem sich die Arithmetik versteckt. Regeln zu erstellen ist ein eigenes Thema, behandelt in bedingte Formatierung und Rich-Text-Stile, und was mit einem Regelbereich passiert, wenn Zeilen sich verschieben, behandelt Partitionierung verankerter bedingter Formate. Diese Notiz handelt von der Auswertung

Was das conditionalFormatting-Element sagt — und was nicht

ECMA-376 und ISO 29500-1 definieren Speicherung, und mehr nicht. Ein conditionalFormatting-Element (§18.3.1.18) trägt ein sqref und eine Liste von cfRule-Kindern (§18.3.1.10). Jede Regel hat einen type, einen optionalen operator, eine priority, ein stopIfTrue-Flag, ein oder zwei formula-Kinder und, bei den visuellen Familien, eine Menge von cfvo-Schwellenwerten. All das hält getreu fest, was der Anwender im Dialog geklickt hat. Keines davon ist ein Algorithmus

Für viele Regeltypen ist diese Lücke harmlos. cellIs mit operator="greaterThan" bedeutet größer als, und containsText bedeutet, die Teilzeichenkette ist vorhanden. Die Probleme beginnen bei Regeln, deren Urteil vom Rest des Bereichs abhängt: top10 mit percent="1" sagt nicht, wie eine gebrochene Anzahl rundet, aboveAverage mit stdDev="1" sagt nicht, ob die Abweichung Stichprobe oder Grundgesamtheit ist, und keine der beiden sagt, ob ein Wert gleich dem Grenzwert mitzählt. Wählt man falsch, widerspricht die eigene PDF der Arbeitsmappe, die der Kunde im Fenster daneben geöffnet hat

TCondFormatRule.Evaluate: was eine einzelne Zelle allein entscheiden kann

HotXLS hat zwei Auswertungsflächen, und sie unterscheiden darin, wozu sie sich bereit erklären. Die klassische (.xls)-Engine bietet TCondFormatRule.Evaluate in lxCondFormat.pas an, das einen Calculator, einen Blattindex, eine Zellposition und den Zellwert entgegennimmt und für eine Regel ein Boolean zurückgibt. Es deckt die acht cellIs-Operatoren ab (between, not between, equal, not equal, greater, less, greater or equal, less or equal), freiformulierte expression-Regeln, die an der Zelle ausgewertet werden, damit relative Referenzen korrekt neu verankert werden, die vier Textprädikate sowie die Leer- und Fehlerprädikate. Schwellwertformeln werden an der Zellposition aufgelöst, und einer between-Regel, deren Grenzen vertauscht ankommen, werden diese getauscht statt zurückgewiesen. Textvergleiche sind case-insensitiv, wie in Excel

// Klassische Engine: eine Regel, eine Zelle, ein 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, Durchschnitte, Duplikate, Zeiträume und die visuellen Arten
    // antworten hier mit False; sie brauchen den ganzen Bereich
    if CondFormat.Rule(I).Evaluate(Calculator, SheetIndex, Row, Col, Value) then
      Exit(True);
end;

Die Methode liefert False für top10, aboveAverage, belowAverage, duplicateValues und uniqueValues. Eine einzelne Zelle kann nicht wissen, ob sie in den oberen zehn Prozent liegt, ohne die anderen Werte zu sehen, also würde eine Antwort bedeuten, zu raten. Relative Datumsregeln (timePeriod) antworten auf diesem Pfad ebenfalls mit False. Die vier visuellen Familien, dataBar, colorScale2, colorScale3 und iconSet, antworten aus einem anderen Grund mit False: Sie erzeugen nie ein Ja oder Nein, sondern eine Farbe, eine Balkenlänge oder einen Symbolindex, und ein Boolean hat dafür keinen Platz. Ein False dieses Aufrufs bedeutet daher genauso oft „hier nicht entschieden“ wie „passt nicht“, was für eine schnelle Prüfung von Vergleichsregeln in Ordnung ist und für alles, was man ausliefert, unsicher

Wie vermeidet ein Arbeitsblatt-Auswerter das erneute Durchsuchen des Blatts für jede Zelle?

Indem er die gesamte bereichsweite Arbeit einmal erledigt — bei seiner Erzeugung. TXLSXConditionalFormatEvaluator in lxHandleX.pas ist ein unveränderlicher Snapshot der bedingten Formate eines Arbeitsblatts, geliefert von TXLSXWorksheet.CreateConditionalFormatEvaluator; der Aufrufer besitzt ihn und gibt ihn frei. Die naive Alternative, bei der jede gemalte Zelle einen Scan ihres Regelbereichs auslöst, um einen Durchschnitt oder eine Rangzahl neu zu berechnen, macht aus einem 10.000-Zellen-Export 10.000 Bereichsscans pro Regel

Bei der Konstruktion parst der Evaluator jedes eigenständige sqref genau einmal, sodass zehn Regeln über denselben Bereich einen Parse-Vorgang und einen Durchlauf über seine befüllten Zellen teilen. Dieser Durchlauf sammelt Mittelwert, Populationsabweichung, Minimum und Maximum in einem einzigen Gang und behält eine sortierte Kopie der Zahlen nur dann, wenn eine Top/Bottom-Regel oder ein Perzentilschwellwert tatsächlich Ordnungsstatistiken braucht. Duplikat- und Unique-Regeln bekommen ihre Wertschlüssel einmal aufgebaut und sortiert, sodass jeder Lookup danach eine binäre Suche ist. Regelformeln werden einmal kompiliert und pro Zelle mit dem Zelloffset erneut ausgewertet, sodass =A1>AVERAGE($A$1:$A$100) seine relativen und absoluten Teile exakt so verhält wie in Excel, ohne für jede Zeile neu geparst zu werden. Zeilen werden so gruppiert, dass EvaluateCell nur Regeln besucht, deren Bereiche die angefragte Zeile überhaupt erreichen können

Regeln werden dann in priority-Reihenfolge angewendet, kleinste Zahl zuerst, Gleichstände nach Position in der Datei gebrochen. Jede Formatierungseigenschaft stammt aus der ersten passenden Regel, die sie setzt, und Regeln mit niedrigerer Priorität füllen nur noch leere Eigenschaften: eine Regel der Priorität 1, die Fett setzt, und eine Regel der Priorität 2, die eine rote Füllung setzt, zeigen beide, und die Zelle kommt fett auf Rot heraus. Ein Treffer auf eine Regel mit StopIfTrue beendet den Gang für diese Zelle

procedure PaintConditional(Sheet: TXLSXWorksheet; Canvas: TCanvas;
  FirstRow, LastRow, FirstCol, LastCol: Integer);
var
  Evaluator: TXLSXConditionalFormatEvaluator;
  Res: TXLSXCfCellResult;
  Row, Col: Integer;
begin
  // Ein Snapshot pro Render-Durchlauf; Zeilen und Spalten sind 1-basiert
  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, gleiches Layout wie TColor
        if Res.HasDataBar then  // DrawBar ist Ihre eigene Routine
          DrawBar(Row, Col, Res.DataBarAxis, Res.DataBarEnd, Res.DataBarColor);
        if Res.HasIcon then     // DrawIcon ist Ihre eigene Routine
          DrawIcon(Row, Col, Res.IconSetType, Res.IconIndex);
        if Res.ShowCellValue and Res.HasNumberFormat and
           Sheet.Cells.HasCell(Row, Col) then  // DrawCellText ist Ihre eigene Routine
          DrawCellText(Row, Col,
            Evaluator.FormatValue(Sheet.Cells[Row, Col].Value, Res.NumberFormatId));
      end;
  finally
    Evaluator.Free;
  end;
end;

Der Snapshot wird mit Absicht einmal pro Durchlauf gebaut. Ihn innerhalb der inneren Schleife zu erzeugen, kompiliert und scannt für jede Zelle alles erneut — genau die Kosten, gegen die das Design existiert. ShowCellValue fällt False aus, wenn eine Datenbalkenregel showValue="0" trägt oder ein Symbolsatz nur das Symbol zeigen soll, sodass der Renderer den Balken beziehungsweise das Zeichen malt und die Zahl weglässt

Wie viele Zellen hebt eine Top-10-Prozent-Regel wirklich hervor?

Die Anzahl wird abgerundet, nie unter eins gelassen, und jeder Wert, der mit dem Grenzwert gleichzieht, wird mitgenommen. Das steht nicht in der Spezifikation; es ist das, was Excel tut, und Sie können es in einer Minute nachprüfen, indem Sie die 27 Werte aus der Abbildung in eine Spalte tippen, Top 10 % anwenden und die gefärbten Zellen zählen. TXLSXConditionalFormatEvaluator berechnet die Ranganzahl als Floor(Count * Min(Rank, 100) / 100), hebt sie auf 1 an, wenn sie bei null landet, begrenzt sie auf die Zahl der befüllten numerischen Zellen, liest den Grenzwert aus den sortierten Werten und vergleicht dann jede Zelle mit >=. Für die 27-Werte-Spalte heißt das: eine Anzahl von zwei, ein Grenzwert von 88 und drei gefärbte Zellen, weil zwei davon 88 tragen. Bottom-N-Regeln spiegeln das mit <=

Überdurchschnitt-Regeln tragen die zweite Mehrdeutigkeit. Mit stdDev="1" wählt die Regel Zellen mindestens eine Standardabweichung über dem Mittelwert, und Stichproben- und Populationsabweichung unterscheiden sich um die Bessel-Korrektur, was genau auf den kurzen Bereichen am stärksten ins Gewicht fällt, auf denen Leute diese Regel tatsächlich einsetzen. Bei fünf Werten liegen die beiden etwa 12 Prozent auseinander. HotXLS verwendet die Populationsabweichung, dieselbe Zahl, die STDEV.P zurückgibt, also ist ein schneller Weg, jeden Renderer zu prüfen, =AVERAGE(r)+STDEV.P(r) neben den Bereich zu stellen und zu sehen, welche Zellen die Marke räumen. Vergleiche gegen ein Abweichungsband sind einschließend; der plain Vergleich darüber oder darunter ist streng, sofern die Regel nicht equalAverage="1" trägt

Duplikat- und Unique-Regeln hängen davon ab, was als „derselbe Wert“ zählt. Die Zahl 100 und der Text „100“ teilen sich einen Schlüssel, weil numerischer Text in den numerischen Schlüsselraum normalisiert wird; Textschlüssel vergleichen case-insensitiv; TRUE und 1 bleiben unterschieden, ebenso Fehlerwerte verschiedener Art. Text mit * oder ? wird als Wildcard-Muster im COUNTIF-Stil verglichen, sodass eine Zelle mit Q* gegen jeden Wert zählt, der mit Q beginnt. Leere Zellen sind die umgekehrte Falle: Eine wirklich leere Zelle im Bereich wird nie als Duplikat gefärbt, also leuchtet eine unfertige Spalte nicht von oben bis unten auf, während eine Zelle, die eine leere Zeichenkette speichert, bei den Leerzellen mitzählt

Farbskalen, Datenbalken und Symbolsätze kommen als Zahlen zurück

Die visuellen Familien lösen zu render-fertigen Werten auf, und ihre Ränder sind der Ort, an dem handgeschriebene Renderer abdriften. Bei einer Farbskala begrenzt EvaluateCell die Position auf das Intervall 0 bis 1 und interpoliert jeden RGB-Kanal mit Abschneiden, sodass ein Wert unterhalb des Minimum-Stopps die Minimumfarbe bekommt statt einer extrapolierten. Eine Dreipunkt-Skala verwendet das Paar Mitte-bis-Maximum für Werte oberhalb der Mittelpunktschwelle und das Paar Minimum-bis-Mitte sonst. Eine Skala, deren erster und letzter Schwellwert auf dieselbe Zahl hinauslaufen, gibt die letzte Farbe zurück statt durch null zu teilen. Perzentilschwellwerte werden linear zwischen benachbarten sortierten Werten interpoliert, nach der PERCENTILE.INC-Definition

Datenbalken werden auf die Zellbreite normalisiert. Bei einem rein positiven Bereich ist DataBarAxis 0, und DataBarEnd läuft zwischen der minimalen und maximalen Balkenlänge der Regel, die standardmäßig bei 10 und 90 Prozent liegen, sodass der kleinste Wert noch einen kurzen Balken bekommt und der größte die Zelle nie ganz füllt. Spannt der Bereich die Null ein, sitzt die Achse an ihrer proportionalen Position, und DataBarEnd markiert, wo der Balken endet — auf der fernen Seite der Achse bei negativen Werten. Richtung, Verlauf, Rahmen und Farben der negativen Balken werden in denselben Record eingetragen, mit Theme-Farben und Tints bereits aufgelöst

Symbolsätze brauchen Sorgfalt bei der Vergleichsstrenge jedes Schwellwerts, denn jeder cfvo nach dem ersten trägt sein eigenes gte-Flag. Der Evaluator läuft die Schwellwerte aufwärts ab und wendet je Schwellwert >= oder > an, wie ThresholdEqualsInclude es vorgibt, und der höchste Schwellwert, den der Wert räumt, gewinnt. Ein umgekehrter Satz spiegelt den aufgelösten Index, Per-Symbol-Overrides können ein Zeichen aus einer anderen Familie über IconSetType einsetzen, und ein nicht auflösbarer Schwellwert bricht die Regel für diese Zelle ab, sodass die Zelle gar kein Symbol bekommt statt eines plausibel wirkenden, falschen

Ein Ergebnis für Grid, HTML-Export und PDF

TXLSXCfCellResult trägt alles, was ein Renderer braucht: Füll- und Schriftfarbe mit bereits angewendetem Theme-Tint, Fett, Kursiv, Unterstreichung, Durchgestrichen, eine Zahlenformat-ID, die Datenbalken-Geometrie und -farben sowie Symbolfamilie und -index. HotXLS speist denselben Record in seinen HTML-Export, seinen PDF-Export und TXLSWorkbookViewer, was die drei Ausgaben zellweise im Einklang hält. Der Viewer erzeugt beim ersten Malen einen Evaluator für das aktive Arbeitsblatt und verwendet ihn über Scrollen, Auswahl und Neuzeichnen hinweg wieder, gibt ihn frei, wenn Sie das Blatt wechseln oder eine andere Arbeitsmappe zuweisen

// Bearbeiten hinter einem Live-Viewer: der zwischengespeicherte Snapshot sieht die Änderung nicht
Sheet := Viewer.XlsxWorkbook.Sheets[1];   // 1-basierter Blattindex
Sheet.Cells[5, 2].Value := 4200;          // verschiebt Mittelwert, Maximum und die Rangfolge
Viewer.RefreshConditionalFormats;         // Snapshot verwerfen und neu zeichnen

Weil der Snapshot unveränderlich ist, lassen eine Direktbearbeitung Durchschnitte, Rangzahlen und Farbskalen-Endpunkte veralten, bis RefreshConditionalFormats läuft. Dasselbe gilt für den eigenen Code: Ein Evaluator, den man über Änderungen hinweg behält, muss freigegeben und neu erzeugt werden, denn CreateConditionalFormatEvaluator erfasst die Werte in dem Moment, in dem er aufgerufen wird

Relative Datumsregeln verdienen eine Entscheidung im Vorfeld. Der Arbeitsblatt-Evaluator vergleicht timePeriod-Regeln gegen das aktuelle Datum der Maschine, sodass eine „diese Woche“-Regel in einer heute erzeugten PDF anders aussieht als beim Export derselben Arbeitsmappe nächsten Dienstag. Das ist korrektes Verhalten und wird trotzdem zum Support-Ticket, wenn das eigene Archiv reproduzierbar sein soll. Für archivierte Reports ersetzen Sie timePeriod-Regeln durch eine expression-Regel, die gegen ein in einer Zelle gespeichertes Reportdatum vergleicht, etwa =AND(A2>=$Z$1-6,A2<=$Z$1) für ein Sieben-Tage-Fenster; die Ausgabe hängt dann nur noch von der Arbeitsmappe ab und von nichts sonst. Excel verweigert außerdem strukturierte Tabellenreferenzen innerhalb einer Formel für bedingte Formatierung, also zeigen Sie die Regel stattdessen auf den Zellbereich der Spalte

Zielt die Ausgabe auf einen Bildschirm statt auf eine Datei, treibt derselbe Ergebnis-Record auch das an anderer Stelle in diesem Blog beschriebene eigene VCL-Tabellenkalkulations-Grid an. Das Regelmodell, die API-Referenz des Evaluators und Testbuilds finden Sie auf der Seite der HotXLS-Delphi-Tabellenkalkulationskomponente