HotXLS автоматично коригира препратките във формули, когато вмъквате или изтривате редове или колони в XLSX работен лист. Методите на ядрото InsertRows, DeleteRows, InsertCols и DeleteCols пренаписват всяка останала формула, така че нейните препратки в нотация A1 – относителни, абсолютни и диапазони – да продължат да сочат към същите данни след структурната промяна, а препратките към изтрит блок се преврят в #REF!, съвпадайки с поведението на Excel
Грешката, която това поведение предотвратява, е една от най-трудните за улавяне при генериране на отчети. Програма записва ежедневни данни в диапазона C2:C9 с формула =SUM(C2:C9) под тях, а след това се вмъква заглавен ред най-отгоре. Ако ядрото премести само стойностите на клетките и остави непроменен текста на формулата, тогава SUM все още ще чете C2:C9, докато данните вече се намират в C3:C10 – така че общата сума тихомълком ще изпусне последния ден и ще брои двойно заглавния ред. Нищо не предизвиква изключение, файлът се отваря нормално, но числото е просто грешно. Преди версия 2.160 XLSX ядрото на HotXLS оставяше формулите непроменени при структурни редакции; от версия 2.160 нататък актуализацията е автоматична и няма опция, която трябва да се настройва
Какво се случва с формулите при вмъкване на ред в Excel?
Правилото на Excel е, че препратките следват данните, а не адресите. При вмъкване на ред всяка препратка, чийто индекс на ред се намира на или под точката на вмъкване, се премества надолу с броя на вмъкнатите редове; препратките, намиращи се изцяло над точката на вмъкване, не се променят. Изтриването на редове изпълнява същото правило в обратна посока: препратките под изтрития блок се изместват нагоре, а препратките към самия изтрит блок се превръщат в #REF!, тъй като клетките, към които са сочили, вече не съществуват. Колоните се държат по идентичен начин по другата ос. Библиотека за електронни таблици, която трябва да гарантира коректността на резултатите за потребителите на Excel, трябва да пресъздаде точно тази механика, тъй като потребителите разсъждават за формулите си по този начин, без дори да се замислят
Частта, която изненадва разработчиците, е че абсолютните препратки също се изместват. Знаците $ в $B$2 контролират какво се случва, когато формула се копира или запълва в друга клетка – те не влияят при структурни редакции. Вмъкнете ред над ред 2 и Excel ще пренапише $B$2 в $B$3 с непроменени знаци за долар, тъй като стойността, от която зависи формулата, физически се е преместила на ред 3. Ядро, което измества само относителните препратки, би повредило точно формулите, които хората свързват най-съзнателно. HotXLS измества и двете форми и запазва знаците $ в пренаписания текст
Как HotXLS измества автоматично препратките на формули?
И четирите метода за структурна редакция в TXLSXWorksheet делегират задачата на един общ геометричен модул: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). Методът InsertRows(BeforeRow, Count) го извиква с положителна промяна на редовете, DeleteRows(StartRow, Count) – с отрицателна промяна, а методите за колони правят същото по оста на колоните. Процедурата първо премества самите клетки – премахвайки всяка клетка, попадаща в рамките на изтрит блок – и след това обхожда всяка останала клетка с формула и подава нейния текст на XlsxAdjustFormulaRowColRefs – скенер, който намира препратките в стил A1 и пренаписва техните компоненти за ред и колона спрямо изместването. Същият процес премества обединените диапазони, хипервръзките, коментарите, изображенията, диаграмите, условните формати, валидирането на данни и табличните диапазони, така че целият лист се премества като една структура
// 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 dataКога изтрита препратка се превръща в #REF!?
Правилото за изместване при единичен индекс за ред или колона има три възможни резултата. Индекс преди точката на редакция остава непроменен. Индекс в или след точката на редакция се премества спрямо делтата. При изтриване индексът, който попада в изтрития блок, няма съответстваща нова стойност – клетката е изчезнала – така че скенерът пренаписва цялата препратка като #REF!. При препратка за диапазон и двете крайни точки се превеждат по същите правила, и ако някоя от тях попадне в изтрития блок, препратката се пренаписва изцяло като #REF!, вместо да бъде оставена частично валидна
Генерирането на грешка #REF! вместо скрито пренасочване е правилното решение и е точно това, което прави Excel. Формула, която сочи към съседна клетка след изтриване на нейната реална входна стойност, би върнала измамно вярно число; грешката #REF! се разпространява по зависимите формули и се забелязва лесно при първия тест. Същото конвертиране се прилага и по оста на колоните
// 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 -> A7Кои форми на препратки не се пренаписват?
Скенерът е насочен към препратки в стил A1 в същия работен лист с ясна форма от буква за колона и номер на ред, като е важно да посочим какво остава извън този обхват. Препратки за цели колони като A:A и за цели редове като 1:1 не съдържат единия от двата компонента, така че скенерът ги оставя непроменени. Структурираните препратки към таблици (Table1[Amount]) също остават непокътнати. Промяната се извършва строго в нотация A1 – ако вашият код изгражда формули в стил R1C1, конвертирайте ги към A1 преди структурна редакция, както е описано в съпътстващата статия за R1C1 формула нотация в Delphi
Конструкциите между различни листове и на ниво работна книга се обработват от отделни процеси, а не от скенера на клетки. След коригиране на редактирания лист, методът ShiftSheetGeometry предава същата геометрична промяна към формули на други листове, рефериращи към променения лист, към диапазони от данни в диаграми, към вътрешни цели на хипервръзки и дефинирани имена. Дефинираните имена получават допълнителна защита на ниво жизнен цикъл на листа: от версия 2.150 изтриването на работен лист пренаписва всеки префикс SheetN! във формулата на дефинирано име до #REF!, а преименуването на лист актуализира префикса с новото име, така че имената никога не сочат към несъществуващ лист. Начинът на работа на имена и формули между различни листове е разгледан в статията за дефинирани имена и формули между листове
Преизчисляване след изместването
Коригирането на препратките пренаписва само текста на формулата; то не преизчислява резултатите. След структурна редакция кешираните стойности на формулите описват старата геометрия, затова вярната последователност е: първо извършете всички вмъквания и изтривания, след това извикайте преизчисляване веднъж и запишете файла. Извършването на промяната преди преизчисляване поддържа коректна информацията за зависимостите – всяка пренаписана препратка сочи към своя реален източник, което е необходимо на ядрото за инкрементално преизчисляване и графа на зависимостите за актуализиране на минималния набор от засегнати клетки. Ако пренаписана формула вече съдържа #REF!, преизчисляването веднага ще покаже грешката, вместо да остави стара стойност във файла
Коригирането на препратки във формули се предлага като стандартно поведение на XLSX ядрото в HotXLS Delphi Excel Component за Delphi и C++Builder; продуктовата страница съдържа пълната документация за приложния програмен интерфейс (API) за редактиране на работни листове, включително представените тук методи за вмъкване и изтриване