HotXLS, komponent Excel pre Delphi a C++Builder, automaticky rozdelí pravidlo podmieneného formátovania alebo validácie dát na dva či viac samostatných objektov pravidiel vždy, keď vloženie alebo odstránenie riadka či stĺpca preseká rozsah pokrytý pravidlom na kúsky, ktoré potrebujú odlišné relatívne ukotvenia vzorca, a potom každému pravidlu podmieneného formátu prideľuje nové, jedinečné číslo priority. Toto správanie sa objavilo vo verzii 2.196 enginu XLSX a beží automaticky, bez akéhokoľvek nastavenia na jeho vypnutie. Spúšťač je úzky, no bežný: pravidlo cellIs alebo expression, ktorého vzorec číta bunku relatívne voči vlastnému rozsahu, žijúce na hárku, do ktorého sa neskôr niekde uprostred práve tohto rozsahu vloží alebo odstráni riadok
Väčšina textov o automatizácii Excelu sa zastaví pri probléme textu vzorca: posunúť čísla riadkov a stĺpcov vnútri každého SUM() a každého VLOOKUP(), aby odkazy stále ukazovali na správne bunky. Táto polovica príbehu je skutočná a je opísaná v sprievodnom článku o tom, ako HotXLS prepisuje odkazy vzorcov pri presune riadkov a stĺpcov, no podmienený formát alebo pravidlo validácie dát nie je len vzorec sedieaci v bunke. Páruje vzorec s rozsahom, sqref v terminológii ECMA-376, a oba sa musia pohybovať spolu. Keď štrukturálna úprava rozreže tento rozsah na dva kúsky, ktoré by na to, aby zostali správne, potrebovali dva odlišné relatívne posuny, udržiavanie jedného objektu pravidla s jedným reťazcom vzorca prestáva byť možnosťou, a predstierať opak je presne to, ako pravidlo zvýraznenia ticho začne porovnávať nesprávne riadky
Prečo vloženie riadka rozdelí pravidlo podmieneného formátovania namiesto toho, aby ho jednoducho posunulo?
Podmienený formát alebo pravidlo validácie dát drží pre celý svoj rozsah presne jeden vzorec, vyhodnocovaný relatívne voči jedinej kotviacej bunke, takže hneď ako úprava vynúti, aby dve časti tohto rozsahu potrebovali dva odlišné relatívne posuny, jeden vzorec už nedokáže správne opísať obe časti. ECMA-376 vyjadruje pokrytie pravidla ako atribút sqref na elemente conditionalFormatting alebo dataValidation, a Excel vyhodnocuje Formula1 a Formula2 tak, akoby bol text napísaný do ľavej hornej bunky tohto sqref a vyplnený cez zvyšok, rovnako ako obyčajný relatívny vzorec vyplní stĺpec smerom nadol. Predstavte si zvýraznenie odchýlky nad B2:B50, ktoré označí akúkoľvek skutočnú hodnotu prevyšujúcu rozpočet, postavené ako pravidlo cellIs, ktorého Formula1 je doslovný text C2, čo znamená porovnať bunku B aktuálneho riadka s bunkou C toho istého riadka
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 oddeľovací riadok na starý riadok 25 a riadky nad bodom vloženia sa nepohnú, takže ich časť pravidla stále číta Formula1 ako C2 správne. Riadky, ktoré boli predtým 25 až 50, sa posunú nadol na 26 až 51, a pre ne je C2 teraz úplne nesprávna bunka, keďže riadok 26 potrebuje porovnávať voči C26, nie voči rozpočtovej hodnote o dva tucty riadkov vyššie
Ako HotXLS rozhoduje, či pravidlo potrebuje rozdeliť
HotXLS vytvára ďalšie objekty pravidiel iba vtedy, keď to geometria naozaj vyžaduje: interná rutina XlsxBuildShiftedRuleParts prejde každú nesúvislú oblasť v sqref pravidla, zistí, akou bola kotviaca bunka danej oblasti pred úpravou a akou sa stáva po nej, a skontroluje, či by každý výsledný kúsok potreboval rovnakú opravu relatívneho posunu. Ak sa všetky kúsky zhodujú, prežije jedno pravidlo, jeho sqref sa znova zostaví ako zjednotenie posunutých kúskov a jeho vzorec sa raz prepočíta na nový základ. Skutočné rozdelenie nastane iba vtedy, keď sa kúsky nezhodujú, presne prípad B2:B50 vyššie, kde si horný blok ponechá pôvodnú kotvu a spodný blok potrebuje novú
Prepočítanie vzorca kúsku na nový základ je dvojkrokový pohyb, ktorý znova využíva mechaniku, akú už HotXLS nesie pre skupiny zdieľaných vzorcov OOXML: najprv sa vzorec preloží tak, akoby bol pôvodne ukotvený v ľavej hornej bunke tohto kúsku, pomocou rovnakej matematiky relatívneho posunu, akú používa expanzia zdieľaného vzorca cez jeho rozsah, potom výsledok prejde tým istým skenerom posunu riadkov a stĺpcov, ktorý prepisuje bežné vzorce hárka. Takto sa Formula1 dostane z C2 na C26 v dvoch krokoch namiesto jedného ručne napísaného špeciálneho prípadu: preložiť C2 vpred o 23 riadkov na C25, akoby pravidlo vždy začínalo tam, a potom nechať obyčajný posun na riadku 25, aby ho posunul ďalej na C26. Každá ďalšia vlastnosť, farba výplne, zastaviť-ak-pravda, samotný operátor, sa nezmenená prenesie na nový objekt pravidla, takže obe polovice ďalej maľujú bunky farbou, akou vždy maľovali
// ConditionalFormats now holds two rules instead of one:
// B2:B25 Formula1 = 'C2' (rows above the insert)
// B26:B51 Formula1 = 'C26' (rows that shifted down)Rozdeľujú sa dátové pruhy a sady ikon rovnako ako pravidlá cellIs?
Nie: HotXLS rozdeľuje iba tie druhy pravidiel, ktorých správnosť skutočne závisí od relatívneho vzorca na jednotlivý región, teda porovnania cellIs a pravidlá expression, a ponecháva každý iný druh podmieneného formátu ako jediný objekt pravidla, ktorého sqref jednoducho narastie tak, aby pokryl posunuté kúsky ako viacoblastné zjednotenie. Interne je táto vetva obyčajná kontrola Kind, cf.Kind in [cfkCellIs, cfkExpression], nič exotickejšie než to. Dátové pruhy, dvoj- a trojfarebné škály, sady ikon, rebríčky top a bottom, a detektory duplicít, prázdnych buniek a chýb nesú payload, farbu pruhu, sadu bodov škály, rodinu ikon, ktorý opisuje celý pokrytý rozsah naraz, nie relatívne porovnanie po jednotlivých bunkách, takže ich rozdelenie na niekoľko uprednostnených objektov pravidiel by nič nezískalo na správnosti a iba by pridalo pravidlá na správu. Keď úprava rozdelí ich rozsah, HotXLS znova skombinuje kúsky do jedného pravidla s viacoblastným sqref a znova ukotví payload ako jednu jednotku namiesto klonovania nového objektu pravidla pre každý kúsok. Toto rozlíšenie sa zhoduje s taxonómiou druhov pravidiel v článku o základoch podmieneného formátovania a formátovaného textu: dátové pruhy, farebné škály a sady ikon už stoja mimo pravidiel cellIs tým, že úplne ignorujú vlastnosť Style, a teraz sa ukazuje, že stoja mimo znova ukotvovania po regiónoch z toho istého základného dôvodu
Prečo sa priority pravidiel menia po štrukturálnej úprave?
Priority sa menia preto, lebo každý klon začína s presne rovnakou hodnotou priority ako pravidlo, z ktorého sa rozdelil, a HotXLS potom spustí normalizačný prechod, ktorý vyriešené duplicity zoradí do čistého poradia bez medzier namiesto toho, aby nechal dve pravidlá s remízou na rovnakom mieste. Druhá interná rutina, XlsxNormalizeConditionalFormatPriorities, vezme aktuálnu prioritu každého podmieneného formátu, pre akékoľvek pravidlo, ktoré nikdy nemalo prioritu nastavenú výslovne, sa vráti k jeho pozícii v kolekcii, celý zoznam stabilne zoradí tak, aby si remízy zachovali svoje pôvodné relatívne poradie, a preusporiada zoradený výsledok na hustú postupnosť 1, 2, 3 bez medzier a bez opakovaní. HotXLS ju spustí raz pred začiatkom posunu, takže klonovanie začína z čistého základu, a znova po každom rozdelení a po odstránení každého vyprázdneného pravidla, takže uložený súbor nikdy nemá dva záznamy pravidiel nárokujúce si rovnakú prioritu. Na tom záleží, ak ste sa riadili radou v článku o základoch podmieneného formátovania nechávať medzery medzi hodnotami priority, aby sa neskoršie pravidlo mohlo vsunúť bez preusporiadania zvyšku: medzery prežijú až do najbližšej úpravy riadka alebo stĺpca, ktorá sa dotkne daného hárka, potom sa zrútia, pretože normalizácia zaručuje iba jedinečnosť a stabilné poradie, nie to, že sa vaša pôvodná schéma číslovania vráti nezmenená
Pravidlá validácie dát sa tiež rozdeľujú, no bez priority na preusporiadanie
Pravidlá validácie dát prechádzajú tou istou logikou rozdeľovania rozsahu ako pravidlá cellIs a expression podmieneného formátovania, a na rozdiel od podmieneného formátovania touto cestou prechádza rovnako každý typ validácie: HotXLS nemá pre validáciu dát samostatnú neformulovú rodinu, tak ako ju majú dátové pruhy a sady ikon pre podmienené formátovanie, takže obyčajné pravidlo so zoznamom alebo celým číslom sa rozdeľuje tou istou rutinou, ktorá spracúva relatívny vlastný vzorec. Čo sa líši, je priorita: ECMA-376 nedáva elementu dataValidation vôbec žiadny atribút priority, takže pre validácie neexistuje krok preusporiadania tak, ako existuje pre podmienené formáty. Predstavte si validáciu s vlastným vzorcom, ktorá udržiava, aby skutočná suma každého riadka neprekročila jeho vlastný rozpočet v susednom stĺpci
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 tomto záleží z rovnakého dôvodu, prečo článok o základoch validácie dát varuje pred pripojením pravidla ešte pred tým, ako je počet riadkov konečný: validácia pokrýva iba doslovné bunky, ktoré ste jej dali, a neskoršia štrukturálna úprava môže zanechať dve alebo viac pravidiel vykonávajúcich prácu, ktorú predtým robilo jedno. Funkčne sa nič nepokazí: každá bunka v pôvodnom rozsahu je stále niečím validovaná, no kód, ktorý predpokladá jeden záznam DataValidations na stĺpec, začne po prvej úprave, ktorá sa ho dotkne, nesprávne indexovať. Existuje tvrdý strop, kam až sa to môže dostať: ak by rozdelenie posunulo hárok nad 65 534 pravidiel validácie dát, HotXLS vyvolá výnimku namiesto toho, aby zapísal súbor, ktorý by Excel ticho odmietol — knižnica tak radšej odmietne vyrobiť poškodený zošit, než aby narazila na limit, ktorý bežné použitie sotva kedy dosiahne
Čo skontrolovať po hromadnom vložení alebo odstránení
Dve veci, ktoré sa oplatí overiť po tom, čo skript spustí dávku úprav riadkov alebo stĺpcov nad hárkom plným podmienených formátov a validácií, sú celkový počet pravidiel a poradie priorít, keďže oboje sa môže posunúť spôsobom, ktorý je ľahké prehliadnuť pri code review a zjavný vo chvíli, keď niekto otvorí Spravovať pravidlá v Exceli. Jedna úprava málokedy napácha veľa škody: jediné vloženie uprostred jedného pravidla cellIs vyprodukuje najviac dva objekty pravidiel tam, kde bol predtým jeden. Riziko sa zväčšuje, keď rutina na generovanie reportov vkladá riadky po jednom v slučke nad hárkom, ktorý už nesie niekoľko pravidiel ukotvených vzorcom: každý priechod môže znova rozdeliť pravidlá, ktoré už predchádzajúci priechod rozdelil, a päť pôvodných pravidiel cellIs môže skončiť ako niekoľkonásobne viac fragmentov s nízkou hodnotou, pokrývajúcich úlomky pôvodného rozsahu. Dávkovanie štrukturálnych úprav, vloženie celého nového bloku v jednom volaní namiesto po jednom riadku, drží počet pravidiel naviazaný na počet skutočne odlišných kotiev, nie na počet vykonaných úprav
Rozdeľovanie pravidiel a normalizácia priorít sa dodávajú ako štandardné správanie enginu XLSX v komponente HotXLS Delphi Excel pre Delphi a C++Builder; stránka produktu nesie plnú referenciu API na úpravu hárkov, vrátane tu opísaných metód podmieneného formátovania a validácie dát