HotXLS, Delphi- ja C++Builder-Excel-komponentti, jakaa automaattisesti ehdollisen muotoilun tai tietojen kelpoisuustarkistuksen säännön kahdeksi tai useammaksi erilliseksi sääntöolioksi aina, kun rivin tai sarakkeen lisäys tai poisto leikkaa säännön kattaman alueen palasiksi, jotka tarvitsevat eri suhteellisia kaava-ankkureita, ja antaa sitten jokaiselle ehdollisen muotoilun säännölle uuden, yksilöllisen prioriteettinumeron. Tämä käytös julkaistiin XLSX-moottorin versiossa 2.196 ja toimii automaattisesti, ilman asetusta, jolla siitä voisi kieltäytyä. Laukaisin on kapea mutta yleinen: cellIs- tai lausekesääntö, jonka kaava lukee solun suhteessa omaan alueeseensa, työarkilla, jolle myöhemmin lisätään tai poistetaan rivi jostain keskeltä juuri tuota aluetta
Useimmat kirjoitukset Excel-automaatiosta pysähtyvät kaavatekstiongelmaan: siirrä rivi- ja sarakenumerot jokaisen SUM()- ja VLOOKUP()-kutsun sisällä, jotta viittaukset osoittavat yhä oikeisiin soluihin. Tuo puoli tarinasta on todellinen, ja se käsitellään artikkelissa rinnakkaisartikkeli siitä, miten HotXLS kirjoittaa kaavaviittaukset uudelleen, kun rivejä ja sarakkeita siirretään, mutta ehdollinen muotoilu tai kelpoisuustarkistussääntö ei ole vain solussa istuva kaava. Se yhdistää kaavan alueeseen, sqref ECMA-376-termein, ja näiden kahden on liikuttava yhdessä. Kun rakenteellinen muokkaus viipaloi tuon alueen kahdeksi osaksi, jotka tarvitsisivat kaksi eri suhteellista siirtymää pysyäkseen oikeina, yhden sääntöolion pitäminen yhdellä kaavamerkkijonolla lakkaa olemasta vaihtoehto, ja muun väittäminen on juuri se, miten korostussääntö alkaa hiljaa vertailla vääriä rivejä
Miksi rivin lisääminen jakaa ehdollisen muotoilun säännön sen sijaan, että vain siirtäisi sitä?
Ehdollisen muotoilun tai kelpoisuustarkistuksen sääntö pitää täsmälleen yhden kaavan koko alueelleen, arvioituna suhteessa yhteen ankkurisoluun, joten heti kun muokkaus pakottaa alueen kaksi osaa tarvitsemaan kaksi eri suhteellista siirtymää, yksi kaava ei enää voi kuvata molempia osia oikein. ECMA-376 ilmaisee säännön kattavuuden sqref-attribuuttina conditionalFormatting- tai dataValidation-elementissä, ja Excel arvioi Formula1- ja Formula2-kaavat ikään kuin teksti olisi kirjoitettu tuon sqref-alueen vasempaan yläkulmasoluun ja täytetty sen loppuosan yli, samalla tavalla kuin tavallinen suhteellinen kaava täyttyy sarakkeessa alaspäin. Kuvittele varianssikorostus alueella B2:B50, joka merkitsee minkä tahansa toteutuneen luvun, joka ylittää budjettinsa, rakennettuna cellIs-sääntönä, jonka Formula1 on kirjaimellinen teksti C2, eli vertaa nykyisen rivin B-solua saman rivin C-soluun
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 25Lisää tuo yksi erotinrivi vanhaan riviin 25, ja lisäyskohdan yläpuoliset rivit eivät liiku, joten niiden osa säännöstä lukee Formula1-arvon C2 yhä oikein. Rivit, jotka olivat aiemmin 25–50, liukuvat alas riveiksi 26–51, ja niille C2 on nyt täysin väärä solu, koska rivin 26 on verrattava soluun C26, ei budjettilukuun kaksikymmentä riviä ylempänä
Miten HotXLS päättää, tarvitseeko sääntö jakaa?
HotXLS luo ylimääräisiä sääntöolioita vain, kun geometria todella sitä vaatii: sisäinen rutiini, XlsxBuildShiftedRuleParts, käy läpi jokaisen erillisen alueen säännön sqref-arvossa, selvittää, mikä tuon alueen ankkurisolu oli ennen muokkausta ja mikä siitä tulee sen jälkeen, ja tarkistaa, tarvitsisiko jokainen tuloksena syntyvä pala saman suhteellisen siirtymäkorjauksen. Jos kaikki palat ovat samaa mieltä, yksi sääntö säilyy, sen sqref rakennettuna uudelleen siirrettyjen palojen yhdisteenä ja kaavansa uudelleenankkuroituna kerran. Todellinen jako tapahtuu vain, kun palat ovat eri mieltä, täsmälleen yllä olevassa B2:B50-tapauksessa, jossa yläosa säilyttää alkuperäisen ankkurinsa ja alaosa tarvitsee uuden
Palan kaavan uudelleenankkurointi on kaksivaiheinen liike, joka käyttää uudelleen koneistoa, jota HotXLS jo kantaa OOXML:n jaettuja kaavaryhmiä varten: ensin kaava käännetään ikään kuin se olisi alun perin ankkuroitu tuon palan omaan vasempaan yläkulmasoluun, käyttäen samaa suhteellisen siirtymän matematiikkaa, joka laajentaa jaetun kaavan alueensa yli, ja sitten tulos kulkee läpi saman rivi- ja sarakesiirtymäskannerin, joka kirjoittaa uudelleen tavalliset työarkin kaavat. Näin Formula1 muuttuu C2:sta C26:ksi kahdella liikkeellä yhden käsin kirjoitetun erikoistapauksen sijaan: käännä C2 eteenpäin 23 riviä saadaksesi C25, ikään kuin sääntö olisi aina alkanut sieltä, ja anna sitten tavallisen rivin 25 siirtymän työntää sitä eteenpäin arvoon C26. Jokainen muu ominaisuus, täyttöväri, stop-if-true, operaattori itse, kulkee muuttumattomana mukana uuteen sääntöolioon, joten molemmat puoliskot jatkavat solujen maalaamista samalla värillä kuin aina ennenkin
// ConditionalFormats now holds two rules instead of one:
// B2:B25 Formula1 = 'C2' (rows above the insert)
// B26:B51 Formula1 = 'C26' (rows that shifted down)Jakautuvatko datapalkit ja kuvakesarjat samalla tavalla kuin cellIs-säännöt?
Ei: HotXLS osittaa vain ne sääntötyypit, joiden oikeellisuus todella riippuu aluekohtaisesta suhteellisesta kaavasta, cellIs-vertailuista ja lausekesäännöistä, ja jättää jokaisen muun ehdollisen muotoilun tyypin yhdeksi sääntöolioksi, jonka sqref yksinkertaisesti kasvaa kattamaan siirtyneet palat monialueisena yhdisteenä. Sisäisesti haara on tavallinen Kind-tarkistus, cf.Kind in [cfkCellIs, cfkExpression], ei mitään eksoottisempaa. Datapalkeilla, kaksi- ja kolmiväriasteikoilla, kuvakesarjoilla, ylimpien ja alimpien järjestyksillä sekä duplikaatti-, tyhjä- ja virhetunnistimilla on hyötykuorma, palkkiväri, joukko asteikkopysäkkejä, kuvakeperhe, joka kuvaa koko katetun alueen kerralla eikä solukohtaista suhteellista vertailua, joten niiden jakaminen useaksi priorisoiduksi sääntöolioksi ei toisi mitään oikeellisuushyötyä ja vain lisäisi hallittavia sääntöjä. Kun muokkaus jakaa niiden alueen, HotXLS yhdistää palat takaisin yhdeksi säännöksi, jolla on monialueinen sqref, ja uudelleenankkuroi hyötykuorman yhtenä kokonaisuutena kloonaamatta uutta sääntöoliota per pala. Ero linjautuu sääntötyyppitaksonomian kanssa artikkelissa ehdollisen muotoilun ja rich text -tyylien perusteet: datapalkit, väriasteikot ja kuvakesarjat erottuvat jo cellIs-säännöistä sivuuttamalla Style-ominaisuuden kokonaan, ja nyt käy ilmi, että ne erottuvat myös aluekohtaisesta uudelleenankkuroinnista samasta taustasyystä
Miksi säännön prioriteetit muuttuvat rakenteellisen muokkauksen jälkeen?
Prioriteetit muuttuvat, koska jokainen klooni alkaa täsmälleen samalla prioriteettiarvolla kuin sääntö, josta se jakautui, ja HotXLS ajaa sen jälkeen normalisointikierroksen, joka ratkaisee syntyneet duplikaatit puhtaaksi, aukottomaksi järjestykseksi sen sijaan, että jättäisi kaksi sääntöä tasapeliin samasta sijasta. Toinen sisäinen rutiini, XlsxNormalizeConditionalFormatPriorities, ottaa jokaisen ehdollisen muotoilun nykyisen prioriteetin, palautuu kyseisen säännön sijaintiin kokoelmassa minkä tahansa säännön kohdalla, jolle sitä ei koskaan asetettu nimenomaisesti, lajittelee koko listan vakaasti niin, että tasapelit säilyttävät alkuperäisen suhteellisen järjestyksensä, ja numeroi lajitellun tuloksen uudelleen tiiviiksi 1, 2, 3 -sarjaksi ilman aukkoja tai toistoja. HotXLS ajaa sen kerran ennen siirtymän alkua, joten kloonaus alkaa puhtaalta pohjalta, ja uudelleen jokaisen jaon jälkeen ja jokaisen tyhjentyneen säännön poistamisen jälkeen, joten tallennettavassa tiedostossa ei koskaan ole kahta sääntömerkintää, jotka väittävät samaa prioriteettia. Tämä on merkityksellistä, jos noudatit ehdollisen muotoilun perusteet -artikkelin neuvoa jättää aukkoja prioriteettiarvojen väliin, jotta myöhempi sääntö voisi mahtua väliin ilman muiden uudelleennumerointia: aukot säilyvät seuraavaan rivi- tai sarakemuokkaukseen asti, joka koskettaa tuota työarkkia, ja romahtavat sitten, koska normalisointi takaa vain yksilöllisyyden ja vakaan järjestyksen, ei sitä, että alkuperäinen numerointijärjestelmäsi palaisi muuttumattomana
Tietojen kelpoisuustarkistuksen säännöt jakautuvat myös, ilman prioriteettia uudelleennumeroitavaksi
Tietojen kelpoisuustarkistuksen säännöt kulkevat saman aluepohjaisen osituslogiikan läpi kuin cellIs- ja lausekemuotoiset ehdolliset muotoilut, ja toisin kuin ehdollisessa muotoilussa, jokainen kelpoisuustarkistustyyppi kulkee tuota polkua yhtenäisesti: HotXLS:llä ei ole erillistä ei-kaavaperhettä tietojen kelpoisuustarkistukselle samalla tavalla kuin datapalkit ja kuvakesarjat ovat ehdolliselle muotoilulle, joten tavallinen luettelo- tai kokonaislukusääntö osittuu samalla rutiinilla, joka käsittelee suhteellisen mukautetun kaavan. Se, mikä eroaa, on prioriteetti: ECMA-376 ei anna dataValidation-elementille lainkaan priority-attribuuttia, joten kelpoisuustarkistuksille ei ole uudelleennumerointivaihetta samalla tavalla kuin ehdollisille muotoiluille. Kuvittele mukautetun kaavan kelpoisuustarkistus, joka estää jokaisen rivin toteutunutta määrää ylittämästä sen omaa budjettia vieressä olevassa sarakkeessa
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)Tämä on merkityksellistä samasta syystä kuin miksi tietojen kelpoisuustarkistuksen perusteet -artikkeli varoittaa säännön liittämisestä ennen kuin rivimäärä on lopullinen: kelpoisuustarkistus kattaa vain ne kirjaimelliset solut, jotka sille annoit, ja myöhempi rakenteellinen muokkaus voi jättää kaksi tai useampia sääntöjä tekemään työtä, jonka yksi teki ennen. Mikään ei hajoa toiminnallisesti: jokainen solu alkuperäisellä alueella on yhä jonkin kelpoisuustarkistuksen alaisena, mutta koodi, joka olettaa yhden DataValidations-merkinnän per sarake, alkaa indeksoida väärin ensimmäisen muokkauksen jälkeen, joka sitä koskettaa. Tälle on kova katto: jos jakaminen työntäisi työarkin yli 65 534 tietojen kelpoisuustarkistussäännön, HotXLS nostaa poikkeuksen sen sijaan, että kirjoittaisi tiedoston, jonka Excel hiljaa hylkäisi, mikä on kirjaston kieltäytyminen valmistamasta vioittunutta työkirjaa sen sijaan, että se olisi raja, johon tavallinen käyttö todennäköisesti osuisi
Mitä kannattaa tarkistaa massalisäyksen tai -poiston jälkeen
Kaksi asiaa, jotka kannattaa varmistaa sen jälkeen, kun skripti on ajanut erän rivi- tai sarakemuokkauksia arkilla, joka on täynnä ehdollisia muotoiluja ja kelpoisuustarkistuksia, ovat sääntöjen kokonaismäärä ja prioriteettijärjestys, koska molemmat voivat ajautua tavoilla, jotka on helppo ohittaa koodikatselmuksessa ja ilmeisiä heti, kun joku avaa Manage Rules -valikon Excelissä. Yksi muokkaus harvoin aiheuttaa paljon vahinkoa: yksi lisäys keskellä yhtä cellIs-sääntöä tuottaa enintään kaksi sääntöoliota, joissa oli yksi. Riski kasautuu, kun raportintuottorutiini lisää rivejä yksi kerrallaan silmukassa arkilla, joka jo kantaa useita kaavaankkuroituja sääntöjä: jokainen kierros voi jakaa uudelleen sääntöjä, jotka edellinen kierros jo jakoi, ja viisi alkuperäistä cellIs-sääntöä voi päätyä moninkertaiseksi määräksi vähäarvoisia sirpaleita, jotka kattavat viipaleita alkuperäisestä alueesta. Rakenteellisten muokkausten eräkäsittely, koko uuden lohkon lisääminen yhdellä kutsulla yksittäisen rivin sijaan, pitää sääntömäärän sidottuna aidosti erillisten ankkureiden määrään suoritettujen muokkausten määrän sijaan
Säännön ositus ja prioriteetin normalisointi toimitetaan vakiokäytöksenä XLSX-moottorissa HotXLS Delphi Excel -komponentissa Delphille ja C++Builderille; tuotesivulla on koko työarkin muokkaus-API:n viite, mukaan lukien tässä kuvatut ehdollisen muotoilun ja tietojen kelpoisuustarkistuksen metodit