Partiționarea formatărilor condiționate ancorate în HotXLS

HotXLS, componenta Excel pentru Delphi și C++Builder, împarte automat o regulă de formatare condiționată sau de validare a datelor în două sau mai multe obiecte de regulă separate ori de câte ori o inserare sau ștergere de rând sau coloană taie intervalul acoperit de regulă în bucăți care au nevoie de ancore de formulă relative diferite, apoi reatribuie fiecărei reguli de formatare condiționată un număr de prioritate nou și unic. Acest comportament a fost livrat în versiunea 2.196 a motorului XLSX și rulează automat, fără nicio setare de dezactivare. Declanșatorul este restrâns dar comun: o regulă cellIs sau expression a cărei formulă citește o celulă relativ la propriul interval, aflată pe o foaie de calcul căreia i se inserează sau i se elimină mai târziu un rând undeva la mijlocul acelui interval exact

Majoritatea prezentărilor automatizării Excel se opresc la problema textului formulei: deplasarea numerelor de rânduri și coloane în interiorul fiecărui SUM() și al fiecărui VLOOKUP(), astfel încât referințele să tot indice spre celulele corecte. Aceea este jumătate din poveste, reală, și este acoperită în articolul complementar despre cum rescrie HotXLS referințele de formule când rândurile și coloanele se mută, dar o formatare condiționată sau o regulă de validare a datelor nu este doar o formulă așezată într-o celulă. Ea asociază o formulă cu un interval, sqref în termenii ECMA-376, iar cele două trebuie să se miște împreună. Când o editare structurală taie acel interval în două bucăți care ar avea nevoie de două decalaje relative diferite pentru a rămâne corecte, păstrarea unui singur obiect de regulă cu un singur șir de formulă încetează să mai fie o opțiune, iar a te preface altfel este modul în care o regulă de evidențiere ajunge silențios să compare rândurile greșite

De ce inserarea unui rând împarte o regulă de formatare condiționată în loc să o mute pur și simplu?

O regulă de formatare condiționată sau de validare a datelor păstrează exact o formulă pentru întregul său interval, evaluată relativ la o singură celulă de ancorare, așa că odată ce o editare forțează două părți ale acelui interval să aibă nevoie de două decalaje relative diferite, o singură formulă nu mai poate descrie corect ambele părți. ECMA-376 exprimă acoperirea unei reguli ca atributul sqref pe elementul conditionalFormatting sau dataValidation, iar Excel evaluează Formula1 și Formula2 ca și cum textul ar fi fost tastat în celula din colțul stânga-sus al acelui sqref și umplut pe restul lui, la fel cum o formulă relativă obișnuită se umple în jos pe o coloană. Imaginați-vă o evidențiere de varianță peste B2:B50 care semnalează orice cifră reală care depășește bugetul său, construită ca o regulă cellIs a cărei Formula1 este textul literal C2, adică se compară celula B a rândului curent cu celula C a aceluiași rând

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 25

Inserați acel unic rând separator la vechiul rând 25, iar rândurile de deasupra punctului de inserare nu se mută, așa că partea lor din regulă tot citește corect Formula1 ca C2. Rândurile care erau anterior 25 până la 50 alunecă în jos la 26 până la 51, iar pentru ele C2 este acum complet celula greșită, întrucât rândul 26 trebuie să se compare cu C26, nu cu o cifră de buget cu două zeci de rânduri deasupra sa

Cum decide HotXLS dacă o regulă trebuie împărțită

HotXLS creează obiecte de regulă suplimentare doar atunci când geometria chiar o cere: o rutină internă, XlsxBuildShiftedRuleParts, parcurge fiecare zonă disjunctă din sqref-ul regulii, calculează care era celula de ancorare a acelei zone înainte de editare și ce devine după, și verifică dacă fiecare bucată rezultată ar avea nevoie de aceeași corecție de decalaj relativ. Dacă toate bucățile sunt de acord, o singură regulă supraviețuiește, cu sqref-ul său reconstruit ca uniunea bucăților deplasate și formula sa rebazată o singură dată. O împărțire propriu-zisă se întâmplă doar atunci când bucățile nu sunt de acord, exact cazul B2:B50 de mai sus, unde blocul de sus își păstrează ancorajul original, iar blocul de jos are nevoie de unul nou

Rebazarea formulei unei bucăți este o mișcare în doi pași care refolosește mecanismul pe care HotXLS îl poartă deja pentru grupurile de formule partajate OOXML: mai întâi formula este tradusă ca și cum ar fi fost inițial ancorată la propria celulă din colțul stânga-sus a acelei bucăți, folosind aceeași matematică de decalaj relativ care extinde o formulă partajată pe întregul ei interval, apoi rezultatul trece prin același scaner de deplasare de rânduri și coloane care rescrie formulele obișnuite ale foii de calcul. Așa ajunge Formula1 de la C2 la C26 în două mișcări, nu într-un caz special scris manual: se traduce C2 înainte cu 23 de rânduri pentru a obține C25, ca și cum regula ar fi început întotdeauna acolo, apoi deplasarea obișnuită de la rândul 25 o împinge mai departe la C26. Fiecare altă proprietate, culoarea de umplere, stop-if-true, operatorul însuși, se transferă neschimbată pe noul obiect de regulă, astfel încât ambele jumătăți continuă să vopsească celulele cu culoarea pe care au avut-o dintotdeauna

// ConditionalFormats now holds two rules instead of one:
//   B2:B25    Formula1 = 'C2'    (rows above the insert)
//   B26:B51   Formula1 = 'C26'   (rows that shifted down)

Se împart data bars și seturile de iconuri la fel ca regulile cellIs?

Nu: HotXLS partiționează doar tipurile de reguli a căror corectitudine chiar depinde de o formulă relativă per-regiune, comparațiile cellIs și regulile expression, și lasă fiecare alt tip de formatare condiționată ca un singur obiect de regulă al cărui sqref pur și simplu crește pentru a acoperi bucățile deplasate ca o uniune multi-zonă. Intern, ramura este o simplă verificare de Kind, cf.Kind in [cfkCellIs, cfkExpression], nimic mai exotic decât atât. Data bars, scalele cu două și trei culori, seturile de iconuri, clasamentele de sus și de jos, și detectoarele de duplicate, celule goale și erori poartă o încărcătură, o culoare de bară, un set de opriri de scală, o familie de iconuri, care descrie întregul interval acoperit deodată, nu o comparație relativă per-celulă, așa că împărțirea lor în mai multe obiecte de regulă prioritizate nu ar cumpăra nicio corectitudine și ar adăuga doar reguli de gestionat. Când o editare divizează intervalul lor, HotXLS recombină bucățile într-o singură regulă cu un sqref multi-zonă și reancorează încărcătura ca o singură unitate, în loc să cloneze un obiect de regulă nou per bucată. Distincția se aliniază cu taxonomia tipurilor de reguli din articolul de bază despre formatarea condiționată și text îmbogățit: data bars, scalele de culoare și seturile de iconuri deja se disting de regulile cellIs ignorând complet proprietatea Style, iar acum se dovedește că se disting și prin reancorarea per-regiune, din același motiv de bază

De ce se schimbă prioritățile regulilor după o editare structurală?

Prioritățile se schimbă pentru că fiecare clonă pornește păstrând exact aceeași valoare de prioritate ca regula din care s-a împărțit, iar HotXLS rulează o trecere de normalizare ulterior, care rezolvă duplicatele rezultate într-o ordonare curată, fără goluri, în loc să lase două reguli la egalitate pe același rang. O a doua rutină internă, XlsxNormalizeConditionalFormatPriorities, preia prioritatea curentă a fiecărei formatări condiționate, revine la poziția acelei reguli în colecție pentru orice regulă căreia nu i s-a setat vreodată una explicit, sortează întreaga listă stabil, astfel încât egalitățile își păstrează ordinea relativă originală, și renumerotează rezultatul sortat la o secvență densă 1, 2, 3, fără goluri și fără repetări. HotXLS o rulează o dată înainte de a începe o deplasare, astfel încât clonarea pornește de la o bază curată, și din nou după fiecare împărțire, iar fiecare regulă golită este eliminată, astfel încât fișierul salvat nu are niciodată două intrări de regulă care revendică aceeași prioritate. Asta contează dacă ați urmat sfatul din articolul de bază despre formatarea condiționată de a lăsa goluri între valorile de prioritate, astfel încât o regulă ulterioară să se poată introduce fără a renumerota restul: golurile supraviețuiesc până când următoarea editare de rând sau coloană atinge acea foaie de calcul, apoi se prăbușesc, pentru că normalizarea garantează doar unicitatea și ordinea stabilă, nu că schema dvs. originală de numerotare revine neschimbată

Regulile de validare a datelor se împart și ele, fără o prioritate de renumerotat

Regulile de validare a datelor trec prin aceeași logică de partiționare a intervalului ca regulile cellIs și expression de formatare condiționată, iar spre deosebire de formatarea condiționată, fiecare tip de validare urmează acea cale uniform: HotXLS nu are o familie separată non-formulă pentru validarea datelor așa cum sunt data bars și seturile de iconuri pentru formatarea condiționată, așa că o regulă simplă de listă sau de număr întreg este partiționată de aceeași rutină identică care gestionează o formulă personalizată relativă. Ce diferă este prioritatea: ECMA-376 nu dă elementului dataValidation niciun atribut priority deloc, așa că nu există niciun pas de renumerotare pentru validări așa cum există pentru formatările condiționate. Imaginați-vă o validare cu formulă personalizată care păstrează suma reală a fiecărui rând de la a depăși propriul buget din coloana alăturată

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)

Asta contează din același motiv pentru care articolul de bază despre validarea datelor avertizează împotriva atașării unei reguli înainte ca numărul de rânduri să fie final: o validare acoperă doar celulele literale pe care i le-ați dat, iar o editare structurală ulterioară poate lăsa două sau mai multe reguli făcând treaba pe care obișnuia să o facă una singură. Nimic nu se rupe funcțional: fiecare celulă din intervalul original este încă validată de ceva, dar codul care presupune o singură intrare DataValidations per coloană va începe să indexeze greșit după prima editare care o atinge. Există un plafon dur cât de departe poate merge asta: dacă împărțirea ar împinge o foaie de calcul peste 65.534 de reguli de validare a datelor, HotXLS ridică o excepție, în loc să scrie un fișier pe care Excel l-ar respinge silențios, ceea ce este biblioteca refuzând să fabrice un registru de lucru corupt, nu o limită pe care utilizarea obișnuită are șanse să o atingă

Ce merită verificat după o inserare sau ștergere în masă

Cele două lucruri care merită verificate după ce un script rulează un lot de editări de rânduri sau coloane peste o foaie plină de formatări condiționate și validări sunt numărul total de reguli și ordinea de prioritate, întrucât ambele pot deriva în moduri ușor de ratat la revizuirea codului și evidente în clipa în care cineva deschide Manage Rules în Excel. O singură editare rareori face mult rău: o singură inserare la mijlocul unei reguli cellIs produce cel mult două obiecte de regulă unde era una. Riscul se compune atunci când o rutină de generare de rapoarte inserează rânduri unul câte unul într-o buclă peste o foaie care poartă deja mai multe reguli ancorate de formule: fiecare trecere poate re-împărți reguli pe care o trecere anterioară le-a împărțit deja, iar cinci reguli cellIs originale pot ajunge de mai multe ori acel număr de fragmente cu valoare redusă, acoperind fâșii din intervalul original. Gruparea editărilor structurale, inserarea întregului bloc nou într-un singur apel în loc de câte un rând odată, păstrează numărul de reguli legat de numărul de ancoraje cu adevărat distincte, nu de numărul de editări efectuate

Partiționarea regulilor și normalizarea priorității sunt livrate ca un comportament standard al motorului XLSX din componenta Excel HotXLS pentru Delphi pentru Delphi și C++Builder; pagina de produs conține referința completă a API-ului de editare a foilor de calcul, inclusiv metodele de formatare condiționată și validare a datelor descrise aici