Умовне форматування, Rich Text та стилі комірок у Delphi з HotXLS

Правило умовного форматування в OOXML — це дві окремі речі під однією назвою. Умова (порівняння, формула, збіг тексту) вирішує, які клітинки їй відповідають. Вигляд (запис диференційного формату, dxf у термінах ECMA-376) вирішує, як ці клітинки виглядатимуть. Діалог Excel ховає цей шов, змушуючи заповнити обидві частини заразом. HotXLS цього не робить. Створіть правило cellIs з Delphi й пропустіть стиль — правило буде дійсним, діапазон правильним, формула дасть true рівно на потрібних клітинках, і жодна не змінить кольору, бо вказівка правила звучала як «істина, не малюй нічого». Ця щілина між умовою та наслідком — перше, що треба зробити правильно, і саме вона пояснює більшість правил, які в діалозі керування правилами мають бездоганний вигляд, але нічого не підсвічують

HotXLS записує умовне форматування нативно і в BIFF8 .xls, і в OOXML .xlsx, і так само чинить із фрагментами форматованого тексту та пуловою моделлю стилів клітинок. Ці три можливості мають більше спільної проводки, ніж підказує пласка поверхня API, а місця, де результат розходиться з наміром, — це зазвичай саме стики між ними

Умові потрібен наслідок: стиль dxf

На аркуші XLSX правила порівняння створює AddConditionalFormat: він бере діапазон, оператор із TXLSXCfOperator та формулу чи літерал і повертає індекс нового правила в колекції ConditionalFormats аркуша. Об’єкт правила за цим індексом відкриває властивість Style, і саме там живе підсвічування. Задайте йому заливку — і клітинки, що підпадають під умову, її отримають. Лишіть її незайманою — і ви побудували те саме невидиме правило, описане вище

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Idx: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('kpi.xlsx');
    Sheet := Book.Sheets[0];

    // Від’ємне відхилення: світло-червона заливка
    Idx := Sheet.AddConditionalFormat('D2:D200', xlsxCfOpLessThan, '0');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    // Дублікати ID замовлень позначаються так само
    Idx := Sheet.AddCondFormatDuplicateValues('A2:A200');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFEB9C);

    // Власне правило-формула: підсвітити рядки, де факт не дотягує до 90% цілі
    Idx := Sheet.AddCondFormatExpression('B2:B200', '$C2<$B2*0.9');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    Book.SaveAs('kpi-flagged.xlsx');
  finally
    Book.Free;
  end;
end;

Кольори тут — 32-бітні значення ARGB, тож $FFFFC7CE — це той самий «світло-червоний» Excel, який ви знаєте з діалогу, з повністю непрозорим байтом альфи попереду RGB. Кожен вид правила, що спрацьовує на поклітинковій умові, має ту саму форму «створи, потім застилюй». Текстові зіставлювачі (AddCondFormatContainsText, AddCondFormatBeginsWith, AddCondFormatEndsWith) повертають індекс, який ви стилюєте згодом, і так само чинять AddCondFormatTop10, AddCondFormatAboveAverage та детектори порожніх клітинок і помилок. Вивчіть цей шаблон один раз — і вся текстово-порівняльна родина поводитиметься однаково

Гістограми, кольорові шкали та набори значків малюють себе самі

Візуальні види правил працюють навпаки. Вони несуть свій вигляд усередині означення правила й цілком ігнорують властивість Style. Призначте заливку правилу з гістограмою — і нічого не станеться, що читається як вада, доки не клацне таксономія: AddCondFormatDataBar бере колір смуги прямим аргументом, дво- і триточкові кольорові шкали так само беруть кольори своїх кінцевих точок, а AddCondFormatIconSet обирає один із 26 типів наборів значків, як-от icsTrafficLights3. Тут немає окремого запису стилю, який можна забути, бо окремого запису стилю немає взагалі

Параметри, над якими варто подумати в цих викликах, — це прив’язки значень, типізовані як TXLSCfValueKind. Кінцева точка смуги чи шкали може сидіти на мінімумі або максимумі діапазону, на літеральному числі, на відсотку чи процентилі або на результаті формули. Усталені значення, мінімум і максимум діапазону, гарно поводяться на охайних демонстраційних даних, а потім зраджують на реальних даних із викидами: одне значення, що втекло вгору, розтягує шкалу й сплющує всі інші смуги до огризків. Коли інформаційну панель мають читати через кілька періодів, прив’яжіть кінцеві точки натомість до фіксованих чисел або процентилів, щоб половина смуги в березні означала ту саму величину, що й половина смуги у квітні. Автомасштабована смуга порівнянна лише сама із собою

Записувач XLS охоплює чотири види правил, не більше

Спадковий бік BIFF8 — не зменшене дзеркало боку XLSX; це навмисна підмножина. Фасад XLS уміє створювати рівно чотири форми умовних правил — гістограми, двоколірні шкали, триколірні шкали й набори значків, — що виводяться в потік як записи CF12. Він не має API створення для правил cellIs, виразів чи тексту. Правила таких видів, що вже живуть у файлі, який ви відкриваєте, читаються, зберігаються й записуються назад без змін, тож відкриття та перезбереження клієнтського .xls ніколи не псує форматування, з яким той файл прийшов. Чого зробити не вийде — це згенерувати порогове підсвічування з нуля в .xls. Вибір там такий: або підробити його звичайними заливками клітинок, обчисленими в коді, або зробити результатом .xlsx, де доступна вся родина правил

Це обмеження варто владнати до того, як з’явиться шар даних, а не після, бо воно змінює рішення про формат файлу для всього, що має форму інформаційної панелі. Команда, яка обрала .xls заради сумісності, а потім описує в специфікації KPI-звіт із порогами cellIs, обрала дві речі, що не поєднуються, і дешевше помітити це на етапі вибору формату, ніж на третьому тижні розробки

Накладання правил, пріоритет і діапазони, що перетинаються

Справжні інформаційні панелі рідко обходяться одним правилом на діапазон. Стовпець відхилень може нести гістограму для величини, правило cellIs для жорсткого порогу й правило-вираз рівня рядка над обома для ескалацій. Кожен TXLSXConditionalFormat відкриває значення Priority, і Excel розв’язує конкуренцію правил у порядку пріоритету. Коли два правила хочуть замалювати ту саму клітинку, переможця визначає число, яке задали ви, а не той порядок, у якому рецензент випадково прогортає діалог керування правилами

Ставтеся до пріоритету так, як графічний редактор ставиться до z-порядку. Призначайте його свідомо всюди, де два правила можуть дістати до тих самих клітинок, і лишайте проміжки між значеннями, щоб пізніше правило вставилося без перенумерування решти. Там, де правила зіткнутися не можуть, скажімо гістограма, замкнена в стовпці E, і текстове правило, замкнене в стовпці G, порядку створення досить, а пріоритет уваги не вартий. Витратьте цю увагу натомість на межі діапазонів, бо дорогі вади тут майже ніколи не є інверсіями пріоритету. Це діапазони на кшталт B2:B200 у звіті, що виріс до 350 рядків, де непокритий хвіст промальовується як звичайні клітинки, які виглядають точнісінько як здорові дані. Виводьте діапазон кожного правила з того самого значення кількості останнього рядка, яке керує рядами діаграм і діапазонами перевірки даних деінде в книзі, — і хвіст перестане відпадати

Одна перевірочна звичка себе виправдовує. Після генерації відкрийте файл в Excel, виділіть форматований діапазон і пройдіться діалогом керування правилами один раз на кожну зміну шаблону. Умовне форматування — одна з небагатьох ділянок, де єдиний авторитетний рушій промальовування — це застосунок, який споживає файл, тож модульний тест над XML доводить, що правило було записане, а не що Excel малює його так, як ви задумали. Хвилина огляду очима закриває цю щілину

Форматований текст: багато форматів усередині однієї клітинки

Клітинка з форматованим текстом у моделі XLSX містить список фрагментів, де кожен фрагмент — це відтинок тексту плюс власні атрибути шрифту. Ви будуєте цей список збоку як об’єкт TXLSXRichText, додаєте до нього фрагменти, а потім чіпляєте все разом до клітинки. Кусається саме правило володіння. Присвоєння до Cell.RichText передає володіння цим об’єктом клітинці, і клітинка звільняє його під час власного руйнування. Звільніть його ще й самі — і матимете подвійне звільнення, з тих, що мовчать протягом запуску, який їх спричинив, і виринають збоєм десь зовсім в іншому місці набагато пізніше

var
  Rich: TXLSXRichText;
  Run: TXLSXRichTextRun;
begin
  Rich := TXLSXRichText.Create;
  Rich.AddRunText('Status: ');
  Run := Rich.AddRunText('OVERDUE');
  Run.Bold := True;
  Run.Color := $FFC00000;
  Run.ColorIsAuto := False;
  Run := Rich.AddRunText(' (escalated to regional manager)');
  Run.Italic := True;
  Sheet.Cells[2, 7].RichText := Rich;   // володіння переходить до клітинки: не звільняйте
end;

Явне ColorIsAuto := False — не необов’язкова оздоба. Фрагмент несе прапорець автоматичного кольору, і присвоєння кольору береться до уваги лише після того, як цей прапорець знято. Задайте Color і забудьте про ColorIsAuto — і фрагмент вийде жирним, але вперто чорним, без жодної помилки, яка вказала б на причину. Фрагменти також підтримують перекреслення, варіанти підкреслення та вертикальне вирівнювання для верхнього й нижнього індексів, тоді як PlainText сплющує весь список назад в один рядок, коли треба експортувати або порівняти текстовий вміст

Форматований текст на рівні клітинки доступний лише в XLSX. Фасад XLS не має публічного API для його запису, хоча фрагменти там доступні на примітках і текстових полях через TextRuns, а форматовані рядки, прочитані з наявного .xls, переживають цикл читання-запису неушкодженими. Тяжіння те саме, що й з умовним форматуванням: усе, що змішує формати всередині клітинки, належить записувачу XLSX

Пул стилів і зсув на одиницю, який доїжджає до релізу

Звичайне стилювання клітинок у моделі XLSX іде через пулові колекції на книзі. Fonts.Add, Fills.AddSolid і Borders.Add кожен реєструє означення й повертає його індекс у пулі. Ці індекси нумеруються з нуля. Властивості з боку клітинки, які їх споживають, як-от FontIndex, резервують 0 під «усталене», тож значення, яке ви присвоюєте клітинці, — це індекс у пулі плюс одиниця:

HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);  // індекс у пулі, від нуля
for Col := 1 to 6 do
  Sheet.Cells[1, Col].FontIndex := HeaderFont + 1;          // індекс клітинки, від одиниці

Приберіть + 1 — і кожен заголовок відкотиться до усталеного шрифту. Ні винятку, ні попередження, лише книга, яка має вигляд такої, що її ніхто не стилював. Помилка другого порядку ховається в циклі: виклик Fonts.Add раз на кожен рядок. Однакові означення шрифтів дедуплікуються, тож файл не псується, але робота витрачається намарно, а пул вирівнювання зокрема віддає свіжий об’єкт на кожен виклик, замість згортати дублікати. Побудуйте ту жменю стилів один раз перед циклом і перевикористовуйте їхні індекси. На звітах у сто тисяч рядків ця єдина зміна — один із важелів, розглянутих у статті налаштування продуктивності великих книг для HotXLS. Коли вам потрібен лише стандартний семантичний вигляд, обидва фасади відкривають ApplyBuiltinStyle на діапазонах, що відображається на вбудовані стилі Excel Good, Bad, Neutral та акцентні, і вам не доводиться чіпати пули взагалі

Умовне форматування, форматований текст і пулові стилі — це остання миля звіту, застосована вже після того, як модель даних і компонування владнані, а ці ранніші етапи є темою статті генерація звітів на основі шаблонів із HotXLS. Повний довідник правил, фрагментів і стилів живе на сторінці продукту HotXLS Delphi Component