Delphi HotXLS 中的 Excel 條件式格式評估

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 就是子字串存在。落差在聚合家族上會展開。一條 rank="10" 且 percent="1" 的 top10 規則，在 27 個有值的數值儲存格中，會高亮幾個？二點七不是一個數字。四捨五入、無條件捨去，還是無條件進位——規範沒有說，選錯了，你的 PDF 就會與客戶正開著的活頁簿意見不合

單一儲存格規則，以及 TCondFormatRule.Evaluate 到此為止

HotXLS 先啃了容易的那一半。lxCondFormat.pas 裡的 TCondFormatRule.Evaluate，在 2.199.0 加入，能在對範圍其餘部分一無所知的情況下，回答某條規則是否對某個儲存格觸發。它處理 cellIs 背後的八種 BIFF 比較運算子（介於、不介於、等於、不等於、大於、小於、大於等於、小於等於）、在儲存格處求值以讓相對參照正確重新定位的自由格式 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：它們從來就不會產生一個布林值，它們產生的是一份渲染酬載，而布林回傳型別對它們來說是錯的形狀

工作表層級的評估器如何避免重新掃描整張表？

靠的是在建構時把每個共用量計算一次，之後永不重算。lxHandleX.pas 裡的 TXLSXConditionalFormatEvaluator，是單一工作表的不可變快照，透過 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 百分比規則做捨入的？

它無條件捨去，最少取一個，並把落在門檻上的並列一起納入。這件事在 ISO 29500-1 的任何地方都沒寫下來——它是靠拿手動建構的活頁簿去試探 Excel 16、讀回應用程式高亮了哪些儲存格才釘定的。HotXLS 實作的正是這一套：命中數量是 Floor(Count * Min(Rank, 100) / 100)，落在零時提升到 1，鉗制到有值的儲存格數，門檻值再用 >= 比較，所以每個等於邊界值的儲存格都會被高亮，即使這樣會超出要求的數量。二十七個值搭配一條 10% 規則，會高亮兩個儲存格，加上任何與第二名並列的儲存格

高於平均規則藏著第二個歧義：aboveAverage 搭配 stdDev="1" 選取高於平均值一個標準差的儲存格，但樣本與母體標準差因貝索校正而不同，兩者在小範圍上的差異相當明顯，而條件式格式恰好就常用在小範圍上。Excel 16 用的是母體標準差，HotXLS 與它一致，equalAverage 旗標只有在沒有標準差區間介入時，才會把嚴格比較改成含邊界比較。重複值與唯一值規則靠的則是鍵值身分。如果一個儲存格存放數字 100，另一個存放文字「100」，Excel 把它們視為相同的重複鍵，所以 HotXLS 把數值文字正規化進數值鍵空間，而不是比較原始字串。空白儲存格則是相反的情況：一個真正的空儲存格會參與範圍計數，卻不會被上色，所以一欄裡的空白儲存格不會全部被當成彼此的重複值而亮起來

色階與圖示集：插值與邊界規則

視覺家族解析成可直接渲染的數字，而不是布林值，它們的邊界行為也是用同樣的方式釘定的。對於帶有明確數值閾值的色階，HotXLS 把位置分數鉗制到零到一的閉區間，再逐色版以截斷而非四捨五入的方式插值——低於最小門檻的值會拿到最小顏色，而不是外推出來的顏色；一個三段的色階以中間門檻為分界來挑選配對；兩端門檻相同的退化色階，會收斂到頂端顏色，而不是除以零。圖示集需要的是相反類型的細心，因為第一個之後的每個 cfvo 都帶有自己的比較嚴格性：HotXLS 讀取每個門檻的 ThresholdEqualsInclude，據此套用 >= 或 >，向上走訪，讓最高滿足的門檻贏得圖示索引。一個反轉集會反轉解析出的索引，而不是反轉門檻；逐圖示的覆寫可以從別的家族抽出一個圖示；任何無效門檻都會讓規則中止，而不是產出一個看似合理、實則錯誤的圖示

用一份結果餵給格線、HTML 匯出與 PDF

因為 EvaluateCell 回傳的是一個完全解析好的 TXLSXCfCellResult——差異填色與字型顏色都已套用主題色調、粗體、斜體、底線、數字格式 id、正負方向的資料橫條延伸量、軸位置、圖示家族與索引——每個消費者讀的是同一筆記錄，沒有一個需要理解規則內部細節。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 試算表格線。HotXLS Delphi 試算表元件的完整 API 文件、規則模型與試用下載都在產品頁面上