HotXLS ist eine native Tabellenkalkulationskomponente für Delphi und C++Builder, und seit Version 2.209.0 kann sie die Frage beantworten, die Excel normalerweise für sich behält: Welche Regeln der bedingten Formatierung greifen für genau diese Zelle, und zu welcher Füllung, Schrift, Datenleiste oder welchem Symbol lösen sie auf. Genau diese Antwort braucht man in dem Moment, in dem die eigene Ausgabe ein HTML-Report, eine PDF-Datei oder ein selbst gemaltes Grid ist
Das ist ein anderes Problem als das Erstellen von Regeln. Zwei frühere Beiträge behandeln die Autorenseite: bedingte Formatierung und Rich-Text-Stile befasst sich mit dem Anhängen von Regeln und Differential-Formaten an einen Bereich, und Partitionierung verankerter bedingter Formate befasst sich damit, was mit einem Regelbereich passiert, wenn Zeilen und Spalten eingefügt oder gelöscht werden. Beide sind strukturell. Dieser Beitrag handelt von Semantik: Gegeben eine Arbeitsmappe, die bereits Regeln trägt, die Hervorhebung berechnen
Warum das Dateiformat nicht verrät, welche Zellen aufleuchten
Die kurze Antwort ist, dass ECMA-376 und ISO 29500-1 Speicherung definieren, nicht Auswertung. Ein conditionalFormatting-Element (§18.3.1.18) trägt ein sqref und eine Liste von cfRule-Kindern (§18.3.1.10), und jede Regel trägt einen type, einen optionalen operator, eine priority, ein stopIfTrue-Flag, eine oder zwei formula-Kinder, und für die visuellen Familien eine Menge von cfvo-Schwellenwerten. Jedes davon beschreibt getreu, was der Anwender konfiguriert hat, und keines davon ist ein Algorithmus. Bei der Hälfte der Regeltypen spielt diese Lücke keine Rolle: cellIs mit operator="greaterThan" bedeutet größer als, und containsText bedeutet, die Teilzeichenkette ist vorhanden. Die Lücke öffnet sich bei den Aggregatfamilien. Eine top10-Regel mit rank="10" und percent="1" über 27 befüllte numerische Zellen hebt wie viele Zellen hervor? Zwei Komma sieben ist keine Zahl. Runden, Abrunden oder Aufrunden – die Spezifikation schweigt, und die falsche Wahl bedeutet, dass die eigene PDF-Datei nicht mit der Arbeitsmappe übereinstimmt, die der Kunde daneben geöffnet hat
Regeln für einzelne Zellen, und wo TCondFormatRule.Evaluate aufhört
HotXLS hat zuerst die günstigere Hälfte in Angriff genommen. TCondFormatRule.Evaluate in lxCondFormat.pas, hinzugefügt in 2.199.0, beantwortet, ob eine Regel für eine Zelle greift, ohne irgendetwas über den Rest des Bereichs zu wissen. Es behandelt die acht BIFF-Vergleichsoperatoren hinter cellIs (between, notBetween, equal, notEqual, greater, less, greaterEqual, lessEqual), 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. Schwellenwerte kommen aus FFormula1 und FFormula2, aufgelöst über TXLSCalculator.GetRangeValue an der Zellposition, und vertauschte Grenzen werden getauscht statt zurückgewiesen
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;Der ehrliche Teil dieser Methode ist das, was sie zu raten verweigert. top10, aboveAverage, belowAverage, duplicateValues und uniqueValues liefern False, nicht weil sie schwierig sind, sondern weil sie von einer einzelnen Zelle aus unentscheidbar sind – jede von ihnen braucht eine Statistik über die gesamte Domäne. Die vier visuellen Familien, dataBar, colorScale2, colorScale3 und iconSet, liefern aus einem anderen Grund False: Sie erzeugen überhaupt nie einen Boolean, sondern eine Rendering-Nutzlast, und ein boolescher Rückgabetyp ist für sie die falsche Form
Wie vermeidet ein Auswerter auf Arbeitsblattebene ein erneutes Scannen des Blatts?
Indem jede gemeinsam genutzte Größe einmal bei der Konstruktion berechnet wird, und nie wieder. TXLSXConditionalFormatEvaluator in lxHandleX.pas ist ein unveränderlicher Schnappschuss für ein Arbeitsblatt, aufgebaut über TXLSXWorksheet.CreateConditionalFormatEvaluator, und sein gesamtes Design ist eine Verteidigung gegen die naive Implementierung, bei der jede gemalte Zelle einen vollständigen Bereichs-Scan auslöst
Vier Dinge geschehen im Konstruktor. Jedes eigenständige mehrflächige sqref wird genau einmal in einen TXlsxCfRangeSnapshot geparst, sodass zehn Regeln, die sich einen Bereich teilen, sich auch einen Parse-Vorgang und einen Statistikdurchlauf teilen. Dieser Durchlauf streamt Mittelwert, Populationsabweichung, Minimum und Maximum über die befüllten Zellen in einem einzigen Durchgang und behält ein geordnetes numerisches Array nur dann, wenn eine Top/Bottom- oder Perzentilregel tatsächlich Ordnungsstatistiken braucht. Duplikat- und Unique-Schlüssel werden Unicode-sicher aufgebaut und einmal batch-sortiert statt pro Lookup. Dann wird die Zeilenachse an jeder Bereichsgrenze in Bänder geschnitten, sodass EvaluateCell ein Band binär sucht und nur Regeln besucht, deren Bereiche diese Zeile überhaupt erreichen können
Der vierte Punkt ist der im großen Maßstab wichtigste. Eine relative Regelformel wie =A1>AVERAGE($A$1:$A$100) bedeutet in jeder Zelle der Domäne etwas anderes, und die naheliegende Implementierung kompiliert pro Zelle einen frischen Syntaxbaum. TXlsxCfRulePlan kompiliert ihn einmal und wertet denselben Baum über umkehrbare Koordinatenverschiebungen erneut aus, was das Verankerungsverhalten von Excel bewahrt, ohne pro Zelle eine Syntaxbaum-Allokation. Regeln werden dann nach priority geschichtet, und ein Treffer auf eine Regel, deren StopIfTrue gesetzt ist, bricht die Schleife ab, genau wie Excel abkürzt
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;Wie rundet Excel eine Top-10-Prozent-Regel tatsächlich?
Es rundet ab, mit einem Minimum von eins, und schließt Gleichstände am Grenzwert mit ein. Das steht nirgends in ISO 29500-1 – es wurde durch Sondieren von Excel 16 mit handgebauten Arbeitsmappen und Ablesen ermittelt, welche Zellen die Anwendung hervorhob. HotXLS setzt genau das um: Die Rang-Anzahl ist Floor(Count * Min(Rank, 100) / 100), auf 1 angehoben, wenn sie bei null landet, auf die befüllte Anzahl begrenzt, und der Grenzwert wird dann mit >= verglichen, sodass jede Zelle, die dem Grenzwert entspricht, hervorgehoben wird, selbst wenn das die angeforderte Anzahl übersteigt. Siebenundzwanzig Werte und eine 10-Prozent-Regel heben zwei Zellen hervor, plus jede weitere Zelle, die mit der zweiten gleichsteht
Überdurchschnitt-Regeln bargen eine zweite Mehrdeutigkeit: aboveAverage mit stdDev="1" wählt Zellen eine Standardabweichung über dem Mittelwert, aber Stichproben- und Populationsabweichung unterscheiden sich durch die Bessel-Korrektur, und bei kleinen Bereichen weichen sie sichtbar voneinander ab – genau dort, wo bedingte Formatierung typischerweise zum Einsatz kommt. Excel 16 verwendet die Populationsabweichung, und HotXLS gleicht das ab, wobei das Flag equalAverage den strengen Vergleich nur dann einschließend macht, wenn kein Abweichungsband im Spiel ist. Duplikat- und Unique-Regeln setzen stattdessen auf Schlüsselidentität. Trägt eine Zelle die Zahl 100 und eine andere den Text "100", behandelt Excel sie als denselben Duplikat-Schlüssel, sodass HotXLS numerischen Text in den numerischen Schlüsselraum normalisiert, statt rohe Zeichenketten zu vergleichen. Leere Zellen sind der Spiegelfall: Eine echte leere Zelle zählt bei der Bereichsermittlung mit, wird aber selbst nicht gestylt, sodass die leeren Zellen einer Spalte nicht alle als gegenseitige Duplikate aufleuchten
Farbskalen und Symbolsätze: Interpolation und Grenzwertregeln
Die visuellen Familien lösen zu render-fertigen Zahlen auf statt zu Booleans, und ihr Randverhalten wurde auf dieselbe Weise festgelegt. Bei einer Farbskala mit expliziten numerischen Schwellenwerten begrenzt HotXLS den Positionsanteil auf das geschlossene Intervall null bis eins und interpoliert dann pro Kanal mit Abschneiden statt Runden – ein Wert unterhalb des Minimum-Stopps erhält die Minimumfarbe statt einer extrapolierten, eine Dreipunkt-Skala wählt ihr Paar durch Vergleich mit dem mittleren Stopp, und eine entartete Skala, deren beide Enden denselben Schwellenwert tragen, fällt auf die obere Farbe zurück statt durch null zu teilen. Symbolsätze brauchten die entgegengesetzte Art von Sorgfalt, weil jeder cfvo nach dem ersten seine eigene Vergleichsstrenge trägt: HotXLS liest ThresholdEqualsInclude pro Schwellenwert und wendet entsprechend >= oder > an, wobei aufwärts gelaufen wird, sodass der höchste erfüllte Schwellenwert den Symbolindex gewinnt. Ein umgekehrter Satz spiegelt den aufgelösten Index statt der Schwellenwerte, Per-Symbol-Overrides können ein Symbol aus einer anderen Familie ziehen, und jeder ungültige Schwellenwert bricht die Regel ab, statt ein plausibel wirkendes, aber falsches Symbol zu erzeugen
Ein Grid, einen HTML-Export und eine PDF-Datei aus einem Ergebnis speisen
Weil EvaluateCell ein vollständig aufgelöstes TXLSXCfCellResult zurückgibt – Differential-Füllung und Schriftfarbe mit bereits angewendetem Theme-Tint, Fett, Kursiv, Unterstrichen, Zahlenformat-ID, gerichtete positive und negative Balkenausdehnung, Achsenposition, Symbolfamilie und -index –, liest jeder Verbraucher denselben Datensatz, und keiner muss die internen Regeln verstehen. HotXLS nutzt diesen einen Pfad für HTML-Export, PDF-Export und den interaktiven Viewer, was der einzig praktikable Weg ist, drei Renderer davon abzuhalten, auseinanderzudriften. Version 2.210.0 hat das in TXLSWorkbookViewer verdrahtet, das pro aktivem Arbeitsblatt einen vorbereiteten Evaluator zwischenspeichert und ihn über Scrollen, Auswahl und Neuzeichnen hinweg wiederverwendet, ihn freigibt, wenn sich Arbeitsmappe oder Arbeitsblatt ändern – den Schnappschuss bei jedem Paint neu aufzubauen würde das gesamte zur Konstruktionszeit ausgelegte Design zunichtemachen. Dieser Cache ist auch der Grund, warum TXLSWorkbookViewer.RefreshConditionalFormats existiert: Der Schnappschuss ist unveränderlich, sodass die Aggregatstatistiken und aufgelösten Schwellenwerte veraltet sind, wenn man die angehängte Arbeitsmappe direkt verändert, bis man diese Methode aufruft
// 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, repaintWas der Evaluator nicht für einen erledigt
Drei Grenzen sind es wert, klar benannt zu werden. Das klassische Einzelzellen-TCondFormatRule.Evaluate und der Arbeitsblatt-Evaluator TXLSXConditionalFormatEvaluator sind unterschiedliche Oberflächen mit unterschiedlichen Fähigkeiten, und die Einzelzellen-Variante lehnt die Aggregat- und visuellen Familien bewusst ab, statt sie anzunähern – wer Top/Bottom oder eine Farbskala braucht, baut den Evaluator. Relative Zeiträume hängen von der Systemuhr zum Auswertungszeitpunkt ab, sodass eine timePeriod-Regel in einer heute erzeugten PDF-Datei anders aussieht als in einer nächste Woche erzeugten, was korrektes Verhalten ist und trotzdem ein Support-Ticket wird, wenn das eigene Archiv byte-stabil sein soll. Der dritte Punkt ist grammatikalisch statt technisch: Die Formelgrammatik für bedingte Formatierung verbietet strukturierte Tabellenreferenzen, sodass eine Regel eine Tabellenspalte nicht namentlich adressieren kann, wie es eine Arbeitsblattformel könnte, und das ist eine Einschränkung des Formats, nicht der Implementierung
Wer Report-Ausgaben, eine Export-Pipeline oder ein eigenes Grid baut, das mit Excel zellgenau übereinstimmen muss, dem dient dasselbe aufgelöste Ergebnis auch dem an anderer Stelle in diesem Blog beschriebenen eigenen VCL-Tabellenkalkulations-Grid. Vollständige API-Dokumentation, das Regelmodell und Testdownloads für die HotXLS-Delphi-Tabellenkalkulationskomponente sind auf der Produktseite verfügbar