HotXLS, Excelova komponenta za Delphi in C++Builder, samodejno razdeli pravilo pogojnega oblikovanja ali preverjanja podatkov na dva ali več ločenih objektov pravil, kadar vstavljanje ali brisanje vrstice oziroma stolpca razreže pokriti obseg pravila na dele, ki potrebujejo različna relativna sidra formul, nato pa vsakemu pravilu pogojnega oblikovanja znova dodeli svežo, enolično prednostno številko. To vedenje je bilo vključeno v različico 2.196 pogona XLSX in deluje samodejno, brez nastavitve za izklop. Sprožilec je ozek, vendar pogost: pravilo cellIs ali expression, katerega formula bere celico glede na lasten obseg in je na delovnem listu, v katerega je pozneje vstavljena ali odstranjena vrstica nekje sredi tega natančnega obsega
Večina opisov avtomatizacije Excela se ustavi pri težavi besedila formule: premakniti številke vrstic in stolpcev znotraj vsakega SUM() in vsakega VLOOKUP(), da sklici še vedno kažejo na prave celice. Ta del zgodbe je resničen in opisan v spremljevalnem članku o tem, kako HotXLS prepiše sklice formul, ko se vrstice in stolpci premaknejo, vendar pogojno oblikovanje ali pravilo preverjanja podatkov ni le formula v celici. Povezuje formulo z obsegom, v izrazju ECMA-376 imenovanim sqref, in oba se morata premakniti skupaj. Ko strukturno urejanje razdeli ta obseg na dva dela, ki bi za pravilnost potrebovala dva različna relativna odmika, en objekt pravila z enim nizom formule ni več ustrezna možnost, pretvarjanje, da je, pa povzroči, da pravilo označevanja tiho začne primerjati napačne vrstice
Zakaj vstavljanje vrstice razdeli pravilo pogojnega oblikovanja, namesto da bi ga samo premaknilo?
Pravilo pogojnega oblikovanja ali preverjanja podatkov hrani natanko eno formulo za celoten obseg, ovrednoteno glede na eno sidrno celico, zato ena formula ne more več pravilno opisati obeh delov, ko urejanje povzroči, da ta dela potrebujeta različna relativna odmika. ECMA-376 izraža pokritost pravila z atributom sqref na elementu conditionalFormatting ali dataValidation, Excel pa ovrednoti Formula1 in Formula2, kot da bi bilo besedilo vneseno v zgornjo levo celico tega sqref in zapolnjeno čez njegov preostanek, enako kot se navadna relativna formula zapolni navzdol po stolpcu. Predstavljajte si označevanje odstopanja v obsegu B2:B50, ki označi vsako dejansko vrednost, večjo od njenega proračuna, zgrajeno kot pravilo cellIs, pri katerem je Formula1 dobesedno besedilo C2, kar pomeni primerjavo celice B v trenutni vrstici s celico C v isti vrstici
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 25Vstavite eno ločilno vrstico v staro vrstico 25 in vrstice nad točko vstavljanja se ne premaknejo, zato njihov del pravila še vedno pravilno bere Formula1 kot C2. Vrstice, ki so bile prej od 25 do 50, se pomaknejo navzdol na 26 do 51, zanje pa je C2 zdaj povsem napačna celica, saj mora vrstica 26 primerjati s C26 in ne s proračunsko vrednostjo dva ducata vrstic više
Kako HotXLS odloči, ali je treba pravilo razdeliti
HotXLS ustvari dodatne objekte pravil samo takrat, ko geometrija to zares zahteva: notranja rutina XlsxBuildShiftedRuleParts prehodi vsa ločena območja v sqref pravila, ugotovi, katera je bila sidrna celica tega območja pred urejanjem in katera je po njem, nato pa preveri, ali bi vsak nastali del potreboval enak popravek relativnega odmika. Če se vsi deli ujemajo, preživi eno pravilo, njegov sqref pa se znova sestavi kot unija premaknjenih delov in njegova formula se enkrat znova zasidra. Do prave razdelitve pride samo, ko se deli ne ujemajo, natančno kot v primeru B2:B50 zgoraj, kjer zgornji blok ohrani izvirno sidro, spodnji pa potrebuje novo
Sidranje formule dela je dvostopenjski premik, ki znova uporabi mehanizme, ki jih HotXLS že ima za skupine deljenih formul OOXML: najprej se formula prevede, kot da bi bila prvotno zasidrana v lastni zgornji levi celici tega dela, pri čemer se uporabi ista matematika relativnih odmikov, ki razširi deljeno formulo čez njen obseg, nato pa rezultat obdela isti pregledovalnik premikov vrstic in stolpcev, ki prepisuje navadne formule delovnega lista. Tako Formula1 preide iz C2 v C26 v dveh premikih in ne v enem ročno napisanem posebnem primeru: C2 se najprej premakne za 23 vrstic naprej, da dobimo C25, kot da bi se pravilo tam vedno začelo, nato pa ga navadni premik pri vrstici 25 pomakne na C26. Vsaka druga lastnost, barva polnila, stop-if-true in sam operator, se nespremenjena prenese na novi objekt pravila, zato obe polovici še naprej obarvata celice z isto barvo kot prej
// ConditionalFormats now holds two rules instead of one:
// B2:B25 Formula1 = 'C2' (rows above the insert)
// B26:B51 Formula1 = 'C26' (rows that shifted down)Ali se podatkovne vrstice in nabori ikon razdelijo enako kot pravila cellIs?
Ne: HotXLS razdeli samo vrste pravil, katerih pravilnost je dejansko odvisna od relativne formule za posamezno območje, torej primerjave cellIs in pravila expression, vse druge vrste pogojnega oblikovanja pa pusti kot en objekt pravila, katerega sqref se preprosto poveča, da pokrije premaknjene dele kot unijo več območij. Interno je veja navadno preverjanje Kind, cf.Kind in [cfkCellIs, cfkExpression], nič bolj zapletenega. Podatkovne vrstice, dvobarvne in tribarvne lestvice, nabori ikon, razvrstitve od vrha in dna ter detektorji dvojnikov, praznih in napačnih vrednosti nosijo vsebino, barvo vrstice, nabor mej lestvice ali družino ikon, ki opisuje celoten pokriti obseg naenkrat in ne primerjave glede na celico. Razdelitev na več prednostnih objektov pravil zato ne bi prinesla pravilnosti, temveč bi samo dodala pravila za upravljanje. Ko urejanje razdeli njihov obseg, HotXLS dele znova združi v eno pravilo z večobmočnim sqref in vsebino znova zasidra kot eno enoto, namesto da bi za vsak del kloniral nov objekt pravila. Razlika se ujema s taksonomijo vrst pravil v članku o osnovah pogojnega oblikovanja in slogov obogatenega besedila: podatkovne vrstice, barvne lestvice in nabori ikon se od pravil cellIs že razlikujejo po tem, da v celoti prezrejo lastnost Style, zdaj pa se iz istega temeljnega razloga razlikujejo tudi od ponovnega sidranja po območjih
Zakaj se prednosti pravil po strukturnem urejanju spremenijo?
Prednosti se spremenijo, ker vsak klon na začetku vsebuje popolnoma isto vrednost prednosti kot pravilo, iz katerega je nastal, HotXLS pa nato zažene prehod normalizacije, ki nastale dvojnike razreši v urejen vrstni red brez vrzeli, namesto da bi pustil dve pravili izenačeni na istem mestu. Druga notranja rutina, XlsxNormalizeConditionalFormatPriorities, vzame trenutno prednost vsakega pogojnega oblikovanja, za pravilo brez izrecno nastavljene vrednosti uporabi njegov položaj v zbirki, stabilno razvrsti celoten seznam, da vezi ohranijo prvotni relativni vrstni red, nato pa razvrščeni rezultat preštevilči v zgoščeno zaporedje 1, 2, 3 brez vrzeli in ponovitev. HotXLS jo zažene enkrat pred začetkom premika, zato kloniranje izhaja iz čiste osnovne vrednosti, in znova po vsaki razdelitvi ter odstranitvi vsakega izpraznjenega pravila, zato shranjena datoteka nikoli nima dveh vnosov pravil z isto prednostjo. To je pomembno, če ste upoštevali nasvet iz članka o osnovah pogojnega oblikovanja, naj med vrednostmi prednosti pustite vrzeli, da se lahko poznejše pravilo vstavi brez preštevilčenja preostalih: vrzeli ostanejo do naslednjega urejanja vrstice ali stolpca na tem delovnem listu, nato pa se skrčijo, ker normalizacija zagotavlja samo enoličnost in stabilen vrstni red, ne pa nespremenjenega prvotnega sistema številčenja
Razdelijo se tudi pravila preverjanja podatkov, vendar brez prednosti za preštevilčenje
Pravila preverjanja podatkov uporabljajo isto logiko razdelitve obsega kot pogojna oblikovanja cellIs in expression, vendar za razliko od pogojnega oblikovanja vsaka vrsta preverjanja enotno uporablja to pot: HotXLS nima ločene družine brez formul za preverjanje podatkov, kakršni sta podatkovna vrstica in nabor ikon pri pogojnem oblikovanju, zato se navadno pravilo seznama ali celih števil razdeli z isto rutino, ki obravnava relativno formulo po meri. Razlika je v prednosti: element ECMA-376 dataValidation sploh nima atributa priority, zato pri preverjanjih ni koraka preštevilčenja, kakršen obstaja pri pogojnih oblikovanjih. Predstavljajte si preverjanje s formulo po meri, ki prepreči, da bi dejanski znesek v vsaki vrstici presegel njen proračun v sosednjem stolpcu
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)To je pomembno iz istega razloga, zaradi katerega članek o osnovah preverjanja podatkov opozarja, naj pravila ne pripnete, preden je število vrstic dokončno: preverjanje pokriva samo dobesedne celice, ki ste jih navedli, poznejše strukturno urejanje pa lahko povzroči, da nalogo, ki jo je prej opravljalo eno pravilo, zdaj opravljata dve ali več pravil. Funkcionalno se nič ne pokvari: vsako celico v prvotnem obsegu še vedno preverja nekaj, vendar bo koda, ki predpostavlja en vnos DataValidations na stolpec, po prvem urejanju tega obsega začela napačno indeksirati. Obstaja trdna zgornja meja: če bi razdelitev delovni list potisnila čez 65,534 pravil preverjanja podatkov, HotXLS sproži izjemo, namesto da bi zapisal datoteko, ki bi jo Excel tiho zavrnil, saj knjižnica tako noče ustvariti poškodovanega delovnega zvezka, omejitev pa je v običajni uporabi malo verjetna
Kaj preveriti po množičnem vstavljanju ali brisanju
Dve stvari, ki ju je vredno preveriti, ko skript izvede paketno urejanje vrstic ali stolpcev na delovnem listu, polnem pogojnih oblikovanj in preverjanj, sta skupno število pravil in vrstni red prednosti, saj se lahko oba neopazno spremenita pri pregledu kode in postaneta očitna takoj, ko nekdo v Excelu odpre Upravljanje pravil. Eno urejanje običajno ne povzroči velike škode: eno vstavljanje sredi pravila cellIs ustvari največ dva objekta pravil namesto enega. Tveganje se poveča, ko rutina za ustvarjanje poročila v zanki eno za drugo vstavi vrstice na delovni list, ki že vsebuje več pravil s formulami: vsak prehod lahko znova razdeli pravila, ki jih je prejšnji prehod že razdelil, in pet prvotnih pravil cellIs lahko postane večkratnik tega števila manj vrednih delcev, ki pokrivajo drobce prvotnega obsega. Paketno izvajanje strukturnih sprememb, torej vstavljanje celotnega novega bloka z enim klicem namesto po ene vrstice, ohrani število pravil povezano s številom resnično različnih sider in ne s številom izvedenih urejanj
Razdelitev pravil in normalizacija prednosti sta standardno vedenje pogona XLSX v Excelovi komponenti HotXLS za Delphi za Delphi in C++Builder; stran izdelka vsebuje celoten API za urejanje delovnih listov, vključno z metodami za pogojno oblikovanje in preverjanje podatkov, opisanimi tukaj