HotXLS, il componente Excel per Delphi e C++Builder, suddivide automaticamente una regola di formattazione condizionale o di validazione dati in due o più oggetti regola separati ogni volta che un inserimento o un'eliminazione di riga o colonna taglia l'intervallo coperto dalla regola in porzioni che richiedono ancore di formula relative diverse, quindi riassegna a ogni regola di formato condizionale un nuovo numero di priorità univoco. Questo comportamento è stato introdotto nella versione 2.196 del motore XLSX e viene eseguito automaticamente, senza alcuna impostazione per disattivarlo. Il fattore scatenante è circoscritto ma comune: una regola cellIs o expression la cui formula legge una cella relativa al proprio intervallo, su un foglio di lavoro in cui successivamente viene inserita o rimossa una riga da qualche parte nel mezzo di quell'esatto intervallo
La maggior parte delle trattazioni sull'automazione di Excel si ferma al problema del testo delle formule: spostare i numeri di riga e colonna dentro ogni SUM() e ogni VLOOKUP() cosicché i riferimenti continuino a puntare alle celle giuste. Quella metà della storia è reale, ed è trattata nell'articolo di approfondimento su come HotXLS riscrive i riferimenti di formula quando righe e colonne si spostano, ma una regola di formato condizionale o di validazione dati non è solo una formula seduta in una cella. Abbina una formula a un intervallo, sqref nei termini di ECMA-376, e i due devono muoversi insieme. Quando una modifica strutturale affetta quell'intervallo in due porzioni che richiederebbero due diversi offset relativi per restare corrette, mantenere un unico oggetto regola con un'unica stringa di formula smette di essere un'opzione, e far finta del contrario è come una regola di evidenziazione inizia silenziosamente a confrontare le righe sbagliate
Perché inserire una riga divide una regola di formattazione condizionale invece di limitarsi a spostarla?
Un formato condizionale o una regola di validazione dati mantiene esattamente una formula per l'intero proprio intervallo, valutata relativamente a una singola cella di ancoraggio, quindi una volta che una modifica costringe due parti di quell'intervallo a richiedere due diversi offset relativi, un'unica formula non può più descrivere correttamente entrambe le parti. ECMA-376 esprime la copertura di una regola tramite l'attributo sqref sull'elemento conditionalFormatting o dataValidation, ed Excel valuta Formula1 e Formula2 come se il testo fosse stato digitato nella cella in alto a sinistra di quel sqref e poi riempito nel resto di esso, allo stesso modo in cui una normale formula relativa si riempie scorrendo lungo una colonna. Immagina un'evidenziazione di scostamento su B2:B50 che segnala qualsiasi valore effettivo che ecceda il proprio budget, costruita come regola cellIs la cui Formula1 è il testo letterale C2, ossia confronta la cella B della riga corrente con la cella C di quella stessa riga
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 25Inserisci quella singola riga separatrice alla vecchia riga 25 e le righe sopra il punto di inserimento non si spostano, quindi la loro quota della regola continua a leggere Formula1 come C2 correttamente. Le righe che prima erano 25-50 scorrono verso il basso diventando 26-51, e per esse C2 è ora del tutto la cella sbagliata, poiché la riga 26 deve confrontarsi con C26, non con una cifra di budget due dozzine di righe più in alto
Come decide HotXLS se una regola deve dividersi
HotXLS crea oggetti regola aggiuntivi solo quando la geometria lo richiede realmente: una routine interna, XlsxBuildShiftedRuleParts, percorre ogni area disgiunta nello sqref della regola, calcola qual era la cella di ancoraggio di quell'area prima della modifica e cosa diventa dopo, e verifica se ogni porzione risultante richiederebbe la stessa correzione di offset relativo. Se tutte le porzioni concordano, sopravvive un'unica regola, il cui sqref viene ricostruito come unione delle porzioni spostate e la cui formula viene ribasata una sola volta. Una vera divisione avviene solo quando le porzioni sono in disaccordo, esattamente il caso B2:B50 sopra, dove il blocco superiore mantiene l'ancora originale e il blocco inferiore ne richiede una nuova
Ribasare la formula di una porzione è un'operazione in due passaggi che riutilizza i meccanismi che HotXLS già possiede per i gruppi di formule condivise OOXML: prima la formula viene tradotta come se fosse stata originariamente ancorata alla cella in alto a sinistra propria di quella porzione, usando la stessa matematica di offset relativo che espande una formula condivisa sul proprio intervallo, poi il risultato passa attraverso lo stesso scanner di spostamento righe e colonne che riscrive le normali formule del foglio di lavoro. È così che Formula1 passa da C2 a C26 in due mosse invece che con un caso speciale scritto a mano: traduci C2 in avanti di 23 righe per ottenere C25, come se la regola fosse sempre iniziata lì, poi lascia che il normale spostamento alla riga 25 lo spinga fino a C26. Ogni altra proprietà, colore di riempimento, stop-if-true, l'operatore stesso, viaggia invariata sul nuovo oggetto regola, cosicché entrambe le metà continuino a dipingere le celle dello stesso colore di sempre
// ConditionalFormats now holds two rules instead of one:
// B2:B25 Formula1 = 'C2' (rows above the insert)
// B26:B51 Formula1 = 'C26' (rows that shifted down)Le barre dati e i set di icone si dividono nello stesso modo delle regole cellIs?
No: HotXLS partiziona solo i tipi di regola la cui correttezza dipende realmente da una formula relativa per singola regione, i confronti cellIs e le regole expression, e lascia ogni altro tipo di formato condizionale come un unico oggetto regola il cui sqref semplicemente cresce per coprire le porzioni spostate come unione multi-area. Internamente il ramo è un semplice controllo su Kind, cf.Kind in [cfkCellIs, cfkExpression], nulla di più esotico. Le barre dati, le scale a due e tre colori, i set di icone, le classifiche in alto e in basso, e i rilevatori di duplicati, vuoti ed errori portano un payload, un colore di barra, un insieme di punti di scala, una famiglia di icone, che descrive l'intero intervallo coperto in un colpo solo anziché un confronto relativo per singola cella, quindi suddividerli in più oggetti regola con priorità non guadagnerebbe alcuna correttezza e aggiungerebbe solo regole da gestire. Quando una modifica divide il loro intervallo, HotXLS ricombina le porzioni in un'unica regola con uno sqref multi-area e riancora il payload come un'unica unità invece di clonare un nuovo oggetto regola per porzione. La distinzione si allinea con la tassonomia dei tipi di regola nell'articolo sulle basi della formattazione condizionale e del rich text: barre dati, scale colore e set di icone già si distinguono dalle regole cellIs ignorando del tutto la proprietà Style, e ora risulta che si distinguono anche dal riancoraggio per singola regione per lo stesso motivo di fondo
Perché cambiano le priorità delle regole dopo una modifica strutturale?
Le priorità cambiano perché ogni clone parte possedendo esattamente lo stesso valore di priorità della regola da cui si è diviso, e HotXLS esegue in seguito un passaggio di normalizzazione che risolve i duplicati risultanti in un ordinamento pulito e senza vuoti invece di lasciare due regole a pari merito sullo stesso rango. Una seconda routine interna, XlsxNormalizeConditionalFormatPriorities, prende la priorità corrente di ogni formato condizionale, ricade sulla posizione di quella regola nella collezione per qualsiasi regola che non ne abbia mai avuta una impostata esplicitamente, ordina l'intero elenco in modo stabile cosicché i pareggi mantengano il loro ordine relativo originale, e rinumera il risultato ordinato in una sequenza densa 1, 2, 3 senza vuoti e senza ripetizioni. HotXLS la esegue una volta prima che inizi uno spostamento, cosicché la clonazione parta da una base pulita, e di nuovo dopo ogni divisione e dopo che ogni regola svuotata viene rimossa, cosicché il file che viene salvato non abbia mai due voci di regola che rivendicano la stessa priorità. Questo conta se hai seguito il consiglio dell'articolo sulle basi della formattazione condizionale di lasciare vuoti tra i valori di priorità cosicché una regola successiva possa inserirsi senza rinumerare le altre: i vuoti sopravvivono fino alla successiva modifica di riga o colonna che tocca quel foglio di lavoro, poi collassano, perché la normalizzazione garantisce solo unicità e ordine stabile, non che il tuo schema di numerazione originale torni invariato
Anche le regole di validazione dati si dividono, senza una priorità da rinumerare
Le regole di validazione dati passano per la stessa logica di partizionamento dell'intervallo delle regole cellIs ed expression, e a differenza della formattazione condizionale, ogni tipo di validazione segue quel percorso in modo uniforme: HotXLS non ha una famiglia non-formula separata per la validazione dati nel modo in cui lo sono le barre dati e i set di icone per la formattazione condizionale, quindi una semplice regola a elenco o a numero intero viene partizionata dalla stessa identica routine che gestisce una formula personalizzata relativa. Ciò che differisce è la priorità: ECMA-376 non assegna all'elemento dataValidation alcun attributo priority, quindi non esiste un passaggio di rinumerazione per le validazioni come esiste per i formati condizionali. Immagina una validazione a formula personalizzata che impedisce a ogni importo effettivo di una riga di superare il proprio budget nella colonna accanto
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)Questo conta per lo stesso motivo per cui l'articolo sulle basi della validazione dati mette in guardia dall'assegnare una regola prima che il conteggio delle righe sia definitivo: una validazione copre solo le celle letterali che le sono state assegnate, e una modifica strutturale successiva può lasciare due o più regole a svolgere il lavoro che una sola svolgeva prima. Nulla si rompe funzionalmente: ogni cella nell'intervallo originale è ancora validata da qualcosa, ma il codice che presuppone una voce di DataValidations per colonna inizierà a indicizzare erroneamente dopo la prima modifica che la tocca. Esiste un tetto rigido su quanto lontano ciò possa spingersi: se la divisione spingerebbe un foglio di lavoro oltre 65.534 regole di validazione dati, HotXLS solleva un'eccezione invece di scrivere un file che Excel rifiuterebbe silenziosamente, il che rappresenta la libreria che rifiuta di produrre una cartella di lavoro corrotta piuttosto che un limite che l'uso ordinario ha probabilità di raggiungere
Cosa controllare dopo un inserimento o un'eliminazione in blocco
Le due cose che vale la pena verificare dopo che uno script esegue un lotto di modifiche a righe o colonne su un foglio pieno di formati condizionali e validazioni sono il conteggio totale delle regole e l'ordine di priorità, poiché entrambi possono derivare in modi facili da perdere in una revisione del codice e ovvi nel momento in cui qualcuno apre Gestisci Regole in Excel. Una singola modifica raramente causa molti danni: un singolo inserimento nel mezzo di una regola cellIs produce al massimo due oggetti regola dove ce n'era uno. Il rischio si accumula quando una routine di generazione report inserisce righe una alla volta in un ciclo su un foglio che già porta diverse regole ancorate a formula: ogni passaggio può ridividere regole già divise da un passaggio precedente, e cinque regole cellIs originali possono finire per essere diverse volte tanto in frammenti di scarso valore che coprono schegge dell'intervallo originale. Raggruppare le modifiche strutturali, inserendo l'intero blocco nuovo in un'unica chiamata invece che una riga alla volta, mantiene il conteggio delle regole legato al numero di ancore genuinamente distinte piuttosto che al numero di modifiche eseguite
Il partizionamento delle regole e la normalizzazione delle priorità sono comportamento standard del motore XLSX nel componente Excel Delphi HotXLS per Delphi e C++Builder; la pagina del prodotto porta il riferimento API completo per la modifica dei fogli di lavoro, inclusi i metodi di formattazione condizionale e validazione dati descritti qui