HotXLS：在 Delphi 中处理数据验证、筛选与表格

HotXLS 中的三项功能共享一个工作表，却操作着完全不同的对象，而麻烦始于你假设它们做着相似的事情。数据验证把一条规则附加到一个范围上，约束用户可以在其中键入什么。一个 AutoFilter 把一份存储的条件定义附加到一个区域上，并改变查看者显示哪些行。一个表格把一个范围包裹进一个带名称、带类型、带条纹样式的结构中。一个约束输入、一个记录视图、一个施加模式。它们没有一个会自行移动任何单元格的值，而 AutoFilter 尤其愚弄人，因为这个词暗示一个动作，而它存储的只是一份定义。知道每个调用触碰哪个对象、以及效果实际在何时具现，正是把一个在 Excel 中行为与测试中相同的工作簿，与一个悄悄分叉的工作簿分开的东西

AutoFilter 存储的是定义，它不裁剪行

一个保存在文件中的 AutoFilter 是一条条件记录。隐藏行的动作发生在之后，当 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 在你需要一次拿到匹配集合时通过一个回调遍历整个区域。有一种情况两者都不是正确答案：如果要求是被排除的行根本不能存在于文件中——一次隐私裁剪而非一个视图——那就直接删除这些行。筛选在那里是错误的工具，因为任何接收者一次点击就能清除它，而你打算保留的数据就回到了屏幕上

列号是偏移，不是列编号

上面代码片段中的注释标记了这个 API 里最费调试时间的陷阱。AddAutoFilterColumn 用筛选范围内 0 基的位置来标识其目标，而不是用工作表列号。对于一个 A1:E500 上的筛选，两套编号系统恰好相差一，而这正是一种能挺过快速测试、却在同事筛选另一列时立即坏掉的擦边球。对于一个从 C 列开始的筛选，id 0 意味着 C 列，而错位很快变得明显。当筛选范围在运行时计算时，从构建范围字符串的同一个变量派生列号，绝不从一个工作表列常量派生。每列通过那个接收两个运算符、两个条件和一个与/或连接器的重载接受第二个条件，这镜像了 Excel 的自定义筛选对话框。XLS 门面用 SetAutoFilter 加 ApplyAutoFilter 覆盖同样的领域，其条件和运算符参数遵循更老的 COM 风格约定并从 1 开始为字段编号。切换门面意味着切换索引基，所以调用点值得一条说明当前用的是哪个的注释

验证规则是你的用户在其下编辑的契约

在这三项功能中，验证是唯一一个主动约束未来输入的，它在那些发出去待填写再回收处理的工作簿中值得最多的设计关注。列表变体承载了其中大部分工作：

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 接收一个名字、一个范围和一个列列表：

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 产品页