HotXLS, Excel komponenta za Delphi i C++Builder, automatski dijeli pravilo uvjetnog oblikovanja ili provjere valjanosti podataka na dva ili više zasebnih objekata kad umetanje ili brisanje retka ili stupca podijeli pokriveni raspon na dijelove kojima trebaju različita relativna sidra formula, a zatim svakom pravilu uvjetnog oblikovanja dodjeljuje novi jedinstveni broj prioriteta. Takvo je ponašanje dio XLSX mehanizma od verzije 2.196 i odvija se automatski, bez postavke za isključivanje. Okidač je uzak, ali čest: pravilo vrste cellIs ili expression čija formula čita ćeliju relativno prema vlastitom rasponu na radnom listu u koji se poslije umetne ili izbriše redak negdje usred tog istog raspona
Većina objašnjenja automatizacije Excela zaustavlja se na problemu teksta formule: treba pomaknuti brojeve redaka i stupaca u svakom izrazu SUM() i VLOOKUP() kako bi reference i dalje pokazivale na ispravne ćelije. Taj je dio stvaran i obrađen je u pratećem članku o načinu na koji HotXLS prepisuje reference formula pri pomicanju redaka i stupaca, ali uvjetno oblikovanje ili pravilo provjere valjanosti podataka nije samo formula smještena u ćeliju. Ono povezuje formulu s rasponom, koji se u terminologiji ECMA-376 naziva sqref, i ta se dva dijela moraju pomicati zajedno. Kad strukturna izmjena razreže raspon na dva dijela kojima trebaju različiti relativni pomaci da bi ostali ispravni, jedno pravilo s jednom tekstualnom formulom više nije dovoljno, a upravo tako pravilo isticanja može potajno početi uspoređivati pogrešne retke
Zašto umetanje retka dijeli pravilo uvjetnog oblikovanja umjesto da ga samo pomakne?
Pravilo uvjetnog oblikovanja ili provjere valjanosti podataka čuva točno jednu formulu za cijeli raspon, procijenjenu relativno prema jednoj sidrenoj ćeliji, pa jedna formula više ne može ispravno opisati oba dijela čim izmjena prisili ta dva dijela na različite relativne pomake. ECMA-376 opseg pravila izražava atributom sqref na elementu conditionalFormatting ili dataValidation, a Excel procjenjuje Formula1 i Formula2 kao da je tekst upisan u gornju lijevu ćeliju tog sqref-a i ispunjen kroz ostatak raspona, baš kao što se obična relativna formula ispunjava niz stupac. Zamislite isticanje odstupanja u rasponu B2:B50 koje označava svaki stvarni iznos veći od proračunskog, izvedeno kao pravilo cellIs čiji je Formula1 doslovno C2, što znači da se ćelija B u trenutačnom retku uspoređuje s ćelijom C u tom istom retku
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 25Umetnite taj jedan razdjelni redak na staro mjesto 25 i redci iznad točke umetanja neće se pomaknuti, pa njihov dio pravila i dalje ispravno čita Formula1 kao C2. Redci koji su prije bili od 25 do 50 pomaknut će se na 26 do 51, a za njih je C2 sada potpuno pogrešna ćelija jer redak 26 mora uspoređivati s C26, a ne s proračunskom vrijednošću dva desetka redaka iznad
Kako HotXLS odlučuje treba li podijeliti pravilo
HotXLS stvara dodatne objekte pravila samo kad geometrija to zaista zahtijeva: interna rutina XlsxBuildShiftedRuleParts prolazi kroz svako nepovezano područje u sqref-u pravila, određuje koja je bila sidrena ćelija tog područja prije izmjene i koja je nakon nje te provjerava bi li svaki dobiveni dio zahtijevao isti ispravak relativnog pomaka. Ako se svi dijelovi slažu, ostaje jedno pravilo, njegov se sqref ponovno gradi kao unija pomaknutih dijelova, a formula se jednom ponovno usidri. Do stvarne podjele dolazi samo kad se dijelovi ne slažu, upravo kao u slučaju B2:B50, gdje gornji blok zadržava izvorno sidro, a donjem je potrebno novo
Ponovno usidravanje formule nekog dijela odvija se u dva koraka i koristi mehanizme koje HotXLS već ima za grupe zajedničkih formula OOXML-a: najprije se formula prevede kao da je izvorno bila usidrena u gornjoj lijevoj ćeliji tog dijela, uz istu matematiku relativnih pomaka kojom se zajednička formula proširuje kroz raspon, a zatim se rezultat provuče kroz isti skener pomaka redaka i stupaca koji prepisuje obične formule radnog lista. Zato Formula1 prelazi s C2 na C26 u dva koraka, a ne kroz ručno napisanu posebnu iznimku: prevedite C2 unaprijed za 23 retka da dobijete C25, kao da je pravilo oduvijek počinjalo ondje, pa zatim dopustite običnom pomaku na retku 25 da ga pomakne na C26. Sva druga svojstva, boja ispune, stop-if-true i sam operator, nepromijenjena se prenose na novi objekt pravila pa obje polovice i dalje boje ćelije istom bojom
// ConditionalFormats now holds two rules instead of one:
// B2:B25 Formula1 = 'C2' (rows above the insert)
// B26:B51 Formula1 = 'C26' (rows that shifted down)Dijele li se podatkovne trake i skupovi ikona na isti način kao pravila cellIs?
Ne: HotXLS dijeli samo one vrste pravila čija ispravnost stvarno ovisi o relativnoj formuli po području, odnosno usporedbama cellIs i pravilima expression, dok sve druge vrste uvjetnog oblikovanja ostavlja kao jedan objekt pravila čiji se sqref jednostavno proširuje tako da obuhvati pomaknute dijelove kao uniju više područja. Interno je grananje obična provjera vrste, cf.Kind in [cfkCellIs, cfkExpression], bez ičega složenijeg. Podatkovne trake, ljestvice s dvije i tri boje, skupovi ikona, rangiranja pri vrhu i dnu te detektori duplikata, praznih i pogrešnih vrijednosti nose sadržaj, boju trake, skup prijelomnih točaka ljestvice ili obitelj ikona koji odjednom opisuju cijeli pokriveni raspon, a ne relativnu usporedbu po ćeliji. Kad izmjena podijeli njihov raspon, HotXLS ponovno spaja dijelove u jedno pravilo s višepodručnim sqref-om i ponovno sidri sadržaj kao jednu cjelinu, umjesto da klonira novi objekt pravila za svaki dio. Ta se razlika podudara s taksonomijom vrsta pravila u članku o osnovama uvjetnog oblikovanja i stilova obogaćenog teksta: podatkovne trake, ljestvice boja i skupovi ikona već se razlikuju od pravila cellIs time što u potpunosti zanemaruju svojstvo Style, a sada se pokazuje da se iz istog temeljnog razloga razlikuju i po ponovnom sidrenju po područjima
Zašto se prioriteti pravila mijenjaju nakon strukturne izmjene?
Prioriteti se mijenjaju zato što svaki klon na početku ima potpuno istu vrijednost prioriteta kao pravilo iz kojeg je nastao, a HotXLS nakon toga pokreće prolaz normalizacije koji nastale duplikate pretvara u čisti redoslijed bez praznina, umjesto da ostavi dva pravila izjednačena na istom rangu. Druga interna rutina, XlsxNormalizeConditionalFormatPriorities, uzima trenutačni prioritet svakog uvjetnog oblikovanja, za pravilo kojem prioritet nikad nije bio izričito postavljen koristi njegov položaj u zbirci, stabilno sortira cijeli popis tako da izjednačeni elementi zadrže izvorni međusobni redoslijed te rezultat ponovno numerira u gusti niz 1, 2, 3 bez praznina i ponavljanja. HotXLS je pokreće jednom prije početka pomaka, tako da kloniranje polazi od čistog početnog stanja, i ponovno nakon svake podjele te nakon uklanjanja svakog ispražnjenog pravila, pa spremljena datoteka nikad nema dva zapisa pravila koja tvrde da imaju isti prioritet. To je važno ako ste slijedili savjet iz članka o osnovama uvjetnog oblikovanja da između vrijednosti prioriteta ostavite praznine kako bi se kasnije pravilo moglo umetnuti bez ponovnog numeriranja ostatka: praznine ostaju do sljedeće izmjene retka ili stupca koja dotakne taj radni list, a zatim se uklanjaju jer normalizacija jamči jedinstvenost i stabilan redoslijed, ne vraćanje vaše izvorne sheme numeriranja
Pravila provjere valjanosti podataka također se dijele, bez prioriteta za ponovno numeriranje
Pravila provjere valjanosti podataka prolaze istom logikom particioniranja raspona kao uvjetna oblikovanja cellIs i expression, a za razliku od uvjetnog oblikovanja, svaka vrsta provjere valjanosti ravnomjerno prolazi tim putem. HotXLS nema zasebnu obitelj bez formula za provjeru valjanosti, kakvu za uvjetno oblikovanje predstavljaju podatkovne trake i skupovi ikona, pa se obično pravilo popisa ili cijelog broja particionira istom rutinom koja obrađuje relativnu prilagođenu formulu. Razlika je u prioritetu: ECMA-376 elementu dataValidation uopće ne daje atribut priority, pa za provjere valjanosti nema koraka ponovnog numeriranja kakav postoji za uvjetna oblikovanja. Zamislite provjeru s prilagođenom formulom koja sprječava da stvarni iznos u svakom retku premaši vlastiti proračun u susjednom stupcu
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 važno iz istog razloga zbog kojeg članak o osnovama provjere valjanosti podataka upozorava da se pravilo ne veže prije konačnog određivanja broja redaka: provjera obuhvaća samo doslovne ćelije koje ste joj zadali, a kasnija strukturna izmjena može ostaviti dva ili više pravila koja obavljaju posao jednog prijašnjeg pravila. Funkcionalno se ništa ne kvari, svaka ćelija izvornog raspona i dalje je provjerena nekim pravilom, ali kod koji pretpostavlja jedan unos u DataValidations po stupcu počet će pogrešno indeksirati nakon prve izmjene koja ga dotakne. Postoji čvrsta gornja granica: ako bi dijeljenje gurnulo radni list iznad 65,534 pravila provjere valjanosti podataka, HotXLS podiže iznimku umjesto da zapiše datoteku koju bi Excel tiho odbio, čime knjižnica odbija proizvesti oštećenu radnu knjigu, a ne nameće ograničenje do kojeg bi došlo u uobičajenoj upotrebi
Što provjeriti nakon skupnog umetanja ili brisanja
Nakon što skripta izvrši niz izmjena redaka ili stupaca na radnom listu punom uvjetnih oblikovanja i provjera valjanosti, vrijedi provjeriti ukupan broj pravila i redoslijed prioriteta jer oba mogu neprimjetno odlutati tijekom pregleda koda, a postanu očita čim netko u Excelu otvori Upravljanje pravilima. Jedna izmjena obično ne napravi veliku štetu: jedno umetanje usred pravila cellIs stvara najviše dva objekta pravila ondje gdje je prije bio jedan. Rizik raste kad rutina za izradu izvještaja umeće retke jedan po jedan u petlji kroz radni list koji već sadrži nekoliko pravila usidrenih formulama: svaki prolaz može ponovno podijeliti pravila koja su prethodni prolazi već podijelili, pa pet izvornih pravila cellIs može završiti s nekoliko puta većim brojem bezvrijednih fragmenata koji pokrivaju tanke odsječke izvornog raspona. Grupiranje strukturnih izmjena, odnosno umetanje cijelog novog bloka u jednom pozivu umjesto retka po retka, održava broj pravila vezanim uz broj stvarno različitih sidara, a ne uz broj izvršenih izmjena
Particioniranje pravila i normalizacija prioriteta standardne su mogućnosti XLSX mehanizma u HotXLS Delphi Excel Component za Delphi i C++Builder; stranica proizvoda sadržava potpuni API za uređivanje radnih listova, uključujući ovdje opisane metode uvjetnog oblikovanja i provjere valjanosti podataka