HotXLS samodejno prilagodi reference formul ob vstavljanju ali brisanju vrstic ali stolpcev v delovnem listu XLSX. Metode pogona InsertRows, DeleteRows, InsertCols in DeleteCols prepišejo vsako preostalo formulo tako, da njene reference A1 — relativne, absolutne in obsegi — po strukturnem urejanju še naprej kažejo na iste podatke, reference v izbrisani blok pa postanejo #REF!, kar ustreza obnašanju Excela
Hrošč, ki ga to preprečuje, je eden najtišjih pri ustvarjanju poročil. Generator zapiše dnevne številke v C2:C9 s formulo =SUM(C2:C9) pod njimi, kasnejši korak pa na vrh vstavi vrstico z glavo. Če pogon premakne le vrednosti celic in pusti besedilo formule nespremenjeno, tisti SUM še vedno bere C2:C9, medtem ko so podatki zdaj v C3:C10 — tako skupni znesek tiho izpusti zadnji dan in dvakrat šteje glavo. Nič ne sproži izjeme, datoteka se odpre brez težav, številka pa je preprosto napačna. Pred različico 2.160 je pogon HotXLS XLSX pustil formule nespremenjene med strukturnimi urejanji; od različice 2.160 dalje pa je prepisovanje samodejno in ni treba nastaviti nobene zastavice
Kaj se zgodi s formulami, ko v Excelu vstavite vrstico?
Excelovo pravilo je, da reference sledijo podatkom, ne naslovom. Ko je vstavljena vrstica, se vsaka referenca, katere indeks vrstice se nahaja na ali pod točko vstavljanja, premakne navzdol za število vstavljenih vrstic; reference, ki so v celoti nad točko vstavljanja, ostanejo nespremenjene. Brisanje vrstic izvaja enako pravilo v obratni smeri: reference pod izbrisanim blokom se pomaknejo navzgor, reference v sam izbrisani blok pa postanejo #REF!, ker celice, ki so jih poimenovale, ne obstajajo več. Stolpci se obnašajo povsem enako vzdolž druge osi. Knjižnica za preglednice, ki želi, da njen izhod preživi stik z uporabniki Excela, mora te mehanizme natančno reproducirati, saj uporabniki o svojih formulah razmišljajo v teh okvirih, ne da bi o tem sploh razmišljali
Del, ki preseneča razvijalce, je, da se premaknejo tudi absolutne reference. Sidra $ v $B$2 nadzorujejo, kaj se zgodi, ko se formula kopira ali zapolni v drugo celico — med strukturnimi urejanji ne naredijo ničesar. Če vstavite vrstico nad 2. vrstico, Excel prepiše $B$2 v $B$3 z ohranjenimi znaki za dolar, ker se je vrednost, od katere je formula odvisna, fizično premaknila v 3. vrstico. Pogon, ki bi premikal le relativne reference, bi pokvaril prav tiste formule, ki jih ljudje najbolj namerno sidrajo. HotXLS premakne obe obliki in ohrani oznake $ v prepisanem besedilu
Kako HotXLS samodejno premika reference formul?
Vse štiri metode strukturnega urejanja na razredu TXLSXWorksheet delegirajo delo enemu geometrijskemu pogonu: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). InsertRows(BeforeRow, Count) ga pokliče s pozitivno delto vrstic, DeleteRows(StartRow, Count) z negativno, stolpčne metode pa storijo enako na stolpčni osi. Rutina najprej premakne same celice — pri čemer opusti vsako celico, ki pade v izbrisani blok —, nato pa se sprehodi skozi vsako preostalo celico s formulo in posreduje njeno besedilo skozi XlsxAdjustFormulaRowColRefs, optični bralnik (scanner), ki najde reference v slogu A1 in prepiše njihove komponente vrstic in stolpcev glede na premik. Isti prehod premakne tudi združena območja, hiperpovezave, komentarje, slike, grafikone, pogojne formate, validacije podatkov in območja tabel, tako da se celoten list premakne kot ena enota
// 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 dataVredno je poznati nekaj podrobnosti optičnega bralnika. XlsxAdjustFormulaRowColRefs prepozna reference na posamezne celice v vseh štirih oblikah sider (A1, $A1, A$1, $A$1) in območja z dvema kotoma, kot je A1:B3, pri čemer vsako končno točko prilagodi neodvisno. Formule, katerih besedilo se že začne z # — oznako napake iz prejšnjega urejanja —, se preskočijo in se ne berejo ponovno. In InsertCols doda lastno podrobnost za skladnost z Excelom: na novo vstavljeni stolpci podedujejo širino svojega levega soseda, kar stori tudi Excelov ukaz za vstavljanje stolpcev lista
Kdaj izbrisana referenca postane #REF!?
Pravilo premika za en indeks vrstice ali stolpca ima tri možne izide. Indeks pred točko urejanja ostane nespremenjen. Indeks na ali po točki urejanja se premakne za delto. Pri brisanju pa indeks, ki pade znotraj izbrisanega bloka, nima več smiselne nove vrednosti — celica je izginila —, zato optični bralnik prepiše celotno referenco kot #REF!. Za referenco območja se obe končni točki poženeta skozi isto pravilo, in če katera koli končna točka pristane znotraj izbrisanega bloka, se referenca prepiše kot #REF!, namesto da bi ostala polovično veljavna
// 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 -> A7Ustvarjanje očitne napake #REF! namesto tihega ponovnega ciljanja je pravilen kompromis in natanko to naredi Excel. Formula, ki bi kazala na sosednjo celico po tem, ko je bil njen dejanski vhod izbrisan, bi vrnila navidezno pravilen rezultat; #REF! pa se razširi skozi odvisne formule in se pojavi že pri prvem preizkusu delovanja. Enaka pretvorba velja na osi stolpcev
// 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 goneKatere oblike referenc se ne prepišejo?
Optični bralnik cilja na reference A1 na istem listu z eksplicitno obliko črke stolpca plus številke vrstice in vredno je biti natančen o tem, kaj pade izven tega. Reference na celotne stolpce, kot je A:A, in reference na celotne vrstice, kot je 1:1, nimajo ene od dveh komponent, zato jih optični bralnik pusti zapisane, kot so. Strukturirane reference tabel (Table1[Amount]) se prav tako prenesejo nedotaknjene. Prepisu prav tako ustreza le zapis A1 — če vaša koda gradi formule v slogu R1C1, jih pred strukturnim urejanjem pretvorite v A1, kot je opisano v spremljevalnem članku o zapisu formul R1C1 v Delphiju
Konstrukti med listi in na ravni delovnega zvezka se obravnavajo v ločenih prehodih in ne prek optičnega bralnika besedila celic. Po prilagoditvi urejenega lista ShiftSheetGeometry prenese enako spremembo geometrije na formule na drugih listih, ki se nanašajo na urejeni list, na območja serij grafikonov, na cilje notranjih hiperpovezav in na definirana imena. Definirana imena dobijo dodatno zaščito na ravni življenjskega cikla lista: od različice 2.150 brisanje delovnega lista prepiše vsako oznako SheetN! znotraj formule definiranega imena v #REF!, preimenovanje lista pa prepiše oznako v novo ime, tako da imena nikoli ne kažejo na list, ki ne obstaja več. Kako se imena in formule med listi povezujejo, je obravnavano v članku o definiranih imenih in formulah med listi
Ponovni izračun po premiku
Prilagoditev referenc prepiše besedilo formule; ne preračuna pa rezultatov. Po strukturnem urejanju predpomnjene vrednosti, shranjene ob formulah, opisujejo staro geometrijo, zato je zanesljivo zaporedje: najprej izvedite vse vstavke in brisanja, nato enkrat sprožite preračunavanje in šele nato shranite. Izvedba premika pred preračunavanjem ohranja tudi podatke o odvisnosti točne — vsaka prepisana referenca poimenuje svojo pravo predhodnico, kajti to je natanko tisto, kar pogon za inkrementalno preračunavanje in njegov graf odvisnosti potrebujeta za ponovni izračun najmanjšega nabora prizadetih celic. Če prepisana formula zdaj vsebuje #REF!, preračunavanje takoj pokaže vrednost napake, namesto da bi v datoteki pustilo zastarelo številko
Prilagoditev referenc formul se prinaša kot standardno obnašanje pogona XLSX v komponenti HotXLS Delphi Excel Component za Delphi in C++Builder; stran izdelka vsebuje celotno referenco API-ja za urejanje delovnih listov, vključno z zgoraj prikazanimi metodami vstavljanja in brisanja