„HotXLS“ automatiškai pakoreguoja formulių nuorodas, kai įterpiate arba trinate eilutes ar stulpelius XLSX darbalapyje. Variklio metodai „InsertRows“, „DeleteRows“, „InsertCols“ ir „DeleteCols“ perrašo kiekvieną išlikusią formulę taip, kad jos A1 nuorodos — santykinės, absoliučios ir diapazonai — po struktūrinio redagavimo toliau rodytų į tuos pačius duomenis, o nuorodos į ištrintą bloką taptų „#REF!“, kas atitinka „Excel“ elgseną
Ši klaida, kurios išvengiame, yra viena nepastebimiausių ataskaitų generavime. Generatorius įrašo kasdienius rodiklius į C2:C9 with formulę „=SUM(C2:C9)“ po jais, o vėliau viršuje įterpia antraštės eilutę. Jei variklis perkeltų tik langelių reikšmes ir paliktų nepakeistą formulės tekstą, SUM vis tiek nuskaitytų C2:C9, nors duomenys dabar gyvena C3:C10 — todėl bendra suma tyliai praleistų paskutinę dieną ir dvigubai suskaičiuotų antraštę. Jokia išimtis nekyla, failas atsidaro gerai, o skaičius yra tiesiog neteisingas. Iki 2.160 versijos „HotXLS“ XLSX variklis palikdavo formules nepaliestas struktūrinių redagavimų metu; nuo 2.160 versijos perrašymas yra automatinis ir tam nereikia jokių nustatymų
Kas nutinka formulėms, kai įterpiate eilutę „Excel“ programoje?
„Excel“ taisyklė yra ta, kad nuorodos seka paskui duomenis, o ne adresus. Kai įterpiama eilutė, kiekviena nuoroda, kurios eilutės indeksas yra įterpimo taške arba žemiau jo, pasislenka žemyn per įterptų eilučių skaičių; nuorodos, esančios visiškai virš įterpimo taško, lieka nepaliestos. Eilučių trynimas vykdo tą pačią taisyklę atvirkščiai: nuorodos žemiau ištrinto bloko pasislenka viršun, o nuorodos į patį ištrintą bloką tampa „#REF!“, nes langeliai, kuriuos jos nurodė, nebeegzistuoja. Stulpeliai elgiasi identiškai kitoje ašyje. Lentelių biblioteka, norinti, kad jos rezultatas būtų teisingas vartotojams, privalo tiksliai atkurti šį mechanizmą, nes naudotojai būtent taip supranta savo formules, net nesusimąstydami apie tai
Dalis, kuri stebina programuotojus, yra ta, kad absoliučios nuorodos taip pat juda. Simboliai „$“ nuorodoje „$B$2“ kontroliuoja tik tai, kas nutinka, kai formulė kopijuojama ar užpildoma kitame langelyje — jie nieko nedaro struktūrinių redagavimų metu. Įterpkite eilutę virš 2 eilutės, ir „Excel“ perrašys „$B$2“ į „$B$3“ su išsaugotais dolerio ženklais, nes reikšmė, nuo kurios priklauso formulė, fiziškai persikėlė į 3 eilutę. Variklis, kuris keistų tik santykines nuorodas, sugadintų būtent tas formules, kurias žmonės fiksuoja labiausiai apgalvotai. „HotXLS“ pastumia abi formas ir išlaiko „$“ ženklus perrašytame tekste
Kaip „HotXLS“ automatiškai pastumia formulių nuorodas?
Visi keturi struktūrinio redagavimo metodai „TXLSXWorksheet“ klasėje deleguojami vienam geometrijos varikliui: „ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta)“. „InsertRows(BeforeRow, Count)“ iškviečia jį su teigiamu eilučių pokyčiu, „DeleteRows(StartRow, Count)“ — su neigiamu, o stulpelių metodai daro tą patį stulpelių ašyje. Procedūra pirmiausia perkelia pačius langelius (pašalindama bet kurį langelį, kuris patenka į ištrintą bloką), o tada pereina per kiekvieną išlikusį formulės langelį ir praleidžia jo tekstą per „XlsxAdjustFormulaRowColRefs“ — skaitytuvą, kuris suranda A1 stiliaus nuorodas ir perrašo jų eilučių bei stulpelių komponentus pagal poslinkį. Tuo pačiu žingsniu perkeliamos sujungtos sritys, nuorodos, komentarai, vaizdai, diagramos, sąlyginis formatavimas, duomenų tikrinimai ir lentelių sritys, todėl visas lapas juda kaip vienas vienetas
// 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 dataVerta žinoti keletą skaitytuvo detalių. „XlsxAdjustFormulaRowColRefs“ atpažįsta vieno langelio nuorodas visose keturiose formose („A1“, „$A1“, „A$1“, „$A$1“) ir dviejų kampų diapazonus, tokius kaip „A1:B3“, pakoreguodama kiekvieną kampinį tašką nepriklausomai. Formulės, kurių tekstas jau prasideda simboliu „#“ — klaidos žyme iš ankstesnio redagavimo — yra praleidžiamos, o ne skaitomos iš naujo. O „InsertCols“ prideda vieną patogią savybę, atitinkančią „Excel“ elgseną: naujai įterpti stulpeliai paveldi savo kairiojo kaimyno plotį, kas atitinka „Excel“ komandą „Įterpti stulpelius“
Kada ištrinta nuoroda tampa #REF!?
Vienos eilutės ar stulpelio indekso poslinkio taisyklė turi tris galimas baigtis. Indeksas prieš redagavimo tašką nekeičiamas. Indeksas redagavimo taške arba už jo pasislenka per nurodytą pokytį. O trynimo atveju indeksas, patenkantis į ištrintą bloką, nebeturi prasmingos naujos reikšmės — langelis išnyko — todėl skaitytuvas perrašo visą nuorodą kaip „#REF!“. Diapazono nuorodai abu galiniai taškai apdorojami pagal tą pačią taisyklę, ir jei bet kuris iš jų patenka į ištrintą sritį, visa nuoroda perrašoma kaip „#REF!“, užuot palikus ją pusiau galiojančią
// 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 -> A7Aiški klaida „#REF!“ užuot tyliai pakeitus nuorodą yra teisingas pasirinkimas, ir būtent taip elgiasi „Excel“. Formulė, kuri rodytų į kaimyninį langelį po to, kai tikrasis jos įvesties langelis buvo ištrintas, grąžintų tikėtinai atrodantį skaičių; „#REF!“ plinta per priklausomas formules ir pasirodo jau per pirmąjį patikrinimą. Toks pat konvertavimas taikomas ir stulpelių ašyje
// 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 goneKurios nuorodų formos neperrašomos?
Skaitytuvas orientuojasi į to paties lapo A1 nuorodas, turinčias aiškią stulpelio raidės ir eilutės numerio formą, ir verta tiksliai įvardyti tai, kas į šį apibrėžimą nepatenka. Stulpelių nuorodos, tokios kaip „A:A“, ir eilučių nuorodos, tokios kaip „1:1“, neturi vieno iš dviejų komponentų, todėl skaitytuvas jas palieka nepakeistas. Struktūrizuotos lentelių nuorodos („Table1[Amount]“) taip pat lieka nepaliestos. Perrašymas taip pat veikia tik su A1 žymėjimu — jei jūsų kodas kuria formules R1C1 stiliumi, prieš struktūrinį redagavimą konvertuokite jas į A1, kaip aprašyta straipsnyje apie R1C1 formulių žymėjimą Delphi aplinkoje
Tarpusavio lapų ir knygos lygio struktūros tvarkomos atskirais žingsniais, o ne langelių teksto skaitytuvu. Pakoregavęs redaguojamą lapą, „ShiftSheetGeometry“ perduoda tą patį geometrijos pakeitimą formulėms kituose lapuose, kurie nurodo redaguojamą lapą, diagramų sekų diapazonams, vidinėms nuorodoms bei apibrėžtiems pavadinimams (defined names). Apibrėžti pavadinimai gauna papildomą apsaugą lapo gyvavimo ciklo lygiu: nuo 2.150 versijos darbalapio ištrynimas perrašo kiekvieną „SheetN!“ žymeklį apibrėžto pavadinimo formulėje į „#REF!“, o lapo pervadinimas pakeičia žymeklį nauju pavadinimu, todėl pavadinimai niekada nerodo į nebeegzistuojantį lapą. Kaip apibrėžti pavadinimai ir tarpusavio lapų formulės dera tarpusavyje, aprašyta straipsnyje apie apibrėžtus pavadinimus ir formules tarp skirtingų lapų
Perskaičiavimas po poslinkio
Nuorodų pakoregavimas perrašo formulės tekstą; jis neperskaičiuoja rezultatų. Po struktūrinio redagavimo išsaugotos langelių reikšmės aprašo senąją geometriją, todėl patikima seka yra tokia: pirmiausia atlikite visus įterpimus bei trynimus, tada vieną kartą paleiskite perskaičiavimą ir tada išsaugokite failą. Poslinkio paleidimas prieš perskaičiavimą taip pat išlaiko teisingą priklausomybių informaciją — kiekviena perrašyta nuoroda nurodo tikrąjį precedentą, ko reikia inkrementiniam perskaičiavimo varikliui ir jo priklausomybių grafui, kad perskaičiuotų minimalų paveiktų langelių rinkinį. Jei perrašytoje formulėje dabar yra „#REF!“, perskaičiavimas iškart parodys klaidos reikšmę, užuot palikęs pasenusį skaičių faile
Formulių nuorodų pritaikymas tiekiamas kaip standartinė XLSX variklio elgsena „HotXLS Delphi Excel Component“ komponente, skirtame Delphi ir C++Builder; produkto puslapyje pateikiama visa darbalapio redagavimo API dokumentacija, įskaitant čia parodytus įterpimo ir trynimo metodus