HotXLS автоматически корректирует ссылки в формулах при вставке или удалении строк и столбцов на листе XLSX. Методы движка InsertRows, DeleteRows, InsertCols и DeleteCols перезаписывают каждую оставшуюся формулу так, чтобы ее ссылки A1 (относительные, абсолютные и диапазоны) продолжали указывать на те же данные после изменения структуры, а ссылки на удаленный блок превращались в ошибку #REF!, что полностью соответствует поведению Excel
Баг, который это предотвращает, является одним из самых незаметных при генерации отчетов. Генератор записывает ежедневные показатели в диапазон C2:C9 и помещает формулу =SUM(C2:C9) под ними, а на следующем шаге вставляет строку заголовка сверху. Если движок сдвинет только значения ячеек, оставив текст формулы неизменным, сумма SUM продолжит считывать C2:C9, тогда как данные сместились в C3:C10. В итоге итоговая сумма незаметно отбросит последний день и дважды учтет заголовок. Исключений не возникает, файл открывается без проблем, а значение оказывается неверным. До версии 2.160 движок HotXLS XLSX оставлял формулы без изменений при изменении структуры. Начиная с 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Стоит знать несколько деталей о работе сканера. XlsxAdjustFormulaRowColRefs распознает ссылки на отдельные ячейки во всех четырех формах закрепления (A1, $A1, A$1, $A$1) и двухугольные диапазоны вида A1:B3, корректируя каждую границу независимо. Формулы, текст которых уже начинается с # (маркер ошибки от предыдущей правки), пропускаются во избежание повторного сканирования. Кроме того, InsertCols имеет свою приятную деталь совместимости с Excel: вновь вставленные столбцы наследуют ширину своего левого соседа, что аналогично поведению команды "Вставить столбцы листа" в самом Excel
Когда удаленная ссылка превращается в #REF!?
Правило смещения для индекса отдельной строки или столбца имеет три исхода. Индекс до точки редактирования остается неизменным. Индекс в точке редактирования или после нее сдвигается на величину смещения. А при удалении индекс, попадающий внутрь удаленного блока, теряет смысл (ячейка исчезла), поэтому сканер переписывает всю ссылку как #REF!. Для ссылки на диапазон обе конечные точки обрабатываются по тому же правилу, и если хотя бы одна из них попадает в удаленный блок, вся ссылка перезаписывается как #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Выдача явной ошибки #REF! вместо незаметного перенаправления — это правильный компромисс, и именно так поступает Excel. Формула, указывающая на соседнюю ячейку после удаления ее реального источника данных, возвращала бы правдоподобное на вид число. Ошибка #REF! распространяется по зависимым формулам и сразу же обнаруживается при первом же простейшем тесте. Такое же преобразование применяется и по оси столбцов
// 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 goneКакие формы ссылок не перезаписываются?
Сканер нацелен на ссылки 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; на странице продукта представлено полное справочное руководство по редактированию листов, включая описанные здесь методы вставки и удаления