HotXLS, komponenta Excelu pro Delphi a C++Builder, automaticky rozdělí pravidlo podmíněného formátování nebo ověřování dat na dva či více samostatných objektů pravidel, kdykoli vložení nebo smazání řádku či sloupce rozřízne pokrytý rozsah pravidla na kusy, které potřebují odlišné relativní ukotvení vzorce, a pak přeřadí každému pravidlu podmíněného formátu novou, jedinečnou hodnotu priority. Toto chování se dodává od verze 2.196 enginu XLSX a běží automaticky, bez jakéhokoli nastavení pro vypnutí. Spouštěč je úzký, ale běžný: pravidlo cellIs nebo expression, jehož vzorec čte buňku relativně vůči vlastnímu rozsahu, sedící na listu, kterému se později přesně uprostřed tohoto rozsahu vloží nebo odebere řádek
Většina popisů automatizace Excelu se zastaví u problému textu vzorce: posunout čísla řádků a sloupců uvnitř každého SUM() a každého VLOOKUP(), aby odkazy pořád mířily na správné buňky. Tato polovina příběhu je skutečná a popisuje ji doprovodný článek o tom, jak HotXLS přepisuje odkazy ve vzorcích při přesunu řádků a sloupců, ale pravidlo podmíněného formátu nebo ověřování dat není jen vzorec sedící v buňce. Páruje vzorec s rozsahem, sqref v pojmech ECMA-376, a oba se musí pohybovat společně. Když strukturální úprava rozřízne tento rozsah na dvě části, které by k zachování správnosti potřebovaly dva různé relativní posuny, udržet jeden objekt pravidla s jedním řetězcem vzorce přestává být možností, a předstírat opak je způsob, jak zvýrazňovací pravidlo tiše začne porovnávat špatné řádky
Proč vložení řádku rozdělí pravidlo podmíněného formátování místo toho, aby jej jen přesunulo?
Pravidlo podmíněného formátu nebo ověřování dat drží pro celý svůj rozsah přesně jeden vzorec, vyhodnocovaný relativně vůči jediné ukotvující buňce, takže jakmile úprava vynutí, aby dvě části tohoto rozsahu potřebovaly dva různé relativní posuny, jeden vzorec už nedokáže popsat obě části správně. ECMA-376 vyjadřuje pokrytí pravidla jako atribut sqref na elementu conditionalFormatting nebo dataValidation, a Excel vyhodnocuje Formula1 a Formula2, jako by text byl napsán do levé horní buňky tohoto sqref a vyplněn přes zbytek — stejně jako se obyčejný relativní vzorec vyplňuje dolů po sloupci. Představte si zvýraznění odchylky přes B2:B50, které označí každou skutečnou hodnotu přesahující svůj rozpočet, postavené jako pravidlo cellIs, jehož Formula1 je doslovný text C2, což znamená porovnat buňku B aktuálního řádku proti buňce C téhož řádku
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 25Vložte tento jeden oddělovací řádek na starý řádek 25 a řádky nad bodem vložení se nepohnou, takže jejich podíl na pravidle stále čte Formula1 jako C2 správně. Řádky, které bývaly 25 až 50, se posunou dolů na 26 až 51, a pro ně je C2 teď úplně špatná buňka, protože řádek 26 potřebuje porovnávat proti C26, ne proti rozpočtové hodnotě o dva tucty řádků výš
Jak HotXLS rozhoduje, zda pravidlo potřebuje rozdělit
HotXLS vytváří dodatečné objekty pravidel jen tehdy, když to geometrie skutečně vyžaduje: interní rutina XlsxBuildShiftedRuleParts prochází každou nesouvislou oblast v sqref pravidla, zjišťuje, jaká byla ukotvující buňka této oblasti před úpravou a jaká je po ní, a kontroluje, zda by každý výsledný kus potřeboval stejnou korekci relativního posunu. Pokud se všechny kusy shodují, přežije jedno pravidlo, jehož sqref se přestaví jako sjednocení posunutých kusů a jeho vzorec se jednou přepočítá. Ke skutečnému rozdělení dojde jen tehdy, když se kusy neshodují — přesně případ B2:B50 výše, kde horní blok si podrží svou původní ukotvující buňku a spodní blok potřebuje novou
Přepočet vzorce jednoho kusu je dvoukrokový pohyb, který znovu používá mechanismus, jaký HotXLS už nese pro skupiny sdílených vzorců OOXML: nejdřív se vzorec přeloží, jako by byl původně ukotven na levé horní buňce tohoto kusu, pomocí stejné matematiky relativního posunu, která rozvíjí sdílený vzorec přes svůj rozsah, pak výsledek projde stejným skenerem posunu řádků a sloupců, který přepisuje obyčejné vzorce listu. Takto se Formula1 dostane z C2 na C26 ve dvou krocích místo jednoho ručně napsaného speciálního případu: přeložit C2 dopředu o 23 řádků, aby vzniklo C25, jako by pravidlo vždy začínalo tam, a pak nechat obyčejný posun na řádku 25 posunout jej dál na C26. Každá jiná vlastnost — barva výplně, zastavení-při-splnění, samotný operátor — se nezměněná přenese na nový objekt pravidla, takže obě poloviny dál malují buňky barvou, kterou vždy malovaly
// ConditionalFormats now holds two rules instead of one:
// B2:B25 Formula1 = 'C2' (rows above the insert)
// B26:B51 Formula1 = 'C26' (rows that shifted down)Rozdělují se datové pruhy a sady ikon stejným způsobem jako pravidla cellIs?
Ne: HotXLS rozděluje jen ty druhy pravidel, jejichž správnost skutečně závisí na relativním vzorci per region — porovnání cellIs a pravidla expression — a každý jiný druh podmíněného formátu ponechává jako jeden objekt pravidla, jehož sqref jednoduše naroste, aby pokryl posunuté kusy jako víceoblastní sjednocení. Interně je tato větev prostá kontrola Kind, cf.Kind in [cfkCellIs, cfkExpression], nic exotičtějšího. Datové pruhy, dvou- a tříbarevné škály, sady ikon, žebříčky nejvyšších a nejnižších hodnot a detektory duplicit, prázdných buněk a chyb nesou payload — barvu pruhu, sadu zarážek škály, rodinu ikon — který popisuje celý pokrytý rozsah najednou, ne relativní porovnání po buňkách, takže jejich rozdělení na několik prioritizovaných objektů pravidel by nekoupilo žádnou správnost a jen by přidalo pravidla ke správě. Když úprava rozdělí jejich rozsah, HotXLS kusy znovu spojí do jednoho pravidla s víceoblastním sqref a znovu ukotví payload jako jednu jednotku místo toho, aby klonoval nový objekt pravidla pro každý kus. Toto rozlišení odpovídá taxonomii druhů pravidel z článku o základech podmíněného formátování a formátovaného textu: datové pruhy, barevné škály a sady ikon už se od pravidel cellIs liší tím, že úplně ignorují vlastnost Style, a teď se ukazuje, že se stejným způsobem liší i v opětovném ukotvení po regionech
Proč se priority pravidel po strukturální úpravě mění?
Priority se mění proto, že každý klon na začátku drží přesně stejnou hodnotu priority jako pravidlo, ze kterého se rozdělil, a HotXLS po tom spouští normalizační průchod, který výsledné duplicity vyřeší na čisté, bezmezerové řazení místo toho, aby ponechal dvě pravidla remízu na stejné hodnosti. Druhá interní rutina, XlsxNormalizeConditionalFormatPriorities, bere aktuální prioritu každého podmíněného formátu, u pravidla, které nikdy nemělo prioritu explicitně nastavenou, spadne zpět na jeho pozici v kolekci, celý seznam stabilně seřadí, aby si remízy podržely svůj původní relativní pořádek, a přečísluje seřazený výsledek na hustou sekvenci 1, 2, 3 bez mezer a bez opakování. HotXLS ji spustí jednou před začátkem posunu, takže klonování začíná z čisté základní linie, a znovu po každém rozdělení a po odebrání každého vyprázdněného pravidla, takže soubor, který se uloží, nikdy nemá dva záznamy pravidel nárokující stejnou prioritu. Na tom záleží, pokud jste se řídili radou z článku o základech podmíněného formátování, abyste mezi hodnotami priority nechávali mezery, aby se pozdější pravidlo mohlo zasunout bez přečíslování zbytku: mezery přežijí až do další úpravy řádku nebo sloupce, která se toho listu dotkne, a pak se zhroutí, protože normalizace zaručuje jen jedinečnost a stabilní pořadí, ne že se vaše původní číslovací schéma vrátí nezměněné
Rozdělují se i pravidla ověřování dat, bez priority k přečíslování
Pravidla ověřování dat procházejí stejnou logikou dělení rozsahu jako podmíněné formáty cellIs a expression, a na rozdíl od podmíněného formátování touto cestou jednotně prochází každý typ ověřování: HotXLS nemá pro ověřování dat samostatnou nevzorcovou rodinu tak, jak ji mají datové pruhy a sady ikon pro podmíněné formátování, takže obyčejné pravidlo seznamu nebo celého čísla se rozdělí stejnou rutinou, která zpracovává relativní vlastní vzorec. Co se liší, je priorita: ECMA-376 dává elementu dataValidation vůbec žádný atribut priority, takže pro ověřování neexistuje žádný krok přečíslování tak, jako existuje pro podmíněné formáty. Představte si ověřování vlastním vzorcem, které drží, aby skutečná částka každého řádku nepřekročila svůj vlastní rozpočet ve sloupci vedle
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)Na tom záleží ze stejného důvodu, kvůli kterému článek o základech ověřování dat varuje před připojením pravidla dřív, než je počet řádků konečný: ověřování pokrývá jen doslovné buňky, které jste mu dali, a pozdější strukturální úprava může nechat dvě nebo více pravidel dělat práci, kterou dřív dělalo jedno. Funkčně se nic nerozbije: každá buňka v původním rozsahu je pořád něčím ověřená, ale kód, který předpokládá jeden záznam DataValidations na sloupec, začne po první úpravě, která se jej dotkne, chybně indexovat. Existuje tvrdý strop na to, jak daleko to může zajít: pokud by rozdělení posunulo list přes 65 534 pravidel ověřování dat, HotXLS vyvolá výjimku místo toho, aby zapsal soubor, který by Excel tiše odmítl — knihovna tím odmítá vyrobit poškozený sešit, ne limit, na který by běžné použití pravděpodobně narazilo
Co zkontrolovat po hromadném vložení nebo smazání
Dvě věci, které se vyplatí ověřit poté, co skript spustí dávku úprav řádků nebo sloupců přes list plný podmíněných formátů a ověřování, jsou celkový počet pravidel a pořadí priorit, protože obojí se může posunout způsobem, který je snadné přehlédnout při code review a zjevný v okamžiku, kdy někdo otevře Spravovat pravidla v Excelu. Jedna úprava málokdy napáchá velkou škodu: jediné vložení uprostřed jednoho pravidla cellIs vyprodukuje nanejvýš dva objekty pravidla tam, kde bylo jedno. Riziko se násobí, když rutina generování reportu vkládá řádky jeden po druhém ve smyčce přes list, který už nese několik vzorcem ukotvených pravidel: každý průchod může znovu rozdělit pravidla, která už rozdělil předchozí průchod, a pět původních pravidel cellIs může skončit jako několikanásobek tohoto počtu málo hodnotných fragmentů pokrývajících úlomky původního rozsahu. Dávkování strukturálních úprav — vložení celého nového bloku jedním voláním místo po řádcích — udrží počet pravidel svázaný s počtem skutečně odlišných ukotvení místo počtu provedených úprav
Rozdělování pravidel a normalizace priorit se dodávají jako standardní chování enginu XLSX v komponentě HotXLS Delphi Excel pro Delphi a C++Builder; stránka produktu nese úplnou referenci API pro úpravu listu, včetně zde popsaných metod podmíněného formátování a ověřování dat