HotXLS, компонент Excel для Delphi и C++Builder, автоматически разбивает правило условного форматирования или проверки данных на два или более отдельных объекта правил всякий раз, когда вставка или удаление строки либо столбца разрезает покрываемый правилом диапазон на части, которым нужны разные относительные привязки формул, а затем заново присваивает каждому правилу условного форматирования свежий, уникальный номер приоритета. Это поведение появилось в версии 2.196 движка XLSX и работает автоматически, без настройки для отключения. Триггер узкий, но распространённый: правило cellIs или expression, чья формула читает ячейку относительно собственного диапазона, находящееся на листе, где позже где-то посередине этого самого диапазона вставляется или удаляется строка
Большинство описаний автоматизации Excel останавливаются на проблеме текста формул: сдвинуть номера строк и столбцов внутри каждого SUM() и каждого VLOOKUP(), чтобы ссылки по-прежнему указывали на нужные ячейки. Эта половина истории реальна и описана в сопутствующей статье о том, как HotXLS переписывает ссылки формул при перемещении строк и столбцов, но правило условного форматирования или проверки данных — это не просто формула, сидящая в ячейке. Оно сочетает формулу с диапазоном — sqref в терминах ECMA-376, — и оба должны перемещаться вместе. Когда структурное изменение разрезает этот диапазон на две части, которым для сохранения корректности потребовались бы два разных относительных смещения, сохранение одного объекта правила с одной строкой формулы перестаёт быть вариантом, и притворяться иначе — это способ, которым правило выделения незаметно начинает сравнивать не те строки
Почему вставка строки разбивает правило условного форматирования, а не просто перемещает его?
Правило условного форматирования или проверки данных хранит ровно одну формулу для всего своего диапазона, вычисляемую относительно единственной ячейки-якоря, так что как только изменение вынуждает две части этого диапазона нуждаться в двух разных относительных смещениях, одна формула больше не может корректно описывать обе части. ECMA-376 выражает покрытие правила атрибутом sqref на элементе conditionalFormatting или dataValidation, а Excel вычисляет Formula1 и Formula2 так, как если бы текст был введён в верхнюю левую ячейку этого sqref и протянут на весь остальной диапазон, точно так же, как обычная относительная формула протягивается вниз по столбцу. Представьте выделение отклонений над B2:B50, помечающее любую фактическую цифру, превышающую бюджет, построенное как правило cellIs, чья Formula1 — буквальный текст C2, то есть сравнить ячейку B текущей строки с ячейкой C той же строки
Idx := Sheet.AddConditionalFormat('B2:B50', xlsxCfOpGreaterThan, 'C2');
Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);
Sheet.InsertRows(25, 1); // one blank separator row, starting at old row 25Вставьте эту одну разделительную строку на месте старой строки 25 — и строки выше точки вставки не сдвинутся, так что их доля правила по-прежнему корректно читает Formula1 как C2. Строки, которые раньше были с 25-й по 50-ю, съезжают вниз, становясь с 26-й по 51-ю, и для них C2 теперь совершенно неправильная ячейка, поскольку строке 26 нужно сравнение с C26, а не с бюджетной цифрой на два десятка строк выше
Как HotXLS решает, нужно ли правилу разбиение
HotXLS создаёт дополнительные объекты правил только тогда, когда геометрия этого действительно требует: внутренняя процедура XlsxBuildShiftedRuleParts обходит каждую непересекающуюся область в sqref правила, вычисляет, какой была ячейка-якорь этой области до изменения и какой становится после, и проверяет, потребует ли каждый получившийся фрагмент одинаковой поправки на относительное смещение. Если все фрагменты согласуются, сохраняется одно правило, чей sqref перестраивается как объединение сдвинутых фрагментов, а формула перебазируется один раз. Настоящее разбиение происходит только тогда, когда фрагменты расходятся, — именно случай B2:B50 выше, где верхний блок сохраняет исходный якорь, а нижнему нужен новый
Перебазирование формулы фрагмента — это двухшаговый процесс, переиспользующий механизм, который HotXLS уже несёт для групп общих формул OOXML: сначала формула транслируется так, как если бы изначально была привязана к собственной верхней левой ячейке этого фрагмента, с использованием той же арифметики относительного смещения, что разворачивает общую формулу по её диапазону, затем результат проходит через тот же сканер сдвига строк и столбцов, что переписывает обычные формулы листа. Именно так Formula1 переходит от C2 к C26 за два шага, а не через один написанный вручную частный случай: транслировать C2 вперёд на 23 строки, чтобы получить C25, как если бы правило всегда начиналось там, а затем позволить обычному сдвигу на строке 25 продвинуть её дальше, к C26. Все остальные свойства — цвет заливки, «остановиться, если истинно», сам оператор — переносятся на новый объект правила без изменений, так что обе половины продолжают закрашивать ячейки тем же цветом, что и всегда
// ConditionalFormats now holds two rules instead of one:
// B2:B25 Formula1 = 'C2' (rows above the insert)
// B26:B51 Formula1 = 'C26' (rows that shifted down)Разбиваются ли гистограммы данных и наборы значков так же, как правила cellIs?
Нет: HotXLS разбивает на части только те виды правил, чья корректность действительно зависит от относительной формулы по регионам, — сравнения cellIs и правила expression, — оставляя каждый другой вид условного формата единственным объектом правила, чей sqref просто разрастается, чтобы охватить сдвинутые фрагменты как объединение из нескольких областей. Внутри это простая проверка Kind, cf.Kind in [cfkCellIs, cfkExpression], ничего более экзотичного. Гистограммы данных, двух- и трёхцветные шкалы, наборы значков, ранжирования верхних и нижних значений, а также детекторы дубликатов, пустых значений и ошибок несут в себе полезную нагрузку — цвет столбца, набор точек шкалы, семейство значков, — которая описывает весь покрываемый диапазон целиком, а не относительное сравнение по ячейкам, так что разбиение их на несколько приоритизированных объектов правил не дало бы никакого выигрыша в корректности, а лишь добавило бы правил для управления. Когда изменение делит их диапазон, HotXLS вновь объединяет фрагменты в одно правило с многообластным sqref и заново привязывает полезную нагрузку как единое целое, вместо того чтобы клонировать новый объект правила на каждый фрагмент. Это различие согласуется с таксономией видов правил в статье об основах условного форматирования и форматированного текста: гистограммы данных, цветовые шкалы и наборы значков уже отличаются от правил cellIs тем, что полностью игнорируют свойство Style, и теперь выясняется, что они отличаются и в отношении перепривязки по регионам по той же самой причине
Почему приоритеты правил меняются после структурного изменения?
Приоритеты меняются потому, что каждый клон изначально получает точно то же значение приоритета, что и правило, из которого он разбит, а HotXLS впоследствии выполняет проход нормализации, разрешающий получившиеся дубликаты в чистый, без пропусков, порядок вместо того, чтобы оставлять два правила с одинаковым рангом. Вторая внутренняя процедура, XlsxNormalizeConditionalFormatPriorities, берёт текущий приоритет каждого условного формата, откатывается к позиции этого правила в коллекции для любого правила, у которого приоритет никогда явно не устанавливался, стабильно сортирует весь список так, чтобы совпадающие значения сохраняли исходный относительный порядок, и перенумеровывает отсортированный результат в плотную последовательность 1, 2, 3 без пропусков и повторов. HotXLS выполняет это один раз перед началом сдвига, так что клонирование начинается с чистой отправной точки, и снова после каждого разбиения и удаления каждого опустевшего правила, так что в сохранённом файле никогда не окажется двух записей правил, претендующих на один и тот же приоритет. Это важно, если вы следовали совету из статьи об основах условного форматирования оставлять пропуски между значениями приоритета, чтобы более позднее правило могло встать между ними без перенумерации остальных: эти пропуски сохраняются до тех пор, пока следующее изменение строки или столбца не затронет этот лист, а затем схлопываются, потому что нормализация гарантирует только уникальность и стабильный порядок, но не то, что ваша исходная схема нумерации вернётся неизменной
Правила проверки данных тоже разбиваются, но без приоритета для перенумерации
Правила проверки данных проходят через ту же логику разбиения диапазона, что и условные форматы cellIs и expression, и, в отличие от условного форматирования, каждый тип проверки проходит этим путём единообразно: у HotXLS нет отдельного не-формульного семейства для проверки данных, как есть гистограммы данных и наборы значков для условного форматирования, так что простое правило списка или целого числа разбивается той же самой процедурой, что обрабатывает относительную пользовательскую формулу. Отличается приоритет: ECMA-376 вообще не даёт элементу dataValidation атрибута priority, так что для проверок нет шага перенумерации, как есть для условных форматов. Представьте проверку с пользовательской формулой, которая не позволяет фактической сумме каждой строки превысить её собственный бюджет в соседнем столбце
Sheet.AddCustomValidation('D2:D400', 'D2<=C2');
Sheet.DeleteRows(150, 5); // remove five rows out of the validated range
// DataValidations now holds two rules instead of one:
// D2:D149 Formula1 = 'D2<=C2' (rows above the deletion)
// D150:D395 Formula1 = 'D150<=C150' (rows that shifted up)Это важно по той же причине, по которой статья об основах проверки данных предостерегает от прикрепления правила до того, как число строк станет окончательным: проверка покрывает только буквальные ячейки, которые вы ей задали, а последующее структурное изменение может оставить два или более правил, выполняющих работу, которую раньше выполняло одно. Функционально ничего не ломается: каждая ячейка исходного диапазона по-прежнему чем-то проверяется, но код, предполагающий одну запись DataValidations на столбец, начнёт неправильно индексировать после первого же изменения, которое его затронет. Здесь есть жёсткий потолок того, насколько далеко это может зайти: если разбиение вытолкнуло бы лист за пределы 65 534 правил проверки данных, HotXLS выбрасывает исключение вместо того, чтобы записать файл, который Excel молча отклонит, — это библиотека отказывается создавать повреждённую книгу, а не предел, который вероятно достижим при обычном использовании
Что проверить после массовой вставки или удаления
Две вещи, которые стоит проверить после того, как скрипт выполнит пакет изменений строк или столбцов на листе, полном условных форматов и проверок, — это общее число правил и порядок приоритетов, поскольку оба могут «поплыть» способом, который легко упустить при код-ревью и который становится очевидным в тот момент, когда кто-то открывает «Управление правилами» в Excel. Одно изменение редко наносит большой урон: одна вставка посередине одного правила cellIs производит не более двух объектов правил там, где было одно. Риск накапливается, когда процедура генерации отчётов вставляет строки по одной в цикле на листе, уже несущем несколько привязанных к формулам правил: каждый проход может заново разбить правила, уже разбитые предыдущим проходом, и пять исходных правил cellIs могут в итоге превратиться в в несколько раз большее число малоценных фрагментов, покрывающих обрывки исходного диапазона. Пакетирование структурных изменений — вставка всего нового блока одним вызовом вместо построчной вставки — удерживает число правил привязанным к числу действительно различных якорей, а не к числу выполненных изменений
Разбиение правил и нормализация приоритетов поставляются как стандартное поведение движка XLSX в компоненте Excel для Delphi HotXLS для Delphi и C++Builder; на странице продукта представлен полный справочник API редактирования листов, включая описанные здесь методы условного форматирования и проверки данных