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. Всички останали свойства, цветът на запълване, stop-if-true и самият оператор, се пренасят непроменени към новия обект на правило, така че и двете половини продължават да оцветяват клетките по същия начин
// 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 би отхвърлил без обяснение, като така библиотеката отказва да създаде повредена работна книга, а не поставя ограничение, до което обичайната употреба вероятно ще достигне
Какво да проверите след масово вмъкване или изтриване
Двете неща, които си струва да проверите, след като скрипт изпълни пакет от промени по редове или колони в лист, пълен с условни формати и проверки, са общият брой правила и редът на приоритетите, защото и двата могат да се променят по начини, които лесно се пропускат при преглед на кода, но стават очевидни в Manage Rules на Excel. Една промяна рядко причинява големи щети: едно вмъкване в средата на правило cellIs създава най-много два обекта на правила там, където преди е имало един. Рискът се натрупва, когато процедура за генериране на отчет вмъква редове един по един в цикъл върху лист, който вече съдържа няколко правила с формулни котви: всеки проход може отново да раздели правила, които предишен проход вече е разделил, и пет първоначални правила cellIs могат да се превърнат в няколко пъти повече фрагменти с ниска стойност, покриващи малки части от оригиналния диапазон. Пакетирането на структурните промени, тоест вмъкването на целия нов блок с едно извикване вместо ред по ред, поддържа броя правила свързан с броя на действително различните котви, а не с броя на изпълнените промени
Разделянето на правилата и нормализацията на приоритетите са стандартно поведение на XLSX ядрото в компонента HotXLS за Excel на Delphi за Delphi и C++Builder; продуктовата страница съдържа пълния API справочник за редактиране на работни листове, включително описаните тук методи за условно форматиране и проверка на данни