技术文章

HotXLS 文本比较:Delphi 里的 Excel 单词排序

在 Excel 里,="a-b">"ab" 返回 TRUE,="a~b"<"ab" 也返回 TRUE。把同样的两对交给 Delphi 的 CompareText,两个答案全反过来:连字符在码点顺序里排在字母之前,波浪号排在字母之后。Excel 比较文本用的是 Windows 用户区域设置的大小写不敏感“单词排序”,连字符和撇号只用来打破平局,其余标点都排在数字和字母之前

症状很少会提到排序规则这个名字。服务器上算出的 COUNTIF(A:A,">M") 比 Excel 里同一个公式多算两行。报表服务排出来的价目表把 X-100 放到了 Excel 不会放的位置。VLOOKUP("ABC",...) 返回 #N/A,尽管那一列明明白白写着 abc。三个现象归结为一句话:两个操作数都是文本时,哪个更小?Excel 有精确的答案,而 Delphi 代码默认给出的往往不是它

Excel 用什么规则比较两个文本字符串?

Excel 比较两个文本值用的是当前 Windows 用户区域设置的单词排序,忽略大小写。单词排序是 Windows NLS 比较函数的默认排序规则:字母按语言顺序而非码点比较,所以 é 落在 e 和 f 之间,大小写折叠到一起。有两个字符受到特殊对待。连字符 - 和撇号 ' 在第一轮被跳过,这让 co-op 紧挨 coop;只有其余字符打平时,它们的存在与位置才决定先后。其余标点都以完整权重计,排在数字之前,数字又排在字母之前

下表把 Excel 对 IF(A<B,...) 的判定,与 Delphi 开发者最可能先写出的两种比较放在一起

A vs BExcel / HotXLSCompareStr(序数)CompareText
"a-b" vs "ab"大于小于小于
"a'b" vs "ab"大于小于小于
"a~b" vs "ab"小于大于大于
"a_b" vs "ab"小于小于大于
"ab" vs "AB"相等大于相等
"é" vs "f"小于大于大于
"Z" vs "f"大于小于大于

有两个推论容易让人栽跟头。因为连字符只破平局,="a-b"="ab" 是 FALSE:两个字符串在排序里是紧邻的,却并不相等。又因为大小写彻底折叠,ab、AB 和 Ab 对每个比较运算符都是同一个键。在英语用户区域设置下,20 个别扭的词排成 a b、a.b、a_b、a~b、a0、a1b、ab / AB / Ab、ab-、a'b、a-b、-ab、ab1、abc、b、e、é、f、Z。ab 组内部由被跳过字符的位置决定先后:尾部连字符在前,头部连字符最后

HotXLS 单词排序示意图,给全部 20 个测试词排座次:从 a b、a.b、a_b 和 a~b 到 a0 和 a1b,再到带 AB 和 Ab 的 ab 组、a-b 与 a'b 这类连字符和撇号变体,直到 abc、b、e、é、f 和 Z,展示标点在数字前、数字在字母前、大小写被忽略
标点和空格排在数字前,数字排在字母前,大小写折平,连字符和撇号只管破平局;a-b 就是这样落在 ab 旁边却仍比较为大于

自己动手在 Excel 里核对排序规则

Excel 的文档从没写明它用的是哪种排序规则,要在自己的机器上确认,可靠的办法是让 Excel 判定一批字符串对,再把它的判定与不同旗标组合下的 CompareStringW 结果对一遍。把字符串对放进两列,让 Excel 对每行求值 =IF(A1<B1,-1,IF(A1=B1,0,1)),同样的对再跑一遍 API。值得生成的字符串对要混合 ASCII 标点、数字、大小写字母、空格、é、ß、ä 这类带重音的字母、CJK 字符、全角形式和不换行空格,再加上大量只差一个字符的近似对。从词典里随手抽词是坏样本,因为普通字母在本文的每种规则下排序结果都一样

不同的旗标组合会带来明显不同的结果:

  • 只加 NORM_IGNORECASE,配合 LOCALE_USER_DEFAULT,就是纯单词排序,也是 Excel 采用的那一个
  • 加上 SORT_STRINGSORT 就切到字符串排序,连字符和撇号变成有完整权重的普通符号,所有 a-b / ab 风格的对都反过来
  • 加上 NORM_IGNOREWIDTH 会让全角字母等于它的半角形式,而 Excel 把两者区分开

测试脚本本身还藏着一个陷阱。Excel 把在单元格开头输入的撇号当作“按文本存储”前缀,所以整个输入只有一个 ' 的单元格会变成空,这类对报告出的差异与排序规则毫无关系。要构造这种对,就用公式里的字符串字面量(="'"),或者干脆跳过

CompareText 和序数比较为什么错?

CompareText 和序数比较对不上 Excel 的顺序,因为它们比较的是 UTF-16 码元,而码点顺序把标点打散到字母周围。连字符是 U+002D,撇号是 U+0027,都低于任何字母,所以序数比较认为 "a-b" 小于 "ab",而 Excel 把连字符当破平局者。波浪号是 U+007E,高于任何字母,于是 "a~b" 更大,与 Excel 恰好相反。CompareText 还自带第二重扭曲:它只把 a..z 折叠成大写再比较码元,而下划线(U+005F)恰在大写与小写区间之间,折叠把 "a_b" 从 "ab" 之下挪到了它上面。两个函数都不知道 é 该排在 e 和 f 之间

HotXLS 比较示意图,码点顺序对阵 Excel 单词排序:序数比较把撇号、连字符、下划线放在字母周围 0x27、0x2D 和 0x5F 处,a-b 对 ab 判出小于,单词排序则把标点推到数字和字母之前,只把连字符和撇号当破平局者
码点把标点散落在字母四周,序数和 ASCII 折叠比较于是判反;单词排序把标点挪到数字前面,把连字符和撇号降格为破平局者

常见的 Delphi 工具两边都站:

  • CompareStr、字符串 < 运算符和 TComparer<string>.Default 都是序数且区分大小写,不带比较器的 TArray.Sort<string> 会把 Z 排到 f 前面
  • CompareText 和 SameText 是仅做 ASCII 大小写折叠后的序数比较
  • Windows 上 Delphi RTL 的 AnsiCompareText 和 WideCompareText 调用 CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...),正是与 Excel 一致的那次调用。默认设置(UseLocale 为 True、CaseSensitive 为 False)的 TStringList 排序走 AnsiCompareText,同样与 Excel 一致
  • POSIX 目标上 Delphi RTL 把 AnsiCompareText 路由到 ICU collator,那是另一套标点规则不同的算法。Windows 上 Free Pascal 的 AnsiCompareText 先转换到 ANSI 代码页再调 CompareStringA,代码页表示不了的字符全部丢失

所以这些感知区域设置的 RTL 函数在 Windows 上恰好是对的,靠的是它们如今的具体实现。必须与 Excel 一致的代码应当显式发起那次 API 调用并写明意图。更麻烦的问题出现在公式引擎里:每条代码路径都顺手抓最近的一种比较——= 和 < 运算符里转大写后比码点,COUNTIF 条件的 > / < 分支里做区分大小写的 Variant 比较,VLOOKUP 内部又是另一套(这正是 VLOOKUP("ABC",A1:A20,1,FALSE) 找不到 abc 的原因),区域排序里又是一次 locale 调用。每条路径单看都说得过去,合在一起却让同一列数据出现三种不同的顺序

HotXLS 里公式、查找和排序背后的同一次比较

HotXLS 把计算与排序代码里的每一次文本对文本比较都送到同一个函数——lxStandard.pas 里的 XlsCompareText,它调用 CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) 再减去 CSTR_EQUAL。六个比较运算符用它,数组公式里的逐元素比较用它,COUNTIF 风格条件的 >、<、>=、<= 分支和数据库函数用它,精确与近似模式下的 VLOOKUP 和 HLOOKUP 用它,动态数组函数与 XLOOKUP / XMATCH 背后的排序用它,TXLSWorksheet 和 TXLSXWorksheet 上的 SortRange 也用它。把区域排序挂到同一个函数上正是设计的关键点:文本上的近似 VLOOKUP 只有在列已按查找所用顺序排好时才给出合理结果,共用一个函数让这两种顺序不可能彼此漂移

HotXLS 路由示意图,展示所有文本比较路径:从六个比较运算符和 COUNTIF 风格条件,经 VLOOKUP、HLOOKUP、XLOOKUP 到两个引擎的区域排序,全部汇聚到 XlsCompareText,它以 LOCALE_USER_DEFAULT 加 NORM_IGNORECASE 调 CompareStringW,并把 1、2、3 映射成 -1、0、1
运算符、条件、查找和排序共用一个函数,Excel 看到的顺序与 HotXLS 排序用的顺序就此绑死;API 返回 1、2 或 3,零代表失败,不是小于

从 Delphi 里最快看到这条规则的途径是 TXLSXWorkbook.Calculate,它对活动表求值一个公式字符串,以 Variant 返回结果

uses
  System.Variants, lxHandleX;

var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Sheets.Add('Data');  // Calculate 对活动表求值
    Writeln(VarToStr(Book.Calculate('="a-b">"ab"')));   // True:连字符只破平局
    Writeln(VarToStr(Book.Calculate('="a-b"="ab"')));   // False:平局已破,并不相等
    Writeln(VarToStr(Book.Calculate('="a~b"<"ab"')));   // True:标点在前
    Writeln(VarToStr(Book.Calculate('="ABC"="abc"')));  // True:大小写无关
  finally
    Book.Free;
  end;
end;

只有两个操作数都是文本时,单词排序才生效。混合类型按固定等级:所有数字低于所有文本值,所有文本值低于所有布尔值,比较链、空白操作数与 SUMIF 一文有详细描述。"a*"、"=ab" 这类通配符条件是另有规则的匹配与相等测试,COUNTIF、MATCH 与 DSUM 里的 Excel 通配符指南有覆盖;本文讨论的排序规则只决定顺序类运算符

下一个示例把 20 个词装入某表的 A 列,检查三个条件计数和一个查找,然后用 TXLSXWorksheet.SortRange 排序这一列。预期计数可直接从前文给出的排序顺序推得:11 个词排在 ab 组之后,11 个排在 a-b 之前,ab 组的三个成员与那 11 个一起构成 >=AB

const
  Words: array [0..19] of string = ('ab', 'a-b', 'a~b', 'a_b', 'AB', 'a b',
    'ab1', 'ab-', '-ab', 'abc', 'a''b', 'Ab', 'b', 'a.b', 'a1b', 'a0',
    #$00E9, 'e', 'f', 'Z');
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  i: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Words');
    for i := 0 to High(Words) do
      Sheet.Cells[i + 1, 1].Value := WideString(Words[i]);

    // 按单词排序预期得到:11、11、14
    Writeln(VarToStr(Book.Calculate('=COUNTIF(A1:A20,">ab")')));
    Writeln(VarToStr(Book.Calculate('=COUNTIF(A1:A20,"<a-b")')));
    Writeln(VarToStr(Book.Calculate('=COUNTIF(A1:A20,">=AB")')));

    // 找到 abc:查找按大小写不敏感比较,与 Excel 一致
    Sheet.Cells[1, 3].Formula := '=VLOOKUP("ABC",A1:A20,1,FALSE)';
    Book.Recalculate;
    Writeln(VarToStr(Sheet.Cells[1, 3].Value));                // abc

    // 单键列,升序:a b、a.b、a_b、a~b、a0、a1b、ab、AB、Ab、…
    Sheet.SortRange(1, 1, 20, 1, [1], [False]);
    for i := 1 to 20 do
      Writeln(VarToStr(Sheet.Cells[i, 1].Value));
  finally
    Book.Free;
  end;
end;

SortRange 跑的是稳定的归并排序,所以比较相等的 ab、AB 和 Ab 会保持排序前的相对次序。无论键升序还是降序,空白单元格都沉到底部,Excel 也是这么放的

自己的 Delphi 代码怎么对齐 Excel 的排序顺序?

要在工作簿之外复现 Excel 的文本顺序,就调 CompareStringW,传 LOCALE_USER_DEFAULT 和 NORM_IGNORECASE,并且不要加 SORT_STRINGSORT 和 NORM_IGNOREWIDTH。留意返回值。这个 API 返回的不是带符号的结果,而是 CSTR_LESS_THAN(1)、CSTR_EQUAL(2)或 CSTR_GREATER_THAN(3),调用失败时返回 0。减去 2 就得到常见的负数 / 零 / 正数约定,但必须先处理 0 的情况,否则一次失败的调用会变成 -2,被当成“小于”

uses
  Winapi.Windows, System.SysUtils, System.Generics.Defaults,
  System.Generics.Collections;

// Excel 的文本顺序:用户区域单词排序,忽略大小写
function ExcelCompareText(const A, B: string): Integer;
var
  R: Integer;
begin
  R := CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE,
    PWideChar(A), Length(A), PWideChar(B), Length(B));
  if R = 0 then
    RaiseLastOSError;          // 0 是失败,不是比较结果
  Result := R - CSTR_EQUAL;    // 1/2/3 变成 -1/0/1
end;

var
  Keys: TArray<string>;
begin
  Keys := ['abc', 'a-b', 'AB', 'a~b', '-ab', 'ab'];
  TArray.Sort<string>(Keys, TComparer<string>.Construct(
    function(const L, R: string): Integer
    begin
      Result := ExcelCompareText(L, R);
    end));
  // a~b, ab / AB(相等,顺序任一), a-b, -ab, abc
end;

如果 uses 里本来就有 HotXLS,lxStandard.XlsCompareText 是公开的,返回同样的顺序。两者只在 API 调用失败时不同:上面的函数抛异常,而 XlsCompareText 退回按码元比较大写字符串,重算不会被半路打断。独立排序场景里抛异常更好,因为悄悄错误的顺序比一次异常难查得多

TArray.Sort 不稳定,ab 和 AB 这类相等键的先后次序不受保证。如果相等键的原始顺序重要,就改排下标数组,把原始位置当次级键

有时一列偏要无视 Excel 的顺序。料号是典型:X-100 和 X100 是两个不同的编号,应该按码点排序。SortRange 有一个接受 TXLSSortCompareEvent(声明为 function(const Left, Right: Variant): Integer of object)的重载,会用它顶替内置比较

uses
  System.SysUtils, System.Variants, lxStandard, lxHandleX;

type
  TPartNumberOrder = class
    function Compare(const Left, Right: Variant): Integer;
  end;

function TPartNumberOrder.Compare(const Left, Right: Variant): Integer;
begin
  // 自定义比较器也会收到空单元格(以 Null 形式):自己安排它们的位置
  if VarIsNull(Left) or VarIsNull(Right) then
    Exit(Ord(VarIsNull(Left)) - Ord(VarIsNull(Right)));
  Result := CompareStr(VarToStr(Left), VarToStr(Right));   // 序数,区分大小写
end;

var
  Sheet: TXLSXWorksheet;   // 一张填好的表,行 2..501,列 A..D
  Order: TPartNumberOrder;
begin
  // ...
  Order := TPartNumberOrder.Create;
  try
    // 以 A 列为键,升序
    Sheet.SortRange(2, 1, 501, 4, [1], [False], xlsSortByRows,
      xlsSortExcelLike, Order.Compare);
  finally
    Order.Free;
  end;
end;

用自定义比较器时,SortRange 交给你的是原始键值,空白放哪里由你决定,上面的比较器才先处理 Null。降序键时排序会把比较器的返回值取反,所以那个方向上空白会浮到顶部,除非比较器自己照顾到这一点。这样排过的列不再符合近似 VLOOKUP 或二分查找的 XLOOKUP 所期望的顺序;数据按别的顺序排好时这两种模式会出什么问题,XLOOKUP 与 XMATCH 二分查找模式指南里有讲

同一个工作簿为什么在另一台机器上排序不同?

同一份工作簿换台机器排序可能不同,因为 Excel 的文本顺序依赖 Windows 用户区域设置,而 HotXLS 有意跟随这一依赖。单词排序是按语言走的。比如瑞典语的排序规则把 ä 放在 z 之后,英语和德语则让它紧挨 a,斯德哥尔摩的同事重算过的文件,其 COUNTIF(...,">y") 可能与芝加哥一台桌面上同一文件的结果不同。HotXLS 传 LOCALE_USER_DEFAULT,为的是它的答案在同一台机器上等于 Excel 的答案。钉死某个固定 locale 看着整洁,却会让 HotXLS 在每台配置不同的机器上都与 Excel 不一致

对服务器端生成来说,这带来三个实际后果:

  • 起作用的 locale 属于进程运行所用账户。Windows 服务或 IIS 应用程序池的区域设置可能与开发者桌面不同,IDE 里看到的结果未必是生产环境算出来的
  • 写进文件的缓存公式结果带着生成机器的 locale。Excel 用自己的 locale 重算,文件在别处打开重算时值可能变化;Excel 对它自己的文件也是这个行为
  • 区域设置的分歧大多在带重音的字母、被某些语言当作单个字母的字母对和非拉丁文字上。纯英文单词的测试数据永远暴露不了差别,样例里要放几个 Ångström、Öberg 这样的名字

与 Excel 一致的路径就是 Windows 路径:所有 Win32 与 Win64 构建,无论用 Delphi、C++Builder 还是 Lazarus 和 Free Pascal 编译,调的都是同一个 CompareStringW。构建目标是非 Windows 平台时,XlsCompareText 改用 RTL 的 WideCompareText,顺序来自该平台自己的排序规则库

普通词在任何规则下排序都相同,所以错误的排序规则往往能挺过测试,直到带连字符的料号、标点或带重音的客户名字出现。HotXLS 在 XLS 和 XLSX 两个引擎里、从比较运算符到 SortRange,对这些输入都给出 Excel 的答案。支持的 Delphi 与 C++Builder 版本、授权方式和试用下载见 HotXLS Delphi Excel 组件页面