HotXLS — нативный компонент электронных таблиц для Delphi и C++Builder, и начиная с версии 2.209.0 он умеет отвечать на вопрос, который Excel обычно держит при себе: для этой конкретной ячейки, какие правила условного форматирования срабатывают и в какую заливку, шрифт, гистограмму данных или набор значков они разрешаются. Этот ответ нужен вам в тот момент, когда вашим выводом становится HTML-отчёт, PDF или сетка, которую вы рисуете сами
Это другая задача по сравнению с созданием правил. Две более ранние заметки покрывают сторону авторства: условное форматирование и стили форматированного текста посвящена привязке правил и дифференциальных форматов к диапазону, а разбиение привязанных условных форматов — тому, что происходит с диапазоном правила при вставке или удалении строк и столбцов. Обе — структурные. Эта статья о семантике: имея книгу, уже несущую правила, вычислить подсветку
Почему формат файла не сообщает, какие ячейки загораются
Короткий ответ в том, что 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 с rank="10" и percent="1" над 27 заполненными числовыми ячейками подсвечивает сколько ячеек? Два целых семь десятых — не число. Округлить, взять floor или ceiling — спецификация молчит, а выбор неверного варианта означает, что ваш PDF разойдётся с книгой, открытой у клиента рядом
Правила для одной ячейки и где останавливается TCondFormatRule.Evaluate
HotXLS сначала взял более дешёвую половину. TCondFormatRule.Evaluate в lxCondFormat.pas, добавленный в 2.199.0, отвечает, срабатывает ли одно правило для одной ячейки, ничего не зная об остальном диапазоне. Он обрабатывает восемь операторов сравнения BIFF за cellIs (between, notBetween, equal, notEqual, greater, less, greaterEqual, lessEqual), свободные правила expression, вычисляемые в ячейке так, что относительные ссылки правильно перебазируются, четыре текстовых предиката, а также предикаты пустых значений и ошибок. Пороги берутся из FFormula1 и FFormula2, разрешаемых через TXLSCalculator.GetRangeValue в позиции ячейки, а перевёрнутые границы меняются местами, а не отклоняются
var
I: Integer;
Rule: TCondFormatRule;
Value: Variant;
begin
Value := Sheet.Cells[Row, Col].Value;
for I := 0 to CondFormat.RuleCount - 1 do
begin
Rule := CondFormat.Rule(I);
// Single-cell verdict only. Aggregate and visual kinds answer False.
if Rule.Evaluate(Calculator, SheetIndex, Row, Col, Value) then
ApplyHighlight(Row, Col, Rule.Style);
end;
end;Честная часть этого метода — то, что он отказывается угадывать. top10, aboveAverage, belowAverage, duplicateValues и uniqueValues возвращают False не потому, что они сложны, а потому, что неразрешимы по одной ячейке — каждому из них нужна статистика по всему домену. Четыре визуальных семейства, dataBar, colorScale2, colorScale3 и iconSet, возвращают False по другой причине: они вообще никогда не производят булев результат, они производят полезную нагрузку для рендеринга, и булев тип возврата для них попросту неверная форма
Как вычислитель на уровне листа избегает повторного сканирования листа?
Вычисляя каждую общую величину один раз, при построении, и больше никогда. TXLSXConditionalFormatEvaluator в lxHandleX.pas — неизменяемый снимок для одного листа, построенный через TXLSXWorksheet.CreateConditionalFormatEvaluator, и весь его замысел — защита от наивной реализации, где каждая закрашиваемая ячейка запускает полное сканирование диапазона
В конструкторе происходят четыре вещи. Каждый отдельный многоблочный sqref разбирается ровно один раз в TXlsxCfRangeSnapshot, так что десять правил, разделяющих один диапазон, разделяют один разбор и один проход статистики. Этот проход потоково вычисляет среднее, отклонение по генеральной совокупности, минимум и максимум по заполненным ячейкам за один обход, и сохраняет упорядоченный числовой массив только тогда, когда правилу Top/Bottom или процентиля действительно нужны порядковые статистики. Ключи дубликатов и уникальных значений строятся с учётом Unicode и сортируются пакетно один раз вместо сортировки на каждый поиск. Затем ось строк разрезается на полосы по каждой границе блока, так что EvaluateCell выполняет двоичный поиск полосы и посещает только те правила, чьи диапазоны вообще могут достичь этой строки
Четвёртое — самое важное на масштабе. Относительная формула правила вроде =A1>AVERAGE($A$1:$A$100) означает разное в каждой ячейке домена, и очевидная реализация компилирует свежее синтаксическое дерево на ячейку. TXlsxCfRulePlan компилирует его один раз и повторно вычисляет то же дерево через обратимые смещения координат, что сохраняет поведение якорей Excel без выделения синтаксического дерева на ячейку. Затем правила слоятся по priority, и совпадение по правилу с установленным StopIfTrue прерывает цикл, в точности как Excel делает короткое замыкание
var
Evaluator: TXLSXConditionalFormatEvaluator;
Res: TXLSXCfCellResult;
begin
Evaluator := Sheet.CreateConditionalFormatEvaluator;
try
if Evaluator.EvaluateCell(Row, Col, Res) then
begin
if Res.HasFillColor then
Canvas.Brush.Color := TColor(Res.FillColor);
if Res.HasIcon then
// IconIndex is zero-based inside Res.IconSetType
DrawIcon(Res.IconSetType, Res.IconIndex, Res.IconCount);
if Res.HasDataBar then
// DataBarAxis and DataBarEnd are normalised to 0..1
DrawBar(Res.DataBarAxis, Res.DataBarEnd, Res.DataBarColor);
if not Res.ShowCellValue then
Exit; // showValue="0" on the rule hides the number
end;
finally
Evaluator.Free;
end;
end;Как Excel на самом деле округляет правило Top 10 percent?
Через floor, с минимумом единица, и включая ничьи на границе отсечения. Нигде в ISO 29500-1 это не записано — это было зафиксировано зондированием Excel 16 вручную составленными книгами и считыванием, какие ячейки подсветило приложение. HotXLS реализует именно это: число ранга — это Floor(Count * Min(Rank, 100) / 100), поднятое до 1, когда оно оказывается нулём, ограниченное количеством заполненных ячеек, а затем пороговое значение сравнивается через >=, так что каждая ячейка, равная границе, подсвечивается даже когда это превышает запрошенное количество. Двадцать семь значений и правило 10 процентов подсвечивают две ячейки, плюс любые дальнейшие ячейки, связанные ничьей со второй
Правила above-average скрывали вторую неоднозначность: aboveAverage с stdDev="1" выбирает ячейки на одно стандартное отклонение выше среднего, но выборочное отклонение и отклонение генеральной совокупности различаются на поправку Бесселя и заметно расходятся на малых диапазонах, а именно там условное форматирование и используется. Excel 16 использует отклонение генеральной совокупности, и HotXLS совпадает с ним, а флаг equalAverage делает строгое сравнение включающим только тогда, когда полоса отклонения не задействована. Правила дубликатов и уникальных значений включили тождество ключа. Если одна ячейка содержит число 100, а другая — текст «100», Excel трактует их как один и тот же ключ дубликата, поэтому HotXLS нормализует числовой текст в числовое пространство ключей вместо сравнения сырых строк. Пустые ячейки — зеркальный случай: настоящая пустая ячейка участвует в подсчёте диапазона, но сама не оформляется, так что пустые ячейки столбца не загораются все как дубликаты друг друга
Цветовые шкалы и наборы значков: интерполяция и граничные правила
Визуальные семейства разрешаются в готовые к отображению числа, а не в булевы значения, и их граничное поведение зафиксировано тем же способом. Для цветовой шкалы с явными числовыми порогами HotXLS прижимает дробь позиции к замкнутому интервалу от нуля до единицы, затем интерполирует по каналам с усечением, а не округлением — значение ниже минимальной точки получает минимальный цвет, а не экстраполированный, трёхточечная шкала выбирает пару, сравнивая со средней точкой, а вырожденная шкала, чьи две крайние точки несут один и тот же порог, сворачивается к верхнему цвету вместо деления на ноль. Наборы значков потребовали противоположного вида аккуратности, потому что каждый cfvo после первого несёт собственную строгость сравнения: HotXLS читает ThresholdEqualsInclude для каждого порога и применяет >= или > соответственно, идя вверх, так что наивысший удовлетворённый порог выигрывает индекс значка. Перевёрнутый набор переворачивает разрешённый индекс, а не пороги, переопределения на уровне отдельного значка могут взять глиф из другого семейства, а любой некорректный порог прерывает правило вместо выдачи правдоподобно выглядящего неверного значка
Питание сетки, экспорта в HTML и PDF из одного результата
Поскольку EvaluateCell возвращает полностью разрешённый TXLSXCfCellResult — дифференциальная заливка и цвет шрифта с уже применённым оттенком темы, жирный, курсив, подчёркивание, идентификатор числового формата, направленные протяжённости положительной и отрицательной гистограммы, позиция оси, семейство и индекс значка — каждый потребитель читает одну и ту же запись, и никому из них не нужно понимать внутренности правил. HotXLS использует этот единственный путь для экспорта в HTML, экспорта в PDF и интерактивного просмотрщика, что является единственным практичным способом не дать трём рендерерам разойтись. Версия 2.210.0 подключила его к TXLSWorkbookViewer, который кэширует один подготовленный вычислитель на активный лист и переиспользует его при прокрутке, выделении и перерисовке, освобождая его при смене книги или листа — перестройка снимка на каждом Paint свела бы на нет весь замысел, построенный вокруг времени конструирования. Именно из-за этого кэша существует TXLSWorkbookViewer.RefreshConditionalFormats: снимок неизменяем, так что если вы изменяете присоединённую книгу на месте, агрегатная статистика и разрешённые пороги устаревают, пока вы его не вызовете
// Editing behind a live viewer: the cached snapshot must be invalidated.
Sheet := Viewer.XlsxWorkbook.Sheets[1];
Sheet.Cells[5, 2].Value := 4200; // changes mean, min, max, ranking
Viewer.RefreshConditionalFormats; // drop evaluator, repaintЧего вычислитель за вас не сделает
Стоит прямо назвать три границы. Классический TCondFormatRule.Evaluate для одной ячейки и TXLSXConditionalFormatEvaluator уровня листа — разные поверхности с разными возможностями, и вариант для одной ячейки намеренно отказывается от агрегатных и визуальных семейств вместо их приближения — если вам нужен Top/Bottom или цветовая шкала, стройте вычислитель. Относительные периоды дат зависят от системных часов машины в момент вычисления, поэтому правило timePeriod рендерится по-разному в PDF, сгенерированном сегодня, и в том, что будет сгенерирован на следующей неделе, что является корректным поведением и всё же готовой заявкой в службу поддержки, если ваш архив должен быть побайтово стабильным. Третья граница — грамматическая, а не техническая: грамматика формул условного форматирования запрещает структурированные ссылки на таблицы, так что правило не может адресовать столбец таблицы по имени так, как это делает формула листа, и это ограничение формата, а не реализации
Если вы строите вывод отчёта, конвейер экспорта или собственную сетку, которая должна совпадать с Excel ячейка в ячейку, тот же разрешённый результат также питает собственную сетку электронных таблиц VCL, описанную в другой статье этого блога. Полная документация API, модель правил и пробные загрузки для компонента электронных таблиц HotXLS для Delphi доступны на странице продукта