A HotXLS, a Delphi és C++Builder Excel-komponens, automatikusan két vagy több különálló szabályobjektumra bontja fel egy feltételes formázási vagy adatérvényesítési szabályt, valahányszor egy sor- vagy oszlopbeszúrás, illetve -törlés a szabály lefedett tartományát olyan darabokra vágja, amelyeknek eltérő relatív képlet-horgonyokra van szükségük, majd minden feltételes formátum szabálynak friss, egyedi prioritásszámot rendel újra hozzá. A viselkedés az XLSX-motor 2.196-os verziójában érkezett, és automatikusan fut, kikapcsolási lehetőség nélkül. A kiváltó ok szűk, de gyakori: egy cellIs vagy kifejezés-szabály, amelynek képlete egy, a saját tartományához viszonyítva relatív cellát olvas, egy munkalapon, amelybe később egy sort szúrnak be vagy törölnek pontosan annak a tartománynak valahol a közepén
Az Excel-automatizálásról szóló legtöbb írás a képlet-szöveg problémánál áll meg: eltolja a sor- és oszlopszámokat minden SUM()-on és minden VLOOKUP()-on belül, hogy a hivatkozások továbbra is a helyes cellákra mutassanak. A történet ezen fele valós, és a azt tárgyaló kísérőcikkben szerepel, hogyan írja át a HotXLS a képlethivatkozásokat, amikor sorok és oszlopok mozognak, de egy feltételes formátum vagy egy adatérvényesítési szabály nem csupán egy cellában ülő képlet. Egy képletet egy tartománnyal párosít, ECMA-376 nyelven sqref, és a kettőnek együtt kell mozognia. Amikor egy strukturális szerkesztés ezt a tartományt két olyan darabra szeleteli, amelyeknek két különböző relatív eltolásra lenne szükségük ahhoz, hogy helyesek maradjanak, egyetlen szabályobjektum egyetlen képletsztringgel megtartása megszűnik lehetőség lenni, és úgy tenni, mintha nem így lenne, az az út, ahogyan egy kiemelési szabály csendben elkezdi a rossz sorokat összehasonlítani
Miért osztja fel egy sor beszúrása a feltételes formázási szabályt ahelyett, hogy egyszerűen csak mozgatná?
Egy feltételes formátum vagy adatérvényesítési szabály pontosan egy képletet tart fenn a teljes tartományára, egyetlen horgonycellához viszonyítva kiértékelve, így amint egy szerkesztés arra kényszeríti a tartomány két részét, hogy két különböző relatív eltolásra legyen szükségük, egyetlen képlet többé nem tudja mindkét részt helyesen leírni. Az ECMA-376 egy szabály lefedettségét az sqref attribútumként fejezi ki a conditionalFormatting vagy dataValidation elemen, és az Excel úgy értékeli ki a Formula1-et és a Formula2-t, mintha a szöveget annak az sqref-nek a bal felső cellájába gépelték volna be, és onnan töltötték volna ki a többi részére, ugyanúgy, ahogyan egy közönséges relatív képlet lefelé tölt egy oszlopon. Képzelj el egy eltéréskiemelést B2:B50 fölött, amely bármely tényleges számot megjelöl, amely meghaladja a költségvetését, egy cellIs szabályként felépítve, amelynek Formula1-je a szó szerinti C2 szöveg, azaz hasonlítsd össze az aktuális sor B celláját ugyanannak a sornak a C cellájával
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 25Szúrj be egy elválasztó sort a régi 25-ös sornál, és a beszúrási pont fölötti sorok nem mozdulnak, így a szabály rájuk eső része továbbra is helyesen olvassa a Formula1-et C2-ként. A régen 25-től 50-ig terjedő sorok lecsúsznak 26-tól 51-ig, és számukra a C2 most már teljesen rossz cella, mivel a 26. sornak a C26-tal kell összehasonlítania, nem egy két tucat sorral feljebb lévő költségvetési számmal
Hogyan dönti el a HotXLS, hogy egy szabálynak fel kell-e osztódnia
A HotXLS csak akkor hoz létre extra szabályobjektumokat, amikor a geometria valóban megkívánja: egy belső rutin, a XlsxBuildShiftedRuleParts, bejárja a szabály sqref-jének minden diszjunkt területét, kiszámítja, mi volt annak a területnek a horgonycellája a szerkesztés előtt, és mi lesz utána, és ellenőrzi, hogy minden keletkező darab ugyanazt a relatív-eltolás korrekciót igényelné-e. Ha minden darab megegyezik, egyetlen szabály marad életben, az sqref-je az eltolt darabok uniójaként újraépítve, és a képlete egyszer újraalapozva. Egy valódi felosztás csak akkor történik, amikor a darabok nem egyeznek, pontosan a fenti B2:B50 eset, ahol a felső blokk megtartja eredeti horgonyát, az alsó blokknak pedig új kell
Egy darab képletének újraalapozása egy kétlépéses művelet, amely újrahasznosítja azt a gépezetet, amelyet a HotXLS már hordoz az OOXML megosztott képletcsoportokhoz: először a képlet úgy kerül lefordításra, mintha eredetileg annak a darabnak a saját bal felső cellájánál lett volna horgonyozva, ugyanazzal a relatív-eltolási matematikával, amely egy megosztott képletet kiterjeszt a tartományán, majd az eredmény átmegy ugyanazon a sor- és oszlopeltolás-vizsgálón, amely átírja a közönséges munkalap-képleteket. Így jut el a Formula1 a C2-től a C26-ig két lépésben, nem pedig egy kézzel írt speciális esetben: fordítsd le a C2-t 23 sorral előre, hogy megkapd a C25-öt, mintha a szabály mindig ott kezdődött volna, majd hagyd, hogy a közönséges eltolás a 25. sornál továbbtolja C26-ra. Minden más tulajdonság, kitöltőszín, stop-if-true, maga az operátor, változatlanul átkerül az új szabályobjektumra, így mindkét fél továbbra is azzal a színnel festi a cellákat, amivel mindig is tette
// ConditionalFormats now holds two rules instead of one:
// B2:B25 Formula1 = 'C2' (rows above the insert)
// B26:B51 Formula1 = 'C26' (rows that shifted down)Az adatsávok és ikonkészletek ugyanúgy felosztódnak, mint a cellIs szabályok?
Nem: a HotXLS csak azokat a szabálytípusokat particionálja, amelyek helyessége ténylegesen egy régiónkénti relatív képlettől függ, a cellIs összehasonlításokat és a kifejezés-szabályokat, és minden más feltételesformátum-típust egyetlen szabályobjektumként hagy, amelynek sqref-je egyszerűen kibővül, hogy lefedje az eltolt darabokat, mint egy többterületes unió. Belsőleg az elágazás egy egyszerű Kind-ellenőrzés, cf.Kind in [cfkCellIs, cfkExpression], semmi ennél egzotikusabb. Az adatsávok, a két- és háromszínű skálák, az ikonkészletek, a felső és alsó rangsorolások, valamint a duplikátum-, üres- és hibaérzékelők egy hasznos terhet hordoznak, egy sávszínt, egy skálamegállók halmazát, egy ikoncsaládot, amely a teljes lefedett tartományt egyszerre írja le, nem pedig egy cellánkénti relatív összehasonlítást, így ezek több rangsorolt szabályobjektumra bontása nem venne semmilyen helyességi hasznot, és csak több szabályt adna hozzá a kezeléshez. Amikor egy szerkesztés felosztja a tartományukat, a HotXLS visszaolvasztja a darabokat egyetlen szabályba egy többterületes sqref-fel, és a hasznos terhet egységként horgonyozza újra, ahelyett hogy darabonként egy új szabályobjektumot klónozna. A megkülönböztetés illeszkedik a szabálytípus-taxonómiához a feltételes formázás és formázott szöveg alapjait tárgyaló cikkben: az adatsávok, színskálák, és ikonkészletek már így is elkülönülnek a cellIs szabályoktól azáltal, hogy teljesen figyelmen kívül hagyják a Style tulajdonságot, és most kiderül, hogy ugyanezen alapvető okból elkülönülnek a régiónkénti újrahorgonyzástól is
Miért változnak a szabályprioritások egy strukturális szerkesztés után?
A prioritások azért változnak, mert minden klón pontosan ugyanazzal a prioritásértékkel indul, mint az a szabály, amiből felosztották, és a HotXLS ezután egy normalizáló átfutást futtat, amely a keletkező duplikátumokat tiszta, rés nélküli sorrendbe rendezi, ahelyett hogy két szabályt hagyna azonos rangon holtversenyben. Egy második belső rutin, a XlsxNormalizeConditionalFormatPriorities, veszi minden feltételes formátum aktuális prioritását, visszaesik annak a szabálynak a gyűjteményen belüli pozíciójára minden olyan szabálynál, amelynek soha nem volt explicit módon beállítva, stabilan rendezi a teljes listát, hogy a holtversenyek megtartsák eredeti relatív sorrendjüket, és újraszámozza a rendezett eredményt egy sűrű 1, 2, 3 sorozattá, rés és ismétlődés nélkül. A HotXLS egyszer futtatja ezt egy eltolás megkezdése előtt, így a klónozás egy tiszta alapvonalról indul, és újra minden felosztás után, és minden kiürült szabály eltávolítása után, így az elmentett fájlban soha nincs két szabálybejegyzés, amely ugyanazt a prioritást igényli. Ez akkor számít, ha megfogadtad a feltételes formázás alapjairól szóló cikk tanácsát, hogy hagyj réseket a prioritásértékek között, hogy egy későbbi szabály be tudjon illeszkedni a többi újraszámozása nélkül: a rések megmaradnak, amíg egy következő sor- vagy oszlopszerkesztés hozzá nem ér ahhoz a munkalaphoz, majd összeomlanak, mert a normalizálás csak egyediséget és stabil sorrendet garantál, nem azt, hogy az eredeti számozási sémád változatlanul visszatér
Az adatérvényesítési szabályok is felosztódnak, prioritás nélkül, amit újra kellene számozni
Az adatérvényesítési szabályok ugyanazon a tartomány-particionáló logikán mennek keresztül, mint a cellIs és kifejezés feltételes formátumok, és a feltételes formázással ellentétben minden érvényesítési típus egységesen ezt az utat követi: a HotXLS-nek nincs külön nem-képlet családja az adatérvényesítéshez úgy, ahogyan az adatsávok és ikonkészletek vannak a feltételes formázáshoz, így egy egyszerű lista- vagy egészszám-szabályt ugyanaz a rutin particionál, amely egy relatív egyéni képletet kezel. Ami eltér, az a prioritás: az ECMA-376 egyáltalán nem ad priority attribútumot a dataValidation elemnek, így nincs újraszámozási lépés az érvényesítéseknél úgy, ahogy a feltételes formátumoknál van. Képzelj el egy egyéni képletes érvényesítést, amely megakadályozza, hogy minden sor tényleges összege meghaladja a mellette lévő oszlopban lévő saját költségvetését
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)Ez ugyanazért az okért számít, amiért az adatérvényesítés alapjairól szóló cikk óva int attól, hogy egy szabályt a sorszám véglegesítése előtt csatolj: egy érvényesítés csak azokat a szó szerinti cellákat fedi le, amelyeket megadtál neki, és egy későbbi strukturális szerkesztés két vagy több szabályt hagyhat azon a munkán dolgozni, amit korábban egy végzett. Funkcionálisan semmi nem törik el: az eredeti tartomány minden cellája továbbra is érvényesítve van valami által, de az a kód, amely azt feltételezi, hogy egy DataValidations bejegyzés van oszloponként, elkezd rosszul indexelni, miután az első szerkesztés hozzáér. Van egy kemény plafon arra, milyen messzire mehet ez: ha a felosztás egy munkalapot 65 534 adatérvényesítési szabály fölé tolna, a HotXLS kivételt dob ahelyett, hogy egy olyan fájlt írna, amelyet az Excel csendben elutasítana, ez a könyvtár azon döntése, hogy inkább nem gyárt sérült munkafüzetet, nem pedig egy korlát, amelyet a hétköznapi használat valószínűleg elér
Mit érdemes ellenőrizni egy tömeges beszúrás vagy törlés után
A két dolog, amit érdemes ellenőrizni, miután egy szkript egy köteg sor- vagy oszlopszerkesztést futtat egy feltételes formátumokkal és érvényesítésekkel teli lapon, a teljes szabályszám és a prioritási sorrend, mivel mindkettő olyan módon sodródhat el, amelyet könnyű elmulasztani egy kódáttekintés során, és nyilvánvaló abban a pillanatban, amikor valaki megnyitja a Szabályok kezelését az Excelben. Egy szerkesztés ritkán okoz sok kárt: egyetlen beszúrás egy cellIs szabály közepén legfeljebb két szabályobjektumot eredményez ott, ahol egy volt. A kockázat akkor sokszorozódik, amikor egy jelentésgeneráló rutin sorokat szúr be egyesével egy ciklusban egy lapon, amely már több képlet-horgonyozott szabályt hordoz: minden átfutás újra feloszthatja azokat a szabályokat, amelyeket egy korábbi átfutás már felosztott, és öt eredeti cellIs szabály végül ennek többszöröseként végezheti, alacsony értékű töredékekként, amelyek az eredeti tartomány szeleteit fedik le. A strukturális szerkesztések kötegelése, az egész új blokk beszúrása egyetlen hívásban ahelyett, hogy egyszerre egy sort szúrnánk be, a szabályszámot a valóban különálló horgonyok számához köti, nem a végrehajtott szerkesztések számához
A szabályparticionálás és a prioritás-normalizálás az XLSX-motor szabványos viselkedéseként érkezik a Delphihez és C++Builderhez készült HotXLS Delphi Excel Komponensben; a termékoldal hordozza a teljes munkalap-szerkesztési API-referenciát, beleértve az itt leírt feltételes formázási és adatérvényesítési metódusokat