Вычисление условного форматирования Excel в Delphi с HotXLS

Правило Top 10 percent стоит на колонке из 27 цифр продаж, и Excel закрашивает три ячейки. Десять процентов от 27 — это 2.7, значит рендерер с округлением получит три, рендерер с потолком — три, а рендерер с floor — две. Две догадки из трёх совпадут с Excel на этой колонке, и обе неверны: Excel берёт count с floor до двух, а затем закрашивает ещё и третью ячейку, потому что она делит ничью со второй на значении 88. Поменяйте одно значение — и удачные догадки перестанут совпадать

В этом вся сложность отрисовки условного форматирования вне Excel. Правила в файле есть; ответа на вопрос «какие ячейки загораются и каким цветом» в нём нет. Если ваш вывод — HTML-отчёт, PDF или сетка, которую вы рисуете сами, вычислять придётся вам, и именно агрегатные семейства правил прячут в себе арифметику. Создание правил — отдельная тема, её покрывает статья о условном форматировании и стилях форматированного текста, а что происходит с диапазоном правила при сдвиге строк — статья о разбиении привязанных условных форматов. Эта заметка — про вычисление

О чём элемент conditionalFormatting говорит и о чём молчит

ECMA-376 и ISO 29500-1 определяют хранение и на этом останавливаются. Элемент conditionalFormatting (§18.3.1.18) несёт sqref и список дочерних cfRule (§18.3.1.10). У каждого правила есть type, необязательный operator, priority, флаг stopIfTrue, один или два дочерних formula и, для визуальных семейств, набор порогов cfvo. Всё это добросовестно записывает, на что пользователь нажал в диалоге. Ни один из элементов не является алгоритмом

Для многих типов правил разрыв безвреден. cellIs с operator="greaterThan" означает «больше чем», containsText — «подстрока присутствует». Беда начинается с правил, чей вердикт зависит от остального диапазона: top10 с percent="1" не говорит, как округляется дробный count, aboveAverage с stdDev="1" — отклонение выборочное или генеральной совокупности, и ни один не говорит, входит ли значение, равное отсечению. Ошибётесь в выборе — и ваш PDF разойдётся с книгой, которую клиент открыл в соседнем окне

TCondFormatRule.Evaluate: что ячейка может решить сама

У HotXLS две поверхности вычисления, и различаются они тем, на какие вопросы согласны отвечать. Классический движок (.xls) выставляет TCondFormatRule.Evaluate в lxCondFormat.pas: он принимает калькулятор, индекс листа, позицию ячейки и значение ячейки и возвращает Boolean для одного правила. Он покрывает восемь операторов cellIs (between, not between, equal, not equal, greater, less, greater or equal, less or equal), свободные правила expression, вычисляемые в позиции ячейки, чтобы относительные ссылки корректно перестраивались, четыре текстовых предиката и предикаты пустой ячейки и ошибки. Формулы порогов разрешаются в позиции ячейки, а правило between с перепутанными границами получает их переставленными, а не отвергнутыми. Текстовые сравнения нечувствительны к регистру, как и в Excel

// Классический движок: одно правило, одна ячейка, один Boolean
function AnyRuleMatches(Calculator: TXLSCalculator; CondFormat: TCondFormat;
  SheetIndex, Row, Col: Integer; const Value: Variant): Boolean;
var
  I: Integer;
begin
  Result := False;
  for I := 0 to CondFormat.RuleCount - 1 do
    // top10, средние, дубликаты, периоды времени и визуальные виды
    // отвечают здесь False: им нужен весь диапазон
    if CondFormat.Rule(I).Evaluate(Calculator, SheetIndex, Row, Col, Value) then
      Exit(True);
end;

Для top10, aboveAverage, belowAverage, duplicateValues и uniqueValues метод возвращает False. Одна ячейка не может знать, входит ли она в верхние десять процентов, не видя остальных значений, — ответ означал бы догадку. Относительные правила дат (timePeriod) на этом пути тоже отвечают False. Четыре визуальных семейства — dataBar, colorScale2, colorScale3 и iconSet — отвечают False по другой причине: они вообще не производят «да или нет», они производят цвет, длину полосы или индекс значка, и Boolean некуда их положить. Поэтому False от этого вызова означает «здесь не решено» столь же часто, как «не совпадает»: для быстрой проверки правил сравнения это нормально, для всего, что вы поставите клиентам, — небезопасно

Как вычислитель уровня листа избегает повторного сканирования листа для каждой ячейки?

Всю работу по диапазону он выполняет один раз — при создании. TXLSXConditionalFormatEvaluator в lxHandleX.pas — это неизменяемый снимок условных форматов одного листа, получаемый через TXLSXWorksheet.CreateConditionalFormatEvaluator; владеет им и освобождает его вызывающий. Наивная альтернатива, где каждая закрашиваемая ячейка запускает сканирование своего диапазона правил ради пересчёта среднего или ранга, превращает экспорт на 10 000 ячеек в 10 000 сканирований диапазона на каждое правило

При построении вычислитель разбирает каждый отдельный sqref один раз, так что десять правил на одном диапазоне делят один разбор и один проход по его заполненным ячейкам. Этот проход собирает среднее, отклонение генеральной совокупности, минимум и максимум за один обход и хранит отсортированную копию чисел только когда правило Top/Bottom или порог-процентиль действительно нуждается в статистике порядка. Правила дубликатов и уникальных значений строят и сортируют свои ключи значений один раз, так что каждая последующая проверка — это бинарный поиск. Формулы правил компилируются один раз и повторно вычисляются для каждой ячейки со смещением этой ячейки, так что =A1>AVERAGE($A$1:$A$100) ведёт свои относительные и абсолютные части в точности как в Excel, не разбираясь заново на каждую строку. Строки сгруппированы так, что EvaluateCell посещает только правила, чьи диапазоны дотягиваются до запрошенной строки

Затем правила применяются в порядке priority, от меньшего числа к большему, ничьи разбиваются позицией в файле. Каждое свойство форматирования берётся из первого совпавшего правила, которое его задаёт, а правила с более низким приоритетом заполняют только ещё пустые свойства: правило с приоритетом 1, задающее жирный, и правило с приоритетом 2, задающее красную заливку, проявятся оба, и ячейка выйдет жирной на красном. Совпадение с правилом, у которого стоит StopIfTrue, завершает обход для этой ячейки

procedure PaintConditional(Sheet: TXLSXWorksheet; Canvas: TCanvas;
  FirstRow, LastRow, FirstCol, LastCol: Integer);
var
  Evaluator: TXLSXConditionalFormatEvaluator;
  Res: TXLSXCfCellResult;
  Row, Col: Integer;
begin
  // Один снимок на проход отрисовки; строки и колонки нумеруются с единицы
  Evaluator := Sheet.CreateConditionalFormatEvaluator;
  try
    for Row := FirstRow to LastRow do
      for Col := FirstCol to LastCol do
      begin
        if not Evaluator.EvaluateCell(Row, Col, Res) then
          Continue;
        if Res.HasFillColor then
          Canvas.Brush.Color := TColor(Res.FillColor and $00FFFFFF);  // BGR, та же раскладка, что у TColor
        if Res.HasDataBar then  // DrawBar — ваша собственная процедура
          DrawBar(Row, Col, Res.DataBarAxis, Res.DataBarEnd, Res.DataBarColor);
        if Res.HasIcon then     // DrawIcon — ваша собственная процедура
          DrawIcon(Row, Col, Res.IconSetType, Res.IconIndex);
        if Res.ShowCellValue and Res.HasNumberFormat and
           Sheet.Cells.HasCell(Row, Col) then  // DrawCellText — ваша собственная процедура
          DrawCellText(Row, Col,
            Evaluator.FormatValue(Sheet.Cells[Row, Col].Value, Res.NumberFormatId));
      end;
  finally
    Evaluator.Free;
  end;
end;

Снимок строится один раз на проход намеренно. Создание его внутри внутреннего цикла компилирует и сканирует всё заново для каждой ячейки — ровно ту цену, избежать которой этот дизайн и существует. ShowCellValue возвращается False, когда правило гистограммы имеет showValue="0" или набор значков настроен показывать только значок, так что рендерер рисует полосу или глиф и оставляет число за кадром

Сколько ячеек на самом деле подсвечивает правило Top 10 percent?

Count берётся с floor, никогда не опускается ниже единицы, и каждое значение, делящее ничью с отсечением, включается. Спецификация этого не говорит; так делает Excel, и убедиться можно за минуту: введите 27 значений с рисунка в колонку, примените Top 10% и посчитайте закрашенные ячейки. TXLSXConditionalFormatEvaluator вычисляет count ранга как Floor(Count * Min(Rank, 100) / 100), поднимает его до 1, если он вышел нулём, ограничивает числом заполненных числовых ячеек, читает отсечение из отсортированных значений и затем сравнивает каждую ячейку с >=. Для колонки из 27 значений это count два, отсечение 88 и три закрашенные ячейки, потому что в двух из них лежит 88. Правила Bottom-N зеркалят это с <=

Правила above-average несут вторую неоднозначность. С stdDev="1" правило выбирает ячейки минимум на одно стандартное отклонение выше среднего, а выборочное отклонение и отклонение генеральной совокупности различаются на поправку Бесселя, что важнее всего на коротких диапазонах, где это правило реально используют. На пяти значениях они расходятся примерно на 12 процентов. HotXLS использует отклонение генеральной совокупности — ту же величину, что возвращает STDEV.P, так что быстрый способ проверить любой рендерер — положить рядом с диапазоном =AVERAGE(r)+STDEV.P(r) и посмотреть, какие ячейки его пересекают. Сравнения с полосой отклонения включающие; обычное сравнение «выше или ниже» строгое, если только правило не несёт equalAverage="1"

Правила дубликатов и уникальных значений зависят от того, что считается «тем же значением». Число 100 и текст «100» делят один ключ, потому что числовой текст нормализуется в пространство числовых ключей; текстовые ключи сравниваются без учёта регистра; TRUE и 1 остаются различны, как и ошибочные значения разных видов. Текст, содержащий * или ?, сопоставляется как шаблон с подстановочными знаками в духе COUNTIF, так что ячейка с Q* засчитывается против каждого значения, начинающегося с Q. Пустые ячейки — ловушка наоборот: по-настоящему пустая ячейка внутри диапазона никогда не закрашивается как дубликат, так что незаконченная колонка не загорается сверху донизу, а вот ячейка, хранящая пустую строку, засчитывается вместе с пустыми

Цветовые шкалы, гистограммы и наборы значков возвращаются числами

Визуальные семейства разрешаются в готовые к отрисовке значения, и именно на их гранях самописные рендереры сползают. Для цветовой шкалы EvaluateCell прижимает позицию к интервалу от 0 до 1 и интерполирует каждый канал RGB с усечением, так что значение ниже минимальной точки получает минимальный цвет, а не экстраполированный. Шкала с тремя точками использует пару «середина-максимум» для значений выше порога середины и пару «минимум-середина» в остальных случаях. Шкала, у которой первый и последний пороги разрешаются в одно число, возвращает последний цвет вместо деления на ноль. Пороги-процентили интерполируются линейно между соседними отсортированными значениями — определение PERCENTILE.INC

Гистограммы нормализуются к ширине ячейки. Для диапазона из одних положительных значений DataBarAxis равен 0, а DataBarEnd бегает между минимальной и максимальной длиной полосы правила, которые по умолчанию составляют 10 и 90 процентов, так что наименьшее значение всё же получает короткую полосу, а наибольшее никогда не заполняет ячейку до конца. Когда диапазон пересекает ноль, ось встаёт на свою пропорциональную позицию, а DataBarEnd отмечает, где полоса останавливается, по дальнюю от оси сторону для отрицательных значений. Направление, градиент, граница и цвета отрицательной полосы заполняются в той же записи, причём цвета темы и оттенки уже разрешены

С наборами значков нужна аккуратность со строгостью каждого порога, поскольку каждый cfvo после первого несёт собственный флаг gte. Вычислитель идёт по порогам вверх, применяя >= или > для каждого порога, как велит ThresholdEqualsInclude, и выигрывает наивысший порог, который значение проходит. Перевёрнутый набор переворачивает разрешённый индекс, по-значковые переопределения могут подставить глиф из другого семейства через IconSetType, а порог, который не удаётся разрешить, останавливает правило для этой ячейки — она не получает значка вовсе, а не правдоподобно неверный

Один результат для сетки, HTML-экспорта и PDF

TXLSXCfCellResult несёт всё, что нужно рендереру: цвет заливки и шрифта с уже применённым оттенком темы, жирный, курсив, подчёркивание, зачёркивание, идентификатор числового формата, геометрию и цвета гистограммы, семейство и индекс значка. HotXLS скармливает ту же запись HTML-экспорту, PDF-экспорту и TXLSWorkbookViewer, что держит три вывода в согласии ячейка в ячейку. Просмотрщик создаёт один вычислитель для активного листа при первой отрисовке и переиспользует его при прокрутке, выделении и перерисовке, избавляясь от него при переключении листов или назначении другой книги

// Правка за живым просмотрщиком: кэшированный снимок изменения не видит
Sheet := Viewer.XlsxWorkbook.Sheets[1];   // индекс листа с единицы
Sheet.Cells[5, 2].Value := 4200;          // сдвигает среднее, максимум и ранжирование
Viewer.RefreshConditionalFormats;         // сбросить снимок и перерисовать

Поскольку снимок неизменяем, правка на месте оставляет средние, ранги и концы цветовых шкал устаревшими, пока не выполнится RefreshConditionalFormats. То же относится и к вашему коду: вычислитель, который вы держите между правками, нужно освободить и создать заново, ведь CreateConditionalFormatEvaluator фиксирует значения в момент вызова

Относительные правила дат заслуживают решения заранее. Вычислитель листа сравнивает правила timePeriod с текущей датой машины, так что правило «эта неделя» отрисуется одним образом в PDF, созданном сегодня, и другим — когда та же книга будет экспортирована в следующий вторник. Это корректное поведение и всё равно тикет в поддержку, если от вашего архива ожидается воспроизводимость. Для архивных отчётов замените правила timePeriod на правило expression, сравнивающее с датой отчёта, хранящейся в ячейке, например =AND(A2>=$Z$1-6,A2<=$Z$1) для семидневного окна; тогда вывод зависит от книги и больше ни от чего. Excel также отказывается от структурированных ссылок на таблицы внутри формулы условного формата, так что направьте правило на диапазон ячеек колонки

Если цель — экран, а не файл, та же запись результата питает и собственную сетку электронных таблиц VCL, описанную в другой статье этого блога. Модель правил, справочник API вычислителя и пробные сборки — на странице HotXLS Delphi spreadsheet component