LlosLab Software Development Blog面向 PDF、Delphi 與文件軟體開發者的工程筆記

技術文章

HotXLS 在 Delphi 中的條件式格式、富文字與儲存格樣式

HotXLS Component

條件式格式規則在 OOXML 中其實是兩件事共用一個名字。條件，也就是比較、公式或文字比對，決定哪些儲存格符合資格。外觀，也就是差異格式記錄，ECMA-376 中稱為 dxf，決定那些儲存格看起來如何。Excel 的對話方塊把這條界線藏起來，讓您一次把兩邊都填完。HotXLS 不會這麼做。從 Delphi 建立一個 cellIs 規則卻跳過樣式，規則本身仍然有效，範圍也正確，公式也會在正確的儲存格上求值為 true，但外觀不會改變，因為規則的指令其實就是「true，但不要畫任何東西」。先把條件和結果之間的落差弄對，是這類規則最先要做對的地方，而這也是為什麼很多在管理規則裡看起來正確、實際上卻完全沒亮色的規則都會出現在這裡

HotXLS 會原生把條件式格式寫進 BIFF8 .xls 與 OOXML .xlsx 檔案，富文字 run 和共用的儲存格樣式模型也是如此。這三項功能彼此之間的連動，比扁平的 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];

    // Negative variance: light red fill
    Idx := Sheet.AddConditionalFormat('D2:D200', xlsxCfOpLessThan, '0');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    // Duplicate order IDs get flagged the same way
    Idx := Sheet.AddCondFormatDuplicateValues('A2:A200');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFEB9C);

    // Custom formula rule: highlight rows where actual misses 90% of target
    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 對話方塊裡那種熟悉的「淺紅色」，前面帶著一個完全不透明的 alpha 位元。任何按儲存格條件觸發的規則型態，都遵循同樣的先建立、再套樣式形式。文字比對器（AddCondFormatContainsText、AddCondFormatBeginsWith、AddCondFormatEndsWith）會先回傳索引，再由您套樣式，AddCondFormatTop10、AddCondFormatAboveAverage，以及空白與錯誤偵測器也一樣。只要學會這個模式，整個文字與比較家族的行為就都相同

資料條、色階與圖示集會自行著色

視覺型規則的運作方式剛好相反。它們把外觀直接放進規則定義裡，完全忽略 Style 屬性。替資料條規則指派填滿色卻什麼都不發生，這看起來像 bug，直到分類法一切對上：AddCondFormatDataBar 直接以引數接受條形色，二點與三點色階也以同樣方式接受端點色，而 AddCondFormatIconSet 會從 26 種圖示集型態中選一種，例如 icsTrafficLights3。這裡沒有可忘記的獨立樣式記錄，因為根本就沒有獨立樣式記錄

這些呼叫中值得思考的參數是值錨點，型別為 TXLSCfValueKind。條形或色階端點可以位於範圍最小值或最大值、字面數字、百分比或百分位數，或公式結果。預設的範圍最小與範圍最大，在整齊的示範資料上表現良好，到了有離群值的真實資料卻會反咬您一口：一個失控的值會拉伸整個尺度，讓其他所有條形都扁成短刺。當儀表板要跨期間閱讀時，請改把端點錨定在固定數字或百分位數，如此三月的一半條形與四月的一半條形才代表相同數量。自動縮放的條形只和自己可比

XLS 寫入器只涵蓋四種規則

舊版 BIFF8 端不是 XLSX 端的縮小鏡像，而是刻意縮減的子集。XLS 外觀層最多只能建立四種條件規則型態，資料條、兩色階、三色階與圖示集，並以 CF12 記錄寫入串流。它沒有可建立 cellIs、expression 或文字規則的 API。您在開啟檔案時已經存在的這些規則，會被讀入、保留並原樣寫回，所以重新開啟並儲存客戶的 .xls 時，不會破壞它帶進來的格式。您做不到的是在 .xls 中從零產生閾值醒目標示。那裡的選項是用程式計算一般儲存格填滿色來模擬，或者把交付物做成 .xlsx，因為完整的規則家族都在那裡

這是要在資料層存在之前就先決定的限制，而不是之後，因為它會改變任何儀表板形狀內容的檔案格式選擇。團隊若為了相容性選了 .xls，後來又規格化了一份需要 cellIs 閾值的 KPI 報告，那就是兩件無法並存的事，而比較便宜的發現時機，是在格式決策階段，而不是建置三週之後

規則堆疊、優先順序與重疊範圍

真正的儀表板很少是一個範圍一條規則。某個差異欄位可能同時承載一個表示幅度的資料條、一個表示硬閾值的 cellIs 規則，以及一個位於兩者之上的列層級 expression 規則。每個 TXLSXConditionalFormat 都有一個 Priority 值，而 Excel 會依優先順序解決彼此競爭的規則。當兩條規則都想畫同一個儲存格時，勝負是由您設定的數字決定，而不是由審核者在管理規則對話方塊裡剛好先看到哪一條決定

請把優先順序當成繪圖程式對待 z-order 的方式。只要兩條規則可能碰到同一批儲存格，就刻意為它們設定優先順序，並在數值之間留空間，讓後來的規則能插進來而不必重編其餘規則的號碼。若規則不會衝突，例如資料條只限於 E 欄、文字規則只限於 G 欄，則建立順序就夠了，優先順序不值得費心。把心力放在範圍邊界上，因為這裡代價高昂的 bug 幾乎從來不是優先順序顛倒，而是像 B2:B200 這種只涵蓋 350 列報表前 200 列的範圍，未覆蓋的尾端會顯示成看起來和正常資料一模一樣的普通儲存格。把每個規則範圍都從驅動圖表序列與其他活頁簿驗證範圍的同一個最終列數值推導出來，尾端就不會掉出去

有一個驗證習慣很值得。生成後，在 Excel 中開啟檔案、選取已格式化範圍，並在每次範本變更後走一遍管理規則。條件式格式是少數幾個只有消費檔案的應用程式才是權威轉譯器的領域，所以對 XML 做單元測試只能證明規則有被寫進去，不能證明 Excel 會照您想的方式著色。花一分鐘人工目視就能把那個落差補起來

富文字：一個儲存格內的多種格式

XLSX 模型中的富文字儲存格，持有一份 run 清單，每個 run 都是一段文字加上它自己的字型屬性。您先在旁邊建立一個 TXLSXRichText 物件，把 runs 加進去，再把整個物件附到儲存格上。真正容易踩雷的是所有權規則。把值指派給 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;   // ownership moves to the cell: do not Free
end;

明確設定 ColorIsAuto := False 並不是可有可無的裝飾。run 會帶著自動色彩旗標，而色彩指派只有在該旗標被清除後才會生效。設了 Color 卻忘了 ColorIsAuto，run 會變成粗體，卻頑固地維持黑色，而且不會有任何錯誤幫您指出原因。run 也支援刪除線、底線變體，以及用於上標與下標的垂直對齊，而 PlainText 會在您需要匯出或比對文字內容時，把整個清單攤平成單一字串

儲存格層級的富文字只支援 XLSX。XLS 外觀層沒有可寫入它的公開 API，不過 run 在註解與文字方塊中仍可透過 TextRuns 使用，而從既有 .xls 讀入的富字串也會在來回轉換後完整保留。這個取向和條件式格式相同：任何把多種格式混在同一個儲存格中的內容，都屬於 XLSX 寫入器

樣式池與隨貨出現的 off-by-one

XLSX 模型中的一般儲存格樣式，透過活頁簿上的共用集合運作。Fonts.Add、Fills.AddSolid 與 Borders.Add 都各自註冊一個定義，並回傳它在池中的索引。這些索引是從 0 開始的。消耗這些索引的儲存格屬性，例如 FontIndex，會把 0 保留給「預設值」，所以您指派給儲存格的值就是池索引加一

HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);  // pool index, 0-based
for Col := 1 to 6 do
  Sheet.Cells[1, Col].FontIndex := HeaderFont + 1;          // cell index, 1-based

少了 + 1，每個標題都會回到預設字型。沒有例外，也沒有警告，只有一本看起來像完全沒做過樣式的活頁簿。第二層錯誤藏在迴圈裡：每一列都呼叫一次 Fonts.Add。相同的字型定義會去重，因此檔案不會損壞，但這些工作全都白做了，尤其對齊池在每次呼叫時都會回傳一個新物件，而不是把重複項合併起來。請在迴圈前先建立好少數幾個樣式，並重複使用它們的索引。對十萬列報表來說，這個單一變更正是 HotXLS 大型活頁簿效能調校 中涵蓋的槓桿之一。當您只需要一種現成的語意樣式時，兩個外觀層都在範圍上提供 ApplyBuiltinStyle，可對應到 Excel 內建的 Good、Bad、Neutral 與 accent 樣式，而不必碰到共用集合

條件式格式、富文字與共用樣式是報表的最後一步，會在資料模型與版面配置都定案後才套用，而那些前置階段則是 HotXLS 的範本式報表產生 所討論的主題。完整的規則、run 與樣式參考，都在 HotXLS Component 產品頁