HotXLS 下拉列表与输入校验

HotXLS 可以直接在 Delphi 代码里读取 Excel 数据验证下拉列表。TryGetCellDropdownItems 返回单元格的候选字符串，SelectCellDropdownItem 按索引或文本写入其中一项，ValidateCellInput 在任何东西被存储之前报告键入值能否通过该单元格的规则。Classic XLS 的 TXLSWorksheet 和 XLSX 的 TXLSXWorksheet 上都有同样的方法，且都收 1 基的行号列号

需要这项功能的常见起因是一个不是你设计的模板。业务侧的某位同事发来一份工作簿：Status 列锁死在 Draft、Review 和 Final，Channel 列的数据来自隐藏工作表上的一个范围，数量列只接受 1 到 100。你的导入任务、Web 表单或批量填充器现在必须尊重这些规则，而规则在工作簿里，不在你的代码里。把允许值硬编码在模板旁边，就是在等第一个人在 Excel 里改了列表之后，两边各奔东西

为什么代码写入不理会 Excel 数据验证？

数据验证是附加在范围上的元数据，执行它是用户键入内容的那个编辑器的职责。XLSX 里规则是一个 dataValidation 元素（ECMA-376 Part 1，§18.3.1.32），携带类型、操作符、formula1 与 formula2、提示和错误警报；Classic XLS 文件里同样的信息放在 [MS-XLS] 描述的 DV 记录中。两种格式都不标记单元格本身，所以没有任何东西阻止程序存入一个违反规则的值。Excel 自己也是这么表现的：它检查键入的条目，而经其他途径进来的越界值，只有当有人运行“圈释无效数据”时才会现身

HotXLS 刻意遵循这个模型。Sheet.Cells[3, 2].Value := 250 这样的赋值会在 1 到 100 的规则下毫无怨言地存入 250，随附的 DataValidation 演示正是这么证明这一点的。检查是可选择的，本文剩下的部分讲的就是在正确的地方选择它

读取单元格背后的下拉项

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 返回输入消息的标题和文本——想在 Web 表单字段旁显示模板作者自己的提示时正好用得上

按索引或按文本选中条目

SelectCellDropdownItem 把一个列表条目写进单元格，单元格没有列表规则或请求的条目不存在时返回 False。整数重载收的是 TryGetCellDropdownItems 所返回数组里的 0 基索引；文本重载先找精确匹配，再回退到大小写不敏感匹配，所以 'review' 仍能选中 Review

两个重载都经 WriteAutoValue 写入，而不是裸字符串赋值。对以范围为来源的数字列表这一点很关键：条目回来是 '3' 这样的字符串，但存进去的是数字 3——用户从箭头里选时 Excel 存的就是这个，引用该单元格的公式期望的也是这个。DataValidation 演示在 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 重载，与 TXLSXWorksheet 上的对应。它方便，但要看清它做什么：用逗号把条目连接起来再交给字符串重载。所以自身含逗号的条目会变成两个条目，而在 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 处理器的测试，会卡在模态对话框上。查看器的样式枚举是第三种类型 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 演示也在其中