HotXLS, Excel komponenta za Delphi i C++Builder, automatski deli pravilo uslovnog formatiranja ili provere podataka na dva ili više zasebnih objekata pravila kad umetanje ili brisanje reda ili kolone podeli pokriveni opseg na delove kojima su potrebna različita relativna sidra formula, a zatim svakom pravilu uslovnog formatiranja dodeljuje novi, jedinstveni broj prioriteta. Ovo ponašanje je uvedeno u verziji 2.196 XLSX mehanizma i radi automatski, bez podešavanja kojim bi se isključilo. Okidač je specifičan, ali čest: pravilo tipa cellIs ili expression čija formula čita ćeliju relativno u odnosu na sopstveni opseg, na radnom listu u koji se kasnije umetne ili ukloni red negde u sredini baš tog opsega
Većina tekstova o automatizaciji Excela završava se na problemu teksta formule: pomerite brojeve redova i kolona unutar svakog SUM() i svakog VLOOKUP() tako da reference i dalje pokazuju na prave ćelije. Taj deo priče je stvaran i obrađen je u pratećem članku o tome kako HotXLS prepisuje reference formula kada se redovi i kolone pomeraju, ali uslovni format ili pravilo provere podataka nije samo formula upisana u ćeliju. Ono spaja formulu sa opsegom, sqref u terminologiji ECMA-376, i ta dva elementa moraju da se pomeraju zajedno. Kada strukturna izmena preseče taj opseg na dva dela kojima su potrebni različiti relativni pomaci da bi ostali ispravni, jedan objekat pravila sa jednom tekstualnom formulom više nije dovoljan, a upravo tako pravilo za isticanje neprimetno počinje da poredi pogrešne redove
Zašto umetanje reda deli pravilo uslovnog formatiranja umesto da ga samo pomeri?
Pravilo uslovnog formatiranja ili provere podataka čuva tačno jednu formulu za ceo svoj opseg, koja se izračunava relativno u odnosu na jednu sidrenu ćeliju, pa čim izmena učini da su za dva dela tog opsega potrebni različiti relativni pomaci, jedna formula više ne može ispravno da opiše oba dela. ECMA-376 predstavlja pokrivenost pravila atributom sqref na elementu conditionalFormatting ili dataValidation, a Excel procenjuje Formula1 i Formula2 kao da je tekst unet u gornju levu ćeliju tog atributa sqref i popunjen kroz ostatak opsega, isto kao što se obična relativna formula popunjava niz kolonu. Zamislite isticanje odstupanja u opsegu B2:B50 koje označava svaku stvarnu vrednost veću od budžeta, napravljeno kao pravilo cellIs čiji je Formula1 doslovni tekst C2, što znači da se ćelija B u trenutnom redu poredi sa ćelijom C u tom istom redu
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 red za razdvajanje na starom redu 25 i redovi iznad tačke umetanja se ne pomeraju, pa njihov deo pravila i dalje ispravno koristi Formula1 kao C2. Redovi koji su ranije bili od 25 do 50 pomeraju se naniže, postaju redovi od 26 do 51, a za njih je C2 sada potpuno pogrešna ćelija, jer red 26 mora da se poredi sa C26, a ne sa budžetskom vrednošću udaljenom gotovo dve desetine redova
Kako HotXLS odlučuje da li pravilo treba podeliti
HotXLS stvara dodatne objekte pravila samo kada geometrija to zaista zahteva: interna rutina XlsxBuildShiftedRuleParts prolazi kroz svaku nepovezanu oblast u sqref, utvrđuje koja je bila sidrena ćelija te oblasti pre izmene i u šta se pretvara posle nje, pa proverava da li bi svakom dobijenom delu bila potrebna ista korekcija relativnog pomaka. Ako se svi delovi slažu, preživljava jedno pravilo, njegov sqref se ponovo gradi kao unija pomerenih delova, a formula se jednom ponovo usidri. Do stvarnog razdvajanja dolazi samo kada se delovi ne slažu, upravo kao u slučaju B2:B50 iznad, gde gornji blok zadržava prvobitno sidro, a donjem je potrebno novo
Ponovno usidravanje formule jednog dela odvija se u dva koraka i koristi mehanizam koji HotXLS već ima za grupe deljenih formula u OOXML-u: prvo se formula prevede kao da je prvobitno bila usidrena u gornjoj levoj ćeliji tog dela, uz istu matematiku relativnih pomaka koja proširuje deljenu formulu kroz njen opseg, a zatim se rezultat prosleđuje istom skeneru za pomeranje redova i kolona koji prepisuje obične formule radnog lista. Tako Formula1 prelazi sa C2 na C26 u dva koraka, umesto kroz ručno napisani poseban slučaj: prevedite C2 unapred za 23 reda da dobijete C25, kao da je pravilo oduvek počinjalo tamo, pa pustite da ga uobičajeno pomeranje na redu 25 pomeri dalje na C26. Svako drugo svojstvo, boja ispune, stop-if-true i sam operator, prenosi se neizmenjeno na novi objekat pravila, pa obe polovine nastavljaju da boje ćelije istom bojom kao ranije
// ConditionalFormats now holds two rules instead of one:
// B2:B25 Formula1 = 'C2' (rows above the insert)
// B26:B51 Formula1 = 'C26' (rows that shifted down)Da li se trake podataka i skupovi ikona dele na isti način kao cellIs pravila?
Ne: HotXLS deli samo one vrste pravila čija ispravnost zaista zavisi od relativne formule po oblasti, odnosno poređenja cellIs i pravila expression, a svaku drugu vrstu uslovnog formatiranja ostavlja kao jedan objekat pravila čiji se sqref jednostavno proširuje tako da obuhvati pomerene delove kao uniju više oblasti. Interno je grana obična provera vrste, cf.Kind in [cfkCellIs, cfkExpression], bez ičeg složenijeg. Trake podataka, skale sa dve i tri boje, skupovi ikona, rangiranja pri vrhu i pri dnu, kao i detektori duplikata, praznih i neispravnih vrednosti nose sadržaj, boju trake, skup graničnih tačaka skale ili porodicu ikona koji opisuju ceo pokriveni opseg odjednom, umesto relativnog poređenja po ćeliji, pa njihovo razdvajanje na više objekata pravila sa prioritetima ne bi donelo ispravnost, već samo dodatna pravila za upravljanje. Kada izmena podeli njihov opseg, HotXLS ponovo spaja delove u jedno pravilo sa višeoblasnim sqref i ponovo usidri sadržaj kao jednu celinu, umesto da za svaki deo klonira novi objekat pravila. Ta razlika prati taksonomiju vrsta pravila u članku o osnovama uslovnog formatiranja i stilova obogaćenog teksta: trake podataka, skale boja i skupovi ikona već se razlikuju od pravila cellIs po tome što potpuno zanemaruju svojstvo Style, a sada se ispostavlja da se iz istog osnovnog razloga razlikuju i od ponovnog usidravanja po oblastima
Zašto se prioriteti pravila menjaju posle strukturne izmene?
Prioriteti se menjaju zato što svaki klon na početku dobija potpuno istu vrednost prioriteta kao pravilo iz kog je nastao, a HotXLS zatim pokreće prolaz normalizacije koji nastale duplikate pretvara u uredan redosled bez praznina, umesto da ostavi dva pravila sa istim rangom. Druga interna rutina, XlsxNormalizeConditionalFormatPriorities, uzima trenutni prioritet svakog uslovnog formata, za pravilo kojem prioritet nikada nije izričito postavljen koristi njegov položaj u kolekciji, stabilno sortira celu listu tako da izvorni relativni redosled ostane kod izjednačenih vrednosti, a zatim rezultat ponovo numeriše kao gust niz 1, 2, 3 bez praznina i ponavljanja. HotXLS je pokreće jednom pre početka pomeranja, tako da kloniranje kreće od čiste osnove, i ponovo posle svakog razdvajanja i svakog uklanjanja ispražnjenog pravila, pa sačuvana datoteka nikad nema dva unosa pravila koji tvrde da imaju isti prioritet. To je važno ako ste sledili savet iz članka o osnovama uslovnog formatiranja da ostavljate praznine između vrednosti prioriteta kako bi kasnije pravilo moglo da se umetne bez renumerisanja ostatka: praznine opstaju do sledeće izmene reda ili kolone koja dodirne taj radni list, a zatim nestaju, jer normalizacija garantuje samo jedinstvenost i stabilan redosled, ne i nepromenjen povratak vaše prvobitne šeme numerisanja
I pravila provere podataka se dele, bez ponovnog numerisanja prioriteta
Pravila provere podataka prolaze kroz istu logiku particionisanja opsega kao uslovni formati cellIs i expression, a za razliku od uslovnog formatiranja, svaki tip provere ravnomerno prati taj put: HotXLS nema zasebnu porodicu provera bez formula kakvu trake podataka i skupovi ikona imaju kod uslovnog formatiranja, pa se obično pravilo za listu ili ceo broj particioniše istom rutinom koja obrađuje relativnu prilagođenu formulu. Razlikuje se prioritet: ECMA-376 elementu dataValidation uopšte ne daje atribut priority, pa za provere nema koraka renumerisanja kakav postoji za uslovne formate. Zamislite proveru prilagođenom formulom koja sprečava da stvarni iznos u svakom redu premaši budžet u susednoj koloni
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)Ovo je važno iz istog razloga zbog kog članak o osnovama provere podataka upozorava da pravilo ne pridružujete pre nego što konačan broj redova bude poznat: provera obuhvata samo doslovne ćelije koje ste joj zadali, a kasnija strukturna izmena može ostaviti dva ili više pravila da obavljaju posao koji je ranije obavljalo jedno. Funkcionalno se ništa ne kvari: svaku ćeliju prvobitnog opsega i dalje proverava neko pravilo, ali kod koji pretpostavlja jedan unos u DataValidations po koloni počeće pogrešno da indeksira nakon prve izmene tog opsega. Postoji čvrsta gornja granica: ako bi razdvajanje dovelo radni list iznad 65,534 pravila provere podataka, HotXLS podiže izuzetak umesto da upiše datoteku koju bi Excel tiho odbio, čime biblioteka odbija da napravi oštećenu radnu svesku, a ne nameće ograničenje do kog se uobičajena upotreba približava
Šta treba proveriti posle grupnog umetanja ili brisanja
Dve stvari koje vredi proveriti nakon što skripta izvrši grupu izmena redova ili kolona na listu punom uslovnih formata i provera jesu ukupan broj pravila i redosled prioriteta, jer oba mogu da se promene na način koji je lako prevideti u pregledu koda, a postaje očigledan čim neko otvori prozor za upravljanje pravilima u Excelu. Jedna izmena obično ne napravi veliku štetu: jedno umetanje u sredini jednog pravila cellIs stvara najviše dva objekta pravila tamo gde je ranije bio jedan. Rizik se uvećava kada rutina za generisanje izveštaja umeće redove jedan po jedan u petlji nad listom koji već sadrži više pravila usidrenih formulom: svaki prolaz može ponovo da podeli pravila koja je prethodni prolaz već podelio, pa pet prvobitnih cellIs pravila može da se pretvori u nekoliko puta veći broj fragmenata male vrednosti koji pokrivaju uske delove prvobitnog opsega. Grupisanje strukturnih izmena, odnosno umetanje celog novog bloka jednim pozivom umesto reda po reda, drži broj pravila vezanim za broj stvarno različitih sidara, a ne za broj izvršenih izmena
Particionisanje pravila i normalizacija prioriteta standardno su ponašanje XLSX mehanizma u HotXLS Delphi Excel komponenti za Delphi i C++Builder; stranica proizvoda sadrži potpunu referencu API-ja za uređivanje radnih listova, uključujući ovde opisane metode za uslovno formatiranje i proveru podataka