HotXLS, komponent Excel dla Delphi i C++Buildera, automatycznie dzieli regułę formatowania warunkowego lub walidacji danych na dwa lub więcej osobnych obiektów reguł, ilekroć wstawienie lub usunięcie wiersza bądź kolumny tnie zakres pokrywany przez regułę na fragmenty wymagające różnych względnych zakotwiczeń formuły, a następnie przypisuje każdej regule formatowania warunkowego nowy, unikalny numer priorytetu. Zachowanie to zostało wprowadzone w wersji 2.196 silnika XLSX i działa automatycznie, bez ustawienia pozwalającego się z niego wypisać. Wyzwalacz jest wąski, ale częsty: reguła cellIs albo expression, której formuła odczytuje komórkę względem własnego zakresu, na arkuszu, do którego później gdzieś w środku dokładnie tego zakresu wstawiono lub z którego usunięto wiersz
Większość opisów automatyzacji Excela zatrzymuje się na problemie tekstu formuły: przesuń numery wierszy i kolumn wewnątrz każdego SUM() i każdego VLOOKUP(), żeby odwołania nadal wskazywały właściwe komórki. Ta połowa historii jest prawdziwa i jest opisana w towarzyszącym artykule o tym, jak HotXLS przepisuje odwołania formuł przy przesuwaniu wierszy i kolumn, ale reguła formatowania warunkowego czy walidacji danych to nie tylko formuła siedząca w komórce. Paruje ona formułę z zakresem, sqref w terminologii ECMA-376, i oba muszą przesuwać się razem. Gdy edycja strukturalna kroi ten zakres na dwa fragmenty, które wymagałyby dwóch różnych względnych przesunięć, żeby pozostać poprawne, utrzymywanie jednego obiektu reguły z jednym ciągiem formuły przestaje być opcją, a udawanie inaczej to sposób, w jaki reguła wyróżnienia po cichu zaczyna porównywać niewłaściwe wiersze
Dlaczego wstawienie wiersza dzieli regułę formatowania warunkowego zamiast po prostu ją przesunąć?
Reguła formatowania warunkowego lub walidacji danych utrzymuje dokładnie jedną formułę dla całego swojego zakresu, ocenianą względem pojedynczej komórki kotwicy, więc gdy tylko edycja zmusi dwie części tego zakresu do potrzebowania dwóch różnych względnych przesunięć, jedna formuła nie może już poprawnie opisać obu części. ECMA-376 wyraża pokrycie reguły jako atrybut sqref na elemencie conditionalFormatting lub dataValidation, a Excel ocenia Formula1 i Formula2 tak, jakby tekst wpisano w lewą górną komórkę tego sqref i wypełniono na resztę, tak samo jak zwykła względna formuła wypełnia się w dół kolumny. Wyobraź sobie wyróżnienie odchylenia na B2:B50, które flaguje każdą rzeczywistą wartość przekraczającą budżet, zbudowane jako reguła cellIs, której Formula1 to dosłowny tekst C2, czyli porównaj komórkę B bieżącego wiersza z komórką C tego samego wiersza
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 25Wstaw ten jeden wiersz separatora przy starym wierszu 25, a wiersze powyżej punktu wstawienia się nie przesuwają, więc ich udział w regule wciąż poprawnie odczytuje Formula1 jako C2. Wiersze, które były wcześniej 25 do 50, zsuwają się do 26 do 51, i dla nich C2 jest teraz zupełnie niewłaściwą komórką, ponieważ wiersz 26 musi porównywać się z C26, nie z wartością budżetu dwadzieścia kilka wierszy wyżej
Jak HotXLS decyduje, czy reguła wymaga podziału
HotXLS tworzy dodatkowe obiekty reguł tylko wtedy, gdy geometria naprawdę tego wymaga: wewnętrzna procedura, XlsxBuildShiftedRuleParts, przechodzi przez każdy rozłączny obszar w sqref reguły, ustala, jaka była komórka kotwicy tego obszaru przed edycją i jaka jest po niej, i sprawdza, czy każdy wynikowy fragment wymagałby tej samej korekty względnego przesunięcia. Jeśli wszystkie fragmenty się zgadzają, przetrwa jedna reguła, jej sqref zostaje odbudowane jako suma przesuniętych fragmentów, a jej formuła przebudowana jednorazowo. Prawdziwy podział zachodzi tylko wtedy, gdy fragmenty się nie zgadzają, dokładnie jak w powyższym przypadku B2:B50, gdzie górny blok zachowuje swoją oryginalną kotwicę, a dolny blok potrzebuje nowej
Przebudowanie formuły fragmentu to dwuetapowy ruch, który ponownie wykorzystuje mechanizm, jaki HotXLS już nosi dla grup formuł współdzielonych OOXML: najpierw formuła jest tłumaczona tak, jakby pierwotnie była zakotwiczona w lewej górnej komórce tego fragmentu, przy użyciu tej samej matematyki względnego przesunięcia, jaka rozwija współdzieloną formułę na jej zakres, a potem wynik przechodzi przez ten sam skaner przesunięć wierszy i kolumn, który przepisuje zwykłe formuły arkusza. W ten sposób Formula1 przechodzi z C2 na C26 w dwóch ruchach, a nie jako jeden ręcznie napisany przypadek specjalny: przetłumacz C2 do przodu o 23 wiersze, żeby otrzymać C25, tak jakby reguła zawsze zaczynała się tam, a potem pozwól zwykłemu przesunięciu przy wierszu 25 popchnąć ją dalej do C26. Każda inna właściwość — kolor wypełnienia, zatrzymaj-jeśli-prawda, sam operator — przechodzi bez zmian na nowy obiekt reguły, więc obie połówki nadal malują komórki tym samym kolorem co zawsze
// ConditionalFormats now holds two rules instead of one:
// B2:B25 Formula1 = 'C2' (rows above the insert)
// B26:B51 Formula1 = 'C26' (rows that shifted down)Czy paski danych i zestawy ikon dzielą się tak samo jak reguły cellIs?
Nie: HotXLS dzieli tylko te rodzaje reguł, których poprawność faktycznie zależy od względnej formuły na region, porównania cellIs i reguły expression, a każdy inny rodzaj formatowania warunkowego zostawia jako jeden obiekt reguły, którego sqref po prostu rośnie, żeby pokryć przesunięte fragmenty jako sumę wieloobszarową. Wewnętrznie ta gałąź to zwykłe sprawdzenie Kind, cf.Kind in [cfkCellIs, cfkExpression], nic bardziej egzotycznego. Paski danych, dwu- i trójkolorowe skale, zestawy ikon, rankingi górne i dolne oraz detektory duplikatów, pustych i błędów niosą ładunek — kolor paska, zestaw przystanków skali, rodzinę ikon — który opisuje cały pokrywany zakres naraz, a nie względne porównanie na komórkę, więc podzielenie ich na kilka priorytetyzowanych obiektów reguł nic by nie dało w kwestii poprawności, a jedynie dodałoby reguły do zarządzania. Gdy edycja dzieli ich zakres, HotXLS rekombinuje fragmenty w jedną regułę z wieloobszarowym sqref i ponownie kotwiczy ładunek jako jedną jednostkę, zamiast klonować nowy obiekt reguły na fragment. To rozróżnienie zgadza się z taksonomią rodzajów reguł w artykule o podstawach formatowania warunkowego i tekstu sformatowanego: paski danych, skale kolorów i zestawy ikon już wyróżniają się na tle reguł cellIs, całkowicie ignorując właściwość Style, a teraz okazuje się, że wyróżniają się też na tle ponownego kotwiczenia na region z tego samego, leżącego u podstaw powodu
Dlaczego priorytety reguł zmieniają się po edycji strukturalnej?
Priorytety zmieniają się, ponieważ każdy klon zaczyna z dokładnie tą samą wartością priorytetu co reguła, z której się podzielił, a HotXLS uruchamia następnie przebieg normalizacji, który rozwiązuje wynikowe duplikaty w czyste, bezluki uporządkowanie, zamiast zostawiać dwie reguły remisujące na tej samej pozycji. Druga wewnętrzna procedura, XlsxNormalizeConditionalFormatPriorities, bierze bieżący priorytet każdego formatu warunkowego, cofa się do pozycji tej reguły w kolekcji dla każdej reguły, która nigdy nie miała jawnie ustawionego priorytetu, sortuje całą listę stabilnie, żeby remisy zachowały swoją oryginalną względną kolejność, i przenumerowuje posortowany wynik na gęstą sekwencję 1, 2, 3 bez luk i bez powtórzeń. HotXLS uruchamia ją raz przed rozpoczęciem przesunięcia, więc klonowanie zaczyna się od czystego punktu odniesienia, i ponownie po każdym podziale i usunięciu każdej opróżnionej reguły, więc zapisywany plik nigdy nie ma dwóch wpisów reguł roszczących sobie ten sam priorytet. Ma to znaczenie, jeśli zastosowałeś się do rady z artykułu o podstawach formatowania warunkowego, żeby zostawiać luki między wartościami priorytetu, tak by późniejsza reguła mogła wskoczyć bez przenumerowywania reszty: luki przetrwają do następnej edycji wiersza lub kolumny dotykającej tego arkusza, a potem się zwijają, ponieważ normalizacja gwarantuje wyłącznie unikalność i stabilną kolejność, a nie to, że twój oryginalny schemat numeracji wróci niezmieniony
Reguły walidacji danych też się dzielą, bez priorytetu do przenumerowania
Reguły walidacji danych przechodzą przez tę samą logikę partycjonowania zakresu co formaty warunkowe cellIs i expression, i w przeciwieństwie do formatowania warunkowego każdy typ walidacji przechodzi tę ścieżkę jednolicie: HotXLS nie ma osobnej rodziny nie-formułowej dla walidacji danych tak, jak mają ją paski danych i zestawy ikon dla formatowania warunkowego, więc zwykła reguła listy albo liczby całkowitej jest partycjonowana przez identyczną procedurę, która obsługuje względną formułę niestandardową. Różni się priorytet: ECMA-376 w ogóle nie nadaje elementowi dataValidation atrybutu priority, więc dla walidacji nie ma kroku przenumerowania tak, jak jest dla formatów warunkowych. Wyobraź sobie walidację formułą niestandardową, która pilnuje, żeby rzeczywista kwota każdego wiersza nie przekraczała jego własnego budżetu w sąsiedniej kolumnie
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)Ma to znaczenie z tego samego powodu, dla którego artykuł o podstawach walidacji danych ostrzega przed dołączaniem reguły przed ustaleniem ostatecznej liczby wierszy: walidacja obejmuje wyłącznie dosłowne komórki, które jej podano, a późniejsza edycja strukturalna może zostawić dwie lub więcej reguł wykonujących zadanie, które kiedyś wykonywała jedna. Nic nie psuje się funkcjonalnie: każda komórka w oryginalnym zakresie jest nadal przez coś walidowana, ale kod, który zakłada jeden wpis DataValidations na kolumnę, zacznie błędnie indeksować po pierwszej edycji, która go dotknie. Istnieje twardy pułap tego, jak daleko to może zajść: jeśli podział wypchnąłby arkusz ponad 65 534 reguł walidacji danych, HotXLS zgłasza wyjątek zamiast zapisać plik, który Excel po cichu by odrzucił — to biblioteka odmawiająca wyprodukowania uszkodzonego skoroszytu, a nie limit, który zwykłe użycie prawdopodobnie osiągnie
Co sprawdzić po masowym wstawieniu lub usunięciu
Dwie rzeczy warte weryfikacji po tym, jak skrypt wykona partię edycji wierszy lub kolumn na arkuszu pełnym formatów warunkowych i walidacji, to całkowita liczba reguł i kolejność priorytetów, ponieważ obie mogą dryfować w sposób łatwy do przeoczenia w code review, a oczywisty w chwili, gdy ktoś otworzy Zarządzaj regułami w Excelu. Jedna edycja rzadko robi wiele szkody: pojedyncze wstawienie w środku jednej reguły cellIs produkuje co najwyżej dwa obiekty reguł tam, gdzie był jeden. Ryzyko narasta, gdy procedura generowania raportu wstawia wiersze pojedynczo w pętli na arkuszu, który już niesie kilka reguł zakotwiczonych formułą: każdy przebieg może ponownie podzielić reguły, które poprzedni przebieg już podzielił, a pięć oryginalnych reguł cellIs może skończyć jako wielokrotnie tyle niskowartościowych fragmentów pokrywających skrawki oryginalnego zakresu. Grupowanie edycji strukturalnych — wstawienie całego nowego bloku w jednym wywołaniu zamiast po jednym wierszu naraz — utrzymuje liczbę reguł powiązaną z liczbą naprawdę odrębnych kotwic, a nie z liczbą wykonanych edycji
Partycjonowanie reguł i normalizacja priorytetów są dostarczane jako standardowe zachowanie silnika XLSX w komponencie Excel HotXLS dla Delphi dla Delphi i C++Buildera; strona produktu zawiera pełne odniesienie API edycji arkusza, w tym opisane tutaj metody formatowania warunkowego i walidacji danych