HotXLS, Excel-komponenten for Delphi og C++Builder, deler automatisk en betinget formaterings- eller datavalideringsregel inn i to eller flere separate regelobjekter når en rad- eller kolonne-innsetting eller -sletting kutter regelens dekkede område i biter som trenger forskjellige relative formelankere, og tildeler deretter hver betinget-format-regel et ferskt, unikt prioritetsnummer på nytt. Oppførselen ble levert i versjon 2.196 av XLSX-motoren og kjører automatisk, uten noen innstilling å skru av. Utløseren er smal, men vanlig: en cellIs- eller uttrykksregel hvis formel leser en celle relativt til sitt eget område, som bor på et regneark som senere får en rad satt inn eller fjernet et sted midt i nettopp det området
De fleste omtaler av Excel-automatisering stopper ved formel-tekst-problemet: forskyv rad- og kolonnenumrene inne i hver SUM() og hver VLOOKUP() slik at referansene fortsatt peker på riktige celler. Den halvparten av historien er ekte, og den er dekket i følgeartikkelen om hvordan HotXLS skriver om formelreferanser når rader og kolonner flyttes, men et betinget format eller en datavalideringsregel er ikke bare en formel som sitter i en celle. Den parer en formel med et område, sqref i ECMA-376-termer, og de to må flytte seg sammen. Når en strukturell endring skjærer det området i to biter som ville trenge to forskjellige relative forskyvninger for å forbli korrekte, slutter det å være et alternativ å beholde ett regelobjekt med én formelstreng, og å late som noe annet er hvordan en highlight-regel stille begynner å sammenligne feil rader
Hvorfor deler innsetting av en rad en betinget formateringsregel i stedet for bare å flytte den?
Et betinget format eller en datavalideringsregel beholder nøyaktig én formel for hele området sitt, evaluert relativt til én enkelt ankercelle, så når en redigering tvinger to deler av det området til å trenge to forskjellige relative forskyvninger, kan ikke lenger én formel beskrive begge delene korrekt. ECMA-376 uttrykker en regels dekning som sqref-attributtet på conditionalFormatting- eller dataValidation-elementet, og Excel evaluerer Formula1 og Formula2 som om teksten hadde blitt tastet inn i den øvre-venstre cellen til den sqref-en og fylt over resten av den, på samme måte som en vanlig relativ formel fyller ned en kolonne. Se for deg en avviks-highlight over B2:B50 som flagger ethvert faktisk tall som overstiger budsjettet sitt, bygget som en cellIs-regel hvis Formula1 er den bokstavelige teksten C2, som betyr å sammenligne gjeldende rads B-celle mot den samme radens C-celle
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 25Sett inn den ene separatorraden ved gamle rad 25, og radene over innsettingspunktet flytter seg ikke, slik at deres andel av regelen fortsatt leser Formula1 som C2 korrekt. Radene som pleide å være 25 til 50, glir ned til 26 til 51, og for dem er C2 nå helt feil celle, ettersom rad 26 trenger å sammenligne mot C26, ikke mot et budsjettall to dusin rader over den
Hvordan HotXLS avgjør om en regel trenger å deles
HotXLS oppretter bare ekstra regelobjekter når geometrien genuint krever det: en intern rutine, XlsxBuildShiftedRuleParts, går gjennom hvert usammenhengende område i regelens sqref, finner ut hva det området sin ankercelle var før redigeringen og hva den blir etter, og sjekker om hver resulterende bit ville trenge den samme relative-forskyvnings-korreksjonen. Hvis alle bitene er enige, overlever én regel, med sin sqref gjenoppbygd som unionen av de forskjøvne bitene og formelen sin rebasert én gang. En genuin deling skjer bare når bitene er uenige, nøyaktig B2:B50-tilfellet ovenfor, der den øvre blokken beholder sitt opprinnelige anker og den nedre blokken trenger et nytt
Å rebasere en bits formel er et to-trinns grep som gjenbruker maskineri HotXLS allerede bærer for delte OOXML-formelgrupper: først oversettes formelen som om den opprinnelig hadde blitt forankret ved den bitens egen øvre-venstre celle, ved bruk av den samme relative-forskyvnings-matematikken som utvider en delt formel over sitt område, deretter går resultatet gjennom den samme rad-og-kolonne-forskyvningsskanneren som skriver om vanlige regnearkformler. Det er slik Formula1 går fra C2 til C26 i to grep i stedet for ett håndskrevet spesialtilfelle: oversett C2 fremover med 23 rader for å få C25, som om regelen alltid hadde startet der, og la deretter den vanlige forskyvningen ved rad 25 skyve den videre til C26. Hver annen egenskap, fyllfarge, stopp-hvis-sann, selve operatoren, blir med uendret over på det nye regelobjektet, slik at begge halvdelene fortsetter å male celler den fargen de alltid har gjort
// ConditionalFormats now holds two rules instead of one:
// B2:B25 Formula1 = 'C2' (rows above the insert)
// B26:B51 Formula1 = 'C26' (rows that shifted down)Deler datalinjer og ikonsett seg på samme måte som cellIs-regler?
Nei: HotXLS partisjonerer bare de regeltypene hvis korrekthet faktisk avhenger av en per-region relativ formel, cellIs-sammenligninger og uttrykksregler, og lar hver annen betinget-format-type være ett enkelt regelobjekt hvis sqref ganske enkelt vokser til å dekke de forskjøvne bitene som en multi-område-union. Internt er grenen en enkel Kind-sjekk, cf.Kind in [cfkCellIs, cfkExpression], ikke noe mer eksotisk enn det. Datalinjer, to- og tre-farge-skalaer, ikonsett, topp- og bunn-rangeringer, og duplikat-, blank- og feildetektorene bærer en payload, en linjefarge, et sett med skalastopp, en ikonfamilie, som beskriver hele det dekkede området på én gang i stedet for en per-celle relativ sammenligning, så å dele dem inn i flere prioriterte regelobjekter ville ikke kjøpt noen korrekthet og ville bare lagt til regler å administrere. Når en redigering deler området deres, kombinerer HotXLS bitene tilbake til én regel med en multi-område-sqref og re-forankrer payloaden som én enkelt enhet i stedet for å klone et nytt regelobjekt per bit. Skillet stemmer overens med regeltype-taksonomien i artikkelen om grunnleggende betinget formatering og rik tekst: datalinjer, fargeskalaer, og ikonsett skiller seg allerede fra cellIs-regler ved å ignorere Style-egenskapen fullstendig, og nå viser det seg at de skiller seg fra per-region-re-forankring av den samme underliggende grunnen
Hvorfor endrer regelprioriteter seg etter en strukturell redigering?
Prioriteter endrer seg fordi hver klone starter med å holde nøyaktig den samme prioritetsverdien som regelen den ble delt fra, og HotXLS kjører en normaliseringspassering etterpå som løser de resulterende duplikatene til en ren, uten-hull-ordning i stedet for å la to regler stå likt om samme rangering. En andre intern rutine, XlsxNormalizeConditionalFormatPriorities, tar hver betinget formats gjeldende prioritet, faller tilbake til den regelens posisjon i samlingen for enhver regel som aldri fikk en satt eksplisitt, sorterer hele listen stabilt slik at uavgjorte beholder sin opprinnelige relative rekkefølge, og nummererer den sorterte listen på nytt til en tett 1, 2, 3-sekvens uten hull og uten repetisjoner. HotXLS kjører den én gang før en forskyvning begynner, slik at kloningen starter fra en ren basislinje, og igjen etter hver deling og hver tomkjørt regel fjernes, slik at filen som lagres, aldri har to regeloppføringer som hevder samme prioritet. Det betyr noe hvis man fulgte rådet i artikkelen om grunnleggende betinget formatering om å legge igjen hull mellom prioritetsverdier slik at en senere regel kan smettes inn uten å nummerere resten på nytt: hullene overlever inntil neste rad- eller kolonneredigering rører det regnearket, og kollapser deretter, fordi normalisering bare garanterer unikhet og stabil rekkefølge, ikke at ditt opprinnelige nummereringsskjema kommer tilbake uendret
Datavalideringsregler deler seg også, uten en prioritet å nummerere på nytt
Datavalideringsregler går gjennom den samme område-partisjoneringslogikken som cellIs- og uttrykks-betingede-formater, og i motsetning til betinget formatering tar hver valideringstype den veien ensartet: HotXLS har ingen separat ikke-formel-familie for datavalidering slik datalinjer og ikonsett er for betinget formatering, så en enkel liste- eller heltallsregel partisjoneres av den identiske rutinen som håndterer en relativ tilpasset formel. Det som skiller seg, er prioritet: ECMA-376 gir dataValidation-elementet ingen priority-attributt i det hele tatt, så det finnes intet nummererings-på-nytt-trinn for valideringer slik det gjør for betingede formater. Se for deg en tilpasset-formel-validering som holder hver rads faktiske beløp fra å overstige sitt eget budsjett i kolonnen ved siden av
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)Dette betyr noe av samme grunn som artikkelen om grunnleggende datavalidering advarer mot å feste en regel før radantallet er endelig: en validering dekker bare de bokstavelige cellene du ga den, og en senere strukturell redigering kan etterlate to eller flere regler som gjør jobben én pleide å gjøre. Ingenting brekker funksjonelt: hver celle i det opprinnelige området er fortsatt validert av noe, men kode som antar én DataValidations-oppføring per kolonne, vil begynne å feilindeksere etter at den første redigeringen rører den. Det finnes et hardt tak på hvor langt dette kan gå: hvis deling ville presse et regneark forbi 65 534 datavalideringsregler, kaster HotXLS et unntak i stedet for å skrive en fil Excel stille ville avvise, noe som er biblioteket som avstår fra å produsere en korrupt arbeidsbok, ikke en grense vanlig bruk sannsynligvis vil nå
Hva man bør sjekke etter en bulk-innsetting eller -sletting
De to tingene verdt å verifisere etter at et skript kjører en batch med rad- eller kolonneredigeringer over et ark fullt av betingede formater og valideringer, er det totale regelantallet og prioritetsrekkefølgen, ettersom begge kan drifte på måter som er lette å overse i kodegjennomgang og åpenbare i det øyeblikket noen åpner Administrer regler i Excel. Én redigering gjør sjelden mye skade: én enkelt innsetting midt i én cellIs-regel produserer høyst to regelobjekter der det var ett. Risikoen forsterkes når en rapportgenereringsrutine setter inn rader én om gangen i en løkke over et ark som allerede bærer flere formel-forankrede regler: hver passering kan dele regler en tidligere passering allerede delte, og fem opprinnelige cellIs-regler kan ende opp som flere ganger så mange lavverdi-fragmenter som dekker fliker av det opprinnelige området. Å batche strukturelle redigeringer, å sette inn hele den nye blokken i ett kall i stedet for én rad om gangen, holder regelantallet knyttet til antallet genuint distinkte ankere i stedet for antallet redigeringer utført
Regelpartisjonering og prioritetsnormalisering leveres som standard oppførsel av XLSX-motoren i HotXLS Delphi Excel-komponenten for Delphi og C++Builder; produktsiden bærer den fulle regneark-redigerings-API-referansen, inkludert de betinget-formaterings- og datavaliderings-metodene beskrevet her