HotXLS, Excel-komponenten för Delphi och C++Builder, delar automatiskt upp en villkorsformaterings- eller datavalideringsregel i två eller fler separata regelobjekt närhelst en rad- eller kolumninfogning eller -borttagning skär regelns täckta intervall i bitar som behöver olika relativa formelankare, och tilldelar sedan varje villkorsformateringsregel ett nytt, unikt prioritetsnummer. Beteendet levererades i version 2.196 av XLSX-motorn och körs automatiskt, utan någon inställning att stänga av det. Utlösaren är smal men vanlig: en cellIs- eller uttrycksregel vars formel läser en cell relativt sitt eget intervall, som lever på ett kalkylblad där en rad senare infogas eller tas bort någonstans mitt i just det intervallet
De flesta genomgångar av Excel-automation stannar vid formeltextsproblemet: skifta rad- och kolumnnumren inuti varje SUM() och varje VLOOKUP() så att referenserna fortfarande pekar på rätt celler. Den halvan av historien är verklig, och den täcks i följdartikeln om hur HotXLS skriver om formelreferenser när rader och kolumner flyttas, men en villkorsformatering eller datavalideringsregel är inte bara en formel som sitter i en cell. Den parar ihop en formel med ett intervall, sqref i ECMA-376-termer, och de två måste flyttas tillsammans. När en strukturell redigering skär det intervallet i två delar som skulle behöva två olika relativa offset för att förbli korrekta, slutar det att vara ett alternativ att behålla ett regelobjekt med en formelsträng, och att låtsas något annat är hur en markeringsregel tyst börjar jämföra fel rader
Varför delar en radinfogning upp en villkorsformateringsregel i stället för att bara flytta den?
En villkorsformaterings- eller datavalideringsregel håller exakt en formel för hela sitt intervall, utvärderad relativt en enda ankarcell, så så fort en redigering tvingar två delar av det intervallet att behöva två olika relativa offset kan en formel inte längre beskriva båda delarna korrekt. ECMA-376 uttrycker en regels täckning som sqref-attributet på conditionalFormatting- eller dataValidation-elementet, och Excel utvärderar Formula1 och Formula2 som om texten hade skrivits in i den övre vänstra cellen i den sqref och fyllts ut över resten av den, på samma sätt som en vanlig relativ formel fylls ner en kolumn. Föreställ dig en avvikelsemarkering över B2:B50 som flaggar varje faktisk siffra som överstiger sin budget, byggd som en cellIs-regel vars Formula1 är den bokstavliga texten C2, vilket betyder jämför den aktuella radens B-cell mot samma rads C-cell
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 25Infoga den enda avdelarraden vid gamla rad 25 och raderna ovanför infogningspunkten flyttar sig inte, så deras andel av regeln läser fortfarande Formula1 som C2 korrekt. Raderna som brukade vara 25 till 50 glider ner till 26 till 51, och för dem är C2 nu helt fel cell, eftersom rad 26 behöver jämföra mot C26, inte mot en budgetsiffra två dussin rader ovanför den
Hur HotXLS avgör om en regel behöver delas
HotXLS skapar bara extra regelobjekt när geometrin genuint kräver det: en intern rutin, XlsxBuildShiftedRuleParts, går igenom varje disjunkt område i regelns sqref, räknar ut vad det områdets ankarcell var före redigeringen och vad den blir efter, och kontrollerar om varje resulterande del skulle behöva samma relativa offset-korrigering. Om alla delarna är överens överlever en regel, dess sqref ombyggd som unionen av de skiftade delarna och dess formel omankrad en gång. En genuin delning sker bara när delarna är oense, exakt B2:B50-fallet ovan, där det övre blocket behåller sitt ursprungliga ankare och det nedre blocket behöver ett nytt
Att omankra en dels formel är ett tvåstegsflytt som återanvänder maskineri HotXLS redan bär för OOXML-delade formelgrupper: först översätts formeln som om den ursprungligen hade ankrats vid den delens egen övre vänstra cell, med samma relativa-offset-matematik som expanderar en delad formel över dess intervall, sedan går resultatet genom samma rad-och-kolumn-skiftskanner som skriver om vanliga kalkylbladsformler. Det är hur Formula1 går från C2 till C26 i två steg snarare än ett handskrivet specialfall: översätt C2 framåt 23 rader för att få C25, som om regeln alltid hade börjat där, och låt sedan det vanliga skiftet vid rad 25 knuffa den vidare till C26. Varje annan egenskap, fyllfärg, stopp-om-sant, själva operatorn, följer med oförändrad till det nya regelobjektet, så båda hälfterna fortsätter måla celler i samma färg de alltid gjorde
// ConditionalFormats now holds two rules instead of one:
// B2:B25 Formula1 = 'C2' (rows above the insert)
// B26:B51 Formula1 = 'C26' (rows that shifted down)Delas databaler och ikonuppsättningar på samma sätt som cellIs-regler?
Nej: HotXLS partitionerar bara de regeltyper vars korrekthet faktiskt beror på en relativ formel per region, cellIs-jämförelser och uttrycksregler, och lämnar varje annan villkorsformateringstyp som ett enda regelobjekt vars sqref helt enkelt växer för att täcka de skiftade delarna som en flerområdesunion. Internt är grenen en enkel Kind-kontroll, cf.Kind in [cfkCellIs, cfkExpression], inget mer exotiskt än så. Databaler, två- och trefärgsskalor, ikonuppsättningar, topp- och bottenrankningar, och detektorerna för dubbletter, tomma celler och fel bär en nyttolast, en balkfärg, en uppsättning skalstopp, en ikonfamilj, som beskriver hela det täckta intervallet på en gång snarare än en relativ jämförelse per cell, så att dela upp dem i flera prioriterade regelobjekt skulle inte köpa någon korrekthet och skulle bara lägga till regler att hantera. När en redigering delar deras intervall kombinerar HotXLS om delarna till en regel med en flerområdes-sqref och omankrar nyttolasten som en enda enhet i stället för att klona ett nytt regelobjekt per del. Distinktionen stämmer överens med regeltypstaxonomin i artikeln om grunderna i villkorsformatering och rich text: databaler, färgskalor, och ikonuppsättningar skiljer sig redan från cellIs-regler genom att helt ignorera Style-egenskapen, och nu visar det sig att de skiljer sig från per-region-omankring av samma underliggande skäl
Varför ändras regelprioriteter efter en strukturell redigering?
Prioriteter ändras eftersom varje klon börjar med att hålla exakt samma prioritetsvärde som regeln den delades från, och HotXLS kör en normaliseringspassage efteråt som löser de resulterande dubbletterna till en ren, gapfri ordning i stället för att lämna två regler bundna på samma rang. En andra intern rutin, XlsxNormalizeConditionalFormatPriorities, tar varje villkorsformats aktuella prioritet, faller tillbaka till den regelns position i samlingen för varje regel som aldrig fick en satt explicit, sorterar hela listan stabilt så att oavgjorda situationer behåller sin ursprungliga relativa ordning, och omnumrerar det sorterade resultatet till en tät 1, 2, 3-sekvens utan luckor och utan upprepningar. HotXLS kör den en gång innan ett skift börjar, så klonandet startar från en ren baslinje, och igen efter varje delning och varje tömd regel tas bort, så filen som sparas aldrig har två regelposter som gör anspråk på samma prioritet. Det spelar roll om du följde rådet i artikeln om grunderna i villkorsformatering att lämna luckor mellan prioritetsvärden så att en senare regel kan smygas in utan att omnumrera resten: luckorna överlever tills nästa rad- eller kolumnredigering rör det kalkylbladet, och kollapsar sedan, eftersom normalisering bara garanterar unikhet och stabil ordning, inte att ditt ursprungliga numreringssystem kommer tillbaka oförändrat
Datavalideringsregler delas också, utan en prioritet att omnumrera
Datavalideringsregler går genom samma intervallpartitioneringslogik som cellIs- och uttrycksvillkorsformat, och till skillnad från villkorsformatering tar varje valideringstyp den vägen enhetligt: HotXLS har ingen separat icke-formel-familj för datavalidering på det sätt databaler och ikonuppsättningar är för villkorsformatering, så en enkel lista- eller heltalsregel partitioneras av samma rutin som hanterar en relativ anpassad formel. Vad som skiljer sig är prioritet: ECMA-376 ger dataValidation-elementet inget priority-attribut alls, så det finns inget omnumreringssteg för valideringar på det sätt det finns för villkorsformat. Föreställ dig en anpassad-formel-validering som håller varje rads faktiska belopp från att överstiga sin egen budget i kolumnen bredvid
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)Detta spelar roll av samma anledning som artikeln om grunderna i datavalidering varnar mot att fästa en regel innan radantalet är slutgiltigt: en validering täcker bara de bokstavliga celler du gav den, och en senare strukturell redigering kan lämna två eller fler regler som gör jobbet en brukade göra. Inget går sönder funktionellt: varje cell i det ursprungliga intervallet valideras fortfarande av något, men kod som antar en DataValidations-post per kolumn kommer att börja indexera fel efter att den första redigeringen rör den. Det finns ett hårt tak för hur långt det här kan gå: om delning skulle knuffa ett kalkylblad förbi 65 534 datavalideringsregler kastar HotXLS ett undantag i stället för att skriva en fil Excel tyst skulle avvisa, vilket är biblioteket som avstår från att tillverka en korrupt arbetsbok snarare än en gräns vanlig användning sannolikt når
Vad man ska kontrollera efter en massinfogning eller -borttagning
De två sakerna värda att verifiera efter att ett skript kört en batch av rad- eller kolumnredigeringar över ett blad fullt av villkorsformat och valideringar är det totala regelantalet och prioritetsordningen, eftersom båda kan glida iväg på sätt som är lätta att missa i kodgranskning och uppenbara i samma stund någon öppnar Hantera regler i Excel. En enskild redigering gör sällan mycket skada: en enda infogning mitt i en cellIs-regel producerar högst två regelobjekt där det fanns ett. Risken förvärras när en rapportgenereringsrutin infogar rader en i taget i en loop över ett blad som redan bär flera formelankrade regler: varje passage kan dela regler en tidigare passage redan delade, och fem ursprungliga cellIs-regler kan sluta som flera gånger så många lågvärdiga fragment som täcker flisor av det ursprungliga intervallet. Att batcha strukturella redigeringar, infoga hela det nya blocket i ett anrop i stället för en rad i taget, håller regelantalet knutet till antalet genuint distinkta ankare snarare än antalet utförda redigeringar
Regelpartitionering och prioritetsnormalisering levereras som standardbeteende i XLSX-motorn i HotXLS Delphi Excel-komponenten för Delphi och C++Builder; produktsidan bär den fullständiga referensen för kalkylbladsredigerings-API:et, inklusive villkorsformaterings- och datavalideringsmetoderna som beskrivs här