HotXLS 在 Delphi 中求值 Excel 条件格式

一条“前 10%”规则摆在一列 27 个销售数字上，Excel 涂了三个单元格。27 的 10% 是 2.7：四舍五入的渲染器得三，向上取整的得三，向下取整的得二。三种猜测里两种在这一列上恰好与 Excel 一致，但都错——Excel 先把个数向下取整为二，然后又涂了第三个单元格，因为它与第二名并列 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" 没说小数个数怎么取整，aboveAverage 配 stdDev="1" 没说偏差用样本还是总体，两者也都没说恰好等于截止值的算不算。选错了，你的 PDF 就会和客户在另一个窗口里开着的工作簿对不上

TCondFormatRule.Evaluate：单个单元格自己能判定什么

HotXLS 有两个求值入口，差别在于它们愿意回答什么。经典（.xls）引擎在 lxCondFormat.pas 里暴露 TCondFormatRule.Evaluate，它接受计算器、工作表序号、单元格位置和单元格值，对一条规则返回布尔值。它覆盖八个 cellIs 运算符（介于、不介于、等于、不等于、大于、小于、大于等于、小于等于）、在单元格处求值的自由 expression 规则（相对引用因此能正确重定基准）、四个文本谓词，以及空值和错误值谓词。阈值公式按单元格位置解析，between 规则的上下界若颠倒会被交换而不是拒绝。文本比较不区分大小写，与 Excel 一致

// 经典引擎：一条规则、一个单元格、一个布尔值
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。不看其他值，一个单元格无从知道自己是否位列前 10%，硬答就是瞎猜。相对日期规则（timePeriod）在这条路径上也返回 False。四个视觉类 dataBar、colorScale2、colorScale3 和 iconSet 返回 False 的原因不同：它们从不产生是或否，产生的是颜色、条长或图标序号，布尔值没地方放这些。所以这个调用的 False 与其说是“不匹配”，不如说常常只是“不归这里判定”——拿来做比较类规则的快速检查没问题，直接跟着出货就不可靠

工作表求值器如何避免每个单元格都重扫全表？

它在创建时就把所有区间级的工作做完。lxHandleX.pas 里的 TXLSXConditionalFormatEvaluator 是一张工作表条件格式的不可变快照，从 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
  // 每次渲染建一个快照；行号列号从 1 开始
  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 在数据条规则带 showValue="0" 或图标集设为只显示图标时返回 False，渲染器于是只画条或画图标，把数字留在画面之外

“前 10%”规则到底会高亮多少个单元格？

个数向下取整、绝不低于一，与截止值并列的值全部入选。规范没有这样写，这是 Excel 的实际行为；把图里那 27 个值敲进一列、套上“前 10%”再数涂色格，一分钟就能验证。TXLSXConditionalFormatEvaluator 把排名个数算成 Floor(Count * Min(Rank, 100) / 100)，落零时抬到 1，再夹到已填数字单元格数以内，从排序值里读出截止值，然后对每个单元格做 >= 比较。对那列 27 个值来说：个数为二、截止值 88，三个格被涂色，因为其中两格都是 88。Bottom-N 规则镜像地用 <=

高于平均值规则带着第二个歧义。配 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 带着渲染器要的一切：已应用主题色调的填充色和字色、加粗、斜体、下划线、删除线、数字格式 id、数据条几何与颜色，以及图标族和序号。HotXLS 把同一条记录喂给 HTML 导出、PDF 导出和 TXLSWorkbookViewer，三种输出逐格一致。查看器第一次绘制时为活动工作表建一个求值器，滚动、选择和重绘期间一直复用，切换工作表或换工作簿时丢弃

// 在活跃查看器背后编辑：缓存的快照看不见这次修改
Sheet := Viewer.XlsxWorkbook.Sheets[1];   // 工作表序号从 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 页面