HotXLS automaticky upravuje odkazy vo vzorcoch, keď vkladáte alebo odstraňujete riadky alebo stĺpce v XLSX hárku. Metódy InsertRows, DeleteRows, InsertCols a DeleteCols prepíšu každý zostávajúci vzorec tak, aby jeho odkazy tvaru A1 — relatívne, absolútne aj rozsahy — po štrukturálnej úprave naďalej ukazovali na rovnaké dáta, pričom odkazy smerujúce do vymazaného bloku sa zmenia na #REF!, čo presne zodpovedá správaniu Excelu
Chyba, ktorej sa týmto predchádza, patrí pri generovaní reportov k tým najtichším. Generátor zapíše denné hodnoty do buniek C2:C9 so vzorcom =SUM(C2:C9) pod nimi, potom v neskoršom kroku vloží hlavičkový riadok navrch. Ak by engine presunul iba hodnoty buniek a text vzorca ponechal bez zmeny, funkcia SUM by stále načítavala C2:C9, zatiaľ čo údaje by sa už nachádzali v C3:C10 — takže celkový súčet by potichu vynechal posledný deň a dvakrát by započítal hlavičku. Nič nevyvolá výnimku, súbor sa bez problémov otvorí, no výsledné číslo je jednoducho nesprávne. Pred verziou 2.160 ponechával XLSX engine knižnice HotXLS vzorce pri štrukturálnych zmenách bez úpravy; od verzie 2.160 prebieha prepisovanie automaticky a netreba nastavovať žiadne parametre
Čo sa stane so vzorcami pri vložení riadku v Exceli?
Pravidlo Excelu hovorí, že odkazy nasledujú údaje a nie adresy. Pri vložení riadku sa každý odkaz, ktorého index riadku leží na mieste vloženia alebo pod ním, posunie nadol o počet vložených riadkov; odkazy nachádzajúce sa celkovo nad miestom vloženia zostávajú nezmenené. Odstraňovanie riadkov spúšťa rovnaké pravidlo v opačnom poradí: odkazy pod odstráneným blokom sa posunú nahor a odkazy smerujúce do samotného odstráneného bloku sa zmenia na #REF!, keďže bunky, ktoré pomenúvali, už neexistujú. Stĺpce sa správajú identicky pozdĺž druhej osi. Knižnica pre prácu s tabuľkami, ktorá chce, aby jej výstup prežil kontakt s používateľmi Excelu, musí tieto mechanizmy presne replikovať, pretože používatelia uvažujú o svojich vzorcoch v týchto intenciách bez toho, aby nad tým vôbec premýšľali
To, čo vývojárov často prekvapí, je fakt, že sa posúvajú aj absolútne odkazy. Znaky $ v odkaze $B$2 riadia to, čo sa stane, keď sa vzorec kopíruje alebo vypĺňa do inej bunky — počas štrukturálnych úprav nerobia nič. Vložte riadok nad riadok 2 a Excel prepíše $B$2 na $B$3, pričom znaky dolára zostanú zachované, pretože hodnota, od ktorej vzorec závisí, sa fyzicky presunula na riadok 3. Engine, ktorý by posúval iba relatívne odkazy, by poškodil práve tie vzorce, ktoré používatelia ukotvujú najzámernejšie. HotXLS posúva oba formáty a zachováva znaky $ v prepísanom texte
Ako HotXLS automaticky posúva odkazy vo vzorcoch?
Všetky štyri metódy štrukturálnych úprav na TXLSXWorksheet delegujú prácu na jediný geometrický engine: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). Metóda InsertRows(BeforeRow, Count) ho volá s kladným rozdielom riadkov (row delta), DeleteRows(StartRow, Count) so záporným a metódy stĺpcov robia to isté na stĺpcovej osi. Procedúra najprv premiestni samotné bunky — pričom zahodí každú bunku, ktorá spadne do vymazaného bloku — a potom prejde každú zostávajúcu bunku so vzorcom a preženie jej text cez parser XlsxAdjustFormulaRowColRefs, ktorý vyhľadá odkazy štýlu A1 a prepíše ich riadkové a stĺpcové zložky podľa posunu. Rovnaký prechod premiestni aj zlúčené rozsahy (merged ranges), hyperodkazy, komentáre, obrázky, grafy, podmienené formáty, validácie údajov a rozsahy tabuliek, takže celý hárok sa presunie ako jeden celok
// 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 dataStojí za to poznať niekoľko detailov parsera. XlsxAdjustFormulaRowColRefs rozpoznáva odkazy na jednotlivé bunky vo všetkých štyroch formách ukotvenia (A1, $A1, A$1, $A$1) a dvojrohé rozsahy ako A1:B3, pričom upravuje každý koncový bod samostatne. Vzorce, ktorých text už začína znakom # — značkou chyby z predchádzajúcej úpravy — sa preskočia a znova sa neskenujú. A metóda InsertCols pridáva jednu príjemnú drobnosť pre paritu s Excelom: novovložené stĺpce dedia šírku svojho ľavého suseda, čo zodpovedá príkazu Excelu pre vloženie stĺpcov hárka
Kedy sa odstránený odkaz zmení na #REF!?
Pravidlo posunu pre jeden index riadku alebo stĺpca má tri možné výsledky. Index pred bodom úpravy sa nemení. Index na bode úpravy alebo za ním sa posunie o príslušný rozdiel (delta). A pri odstránení platí, že index, ktorý spadne do vymazaného bloku, nemá žiadnu zmysluplnú novú hodnotu — bunka je preč — takže parser prepíše celý odkaz na #REF!. Pri odkazoch na rozsah prechádzajú oba koncové body rovnakým pravidlom, a ak ktorýkoľvek koncový bod pristane vo vnútri vymazaného bloku, celý odkaz sa prepíše na #REF! namiesto toho, aby bol ponechaný napoly platný
// 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 -> A7Generovanie zjavnej chyby #REF! namiesto tichého presmerovania na inú bunku je správnym prístupom a presne tak to robí aj Excel. Vzorec, ktorý by po vymazaní svojho skutočného vstupu ukazoval na susednú bunku, by vrátil zdanlivo priateľné číslo; chyba #REF! sa však prenáša cez závislé vzorce a prejaví sa hneď v prvom smoke teste. Rovnaká konverzia platí aj na stĺpcovej osi
// 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 goneKtoré formy odkazov sa neprepisujú?
Parser sa zameriava na odkazy typu A1 v rámci rovnakého hárku s explicitným tvarom písmeno-stĺpca-plus-číslo-riadku a je dôležité presne určiť, čo do tohto rozsahu nespadá. Odkazom na celé stĺpce, ako A:A, a odkazom na celé riadky, ako 1:1, chýba jedna z dvoch zložiek, takže ich parser ponecháva tak, ako sú napísané. Štruktúrované odkazy na tabuľky (Table1[Amount]) taktiež prechádzajú bez úprav. Prepisovanie funguje striktne na notácii A1 — ak váš kód zostavuje vzorce v štýle R1C1, pred štrukturálnou úpravou ich preveďte na A1, ako je popísané v sprievodnom článku o notácii vzorcov R1C1 v Delphi
Konštrukcie prechádzajúce cez viacero hárkov a na úrovni zošita sa spracúvajú v samostatných krokoch a nie v parseri textu bunky. Po úprave zmeneného hárku prenesie metóda ShiftSheetGeometry rovnakú zmenu geometrie do vzorcov na iných hárkoch odkazujúcich na upravený hárok, do rozsahov radov grafov, do interných cieľov hyperodkazov a do definovaných názvov. Definované názvy získavajú ďalšiu ochranu na úrovni životného cyklu hárku: od verzie 2.150 vymazanie hárku prepíše každý kvalifikátor SheetN! vo vzorci definovaného názvu na #REF! a premenovanie hárku prepíše kvalifikátor na nový názov, takže názvy nikdy neukazujú na hárok, ktorý už neexistuje. To, ako do seba zapadajú názvy a vzorce naprieč hárkami, popisuje článok o definovaných názvoch a vzorcoch medzi hárkami
Prepočítať po posune
Úprava odkazov prepisuje text vzorcov, neprepočítava však výsledky. Po štrukturálnej úprave opisujú cachované hodnoty uložené pri vzorcoch starú geometriu, takže spoľahlivým postupom je: najprv vykonať všetky vloženia a odstránenia, potom spustiť jeden prepočet a následne súbor uložiť. Vykonanie posunu pred prepočtom udržiava v poriadku aj informácie o závislostiach — každý prepísaný odkaz pomenúva svojho skutočného predchodcu, čo je presne to, čo engine prírastkového prepočtu a jeho graf závislostí potrebujú na prepočítanie minimálnej množiny ovplyvnených buniek. Ak prepísaný vzorec teraz obsahuje chybu #REF!, prepočet túto chybu okamžite zobrazí na povrch namiesto ponechania neaktuálnej hodnoty v súbore
Úprava odkazov vo vzorcoch sa dodáva ako štandardné správanie XLSX enginu v komponente HotXLS Delphi Excel Component pre Delphi a C++Builder; produktová stránka prináša kompletnú referenciu API pre úpravu hárkov, vrátane tu zobrazených metód pre vkladanie a odstraňovanie