HotXLS ajustează automat referințele de formule atunci când inserați sau ștergeți rânduri sau coloane într-o foaie de calcul XLSX. Metodele motorului InsertRows, DeleteRows, InsertCols și DeleteCols rescriu fiecare formulă rămasă, astfel încât referințele sale A1 — relative, absolute și de interval — să continue să indice spre aceleași date după modificarea structurală, iar referințele către un bloc șters devin #REF!, respectând comportamentul Excel
Eroarea pe care o previne acest comportament este una dintre cele mai silențioase în generarea de rapoarte. Un generator scrie cifre zilnice în celulele C2:C9 cu o formulă =SUM(C2:C9) dedesubt, apoi un pas ulterior inserează un rând de antet în partea de sus. Dacă motorul mută doar valorile celulelor și lasă textul formulei neschimbat, acea sumă SUM citește în continuare C2:C9 în timp ce datele se află acum în C3:C10 — astfel încât totalul omite silențios ultima zi și numără de două ori un antet. Nimic nu generează o excepție, fișierul se deschide fără probleme, iar numărul este pur și simplu greșit. Înainte de versiunea 2.160, motorul HotXLS XLSX lăsa formulele nemodificate în timpul editărilor structurale; de la versiunea 2.160, rescrierea este automată și nu există niciun indicator de setat
Ce se întâmplă cu formulele când inserați un rând în Excel?
Regula Excel este că referințele urmăresc datele, nu adresele. Când se inserează un rând, fiecare referință al cărei index de rând se află la sau sub punctul de inserare se deplasează în jos cu numărul de rânduri inserate; referințele aflate complet deasupra punctului de inserare rămân nemodificate. Ștergerea rândurilor rulează aceeași regulă în sens invers: referințele de sub blocul șters glisează în sus, iar referințele către blocul șters în sine devin #REF! deoarece celulele pe care le numeau nu mai există. Coloanele se comportă identic de-a lungul celeilalte axe. O bibliotecă de foi de calcul care dorește ca rezultatele sale să supraviețuiască contactului cu utilizatorii Excel trebuie să reproducă exact aceste mecanisme, deoarece utilizatorii își justifică formulele în acești termeni fără să se gândească vreodată la asta
Partea care îi surprinde pe dezvoltatori este că referințele absolute se deplasează și ele. Ancorele $ din $B$2 controlează ce se întâmplă atunci când o formulă este copiată sau completată într-o altă celulă — ele nu fac nimic în timpul editărilor structurale. Inserați un rând deasupra rândului 2 și Excel rescrie $B$2 ca $B$3, cu semnele de dolar intacte, deoarece valoarea de care depinde formula s-a mutat fizic pe rândul 3. Un motor care ar deplasa doar referințele relative ar corupe exact formulele pe care oamenii le ancorează cel mai deliberat. HotXLS modifică ambele forme și păstrează marcajele $ în textul rescris
Cum decalează HotXLS automat referințele de formule?
Toate cele patru metode de editare structurală din TXLSXWorksheet deleagă către un singur motor geometric: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). InsertRows(BeforeRow, Count) îl apelează cu un delta de rând pozitiv, DeleteRows(StartRow, Count) cu unul negativ, iar metodele pentru coloane fac același lucru pe axa coloanelor. Rutina relochează mai întâi celulele în sine — eliminând orice celulă care cade în interiorul unui bloc șters — iar apoi parcurge fiecare celulă cu formulă rămasă și îi trece textul prin XlsxAdjustFormulaRowColRefs, un scanner care găsește referințe în stil A1 și le rescrie componentele de rând și coloană în funcție de deplasare. Aceeași etapă relochează intervalele îmbinate, hyperlink-urile, comentariile, imaginile, diagramele, formatele condiționate, validările de date și intervalele de tabele, astfel încât întreaga foaie se deplasează ca o singură unitate
// Structura înainte de editare:
// C2..C9 cifre zilnice
// C10 =SUM(C2:C9)
Sheet.InsertRows(2, 1); // un rând gol înainte de rândul 2
// Structura după apel:
// C3..C10 cifre zilnice
// C11 =SUM(C3:C10) -- intervalul s-a mutat împreună cu dateleMerită cunoscute câteva detalii despre scanner. XlsxAdjustFormulaRowColRefs recunoaște referințele la o singură celulă în toate cele patru forme de ancorare (A1, $A1, A$1, $A$1) și intervale cu două colțuri, cum ar fi A1:B3, ajustând fiecare capăt în mod independent. Formulele al căror text începe deja cu # — un marker de eroare dintr-o editare anterioară — sunt omise în loc să fie scanate din nou. Iar InsertCols adaugă o facilitate proprie similară cu Excel: coloanele nou inserate moștenesc lățimea vecinului lor din stânga, ceea ce face și comanda Insert Sheet Columns din Excel
Când devine o referință ștearsă #REF!?
Regula de deplasare pentru un singur index de rând sau coloană are trei rezultate. Un index dinaintea punctului de editare rămâne neschimbat. Un index aflat la sau după punctul de editare se deplasează cu valoarea delta. Iar în caz de ștergere, un index care cade în interiorul blocului șters nu are o nouă valoare semnificativă — celula a dispărut — astfel încât scanerul rescrie întreaga referință ca #REF!. Pentru o referință de interval, ambele capete trec prin aceeași regulă, iar dacă oricare dintre capete cade în blocul șters, referința este rescrisă ca #REF! în loc să fie lăsată pe jumătate validă
// A12 conține =A4+A6+A10
Sheet.DeleteRows(5, 3); // șterge rândurile 5..7
// Formula, acum în A9, citește =A4+#REF!+A7
// A4 : deasupra blocului șters, neschimbat
// A6 : în interiorul rândurilor 5..7, dispărut -> #REF!
// A10 : sub bloc, glisează în sus -> A7Generarea unei erori evidente #REF! în loc de o redirecționare silențioasă este decizia corectă, fiind cea pe care o ia și Excel. O formulă care ar indica o celulă vecină după ce intrarea sa reală a fost ștearsă ar returna un număr plauzibil în aparență; #REF! se propagă prin formulele dependente și iese la suprafață la primul smoke test. Aceeași conversie se aplică și pe axa coloanelor
// E1 conține =B1*$C$1
Sheet.DeleteCols(3, 1); // elimină coloana C
// Formula, acum în D1, citește =B1*#REF!
// Ancora absolută nu a protejat $C$1 -- celula în sine a dispărutCare forme de referințe nu sunt rescrise?
Scannerul vizează referințele A1 de pe aceeași foaie cu o formă explicită de literă de coloană plus număr de rând, și merită să fim exacți cu privire la ceea ce se află în afara acestei reguli. Referințele la coloane întregi, cum ar fi A:A, și cele la rânduri întregi, cum ar fi 1:1, nu au una dintre cele două componente, astfel încât scannerul le lasă neschimbate. Referințele structurate de tabel (Table1[Amount]) sunt, de asemenea, lăsate nemodificate. Rescrierea funcționează exclusiv pe notația A1 — dacă codul dvs. construiește formule în stil R1C1, convertiți-le în A1 înainte de o editare structurală, conform descrierii din articolul însoțitor despre notația de formule R1C1 în Delphi
Structurile de tip cross-sheet și cele la nivel de registru de calcul sunt gestionate de etape separate, nu de scannerul de text din celule. După ajustarea foii editate, ShiftSheetGeometry propagă aceeași modificare de geometrie către formulele de pe alte foi care fac referire la foaia editată, către intervalele seriilor de diagrame, către țintele hyperlink-urilor interne și către numele definite. Numele definite primesc o protecție suplimentară la nivelul ciclului de viață al foii: începând cu versiunea 2.150, ștergerea unei foi de calcul rescrie fiecare calificator SheetN! din formula unui nume definit în #REF!, iar redenumirea unei foi rescrie calificatorul cu noul nume, astfel încât numele să nu indice niciodată spre o foaie care nu mai există. Modul în care se îmbină numele și formulele cross-sheet este tratat în articolul despre nume definite și formule cross-sheet
Recalculați după deplasare
Ajustarea referințelor rescrie textul formulei; nu recalculează rezultatele. După o editare structurală, valorile stocate în cache alături de formule descriu vechea geometrie, prin urmare secvența sigură este: efectuați mai întâi toate inserările și ștergerile, apoi declanșați recalcularea o singură dată și abia apoi salvați. Rularea deplasării înainte de recalculare menține corecte și informațiile de dependență — fiecare referință rescrisă își indică precedentul real, ceea ce reprezintă exact lucrul de care are nevoie motorul de recalculare incrementală și graful său de dependențe pentru a recalcula setul minim de celule afectate. Dacă o formulă rescrisă conține acum #REF!, recalcularea scoate la iveală valoarea de eroare imediat, în loc să lase o cifră învechită în fișier
Ajustarea referințelor de formule este inclusă ca comportament standard al motorului XLSX în HotXLS Delphi Excel Component pentru Delphi și C++Builder; pagina produsului conține referința completă a API-ului pentru editarea foilor de calcul, inclusiv metodele de inserare și ștergere prezentate aici