HotXLS 下拉式清單與輸入驗證（Delphi）

HotXLS 能直接從 Delphi 程式碼讀取 Excel 資料驗證的下拉清單。TryGetCellDropdownItems 回傳某個儲存格的候選字串，SelectCellDropdownItem 以索引或文字寫入其中一項，ValidateCellInput 則在真正儲存之前回報輸入值會不會通過該格的規則；同樣的方法在 Classic XLS 的 TXLSWorksheet 與 XLSX 的 TXLSXWorksheet 上都有，而且都以 1 起算的列號與欄號操作

需要這組功能，通常是因為撞上了一份不是你設計的範本。業務端發來一份活頁簿：Status 欄鎖在 Draft、Review、Final 三個值，Channel 欄來自隱藏工作表上的某個範圍，數量欄只收 1 到 100。你的匯入作業、網頁表單或批次填寫器現在必須尊重這些規則，而規則活在活頁簿裡，不在你的程式碼裡；把允許值寫死在範本旁邊，就是等著有人第一次在 Excel 裡改清單時讓兩邊走樣

為什麼程式碼寫入會無視 Excel 的資料驗證？

資料驗證是掛在某個範圍上的 metadata，執行它是使用者輸入的那個編輯器的職責。XLSX 裡，規則是一個 dataValidation 元素（ECMA-376 Part 1，§18.3.1.32），帶著類型、運算子、formula1、formula2、提示與錯誤警示；Classic XLS 檔裡同樣的資訊放在 [MS-XLS] 描述的 DV record 裡。兩種格式都不會標記儲存格本身，所以沒有任何東西阻止程式存進一個違反規則的值；Excel 自己也一樣——它檢查鍵入的輸入，而經其他途徑進來的越界值要等到有人跑 Circle Invalid Data 才現形

HotXLS 刻意遵循這個模型。Sheet.Cells[3, 2].Value := 250 這種指定，會在 1 到 100 的規則下無聲地存進 250，跟產品附的 DataValidation demo 用來證明這一點的做法一模一樣；檢查是選配的，本文剩下的部分就是關於在對的地方選配

讀出儲存格背後的下拉項目

TryGetCellDropdownItems(ARow, ACol, AItems, AShowDropDown) 只有在該格被清單型規則涵蓋時才回傳 True，並把候選填進 TArray<WideString> 型別的 AItems。實務上清單來源有四種寫法，HotXLS 全部解析，呼叫端不必徒手拆 formula1：

• 帶引號的行內清單，如 "Draft,Review,Final"，按逗號切開
• 項目以 #0 字元分隔的行內清單，這種形式出現在 Classic XLS 規則裡
• 同一張或另一張工作表上的範圍，如 =$G$2:$G$10 或 =Lists!$A$2:$A$7，按當前儲存格值求值
• 指向上述範圍的定義名稱，如 =ProductList

合併儲存格也處理了。詢問合併區內的任何一格，HotXLS 會到合併錨點上找規則——Excel 正是把規則與值都放在那裡

AShowDropDown 旗標值得特別一提，因為檔案格式把它存反了。XLSX 裡 showDropDown="1" 實際上會藏起儲存格內箭頭，BIFF8 裡對應的位元是「抑制下拉」旗標；HotXLS 在讀取時把兩者都翻回來，所以 AShowDropDown = True 就是字面上的意思：Excel 會顯示箭頭。箭頭被藏起來的清單規則照樣限制輸入，這正是 TryGetCellDropdownItems 無論如何都回傳項目的原因

uses
  SysUtils, lxHandleX;

procedure DumpChannelList(const FileName: WideString);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Items: TArray<WideString>;
  ShowArrow: Boolean;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open(FileName) <> 1 then
      raise Exception.Create('Cannot open ' + FileName);
    Sheet := Book.Sheets[1];
    // F2：第 2 列、第 6 欄，皆為 1 起算
    if Sheet.TryGetCellDropdownItems(2, 6, Items, ShowArrow) then
    begin
      Writeln(Length(Items), ' items, arrow visible: ', BoolToStr(ShowArrow, True));
      for I := 0 to High(Items) do
        Writeln('  [', I, '] ', Items[I]);
    end
    else
      Writeln('F2 has no list validation');
  finally
    Book.Free;
  end;
end;

如果只要字串，GetCellDropdownItems(ARow, ACol) 直接回傳陣列，沒有清單時給空陣列；TryGetCellValidationPrompt 回傳輸入訊息的標題與文字，想在網頁表單欄位旁顯示範本作者自己的提示時很好用

以索引或文字選取項目

SelectCellDropdownItem 把清單項目寫進儲存格，儲存格沒有清單規則或要求的項目不存在時回傳 False。整數 overload 收 TryGetCellDropdownItems 回傳陣列的 0 起算索引；文字 overload 先找完全符合，再退回不分大小寫的比對，所以 'review' 仍會選中 Review

兩個 overload 都透過 WriteAutoValue 寫入，不走裸字串指定。對數字範圍支撐的清單這點很要緊：項目回來時是 '3' 這樣的字串，但存進去的是數字 3——使用者從箭頭選取時 Excel 存的就是數字，任何參照該格的公式也預期如此。DataValidation demo 在 XLS、XLSX 與 XLSB 下存檔重開之後，驗的正是這件事

// TXLSWorksheet（XLS）與 TXLSXWorksheet（XLSX）上的同樣呼叫
if not Sheet.SelectCellDropdownItem(2, 1, 'Review') then
  Writeln('A2: "Review" is not in the list');

if not Sheet.SelectCellDropdownItem(2, 6, 3) then  // 第 4 項，0 起算
  Writeln('F2: index 3 is out of range');

寫入之前怎麼驗證鍵入的輸入？

ValidateCellInput(ARow, ACol, AText) 把候選字串對涵蓋該格的規則做測試，且絕不修改儲存格；沒有規則適用或候選通過時回傳 True。字串會經過與 WriteAutoValue 相同的文字轉值轉換，所以檢查與最終寫入看到的是同一個值。XLSX 側的轉換是 XlsxAutoTextValue：它認得 TRUE/FALSE、錯誤字面值、ISO 日期、m/d/yyyy 與 d.m.yyyy 日期和不變文化格式的數字，並像 Excel 一樣把 0123 留成文字；Classic XLS 引擎有自己的轉換器，與它自己的 WriteAutoValue 對映

還有兩個同伴補齊這一組。ValidateCellValue(ARow, ACol) 檢查已存在儲存格裡的值，空白格視為有效；TryGetCellValidationError 回傳規則的錯誤警示標題、文字與樣式，規則完全沒設錯誤警示時回傳 False。樣式型別因引擎而異，請與工作表類別配對：TXLSWorksheet 用 lxDataCheck 的 TXLSDvErrorStyle，TXLSXWorksheet 用 TXLSXDvErrorStyle

有一個行為你得自己重現：Excel 不驗證空輸入或以 = 開頭的任何東西。HotXLS 把這個跳過放在檢視器的編輯路徑裡，不在 ValidateCellInput 內，所以伺服器端閘門在呼叫之前也該照做：

uses
  lxDataCheck, lxHandle;

// AText 可以寫入時回傳 True，對映 Excel 的警示樣式
function AcceptTypedValue(Sheet: TXLSWorksheet; ARow, ACol: Integer;
  const AText: WideString; out AMessage: WideString): Boolean;
var
  Title, Text: WideString;
  Style: TXLSDvErrorStyle;
begin
  AMessage := '';
  Result := True;
  // Excel 跳過清空儲存格與公式的驗證
  if (AText = '') or (AText[1] = '=') then
    Exit;
  // 未設定錯誤警示：Excel 什麼都放行
  if not Sheet.TryGetCellValidationError(ARow, ACol, Title, Text, Style) then
    Exit;
  if Sheet.ValidateCellInput(ARow, ACol, AText) then
    Exit;
  AMessage := Title + ': ' + Text;
  // Stop 拒絕；Warning 與 Information 交給呼叫端決定
  Result := Style <> xlsDvErrStop;
end;

// 呼叫端（Msg: WideString）：先驗證，再用同一個轉換寫入
if AcceptTypedValue(Sheet, 2, 2, '42', Msg) then
  Sheet.WriteAutoValue(2, 2, '42');

把 ValidateCellInput 與 WriteAutoValue 配成一對，正是這個範例的重點：用一種轉換驗證、用另一種轉換儲存，遲早有個值以數字身分通過檢查、以文字身分落進儲存格

帶引號的清單項目與 Classic XLS 跳脫陷阱

含雙引號的行內清單項目，是無聲弄丟一條規則的捷徑。行內清單以公式字串儲存，而公式字串以雙寫來跳脫內嵌的 "；把項目包上引號卻不雙寫內部引號，He said "hi",No 這種清單就會產出一條編譯不過的公式——沒有例外，規則只是沒被建出來，下拉跟著一起消失。TXLSWorksheet.AddListValidation 與它的 XLSX 對應版本都會雙寫內嵌引號，這類項目因此完整活下來

TXLSWorksheet.AddListValidation 還有 array of WideString overload，與 TXLSXWorksheet 上的對應版本一致。它很方便，但要看清楚它做什麼：把項目用逗號接起來，再交給字串 overload。所以自帶逗號的項目會裂成兩項，而 Classic 引擎上含 ! 的來源字串會被當成工作表限定的參照而不是行內清單；這類文案請把值放進儲存格範圍，把規則指向它

var
  Book: IXLSWorkbook;
  Sheet: TXLSWorksheet;
begin
  Book := TXLSWorkbook.Create;
  Sheet := Book.Sheets.Add;
  Sheet.AddListValidation('H2:H11', ['He said "hi"', 'Plain option']);
  // 依賴它之前先確認規則存在
  if Sheet.FindDataValidationAt(2, 8) = nil then
    raise Exception.Create('List rule for H2:H11 was not created');
  // ...
end;

存檔重開之後，H2 上的 TryGetCellDropdownItems 回傳 He said "hi"，引號原封不動，XLS、XLSX、XLSB 與 ODS 輸出皆然

讓 TXLSWorkbookViewer 替你執行規則

使用者在畫面上編輯儲存格時，TXLSWorkbookViewer 會替你套用同一套檢查。Editable 設為 True、EnforceDataValidation 維持預設 True 時，破壞帶錯誤警示規則的鍵入編輯會在寫入前被檢查：Stop 拒絕、Warning 詢問、Information 告知。檢視器還會在作用中儲存格畫出下拉箭頭，用點擊或 Alt+Down 打開清單，並在儲存格下方顯示黃色輸入提示；格線本身的細節收錄在以 TXLSWorkbookViewer 打造自訂試算表格線一文

把 CircleInvalidData 設為 True 會在違反規則的已存值周圍畫紅圈，就像 Excel 的同名命令；它只掃可見區域，先用 FindDataValidationAt 快速排除大多數儲存格，而且絕不圈空白格

procedure TMainForm.FormCreate(Sender: TObject);
begin
  Viewer.Editable := True;
  Viewer.EnforceDataValidation := True;
  Viewer.CircleInvalidData := True;
  Viewer.OnValidationError := ViewerValidationError;
end;

procedure TMainForm.ViewerValidationError(Sender: TObject; Row, Col: Integer;
  const Text: WideString; Style: TXLSViewerValidationErrorStyle;
  const AlertTitle, AlertText: WideString;
  var Action: TXLSViewerValidationAction; var Handled: Boolean);
begin
  StatusBar.SimpleText := AlertTitle + ': ' + AlertText;
  if Style = xveStop then
    Action := xvaRetry    // 帶著被退回的文字重新開啟編輯器
  else
    Action := xvaAccept;
  Handled := True;        // 抑制內建對話框
end;

Handled 留在 False 時，檢視器會顯示自己的警示對話框，VCL 與 LCL 下都能運作；這個預設在自動化測試裡會咬人：驅動編輯卻沒有設 Handled 的 OnValidationError handler 的測試，會卡在強制回應對話框上。檢視器的樣式列舉是第三個型別，TXLSViewerValidationErrorStyle 帶 xveStop、xveWarning 與 xveInformation，與兩個工作表列舉互不相干

依賴 HotXLS 輸入驗證之前該知道什麼？

這些檢查對清單、數字、日期、時間與文字長度規則都忠實，另有幾個引擎相關細節值得知道：

• Classic XLS 工作表上，ValidateCellInput 對自訂公式規則的鍵入候選一律回報有效，因為候選值無法在不干擾依賴鏈的前提下暫時放進 BIFF 儲存格；請用會求值自訂公式的 ValidateCellValue 檢查已存值，或改用對候選值也求值自訂規則的 XLSX 引擎
• Classic XLS 上，清單比對把數值相等的值視為同一項，所以 5 與 5.0 都會對上值為 5 的項目
• 檢視器裡貼上的內容跳過驗證，與 Excel 相同；EditSelectedCell 沒有編輯器可重開，所以 Retry 在那裡表現得像 Cancel
• 裸的 Cells[...].Value 指定一律不檢查，這是設計使然；驗證只發生在你的程式碼開口要求的地方

規則本身怎麼建立——包括整數、日期、文字長度與自訂類型——收錄在HotXLS 資料驗證、AutoFilter 與表格指南；XLSB 活頁簿會載進 TXLSXWorkbook，所以這裡展示的每個 TXLSXWorksheet 呼叫在它們上頭原樣可用，說明見在 Delphi 讀寫 XLSB 二進位活頁簿一文

下拉解析、項目選取與輸入驗證隨HotXLS Delphi Excel component 出貨，供 Delphi、C++Builder 與 Lazarus 使用，附帶把本文每個呼叫都跑過一遍的 DataValidation 與 WorkbookViewer demo