HotXLS, Delphi- og C++Builder-Excel-komponenten, opdeler automatisk en betinget formaterings- eller datavalideringsregel i to eller flere separate regelobjekter, hver gang en indsættelse eller sletning af rækker eller kolonner skærer reglens dækkede interval i stykker, der har brug for forskellige relative formelankre, og genfordeler derefter hver betinget-format-regel et frisk, unikt prioritetsnummer. Denne opførsel blev sendt i version 2.196 af XLSX-motoren og kører automatisk, uden nogen indstilling at fravælge. Udløseren er snæver, men almindelig: en cellIs- eller udtryksregel, hvis formel læser en celle relativt til sit eget interval, boende på et regneark, der senere får indsat eller fjernet en række et sted midt i netop det interval
De fleste gennemgange af Excel-automatisering stopper ved formel-tekst-problemet: forskyd række- og kolonnetallene inde i hver SUM() og hver VLOOKUP(), så referencerne stadig peger på de rigtige celler. Den halvdel af historien er rigtig og dækkes i følgeartiklen om, hvordan HotXLS omskriver formelreferencer, når rækker og kolonner flytter sig, men en betinget format- eller datavalideringsregel er ikke bare en formel, der sidder i en celle. Den parrer en formel med et interval, sqref i ECMA-376-termer, og de to skal flytte sig sammen. Når en strukturel redigering skærer det interval i to stykker, der ville have brug for to forskellige relative forskydninger for at forblive korrekte, holder op med at være en mulighed at beholde ét regelobjekt med én formel-streng, og at lade som ingenting er, hvordan en highlight-regel i stilhed begynder at sammenligne de forkerte rækker
Hvorfor deler indsættelse af en række en betinget formateringsregel i stedet for bare at flytte den?
Et betinget format eller en datavalideringsregel holder præcis én formel for hele sit interval, evalueret relativt til én ankercelle, så når en redigering først tvinger to dele af det interval til at have brug for to forskellige relative forskydninger, kan én formel ikke længere beskrive begge dele korrekt. ECMA-376 udtrykker en regels dækning som sqref-attributten på conditionalFormatting- eller dataValidation-elementet, og Excel evaluerer Formula1 og Formula2, som om teksten var blevet skrevet ind i den øverste venstre celle af det sqref og fyldt på tværs af resten af det, på samme måde som en almindelig relativ formel fyldes ned gennem en kolonne. Forestil dig en varians-highlight over B2:B50, der flager enhver faktisk figur, der overstiger sit budget, bygget som en cellIs-regel, hvis Formula1 er den bogstavelige tekst C2, hvilket betyder sammenlign den aktuelle rækkes B-celle med den samme rækkes 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 25Indsæt den ene adskillelsesrække ved gammel række 25, og rækkerne over indsættelsespunktet flytter sig ikke, så deres andel af reglen læser stadig Formula1 som C2 korrekt. Rækkerne, der plejede at være 25 til 50, glider ned til 26 til 51, og for dem er C2 nu den helt forkerte celle, da række 26 har brug for at sammenligne mod C26, ikke mod en budgetfigur to dusin rækker ovenfor
Hvordan afgør HotXLS, om en regel har brug for at blive delt?
HotXLS opretter kun ekstra regelobjekter, når geometrien reelt kræver det: en intern rutine, XlsxBuildShiftedRuleParts, gennemgår hvert adskilt område i reglens sqref, finder ud af, hvad det områdes ankercelle var før redigeringen, og hvad den bliver bagefter, og tjekker om hvert resulterende stykke ville have brug for den samme relative-forskydnings-korrektion. Hvis alle stykkerne er enige, overlever én regel, dens sqref genopbygget som unionen af de forskudte stykker og dens formel rebaseret én gang. En ægte deling sker kun, når stykkerne er uenige, netop B2:B50-tilfældet ovenfor, hvor topblokken beholder sit oprindelige anker, og bundblokken har brug for et nyt et
At rebasere et stykkes formel er et to-trins træk, der genbruger maskineri HotXLS allerede bærer til OOXML delte formel-grupper: først oversættes formlen, som om den oprindeligt havde været forankret ved det stykkes egen øverste venstre celle, ved brug af den samme relative-forskydnings-matematik, der udvider en delt formel på tværs af dens interval, derefter kører resultatet gennem den samme række-og-kolonne-forskydnings-skanner, der omskriver almindelige regneark-formler. Sådan går Formula1 fra C2 til C26 i to træk frem for ét håndskrevet specialtilfælde: oversæt C2 fremad med 23 rækker for at få C25, som om reglen altid havde startet der, og lad derefter den almindelige forskydning ved række 25 skubbe den videre til C26. Hver anden egenskab, fyldfarve, stop-hvis-sand, selve operatoren, følger uændret med til det nye regelobjekt, så begge halvdele bliver ved med at male celler den farve, de altid 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)Deler databjælker og ikonsæt sig på samme måde som cellIs-regler?
Nej: HotXLS opdeler kun de regeltyper, hvis korrekthed rent faktisk afhænger af en per-region relativ formel, cellIs-sammenligninger og udtryksregler, og lader hver anden betinget-format-type forblive som ét regelobjekt, hvis sqref simpelthen vokser til at dække de forskudte stykker som en multi-område-union. Internt er grenen et almindeligt Kind-tjek, cf.Kind in [cfkCellIs, cfkExpression], intet mere eksotisk end det. Databjælker, to- og tre-farve-skalaer, ikonsæt, top- og bund-rangeringer, samt dublet-, blank- og fejl-detektorerne bærer en payload, en bjælkefarve, et sæt skala-stop, en ikonfamilie, der beskriver hele det dækkede interval på én gang frem for en per-celle relativ sammenligning, så at dele dem op i flere prioriterede regelobjekter ville ikke købe nogen korrekthed og ville kun tilføje regler at administrere. Når en redigering deler deres interval, rekombinerer HotXLS stykkerne til én regel med en multi-område-sqref og genforankrer payloaden som én enhed i stedet for at klone et nyt regelobjekt pr. stykke. Skellet stemmer overens med regeltype-taksonomien i artiklen om grundlæggende betinget formatering og rig tekst: databjælker, farveskalaer og ikonsæt skiller sig allerede ud fra cellIs-regler ved helt at ignorere Style-egenskaben, og det viser sig nu, at de skiller sig ud fra per-region-genforankring af den samme underliggende årsag
Hvorfor ændres regelprioriteter efter en strukturel redigering?
Prioriteter ændres, fordi hver klon starter med at holde nøjagtig samme prioritetsværdi som den regel, den blev delt fra, og HotXLS kører en normaliseringsgennemgang bagefter, der løser de resulterende dubletter i en ren, hulfri rækkefølge frem for at lade to regler dele samme rang. En anden intern rutine, XlsxNormalizeConditionalFormatPriorities, tager hver betinget formats aktuelle prioritet, falder tilbage til den regels position i samlingen for enhver regel, der aldrig fik en sat eksplicit, sorterer hele listen stabilt, så uafgjorte beholder deres oprindelige relative rækkefølge, og omnummererer det sorterede resultat til en tæt 1, 2, 3-sekvens uden huller og uden gentagelser. HotXLS kører den én gang, før en forskydning begynder, så kloning starter fra en ren baseline, og igen efter hver deling og hver tømt regel er fjernet, så filen, der gemmes, aldrig har to regelposter, der gør krav på samme prioritet. Det betyder noget, hvis man fulgte rådet i grundlæggende-betinget-formatering-artiklen om at efterlade huller mellem prioritetsværdier, så en senere regel kan sætte sig ind uden at omnummerere resten: hullerne overlever, indtil den næste række- eller kolonneredigering rører det regneark, og kollapser derefter, fordi normalisering kun garanterer unikhed og stabil rækkefølge, ikke at ens oprindelige nummereringsskema kommer uændret tilbage
Datavalideringsregler deler sig også, uden en prioritet at omnummerere
Datavalideringsregler går gennem den samme interval-partitionerings-logik som cellIs- og udtryks-betingede-formater, og i modsætning til betinget formatering tager hver valideringstype den vej ensartet: HotXLS har ingen separat ikke-formel-familie for datavalidering på den måde, databjælker og ikonsæt er for betinget formatering, så en almindelig liste- eller heltalsregel opdeles af den identiske rutine, der håndterer en relativ brugerdefineret formel. Hvad der er forskelligt, er prioritet: ECMA-376 giver dataValidation-elementet slet ingen priority-attribut, så der er intet omnummererings-trin for valideringer på den måde, der er for betingede formater. Forestil dig en brugerdefineret-formel-validering, der holder hver rækkes faktiske beløb fra at overstige sit eget budget i kolonnen ved siden af
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 betyder noget af samme grund, som artiklen om grundlæggende datavalidering advarer mod at knytte en regel før rækkeantallet er endeligt: en validering dækker kun de bogstavelige celler, man gav den, og en senere strukturel redigering kan efterlade to eller flere regler til at gøre det arbejde, én plejede at gøre. Intet går funktionelt i stykker: hver celle i det oprindelige interval er stadig valideret af noget, men kode der antager én DataValidations-post pr. kolonne, vil begynde at fejl-indeksere efter den første redigering rører den. Der er et hårdt loft på, hvor langt dette kan gå: hvis deling ville skubbe et regneark forbi 65.534 datavaliderings-regler, kaster HotXLS en undtagelse frem for at skrive en fil, Excel ville afvise i stilhed, hvilket er biblioteket der afslår at fremstille en korrupt projektmappe frem for en grænse almindelig brug sandsynligvis vil nå
Hvad man skal tjekke efter en masse-indsættelse eller -sletning
De to ting, der er værd at verificere, efter et script kører en batch af række- eller kolonneredigeringer over et ark fuldt af betingede formater og valideringer, er det totale regelantal og prioritetsrækkefølgen, da begge kan drifte på måder, der er lette at overse i kodegennemgang og indlysende, i det øjeblik nogen åbner Administrer Regler i Excel. Én redigering gør sjældent megen skade: en enkelt indsættelse midt i én cellIs-regel producerer højst to regelobjekter, hvor der var én. Risikoen forstærkes, når en rapportgenererings-rutine indsætter rækker én ad gangen i en løkke over et ark, der allerede bærer flere formel-forankrede regler: hvert gennemløb kan gen-dele regler, et tidligere gennemløb allerede delte, og fem oprindelige cellIs-regler kan ende som flere gange så mange lavværdi-fragmenter, der dækker flager af det oprindelige interval. At samle strukturelle redigeringer i batches, at indsætte hele den nye blok i ét kald i stedet for én række ad gangen, holder regelantallet knyttet til antallet af genuint distinkte ankre frem for antallet af udførte redigeringer
Regel-partitionering og prioritets-normalisering leveres som standardopførsel af XLSX-motoren i HotXLS Delphi Excel-komponenten til Delphi og C++Builder; produktsiden bærer den fulde regneark-redigerings-API-reference, inklusive de betinget-formaterings- og datavaliderings-metoder beskrevet her