Ankkuroitujen ehdollisten muotoilujen osittaminen HotXLS:ssä

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

Rivi, joka lisätään vanhalle riville 25, jakaa HotXLS:n ehdollisen muotoilun alueella B2:B50 Delphissä osiin B2:B25, joka säilyttää Formula1 C2:n, ja B26:B51, joka perustetaan uudelleen Formula1 C26:een — Lisäyksen yläpuolella olevat rivit pitävät alkuperäisen C2-ankkurinsa, kun taas alaspäin siirtyvät rivit tarvitsevat tuoreen C26-ankkurin, joten yksi sääntöobjekti ei voi enää palvella molempia lohkoja

Idx := Sheet.AddConditionalFormat('B2:B50', xlsxCfOpGreaterThan, 'C2');
Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

Sheet.InsertRows(25, 1);   // yksi tyhjä erotinrivi, alkaen vanhasta rivistä 25

Lisää 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

Päätösvirta, jonka HotXLS soveltaa Delphissä, kun muokkaus kohtaa ehdollisen muotoilun tai datan validointisäännön: yhdestä siirtymäkorjauksesta samaa mieltä olevat osat säilyttävät yhden säännön, eri mieltä olevat osat jakautuvat erikseen perustetuiksi sääntöobjekteiksi — XlsxBuildShiftedRuleParts pitää yhden säännön vain, kun jokainen osa tarvitsee saman offsetkorjauksen, muussa tapauksessa jokainen osa siirretään uudelleen pohjalle kahdessa vaiheessa ja siitä tulee oma sääntöobjektinsa

// ConditionalFormats sisältää nyt kaksi sääntöä yhden sijaan:
//   B2:B25    Formula1 = 'C2'    (rivit ennen lisäystä)
//   B26:B51   Formula1 = 'C26'   (rivit, jotka siirtyivät alas)

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

HotXLS normalisoi ehdollisten muotoilujen prioriteetit Delphissä jaon jälkeen: prioriteettiin 3 sidotut kloonit vakausalajärjestellään ja numeroidaan uudelleen tiheäksi 1, 2, 3 -sekvenssiksi — Kloonit lähtevät tasapeliin vanhemman prioriteetissa, joten normalisointikierros vakaa-järjestää säännöt ja numeroi ne uudelleen ilman aukkoja ennen työkirjan tallennusta

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);   // poista viisi riviä tarkastetulta alueelta
// DataValidations sisältää nyt kaksi sääntöä yhden sijaan:
//   D2:D149    Formula1 = 'D2<=C2'      (rivit ennen poistoa)
//   D150:D395  Formula1 = 'D150<=C150'  (rivit, jotka siirtyivät ylös)

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