HotXLS, Delphi ir C++Builder Excel komponentas, automatiškai padalija sąlyginio formatavimo arba duomenų tikrinimo taisyklę į du ar daugiau atskirų taisyklių objektų, kai eilutės arba stulpelio įterpimas ar ištrynimas padalija taisyklės aprėpiamą sritį į dalis, kurioms reikia skirtingų santykinių formulės inkarų, tada kiekvienai sąlyginio formato taisyklei iš naujo priskiria naują unikalų prioriteto numerį. Ši elgsena įdiegta XLSX modulyje nuo 2.196 versijos ir veikia automatiškai, be nustatymo jai išjungti. Aktyvavimo atvejis siauras, bet dažnas: cellIs arba expression taisyklė, kurios formulė nuskaito langelį santykinai nuo savo srities, yra darbalapyje, kuriame vėliau tiksliai šios srities viduryje įterpiama arba pašalinama eilutė
Daugelyje Excel automatizavimo aprašymų sustojama ties formulės teksto problema: reikia pakeisti eilučių ir stulpelių numerius kiekviename SUM() ir kiekviename VLOOKUP(), kad nuorodos ir toliau rodytų į tinkamus langelius. Ši istorijos dalis yra tikra ir aprašyta gretimame straipsnyje apie tai, kaip HotXLS perrašo formulių nuorodas perkeliant eilutes ir stulpelius, tačiau sąlyginio formato arba duomenų tikrinimo taisyklė nėra tik langelyje esanti formulė. Ji susieja formulę su sritimi, ECMA-376 terminais sqref, ir abu elementai turi judėti kartu. Kai struktūrinis pakeitimas padalija sritį į dvi dalis, kurioms išlikti teisingoms reikėtų dviejų skirtingų santykinių poslinkių, vienas taisyklės objektas su viena formulės eilute nebėra tinkamas sprendimas, o apsimetus kitaip paryškinimo taisyklė tyliai pradeda lyginti netinkamas eilutes
Kodėl įterpus eilutę sąlyginio formatavimo taisyklė padalijama, užuot tiesiog perkelta
Sąlyginio formato arba duomenų tikrinimo taisyklė visai savo sričiai turi tik vieną formulę, vertinamą vieno inkaro langelio atžvilgiu, todėl kai pakeitimas priverčia dvi šios srities dalis naudoti du skirtingus santykinius poslinkius, viena formulė nebegali teisingai aprašyti abiejų dalių. ECMA-376 taisyklės aprėptį išreiškia sqref atributu, esančiu conditionalFormatting arba dataValidation elemente, o Excel vertina Formula1 ir Formula2 taip, lyg tekstas būtų įvestas į viršutinį kairįjį šio sqref langelį ir užpildytas per likusią sritį, kaip įprasta santykinė formulė užpildoma stulpeliu žemyn. Įsivaizduokite B2:B50 taikomo nuokrypio paryškinimą, pažymintį kiekvieną faktinį dydį, viršijantį jo biudžetą, sukurtą kaip cellIs taisyklę, kurios Formula1 yra literalus tekstas C2, reiškiantis, kad dabartinės eilutės B langelis lyginamas su tos pačios eilutės C langeliu
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 25Įterpus vieną skirtuko eilutę ties sena 25 eilute, virš įterpimo vietos esančios eilutės nepajuda, todėl jų taisyklės dalis vis dar teisingai skaito Formula1 kaip C2. Eilutės, kurios anksčiau buvo nuo 25 iki 50, pasislenka žemyn ir tampa nuo 26 iki 51, o joms C2 dabar yra visiškai netinkamas langelis, nes 26 eilutėje reikia lyginti su C26, o ne su biudžeto dydžiu, esančiu dviem dešimtimis eilučių aukščiau
Kaip HotXLS nusprendžia, ar taisyklę reikia padalyti
HotXLS sukuria papildomus taisyklių objektus tik tada, kai geometrija to iš tikrųjų reikalauja: vidinė procedūra XlsxBuildShiftedRuleParts pereina per kiekvieną nesusijusią taisyklės sqref sritį, nustato, kur buvo tos srities inkaro langelis prieš pakeitimą ir kuo jis tampa po pakeitimo, tada patikrina, ar kiekvienai gautai daliai reikėtų tos pačios santykinio poslinkio korekcijos. Jei visos dalys sutampa, lieka viena taisyklė, jos sqref atkuriamas kaip perkeltų dalių junginys, o jos formulė perinkaruojama vieną kartą. Tikras skaidymas įvyksta tada, kai dalys nesutampa, kaip pirmiau aprašytu B2:B50 atveju, kai viršutinis blokas išlaiko pradinį inkarą, o apatiniam reikia naujo
Dalies formulės perinkaravimas yra dviejų žingsnių veiksmas, pakartotinai naudojantis HotXLS mechanizmus, jau turimus OOXML bendrų formulių grupėms: pirmiausia formulė išverčiama taip, lyg ji iš pradžių būtų buvusi įtvirtinta ties tos dalies viršutiniu kairiuoju langeliu, naudojant tą pačią santykinio poslinkio matematiką, kuri išplečia bendrą formulę per jos sritį, tada rezultatas perduodamas tam pačiam eilučių ir stulpelių poslinkio skeneriui, kuris perrašo įprastas darbalapio formules. Taip Formula1 iš C2 pereina į C26 dviem veiksmais, o ne vienu rankiniu specialiu atveju: perkelkite C2 23 eilutėmis pirmyn, kad gautumėte C25, tarsi taisyklė visada būtų prasidėjusi ten, tada leiskite įprastam poslinkiui ties 25 eilute perkelti ją į C26. Kiekviena kita ypatybė, užpildo spalva, stop-if-true, pats operatorius, nepakitusi perkeliama į naują taisyklės objektą, todėl abi pusės ir toliau spalvina langelius taip, kaip anksčiau
// ConditionalFormats now holds two rules instead of one:
// B2:B25 Formula1 = 'C2' (rows above the insert)
// B26:B51 Formula1 = 'C26' (rows that shifted down)Ar duomenų juostos ir piktogramų rinkiniai skaidomi taip pat kaip cellIs taisyklės
Ne: HotXLS dalija tik tuos taisyklių tipus, kurių teisingumas iš tikrųjų priklauso nuo santykinės formulės kiekviename regione, tai yra cellIs palyginimus ir expression taisykles, o visus kitus sąlyginio formato tipus palieka kaip vieną taisyklės objektą, kurio sqref tiesiog išplečiamas, kad apimtų perkeltas dalis kaip kelių sričių junginį. Viduje naudojamas paprastas Kind patikrinimas cf.Kind in [cfkCellIs, cfkExpression], nieko sudėtingesnio. Duomenų juostos, dviejų ir trijų spalvų skalės, piktogramų rinkiniai, viršutinio ir apatinio reitingo taisyklės, dublikatų, tuščių langelių ir klaidų detektoriai turi naudingąją dalį, juostos spalvą, skalės ribų rinkinį arba piktogramų šeimą, aprašančią visą aprėpiamą sritį iš karto, o ne santykinį palyginimą kiekvienam langeliui, todėl jų skaidymas į kelis prioritetinius taisyklių objektus nesuteiktų jokio teisingumo pranašumo ir tik pridėtų valdomų taisyklių. Kai pakeitimas padalija jų sritį, HotXLS vėl sujungia dalis į vieną taisyklę su kelių sričių sqref ir perinkaruoja naudingąją dalį kaip vieną vienetą, užuot klonavęs naują taisyklės objektą kiekvienai daliai. Šis skirtumas atitinka taisyklių tipų klasifikaciją sąlyginio formatavimo ir raiškiojo teksto pagrindų straipsnyje: duomenų juostos, spalvų skalės ir piktogramų rinkiniai jau skiriasi nuo cellIs taisyklių tuo, kad visiškai ignoruoja Style ypatybę, o dabar paaiškėja, kad dėl tos pačios priežasties jie skiriasi ir nuo skaidymo pagal regiono inkarą
Kodėl po struktūrinio pakeitimo pasikeičia taisyklių prioritetai
Prioritetai pasikeičia todėl, kad kiekvienas klonas iš pradžių turi lygiai tokią pačią prioriteto reikšmę kaip taisyklė, iš kurios jis suskaidytas, o HotXLS vėliau paleidžia normalizavimo veiksmą, kuris išsprendžia atsiradusius dublikatus į tvarkingą nepertraukiamą seką, užuot palikęs dvi taisykles su tuo pačiu rangu. Antroji vidinė procedūra XlsxNormalizeConditionalFormatPriorities paima dabartinį kiekvieno sąlyginio formato prioritetą, taisyklei, kuriai niekada nebuvo aiškiai nustatytas prioritetas, naudoja jos vietą rinkinyje, stabiliai surikiuoja visą sąrašą, kad vienodų reikšmių atveju būtų išlaikyta pradinė santykinė tvarka, ir pernumeruoja surikiuotą rezultatą į tankią 1, 2, 3 seką be tarpų ir pasikartojimų. HotXLS tai atlieka vieną kartą prieš pradedant poslinkį, kad klonavimas prasidėtų nuo švaraus pagrindo, ir dar kartą po kiekvieno skaidymo bei kiekvienos ištuštėjusios taisyklės pašalinimo, todėl išsaugomame faile niekada nebūna dviejų taisyklių įrašų su tuo pačiu prioritetu. Tai svarbu, jei vadovavotės sąlyginio formatavimo pagrindų straipsnio patarimu palikti tarpus tarp prioritetų, kad vėlesnė taisyklė galėtų įsiterpti nepernumeruojant kitų: tarpai išlieka iki kito eilutės arba stulpelio pakeitimo tame darbalapyje, tada susitraukia, nes normalizavimas garantuoja tik unikalumą ir stabilią tvarką, o ne pradinės numeravimo schemos atkūrimą
Duomenų tikrinimo taisyklės taip pat skaidomos, tačiau jų prioriteto pernumeruoti nereikia
Duomenų tikrinimo taisyklėms taikoma ta pati srities skaidymo logika kaip cellIs ir expression sąlyginio formato taisyklėms, o kitaip nei sąlyginio formato atveju, kiekvienas tikrinimo tipas šiuo keliu eina vienodai: HotXLS neturi atskiros neformulinės duomenų tikrinimo šeimos, panašios į sąlyginio formato duomenų juostas ir piktogramų rinkinius, todėl paprasto sąrašo arba sveikojo skaičiaus taisyklė skaidoma ta pačia procedūra, kuri apdoroja santykinę pasirinktinę formulę. Skiriasi prioritetas: ECMA-376 dataValidation elementui apskritai nesuteikia priority atributo, todėl tikrinimams nėra tokio pernumeravimo etapo kaip sąlyginiams formatams. Įsivaizduokite pasirinktinės formulės tikrinimą, neleidžiantį kiekvienos eilutės faktinei sumai viršyti šalia esančiame stulpelyje nurodyto jos biudžeto
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)Tai svarbu dėl tos pačios priežasties, dėl kurios duomenų tikrinimo pagrindų straipsnis įspėja nepriskirti taisyklės prieš nustatant galutinį eilučių skaičių: tikrinimas apima tik pažodžiui nurodytus langelius, o vėlesnis struktūrinis pakeitimas gali palikti dvi ar daugiau taisyklių, atliekančių darbą, kurį anksčiau atliko viena. Funkciškai niekas nesugenda: kiekvienas pradinės srities langelis vis dar tikrinamas pagal kurią nors taisyklę, tačiau kodas, darantis prielaidą, kad kiekvienam stulpeliui tenka vienas DataValidations įrašas, po pirmojo pakeitimo pradės neteisingai indeksuoti. Kiek tai gali tęstis, yra griežta riba: jei skaidymas darbalapyje viršytų 65,534 duomenų tikrinimo taisykles, HotXLS iškelia išimtį, užuot įrašęs failą, kurį Excel tyliai atmestų, todėl biblioteka atsisako gaminti sugadintą darbaknygę, o ne nustato ribą, kurią tikėtina pasiekti įprastai naudojant
Ką tikrinti po masinio įterpimo ar ištrynimo
Du dalykai, kuriuos verta patikrinti po to, kai scenarijus darbalapyje, pilname sąlyginio formato ir tikrinimo taisyklių, atlieka eilutės ar stulpelio pakeitimų paketą, yra bendras taisyklių skaičius ir prioritetų tvarka, nes abu gali pasikeisti taip, kad kodo peržiūroje tai lengva praleisti, bet iškart matoma Excel dialoge Tvarkyti taisykles. Vienas pakeitimas retai sukelia daug žalos: vienas įterpimas vienos cellIs taisyklės viduryje sukuria ne daugiau kaip du taisyklių objektus vietoje vieno. Rizika kaupiasi, kai ataskaitų generavimo procedūra po vieną įterpia eilutes cikle darbalapyje, kuriame jau yra kelios formulėmis įtvirtintos taisyklės: kiekvienas žingsnis gali iš naujo padalyti taisykles, kurias ankstesnis žingsnis jau buvo padalijęs, todėl penkios pradinės cellIs taisyklės gali pavirsti į kelis kartus didesnį skaičių mažaverčių fragmentų, apimančių mažas pradinės srities dalis. Struktūrinių pakeitimų paketavimas, kai visas naujas blokas įterpiamas vienu iškvietimu, o ne po vieną eilutę, susieja taisyklių skaičių su iš tikrųjų skirtingų inkarų skaičiumi, o ne su atliktų pakeitimų skaičiumi
Taisyklių skaidymas ir prioritetų normalizavimas yra standartinė XLSX modulio elgsena HotXLS Delphi Excel komponente, skirtoje Delphi ir C++Builder; produkto puslapyje pateikiama išsami darbalapio redagavimo API nuoroda, įskaitant čia aprašytus sąlyginio formatavimo ir duomenų tikrinimo metodus