Условное форматирование и rich text в HotXLS для Delphi

Правило условного форматирования в OOXML — это две отдельные вещи под одним именем. Условие (сравнение, формула, совпадение текста) решает, какие ячейки подходят. Оформление (запись дифференциального формата, dxf в терминах ECMA-376) решает, как эти ячейки выглядят. Диалог Excel прячет этот шов, заставляя вас заполнять оба сразу. HotXLS так не делает. Создайте правило cellIs из Delphi и пропустите стиль — и правило будет корректным, диапазон верным, формула истинной ровно на нужных ячейках, а цвет не изменится ни у чего, потому что указание правила гласило: «истина, ничего не рисовать». Этот разрыв между условием и следствием — первое, в чём надо разобраться, и им объясняется большинство правил, которые выглядят правильными в диспетчере правил и при этом ничего не подсвечивают

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);

    // Повторяющиеся номера заказов помечаются тем же способом
    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 разрешает конкурирующие правила в порядке приоритета. Когда два правила хотят закрасить одну ячейку, победителя определяет заданное вами число, а не тот порядок, в котором рецензент случайно прокрутит диалог диспетчера правил

Относитесь к приоритету так, как графический редактор относится к порядку слоёв. Назначайте его осознанно везде, где два правила могут дотянуться до одних и тех же ячеек, и оставляйте промежутки между значениями, чтобы более позднее правило встало на место без перенумерации остальных. Там, где правила столкнуться не могут, скажем гистограмма, запертая в столбце E, и текстовое правило, запертое в столбце G, порядка создания достаточно, и приоритет внимания не стоит. Потратьте это внимание на границы диапазонов, потому что дорогие ошибки здесь почти никогда не связаны с инверсией приоритетов. Это диапазоны вроде B2:B200 в отчёте, который дорос до 350 строк, где непокрытый хвост отображается обычными ячейками, выглядящими ровно как здоровые данные. Выводите каждый диапазон правила из того же значения итогового количества строк, которое управляет рядами диаграмм и диапазонами проверок в остальной книге, — и хвост перестанет отваливаться

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

Rich text: много форматов внутри одной ячейки

Ячейка с форматированным текстом в модели 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;   // владение переходит к ячейке: не вызывайте Free
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