金额列里是 250、730 和 1,040。生成的 .xls 给这一列加了 >=100 的 AutoFilter,Excel 打开文件毫无修复提示,表头下拉看得见筛选已设置,然后每一行数据都消失了。字节是合法的 BIFF8。整个问题出在一个类型字节上:操作数 100 被存成了字符串,Excel 拿字符串操作数按文本比较,而任何数字单元格都过不了与 "100" 的文本比较。把操作数存成 IEEE double,同一个筛选就能保住全部三行
BIFF8 AutoFilter 把条件存在哪里
BIFF8 AutoFilter 摊在三种记录类型上,其中只有一种携带条件。AUTOFILTERINFO($009D,[MS-XLS] §2.4.8)记录筛选范围横跨多少列。FILTERMODE($009B)是个没有载荷的标记,HotXLS 只在至少一个字段有生效条件时才发出它。每个生效字段随后有自己的 AUTOFILTER 记录($009E,§2.4.6):一个字段索引、一个低两位是 wJoin 的 grbit 字、两个各恰好 10 字节的 DOPER 结构,以及一个可选尾部,存放字符串操作数的字符。磁盘上的字段索引从零起算,而 TXLSWorksheet.ApplyAutoFilter 的字段编号从 1 开始,所以在十六进制转储里,范围的第三列显示为记录体开头的 02 00
每个 DOPER 的第一个字节 vt 说明后面跟着什么类型的操作数:
$04是 IEEE 754 双精度浮点,存放在剩余 8 字节里,Excel 的数值比较就是这样存的$06是字符串,长度放在单个cch字节里,字符本身则推进记录尾部$08是 Bes 值,一个打包进两个字节的布尔值或错误码$0C和$0E不带任何操作数,分别表示匹配所有空单元格和匹配所有非空单元格
第二个字节 grbitSgn 存放比较符:1 到 6 分别对应 <、=、<=、>、<> 和 >=。在 HotXLS 里设置条件之后,这两个字节仍然可查。TXLSWorksheet.AutoFilterColumns 返回一个 TXLSAutofilter,它的 Find 方法按 1 起算的字段号返回对应条目,该条目把 Criteria1 和 Criteria2 暴露为 TXLSAutofilterDOPER 对象,带只读的 DataType、grbitSgn 和 Value 属性。保存工作簿时写进文件的就是它们,逐字节一致
为什么 '>=100' 筛选在 Excel 里一行都匹配不到?
筛选之所以匹配不到任何行,是因为文本操作数强制了文本比较。最自然的第一个写法会正确剥掉 >= 前缀、把符号位设为 6,然后发出一个装着字符 100 的 vtString DOPER——反正条件到达时都是字符串,Excel 自己的对话框如此,VBA 的 AutoFilter 调用也如此。Excel 照单全收这条记录,并完全按写下的样子求值。在那场比较里,存着 250 的数字单元格永远不会「大于等于」文本 "100",于是整列清空
TXLSWorksheet.ApplyAutoFilter 和 TXLSAutofilter.SetFieldCriteria 按同一套规则转换条件。以六个运算符之一开头、余下文本能解析成数字的字符串,变成以该运算符为符号位的 vtIEEENumber DOPER。数字 Variant(Integer、Int64、Double、Currency 以及其他序数和浮点类型)变成符号位为 2 的数字 DOPER,即判等测试。布尔 Variant 变成 Bes DOPER。不带运算符的裸字符串保持字符串 DOPER,因为 Excel 自己从下拉列表选中一项时就是这么存的
var
Wb: IXLSWorkbook;
Sh: TXLSWorksheet;
Doper: TXLSAutofilterDOPER;
begin
Wb := TXLSWorkbook.Create;
Sh := Wb.Sheets.Add;
Sh.Cells[1, 1].Value := 'Region';
Sh.Cells[1, 2].Value := 'Amount';
Sh.Cells[2, 1].Value := 'North';
Sh.Cells[2, 2].Value := 250;
// 字段 2 = A1:B100 的第二列(API 侧字段从 1 起算)
Sh.ApplyAutoFilter('A1:B100', 2, '>=100');
Doper := Sh.AutoFilterColumns.Find(2).Criteria1;
// 预期:DataType = 4(IEEE 数字),grbitSgn = 6(>=)
// Assert 是你自己加的检查,不属于库
Assert((Doper.DataType = 4) and (Doper.grbitSgn = 6));
Wb.SaveAs('orders.xls');
end;
裸字符串规则是最容易坑人的那条。在 Delphi 里,'100' 和 100 作为 Variant 参数看起来可以互换,但前者产生文本匹配,后者产生数值判等。靠猜更糟:一个以文本形式存着 00100 的零件号列,会拿到针对 100 的数字 DOPER,结果连自己的单元格都匹配不到。裸字符串照抄 Excel 对下拉选项的存法;当你指的是数字时,传数字,或者写 '=100',后者给出符号位为 2 的 IEEE DOPER
小数分隔符、日期与 1904 日期系统
运算符之后的数字解析永远用句点作小数分隔符,Windows 区域设置说什么都不算。'>=1.5' 变成数字 DOPER;'>=1,5' 解析不了,保持字符串 DOPER,在德文或法文机器上清空数字列的效果和在美国机器上一样可靠。分组分隔符同样过不了关,'>=1,000' 是文本。运算符和数字之间的空白会被裁掉,'>= 100' 没问题。把分隔符固定在一处,报告才不会因为出自哪台服务器而表现不同——对文件格式来说这是正确的默认
日期是同一个陷阱换了身戏服。'>=2026-01-01' 不是数字,会被写成文本,而 Excel 把日期单元格存成序列号并按数值比较。请直接传序列号。对 1900 年 3 月 1 日起的任何日期,Delphi 的 TDateTime 整数部分与 Excel 1900 日期系统的序列号相同。切到 1904 日期系统的工作簿从 1904 年 1 月 1 日起算,序列号整体小 1462,IXLSWorkbook.Date1904 会告诉你文件用的是哪套系统。这个偏移的细节见1900 与 1904 日期序列号如何与数字格式互动
var
Fmt: TFormatSettings;
Serial: Integer;
begin
Fmt := TFormatSettings.Create;
Fmt.DecimalSeparator := '.';
// 带小数的阈值:用句点格式化,永远不看区域设置
Sh.ApplyAutoFilter('A1:D500', 3, '>' + FloatToStr(1499.5, Fmt));
// 日期:与 Excel 存进单元格的序列号比较
Serial := Trunc(EncodeDate(2026, 1, 1));
if Wb.Date1904 then
Dec(Serial, 1462);
Sh.AutoFilterColumns.SetFieldCriteria(4, '>=' + IntToStr(Serial),
xlAnd, Unassigned);
end;
数字 Variant 比字符串拼接省事,但 Variant 数字永远意味着判等。任何其他比较都得拆成运算符加文本传递,所以格式化这一步省不掉
AND 和 OR 是怎么连接两个条件的?
AUTOFILTER grbit 的 wJoin 位为 0 表示 AND、为 1 表示 OR,把它们弄反既容易又代价高昂。一换,「至少 100 且低于 500」这样的区间筛选就存成了「至少 100 或低于 500」,每个数字都匹配,看起来就像筛选根本没生效。HotXLS 给 xlAnd 写 0、给 xlOr 写 1;涉及判等条件时,还会设置 wJoin 旁边的 fSimple1 和 fSimple2 位,它们标记符号位为 2 的 DOPER
公开常量自带一个移植隐患。HotXLS 里 xlAnd 是 0、xlOr 是 1,而 Excel automation 把两者编号为 1 和 2。XlAutoFilterOperator 声明为普通的 Byte,所以带字面数字翻译过来的 VBA 宏编译时一声不吭。COM 下表示 AND 的字面量 1 到这里表示 OR;表示 OR 的字面量 2 与 xlBottom10Items 撞号,而 HotXLS 构建两个 DOPER 时把 xlOr 之外的运算符值一律当 AND 处理,于是那个条件被按 AND 连接。两个字面量都翻了。用命名常量可以躲开整桩麻烦:
// 金额从 100(含)到 500(不含)
Sh.ApplyAutoFilter('A1:D500', 3, '>=100', xlAnd, '<500');
with Sh.AutoFilterColumns.Find(3) do
begin
// 你自己加的检查:确认将要写入的内容
Assert(Operator = xlAnd); // 磁盘上 wJoin = 0
Assert(Criteria2.DataType = 4); // IEEE 数字
Assert(Criteria2.grbitSgn = 1); // 1 = 小于
end;
布尔值、空单元格与 255 字符上限
布尔条件以 Bes 值存储([MS-XLS] §2.5.10),Bes 把值字节 bBoolErr 放在前、fError 标志放在后。顺序写反的话,筛 TRUE 会把 1 写进错误标志,Excel 把这个条件读成错误码。麻烦在于:读取端带着同样的顺序翻转时,自家的文件往返完美无缺。一个只读自己写的东西的库,在这方面证明不了任何符合性;把文件放进 Excel 打开、检查筛选对话框才能证明。HotXLS 把 bBoolErr 写在前、fError 写 0,所以传 True 得到 DataType 为 8、Value 为 1 的 DOPER
空值测试不需要操作数。单独一个字符串 '=' 产出匹配全部空单元格的 DOPER($0C),单独一个 '<>' 产出匹配全部非空单元格的 DOPER($0E)。两者都是完整条件,该字段算生效,FILTERMODE 也会写出
字符串操作数在 DOPER 布局里撞上一道硬限制。cch 字段只有一个字节,字符串操作数因此至多 255 字符——Excel 自家筛选对话框执行的也是同一个上限。HotXLS 先剥运算符,再把更长的内容截到 255 字符,免得长度字节回绕、把记录尾部带偏。于是对长描述列的筛选,匹配的可能是你传入内容的前缀。大小写不改动:Excel 应用筛选时本就不区分大小写地匹配筛选文本,改写已存条件只会动用户的数据。BIFF8 的尾部把每个字符串存成单字节标志加 UTF-16 code unit,声明的记录长度必须把这些字节算准——这正是Delphi XLS 写入器中 BIFF 记录长度声明的漂移讲过的同一门记账功夫
筛选多列并检查保存后的文件
每次 ApplyAutoFilter 调用都会重新定义整个筛选范围,两次调用之后只有第二次的条件留得下来。这个方法走的是公开的 TXLSWorksheet.SetAutoFilter,后者在重建范围前清空每个字段。对单列来说正确,第一次筛两列时则出人意料。要多列筛选,先调用一次 ApplyAutoFilter 建立范围和第一个条件,其余用 AutoFilterColumns.SetFieldCriteria 追加——它只动一个字段,范围和其他字段原样不动。两条路径对超出范围的字段号都静默忽略、不抛异常,所以值得做的检查是从重新打开的文件里把条件读回来
Sh.ApplyAutoFilter('A1:D500', 1, 'North'); // 范围 + 字段 1
Sh.AutoFilterColumns.SetFieldCriteria(3, '>=100', xlAnd, Unassigned);
Sh.AutoFilterColumns.SetFieldCriteria(4, True, xlAnd, Unassigned);
Wb.SaveAs('orders.xls');
Wb := TXLSWorkbook.Create;
Wb.Open('orders.xls');
with Wb.Sheets[1].AutoFilterColumns do
begin
// 重新打开文件后你自己加的检查
Assert(Find(1).Active and (Find(1).Criteria1.DataType = 6)); // 文本 'North'
Assert(Find(3).Criteria1.DataType = 4); // 数字 100
Assert(Find(4).Criteria1.DataType = 8); // 布尔 TRUE
end;
AUTOFILTER 记录是一份存储下来的定义。在经典 XLS 工作表上,HotXLS 写入条件,等 Excel 打开文件时求值;服务器端的行级求值在 XLSX 对象模型里,见Delphi 中的 HotXLS 数据验证、AutoFilter 与表格。等 Excel 真把行藏起来,范围下方的合计就取决于SUBTOTAL 和 AGGREGATE 如何对待隐藏行与筛选行——存成文本的数字筛选在那里现形的方式,就是一个总数为零的合计
上述条件转换、Bes 布局和连接位,就是 HotXLS 保存每个 BIFF8 工作簿时写进去的内容,与 Delphi 和 C++Builder 的原生 XLSX 支持并存。版本、完整功能列表与试用下载见 HotXLS Delphi spreadsheet component 页面