HotXLS adegua automaticamente i riferimenti di formula quando si inseriscono o si eliminano righe o colonne in un foglio di lavoro XLSX. I metodi InsertRows, DeleteRows, InsertCols e DeleteCols del motore riscrivono ogni formula rimasta in modo che i relativi riferimenti A1 — relativi, assoluti e di intervallo — continuino a puntare agli stessi dati dopo la modifica strutturale, mentre i riferimenti a un blocco eliminato diventano #REF!, rispecchiando il comportamento di Excel
Il bug che questo meccanismo previene è uno dei più silenziosi nella generazione di report. Un generatore scrive cifre giornaliere in C2:C9 con la formula =SUM(C2:C9) in basso, quindi un passaggio successivo inserisce una riga di intestazione in alto. Se il motore sposta solo i valori delle celle lasciando inalterato il testo della formula, la funzione SUM legge ancora C2:C9 mentre i dati risiedono ora in C3:C10 — di conseguenza il totale omette silenziosamente l'ultimo giorno e conta due volte l'intestazione. Non viene sollevata alcuna eccezione, il file si apre correttamente e il numero risulta semplicemente errato. Prima della versione 2.160, il motore HotXLS XLSX lasciava le formule inalterate durante le modifiche strutturali; a partire dalla 2.160, la riscrittura è automatica e non c'è alcuna opzione da impostare
Cosa succede alle formule quando si inserisce una riga in Excel?
La regola di Excel prevede che i riferimenti seguano i dati, non gli indirizzi. Quando si inserisce una riga, ogni riferimento il cui indice di riga si trova in corrispondenza o al di sotto del punto di inserimento si sposta verso il basso del numero di righe inserite; i riferimenti interamente al di sopra del punto di inserimento rimangono inalterati. L'eliminazione delle righe segue la stessa regola al contrario: i riferimenti al di sotto del blocco eliminato scorrono verso l'alto, e i riferimenti al blocco eliminato stesso diventano #REF! poiché le celle a cui facevano riferimento non esistono più. Le colonne si comportano in modo identico lungo l'altro asse. Una libreria di fogli di calcolo che desideri che il proprio output sopravviva all'utilizzo da parte degli utenti di Excel deve riprodurre esattamente questi meccanismi, poiché gli utenti ragionano sulle formule in questi termini senza nemmeno pensarci
L'aspetto che sorprende gli sviluppatori è che anche i riferimenti assoluti si spostano. I simboli $ in $B$2 controllano ciò che accade quando una formula viene copiata o trascinata in un'altra cella — non hanno alcun effetto durante le modifiche strutturali. Inserendo una riga sopra la riga 2, Excel riscrive $B$2 in $B$3, mantenendo intatti i simboli del dollaro, poiché il valore da cui dipende la formula si è fisicamente spostato alla riga 3. Un motore che modificasse solo i riferimenti relativi corromperebbe proprio le formule che gli utenti ancorano con maggiore intenzione. HotXLS aggiorna entrambe le forme e conserva i marcatori $ nel testo riscritto
In che modo HotXLS sposta automaticamente i riferimenti di formula?
Tutti e quattro i metodi di modifica strutturale in TXLSXWorksheet delegano l'operazione a un singolo motore geometrico: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). InsertRows(BeforeRow, Count) lo chiama con un delta di riga positivo, DeleteRows(StartRow, Count) con uno negativo, e i metodi per le colonne fanno lo stesso sull'asse delle colonne. La routine riposiziona prima le celle stesse — eliminando qualsiasi cella che finisca all'interno di un blocco cancellato — quindi esamina ogni cella con formula rimasta e passa il suo testo attraverso XlsxAdjustFormulaRowColRefs, un analizzatore che trova i riferimenti in stile A1 e ne riscrive i componenti di riga e colonna in base allo spostamento. Lo stesso passaggio riposiziona intervalli uniti, collegamenti ipertestuali, commenti, immagini, grafici, formati condizionali, convalide di dati e intervalli di tabelle, in modo che l'intero foglio si sposti come un'unica unità
// Layout before the edit:
// C2..C9 daily figures
// C10 =SUM(C2:C9)
Sheet.InsertRows(2, 1); // one blank row before row 2
// Layout after the call:
// C3..C10 daily figures
// C11 =SUM(C3:C10) -- the range moved with the dataVale la pena conoscere alcuni dettagli dell'analizzatore. XlsxAdjustFormulaRowColRefs riconosce i riferimenti a cella singola in tutte e quattro le forme di ancoraggio (A1, $A1, A$1, $A$1) e gli intervalli a due angoli come A1:B3, regolando ciascun punto finale in modo indipendente. Le formule il cui testo inizia già con # — un marcatore di errore dovuto a una modifica precedente — vengono saltate invece di essere analizzate nuovamente. Inoltre, InsertCols aggiunge una finezza per la parità con Excel: le colonne appena inserite ereditano la larghezza della colonna adiacente sinistra, proprio come fa il comando Inserisci colonne del foglio di Excel
Quando un riferimento eliminato diventa #REF!?
La regola di spostamento per un singolo indice di riga o colonna presenta tre esiti. Un indice prima del punto di modifica rimane invariato. Un indice in corrispondenza o oltre il punto di modifica si sposta del delta specificato. In caso di eliminazione, un indice che cade all'interno del blocco eliminato non ha un nuovo valore significativo — la cella è sparita — per cui l'analizzatore riscrive l'intero riferimento come #REF!. Per un riferimento a un intervallo, entrambi i punti finali seguono la stessa regola, e se uno dei due punti finali cade all'interno del blocco eliminato, il riferimento viene riscritto come #REF! invece di essere lasciato parzialmente valido
// A12 holds =A4+A6+A10
Sheet.DeleteRows(5, 3); // delete rows 5..7
// The formula, now in A9, reads =A4+#REF!+A7
// A4 : above the deleted block, unchanged
// A6 : inside rows 5..7, gone -> #REF!
// A10 : below the block, slides up -> A7Produrre un errore visibile #REF! invece di reindirizzare silenziosamente è la scelta corretta, ed è quella adottata da Excel. Una formula che puntasse a una cella vicina dopo la cancellazione del suo vero input restituirebbe un numero apparentemente plausibile; l'errore #REF! si propaga invece attraverso le formule dipendenti ed emerge fin dal primo smoke test. La stessa conversione si applica sull'asse delle colonne
// E1 holds =B1*$C$1
Sheet.DeleteCols(3, 1); // remove column C
// The formula, now in D1, reads =B1*#REF!
// The absolute anchor did not protect $C$1 -- the cell itself is goneQuali forme di riferimento non vengono riscritte?
L'analizzatore prende di mira i riferimenti A1 dello stesso foglio che presentano una struttura esplicita con lettera di colonna e numero di riga, ed è opportuno specificare con precisione cosa esuli da questo comportamento. I riferimenti all'intera colonna come A:A e all'intera riga come 1:1 mancano di uno dei due componenti, per cui l'analizzatore li lascia inalterati. Anche i riferimenti di tabella strutturati (Table1[Amount]) vengono ignorati. La riscrittura opera inoltre rigorosamente sulla notazione A1 — se il tuo codice crea formule in stile R1C1, convertile in A1 prima di una modifica strutturale, come descritto nell'articolo correlato sulla notazione di formula R1C1 in Delphi
I costrutti tra diversi fogli e a livello di cartella di lavoro (workbook) sono gestiti da passaggi separati anziché dall'analizzatore del testo delle celle. Dopo aver regolato il foglio modificato, ShiftSheetGeometry propaga la stessa modifica geometrica alle formule presenti su altri fogli che fanno riferimento a quello modificato, agli intervalli delle serie di grafici, ai target dei collegamenti ipertestuali interni e ai nomi definiti. I nomi definiti ottengono un'ulteriore protezione a livello di ciclo di vita del foglio: a partire dalla versione 2.150, l'eliminazione di un foglio di lavoro riscrive ogni qualificatore SheetN! all'interno della formula di un nome definito in #REF!, e la ridenominazione di un foglio riscrive il qualificatore con il nuovo nome, impedendo che i nomi puntino a fogli non più esistenti. Il modo in cui i nomi e le formule tra fogli diversi si collegano è trattato nell'articolo sui nomi definiti e le formule tra fogli di lavoro diversi
Ricalcolare dopo lo spostamento
L'adeguamento dei riferimenti riscrive il testo della formula; non ricalcola i risultati. Dopo una modifica strutturale, i valori memorizzati nella cache insieme alle formule descrivono la vecchia geometria, quindi la sequenza consigliata è: eseguire prima tutti gli inserimenti e le eliminazioni, attivare il ricalcolo una volta sola e infine salvare. L'esecuzione dello spostamento prima del ricalcolo mantiene inoltre coerenti le informazioni sulle dipendenze — ogni riferimento riscritto nomina il suo vero precedente, che è esattamente ciò di cui il motore di ricalcolo incrementale e il relativo grafo delle dipendenze hanno bisogno per ricalcolare l'insieme minimo di celle interessate. Se una formula riscritta contiene ora #REF!, il ricalcolo evidenzia immediatamente il valore di errore invece di lasciare un numero obsoleto nel file
L'adeguamento dei riferimenti di formula viene fornito come comportamento standard del motore XLSX nel Componente HotXLS Delphi Excel per Delphi e C++Builder; la pagina del prodotto contiene il riferimento completo delle API di modifica del foglio di lavoro, inclusi i metodi di inserimento ed eliminazione mostrati qui