HotXLS, die Delphi- und C++Builder-Excel-Komponente, teilt eine Regel für bedingte Formatierung oder Datenvalidierung automatisch in zwei oder mehr separate Regelobjekte auf, sobald das Einfügen oder Löschen einer Zeile oder Spalte den von der Regel abgedeckten Bereich in Teile zerschneidet, die unterschiedliche relative Formelanker benötigen, und weist danach jeder Regel für bedingte Formatierung eine neue, eindeutige Prioritätsnummer zu. Dieses Verhalten wurde in Version 2.196 der XLSX-Engine ausgeliefert und läuft automatisch, ohne Möglichkeit zum Abschalten. Der Auslöser ist eng gefasst, aber häufig: eine cellIs- oder Ausdrucksregel, deren Formel eine Zelle relativ zu ihrem eigenen Bereich liest, auf einem Arbeitsblatt, in das später mitten in genau diesem Bereich eine Zeile eingefügt oder entfernt wird
Die meisten Abhandlungen zur Excel-Automatisierung enden beim Formeltext-Problem: die Zeilen- und Spaltennummern in jedem SUM() und jedem VLOOKUP() verschieben, damit die Referenzen weiterhin auf die richtigen Zellen zeigen. Diese Hälfte der Geschichte ist real und wird in dem begleitenden Artikel dazu behandelt, wie HotXLS Formelreferenzen beim Verschieben von Zeilen und Spalten umschreibt, aber eine bedingte Formatierung oder eine Datenvalidierungsregel ist nicht nur eine Formel, die in einer Zelle sitzt. Sie paart eine Formel mit einem Bereich, sqref in ECMA-376-Terminologie, und beide müssen sich zusammen bewegen. Zerschneidet eine strukturelle Bearbeitung diesen Bereich in zwei Teile, die unterschiedliche relative Offsets bräuchten, um korrekt zu bleiben, hört es auf, eine Option zu sein, ein Regelobjekt mit einem Formel-String zu behalten, und so vorzugehen ist es, wie eine Hervorhebungsregel still beginnt, die falschen Zeilen zu vergleichen
Warum teilt das Einfügen einer Zeile eine bedingte Formatierungsregel, statt sie einfach zu verschieben?
Eine bedingte Formatierungs- oder Datenvalidierungsregel behält genau eine Formel für ihren gesamten Bereich, ausgewertet relativ zu einer einzelnen Ankerzelle, sodass eine einzelne Formel, sobald eine Bearbeitung zwei Teile dieses Bereichs zwingt, zwei unterschiedliche relative Offsets zu benötigen, nicht mehr beide Teile korrekt beschreiben kann. ECMA-376 drückt die Abdeckung einer Regel als sqref-Attribut am conditionalFormatting- oder dataValidation-Element aus, und Excel wertet Formula1 und Formula2 so aus, als wäre der Text in die obere linke Zelle dieses sqref eingegeben und über den Rest davon ausgefüllt worden, genauso wie eine gewöhnliche relative Formel eine Spalte hinunter ausgefüllt wird. Stellen Sie sich eine Abweichungshervorhebung über B2:B50 vor, die jede Ist-Zahl markiert, die ihr Budget überschreitet, aufgebaut als cellIs-Regel, deren Formula1 der wörtliche Text C2 ist, was bedeutet: die B-Zelle der aktuellen Zeile mit der C-Zelle derselben Zeile vergleichen
Idx := Sheet.AddConditionalFormat('B2:B50', xlsxCfOpGreaterThan, 'C2');
Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);
Sheet.InsertRows(25, 1); // one blank separator row, starting at old row 25Fügen Sie diese eine Trennzeile bei der alten Zeile 25 ein, und die Zeilen oberhalb des Einfügepunkts bewegen sich nicht, sodass ihr Anteil an der Regel Formula1 weiterhin korrekt als C2 liest. Die Zeilen, die früher 25 bis 50 waren, rutschen auf 26 bis 51, und für sie ist C2 jetzt komplett die falsche Zelle, da Zeile 26 gegen C26 vergleichen muss, nicht gegen eine Budgetzahl zwei Dutzend Zeilen darüber
Wie HotXLS entscheidet, ob eine Regel aufgeteilt werden muss
HotXLS erzeugt nur dann zusätzliche Regelobjekte, wenn die Geometrie es tatsächlich erfordert: Eine interne Routine, XlsxBuildShiftedRuleParts, durchläuft jeden disjunkten Bereich im sqref der Regel, ermittelt, was die Ankerzelle dieses Bereichs vor der Bearbeitung war und was sie danach wird, und prüft, ob jeder resultierende Teil dieselbe relative Offset-Korrektur benötigen würde. Stimmen alle Teile überein, überlebt eine Regel, ihr sqref wird als Vereinigung der verschobenen Teile neu aufgebaut und ihre Formel einmal neu verankert. Eine echte Aufteilung geschieht nur, wenn die Teile nicht übereinstimmen, genau der obige B2:B50-Fall, bei dem der obere Block seinen ursprünglichen Anker behält und der untere Block einen neuen benötigt
Die Formel eines Teils neu zu verankern ist ein zweistufiger Vorgang, der Mechanik wiederverwendet, die HotXLS bereits für OOXML-Shared-Formula-Gruppen mitbringt: Zuerst wird die Formel übersetzt, als wäre sie ursprünglich an der eigenen oberen linken Zelle dieses Teils verankert gewesen, unter Verwendung derselben relativen Offset-Mathematik, die eine Shared Formula über ihren Bereich expandiert, dann durchläuft das Ergebnis denselben Zeilen-und-Spalten-Verschiebungs-Scanner, der gewöhnliche Arbeitsblattformeln umschreibt. So kommt Formula1 in zwei Schritten statt einem handgeschriebenen Sonderfall von C2 auf C26: C2 um 23 Zeilen nach vorne übersetzen, um C25 zu erhalten, so als hätte die Regel dort immer begonnen, dann die gewöhnliche Verschiebung bei Zeile 25 sie weiter auf C26 schieben lassen. Jede andere Eigenschaft, Füllfarbe, Stop-if-True, der Operator selbst, wandert unverändert auf das neue Regelobjekt mit, sodass beide Hälften weiterhin die Zellen in der Farbe malen, die sie immer hatten
// ConditionalFormats now holds two rules instead of one:
// B2:B25 Formula1 = 'C2' (rows above the insert)
// B26:B51 Formula1 = 'C26' (rows that shifted down)Teilen sich Datenbalken und Symbolsätze genauso auf wie cellIs-Regeln?
Nein: HotXLS partitioniert nur die Regelarten, deren Korrektheit tatsächlich von einer relativen Formel pro Region abhängt, cellIs-Vergleiche und Ausdrucksregeln, und belässt jede andere Art von bedingter Formatierung als ein einziges Regelobjekt, dessen sqref einfach wächst, um die verschobenen Teile als Mehrbereichs-Vereinigung abzudecken. Intern ist der Zweig eine einfache Kind-Prüfung, cf.Kind in [cfkCellIs, cfkExpression], nichts Exotischeres als das. Datenbalken, Zwei- und Dreifarbenskalen, Symbolsätze, Top- und Bottom-Rankings sowie die Duplikat-, Leerzellen- und Fehlererkenner tragen eine Nutzlast, eine Balkenfarbe, eine Menge von Skalen-Stopps, eine Symbolfamilie, die den gesamten abgedeckten Bereich auf einmal beschreibt statt eines relativen Vergleichs pro Zelle, sodass ein Aufteilen in mehrere priorisierte Regelobjekte keine Korrektheit einbringen würde und nur Regeln zum Verwalten hinzufügen würde. Wenn eine Bearbeitung ihren Bereich teilt, kombiniert HotXLS die Teile zu einer Regel mit einem Mehrbereichs-sqref neu und verankert die Nutzlast als eine einzige Einheit neu, statt pro Teil ein neues Regelobjekt zu klonen. Die Unterscheidung deckt sich mit der Regelarten-Taxonomie in dem Grundlagenartikel zu bedingter Formatierung und Rich Text: Datenbalken, Farbskalen und Symbolsätze heben sich bereits von cellIs-Regeln ab, indem sie die Style-Eigenschaft komplett ignorieren, und jetzt stellt sich heraus, dass sie sich aus demselben zugrunde liegenden Grund auch von der Neuverankerung pro Region abheben
Warum ändern sich Regelprioritäten nach einer strukturellen Bearbeitung?
Prioritäten ändern sich, weil jeder Klon zunächst genau denselben Prioritätswert trägt wie die Regel, von der er abgespalten wurde, und HotXLS danach einen Normalisierungsdurchlauf ausführt, der die entstehenden Duplikate zu einer sauberen, lückenlosen Reihenfolge auflöst, statt zwei Regeln mit demselben Rang gleichauf zu belassen. Eine zweite interne Routine, XlsxNormalizeConditionalFormatPriorities, nimmt die aktuelle Priorität jeder bedingten Formatierung, fällt für jede Regel, die nie explizit eine gesetzt hatte, auf deren Position in der Sammlung zurück, sortiert die gesamte Liste stabil, sodass Gleichstände ihre ursprüngliche relative Reihenfolge behalten, und nummeriert das sortierte Ergebnis zu einer dichten Folge 1, 2, 3 ohne Lücken und ohne Wiederholungen um. HotXLS führt sie einmal aus, bevor eine Verschiebung beginnt, sodass das Klonen von einer sauberen Basislinie startet, und erneut nach jeder Aufteilung und nachdem jede leer gewordene Regel entfernt wurde, sodass die gespeicherte Datei nie zwei Regeleinträge hat, die dieselbe Priorität beanspruchen. Das zählt, falls Sie dem Rat aus dem Grundlagenartikel zu bedingter Formatierung gefolgt sind, Lücken zwischen Prioritätswerten zu lassen, damit eine spätere Regel einrasten kann, ohne den Rest umzunummerieren: Die Lücken überleben, bis die nächste Zeilen- oder Spaltenbearbeitung dieses Arbeitsblatt berührt, und kollabieren dann, weil die Normalisierung nur Eindeutigkeit und stabile Reihenfolge garantiert, nicht dass Ihr ursprüngliches Nummerierungsschema unverändert zurückkommt
Datenvalidierungsregeln teilen sich ebenfalls auf, ohne Priorität zum Umnummerieren
Datenvalidierungsregeln durchlaufen dieselbe Bereichspartitionierungslogik wie cellIs- und Ausdrucksregeln für bedingte Formatierung, und anders als bei bedingter Formatierung nimmt jeder Validierungstyp diesen Pfad einheitlich: HotXLS hat keine separate Nicht-Formel-Familie für Datenvalidierung, so wie Datenbalken und Symbolsätze das für bedingte Formatierung sind, sodass eine einfache Listen- oder Ganzzahlregel von derselben Routine partitioniert wird, die eine relative benutzerdefinierte Formel behandelt. Was sich unterscheidet, ist die Priorität: ECMA-376 gibt dem dataValidation-Element überhaupt kein priority-Attribut, sodass es für Validierungen keinen Umnummerierungsschritt gibt, wie es ihn für bedingte Formatierungen gibt. Stellen Sie sich eine Validierung mit benutzerdefinierter Formel vor, die verhindert, dass der Ist-Betrag jeder Zeile ihr eigenes Budget in der Spalte daneben übersteigt
Sheet.AddCustomValidation('D2:D400', 'D2<=C2');
Sheet.DeleteRows(150, 5); // remove five rows out of the validated range
// DataValidations now holds two rules instead of one:
// D2:D149 Formula1 = 'D2<=C2' (rows above the deletion)
// D150:D395 Formula1 = 'D150<=C150' (rows that shifted up)Das zählt aus demselben Grund, aus dem der Grundlagenartikel zu Datenvalidierung davor warnt, eine Regel anzuhängen, bevor die Zeilenanzahl feststeht: Eine Validierung deckt nur die tatsächlichen Zellen ab, die Sie ihr gegeben haben, und eine spätere strukturelle Bearbeitung kann zwei oder mehr Regeln hinterlassen, die die Arbeit erledigen, die früher eine erledigte. Funktional geht nichts kaputt: Jede Zelle im ursprünglichen Bereich wird weiterhin von irgendetwas validiert, aber Code, der eine DataValidations-Eintrag pro Spalte annimmt, wird nach der ersten Bearbeitung, die sie berührt, falsch indizieren. Es gibt eine harte Obergrenze, wie weit das gehen kann: Würde eine Aufteilung ein Arbeitsblatt über 65.534 Datenvalidierungsregeln hinausdrücken, wirft HotXLS eine Exception, statt eine Datei zu schreiben, die Excel still ablehnen würde – die Bibliothek weigert sich, eine korrupte Arbeitsmappe zu erzeugen, statt eine Grenze zu setzen, die gewöhnliche Nutzung wahrscheinlich erreicht
Was nach einem Massen-Einfügen oder -Löschen zu prüfen ist
Die zwei Dinge, die es sich lohnt zu prüfen, nachdem ein Skript eine Charge von Zeilen- oder Spaltenbearbeitungen über ein Blatt voller bedingter Formatierungen und Validierungen ausführt, sind die Gesamtregelanzahl und die Prioritätsreihenfolge, da beide auf Weisen driften können, die in der Code-Review leicht zu übersehen und in dem Moment offensichtlich sind, in dem jemand Regeln verwalten in Excel öffnet. Eine einzelne Bearbeitung richtet selten viel Schaden an: Ein einzelnes Einfügen mitten in einer cellIs-Regel erzeugt höchstens zwei Regelobjekte, wo vorher eines war. Das Risiko summiert sich, wenn eine Report-Generierungsroutine in einer Schleife über ein Blatt, das bereits mehrere formelverankerte Regeln trägt, Zeile für Zeile einfügt: Jeder Durchlauf kann Regeln erneut aufteilen, die ein vorheriger Durchlauf bereits aufgeteilt hat, und fünf ursprüngliche cellIs-Regeln können am Ende als ein Vielfaches davon an geringwertigen Fragmenten enden, die Splitter des ursprünglichen Bereichs abdecken. Strukturelle Bearbeitungen zu bündeln, den gesamten neuen Block in einem Aufruf statt Zeile für Zeile einzufügen, hält die Regelanzahl an die Anzahl der tatsächlich unterschiedlichen Anker gebunden statt an die Anzahl der durchgeführten Bearbeitungen
Regelpartitionierung und Prioritätsnormalisierung sind Standardverhalten der XLSX-Engine in der HotXLS Delphi Excel-Komponente für Delphi und C++Builder; die Produktseite trägt die vollständige Referenz zur Arbeitsblatt-Bearbeitungs-API, einschließlich der hier beschriebenen Methoden für bedingte Formatierung und Datenvalidierung