HotXLS 資料驗證、自動篩選與表格：Delphi

HotXLS 裡的三個功能共用一張工作表，卻在完全不同的物件上運作，而麻煩就從你假設它們做相似的事開始。資料驗證把一條規則附著到一個範圍上，限制使用者能在裡面打什麼。自動篩選把一份儲存的準則定義附著到一個區域，並改變檢視者顯示哪些列。表格把一個範圍包進一個具名、有型別、帶橫條樣式的結構裡。一個限制輸入，一個記錄一個檢視，一個施加一個綱要。它們沒有一個會自己搬動任何一個儲存格數值，而自動篩選尤其會騙到人，因為這個字眼暗示一個動作，但它儲存的只是一份定義。知道每個呼叫碰的是哪個物件、以及效果何時才真正具體化，正是區分一份在 Excel 裡行為與你測試時一致的活頁簿、與一份悄悄分歧的活頁簿的關鍵

HotXLS 在 Delphi 的三個工作表功能圖解：資料驗證約束輸入、AutoFilter 儲存檢視定義、表格強制結構描述 — 資料驗證、AutoFilter 與表格在 HotXLS 中都掛在同一工作表範圍上，卻各自在不同時刻具現 — 輸入時、開檔時與存檔時

自動篩選儲存一份定義，它不裁剪列

一份儲存檔案裡的自動篩選是一筆準則紀錄。隱藏列這件事是稍後才發生的，在 Excel 開啟活頁簿並對資料評估準則時。HotXLS 如實寫下那筆紀錄，卻什麼也不裁剪：你篩選掉的每一列仍然實體存在於檔案裡。一條套用篩選來丟掉被拒訂單、再讀回活頁簿的管線，會看到全部的列，包含被拒的那些，而這段程式碼就 API 而言是正確的，就作者的心智模型而言卻是錯的。在 XLSX 工作表上，SetAutoFilter 宣告被篩選的區域，而 AddAutoFilterColumn 把準則附著到其中一欄。當伺服器端程式碼需要實際結果——為了摘要裡的列數，或只轉發相符的列——函式庫會為你評估準則，而不是假裝檔案變了：

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  R, Visible: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[0];

    Sheet.SetAutoFilter('A1:E500');
    // 欄位 id 3 = 篩選範圍內的第四欄（以 0 為基準的位移）
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');

    Visible := 0;
    for R := 2 to 500 do
      if Sheet.AutoFilterRowVisible(R) then
        Inc(Visible);
    // Visible 現在會與 Excel 開啟檔案後會顯示的內容一致

    Book.SaveAs('orders-filtered.xlsx');
  finally
    Book.Free;
  end;
end;

AutoFilterRowVisible 逐列回答，而當你需要一次取得相符集合時，PreviewAutoFilterRows 會透過回呼走過整個區域。有一種情況兩者都不是正確答案：如果需求是被排除的列絕不能存在於檔案裡——這是一次隱私裁剪，而非一個檢視——那就直接刪除那些列。篩選在那裡是錯的工具，因為任何收件者點一下就清掉它，而你打算保留的資料又回到螢幕上

欄位 id 是位移，不是欄號

上面片段裡的註解標出了這個 API 裡花掉最多除錯時間的陷阱。AddAutoFilterColumn 是以篩選範圍內 0 起算的位置來識別它的目標，而不是以工作表欄號。對於一個作用在 A1:E500 上的篩選，兩套編號系統剛好差一，這正是那種能通過快速測試、卻在同事篩選另一欄的那一刻壞掉的擦邊球。對於一個從 C 欄開始的篩選，id 0 表示 C 欄，而這個不對很快就變得明顯。當篩選範圍是在執行階段算出來的，請從建立範圍字串的同一個變數推導欄位 id，絕不要從工作表欄常數推導。每一欄透過一個接受兩個運算子、兩個準則與一個且/或連接器的多載，接受第二個條件，這映射了 Excel 的自訂篩選對話方塊。XLS 外觀以 SetAutoFilter 加上 ApplyAutoFilter 涵蓋同樣的範圍，其準則與運算子參數遵循較舊的 COM 風格慣例，並從 1 開始為欄位編號。切換外觀意味著切換索引基底，所以呼叫點值得一句註解說明正在用的是哪一個

圖解：HotXLS 的 AutoFilter 把每一列都存進儲存的 Excel 檔，Delphi 預覽 API 則評估 Excel 會顯示哪些列，並附 0 起算的欄 ID 偏移 — 儲存的檔案保留每一列、只記下篩選條件，Excel 則在評估後隱藏列 — AddAutoFilterColumn 以範圍內以零為基準的位移指定欄

驗證規則是你的使用者在底下編輯的合約

在三個功能裡，驗證是唯一一個會主動限制未來輸入的，而在那些發出去填寫、再回收處理的活頁簿裡，它掙得最多的設計關注。清單變體承擔了其中大部分工作：

var
  Idx: Integer;
begin
  Idx := Sheet.AddListValidation('C2:C500', 'New,Approved,Blocked');
  Sheet.DataValidations[Idx].SetPrompt('Status',
    'Pick one of the listed states');
  Sheet.DataValidations[Idx].SetError('Invalid status',
    'Type or paste only listed values', xlsxDvErrStop);
  Sheet.DataValidations[Idx].AllowBlank := False;

  // 數量：整數，零或更多
  Sheet.AddWholeNumberValidation('D2:D500', xlsxDvOpGreaterOrEqual, '0');
end;

在清單與整數之外，同一個家族透過 AddCustomValidation 涵蓋小數、日期、時間、文字長度與自由形式公式，而泛型的 AddDataValidation 為由設定驅動的規則建置器公開了完整的型別與運算子矩陣。錯誤樣式的重要性超過它的名稱所暗示的。xlsxDvErrStop 直接拒絕錯誤輸入；警告與資訊樣式則在點一下之後就放行數值。依據讀回活頁簿的程式碼能否容忍規則之外的數值，逐欄選擇。兩個邊界屬於提示文字或你隨檔案出貨的 README。Excel 裡的驗證防的是打字，但在一個已驗證範圍上貼上一塊資料會悄悄繞過規則，所以任何讀回資料的程式碼都必須再次驗證，而不是信任儲存格。而一條規則涵蓋的是你交給它的字面範圍，這意味著在你還不知道最終列數之前就附上驗證，會讓附加的尾段失去保護。先寫資料，再根據實際範圍調整規則大小

舊式外觀提供了相同的規則家族，只有一個人體工學差異。XLS 那一側的建立器——即 AddWholeNumberValidation、AddDecimalValidation、AddDateValidation、AddTimeValidation、AddTextLengthValidation 與 AddCustomValidation——直接回傳 TDataValidation 物件而非索引，所以提示與錯誤設定是從回傳的參考串接下來，而不是一次查詢。運算子列舉（xlsDvBetween、xlsDvGreaterThan 等等）映射 XLSX 那一組，所以規則建置程式碼除了那個回傳風格差異之外，能在兩個外觀之間移植。提示文字本身值得與規則一樣多的心思。一個用空白錯誤方塊拒絕輸入的下拉選單，教使用者去寄信給 IT；一個點出合法狀態的下拉選單，則教他們修好儲存格然後繼續

一個函式庫為你吸收的極性翻轉

任何手工讀過 OOXML 驗證 XML 的人都遇過那個反向的 showDropDown 屬性：在 ISO/IEC 29500 裡，true 值表示「隱藏下拉箭頭」，與名稱讀起來的意思相反。HotXLS 在內部把它翻轉過來，所以一條驗證規則上的 ShowDropDown 屬性說到做到，true 就是顯示下拉選單。唯一會被燙到的方式，是混合了不同層級的真假：你從程式碼設定屬性，而同事審計已儲存的 XML，並把那個看起來對他們是反過來的屬性「修正」掉。先決定對審查工具而言是屬性還是原始 XML 才是權威，並在那個決策所在之處把這個翻轉寫下來

表格給一個範圍一個綱要與一個名稱

一張工作表表格，用 Excel 的術語是 ListObject，把一個範圍包進一個名稱、有型別的欄、橫條樣式與結構化參照支援裡。它正是讓一份生成活頁簿在使用者開始排序與擴充它時，感覺完工的那個功能。建立方式在兩個外觀上是對稱的，AddTable 接受一個名稱、一個範圍與一個欄清單：

HotXLS 在 Delphi 的工作表表格圖解：帶類型的欄、結構化參照、活頁簿內唯一的名稱，以及附加列時的總計列陷阱 — HotXLS 表格把範圍包進名稱、具型別欄位與帶狀樣式；合計列緊貼資料下方，正是天真地附加在最後一列時撞上的位置

var
  Cols: TStringList;
begin
  Cols := TStringList.Create;
  try
    Cols.CommaText := 'OrderId,Customer,Status,Amount,Owner';
    Sheet.AddTable('Orders', 'A1:E500', Cols);
  finally
    Cols.Free;
  end;
end;

在 XLSX 那一側，產生的表格物件公開了 StyleName（內建的 TableStyleMedium2 家族及其同類）、橫條切換與一個總計列旗標，所以套用自家樣式是一次屬性指派，而非一次手動格式化。在舊式 .xls 檔案裡，同樣的呼叫寫入 BIFF8 表格紀錄，而該外觀還提供了 AddPivotTable，用於從列、欄與資料欄位建置而成的摘要檢視——這提醒了我們，舊格式裡的「表格」觸及的範圍比 OOXML 的 ListObject 更遠。命名表格的方式，要像你命名資料庫檢視那樣。以結構化參照讀取 Orders[Amount] 的下游程式碼，能撐過那種會破壞位置式程式碼的欄重新排序

兩個慣例能在之後省下清理工作。Excel 要求表格名稱在整個活頁簿裡唯一，所以一個每個區域發一張工作表的產生器，需要一套像 Orders_EMEA 的方案，而不是重複使用 Orders。重複在寫入時不會失敗；它會在使用者開啟檔案時，以一個修復對話方塊浮現，而那是發現它最糟糕的地方。另一個慣例關乎總計列：啟用時，它就坐在資料範圍正下方，所以任何稍後以「最後一個使用列加一」來附加的程式碼，會寫進總計帶而不是寫在它後面。把資料範圍與表格範圍分開追蹤，附加就會落在你預期的地方

這三個功能在資料輸入交付物裡自然地組合。一個表格定義可編輯區域，驗證限制使用者打字的欄，而一個預設篩選讓收件者省下頭幾下點擊。出貨時就套用一個篩選，讓活頁簿一開啟就聚焦在重要的列上，這是個合理的論點，只要你記得被排除的列仍然在檔案裡，而一個好奇的收件者能把它們顯露出來。把查詢結果有效率地弄進工作表——這條管線的上游那一半——涵蓋在從 Delphi 把資料庫結果匯出成 Excel裡，而公式會摘要已驗證資料的活頁簿，則受益於用於穩定跨工作表參照的定義名稱

驗證、篩選與表格，正是出貨一片數值格線與出貨一個小型應用程式之間的差別。完整的規則、篩選與表格參考，在HotXLS Delphi Component 產品頁上