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 у компоненті HotXLS Delphi Excel для Delphi та C++Builder; сторінка продукту містить повний довідник API редагування аркуша, включно з методами умовного форматування та перевірки даних, описаними тут