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

HotXLS Delphi Component 从 v2.384.67 起按 Excel 16 的方式比较两个文本值：忽略大小写，采用 Windows 用户区域设置的 word sort（单词排序）顺序，也就是 CompareStringW 加 NORM_IGNORECASE 标志返回的那种。连字符和撇号在第一遍被跳过、只在平局时起作用，所以 ="a-b">"ab" 为 TRUE；其余标点排在数字和字母之前，所以 ="a~b"<"ab" 同样为 TRUE。比较运算符、> / < 条件、区域排序和 VLOOKUP 现在都由同一顺序驱动

没人会提交一个标题叫「排序规则不匹配」的 bug。报告说的是 COUNTIF(A:A,">M") 在服务器上比 Excel 多数了两行，报表服务排出的价目表把 X-100 放在 Excel 不会放的位置，或者 VLOOKUP("ABC",...) 返回 #N/A，而那一列明明白白躺着 abc。三件事归结为同一个问题：两个操作数都是文本时，谁更小？Excel 有精确答案，但不是大多数 Delphi 代码给出的那个，v2.384.67 之前的 HotXLS 更是看哪条代码路径发问就给三种不同答案

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

Excel 用用户区域设置的单词排序比较文本，忽略大小写。单词排序是 Windows NLS 比较函数的默认排序规则：字母按语言学顺序而不是码点比较，带重音的字母紧挨基字母，另有两个字符受特殊照顾。连字符 - 和撇号 ' 在第一遍被忽略，所以 co-op 与 coop 落在一起，只有其余部分打平时它们的存在才决定顺序。其他所有标点都有分量，排在数字之前，而数字排在字母之前

表格展示了这在实践中意味着什么，旁边是 Delphi 开发者最可能抓来的两种比较。Excel 一栏是 Excel 16 对 IF(A<B,...) 给出的判定，HotXLS 从 v2.384.67 起复现它

A vs B	Excel 16 / HotXLS	CompareStr（序数）	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 在任何比较眼里都是同一个键。用 Excel 的 Range.Sort 排这 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 组内部，被忽略字符的位置说了算

Excel 的文本顺序是怎么钉死的？

Excel 的文本顺序是靠测量钉死的，不是靠文档，因为 Excel 的文档根本不说自己用哪种排序规则。测试从 ASCII 标点、数字、大小写字母、空格、é、ß、ä、汉字、全角形式和不换行空格生成 4,000 对随机字符串，长度 0 到 4，其中一半成对地互为近似项。Excel 16 对每一对求值 IF(A<B,-1,IF(A=B,0,1))，判定结果再去与不同标志组合的 Windows 比较 API 对答案

• 单用 NORM_IGNORECASE（默认单词排序，用户区域设置）：没有真不匹配。仅有的 7 处差异是内容整个就是一个 ' 的单元格——Excel 把它当文本前缀字符吃掉——那是采样伪影，不是排序规则差异
• NORM_IGNORECASE 加 SORT_STRINGSORT：41 处不匹配。字符串排序把连字符和撇号当普通符号，这正是 Excel 没有的行为
• 再加 NORM_IGNOREWIDTH：错得换了花样，它让同一字母的全角与半角形式比较相等，而 Excel 是把它们分开的

第二轮手工挑的检查，把 20 个刁钻词抽出的全部 190 对，与 Excel 的 Range.Sort 对同一列的结果相对照。两者都与朴素的 NORM_IGNORECASE 单词排序一致，这 190 个判定加排序后的顺序如今进了 HotXLS 回归套件，经典 TXLSWorkbook 引擎和 XLSX 原生 TXLSXWorkbook 引擎两头都跑

CompareText 和序数比较为什么错？

CompareText 和序数比较之所以对不上 Excel 的顺序，是因为它们比较的是 UTF-16 代码单元，而码点顺序把标点放在相对字母的任意位置。连字符是 U+002D，撇号是 U+0027，都在所有字母之下，于是序数比较认为 "a-b" 比 "ab" 小，而不是把连字符当破平局者。波浪号 U+007E 在所有字母之上，所以 "a~b" 算出来更大，正好跟 Excel 相反。Delphi RTL 里的 CompareText 只把 a..z 折成大写再比较代码单元，这带来第二重失真：下划线 U+005F 卡在大写与小写字母之间，折成大写后 "a_b" 就从 "ab" 之下挪到了之上。两个函数都不知道 é 该待在 e 和 f 之间

常见的 Delphi 工具两边都站：

• CompareStr、字符串 < 运算符和 TComparer<string>.Default（它内部调 CompareStr）都是序数且区分大小写，不带比较器的 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，那是另一套算法、另一套标点规则；Free Pascal 的 AnsiCompareText 在 Windows 上则先转 ANSI 代码页再调 CompareStringA，代码页表示不了的字符直接丢失

所以这些感知区域的 RTL 函数在 Windows 上对，是实现的功劳，不是契约的保证，需要 Excel 顺序的代码最好显式调 API。HotXLS 内部原来也是这么个混搭：比较运算符把两个字符串都转大写再比码点，条件函数的 > / < 分支用 Delphi 区分大小写的 Variant 比较，VLOOKUP / HLOOKUP 匹配文本也用那套区分大小写的 Variant 比较，所以 VLOOKUP("ABC",A1:A20,1,FALSE) 找不到 abc。区域排序倒是一直用 WideCompareText。三条路径，三种顺序

HotXLS v2.384.67 改了什么？

从 v2.384.67 起，HotXLS 计算与排序路径里文本对文本的比较都过同一个函数——lxStandard.pas 里的 XlsCompareText，它调 CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) 再减去 CSTR_EQUAL。调用方有六个比较运算符、数组公式里的逐元素比较、COUNTIF 风格条件与数据库函数的 >、<、>=、<= 分支、VLOOKUP 和 HLOOKUP（精确与近似）、动态数组函数与 XLOOKUP / XMATCH 背后的排序辅助，以及两个引擎的区域排序。让区域排序走同一函数，保证排序顺序与比较顺序再也不会各走各的，这一点很要紧——文本上的近似 VLOOKUP 只有在列按查找所用的比较顺序排过序时才有意义

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 个测试词装进一列，用 TXLSXWorksheet.SortRange 排序，再查一个条件计数和一个查找。那几个计数就是 Excel 16 对同一列返回的值

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]);

    // Excel 16 对同一列：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")')));

    // v2.384.67 之前是 #N/A：查找按大小写敏感比较
    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;

TXLSXWorksheet.SortRange 用稳定的归并排序，所以比较相等的 ab、AB 和 Ab 保住排序前的相对次序。空白单元格在两个方向上都垫底，与 Excel 一致

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

想在自己的 Delphi 代码里对齐 Excel 的文本顺序，就用 LOCALE_USER_DEFAULT 加 NORM_IGNORECASE 调 CompareStringW，别加 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;

TArray.Sort 不稳定，比较相等的键（比如 ab 和 AB）出来的顺序随缘；相等键的原有顺序要紧的话，就排一个索引数组，拿原始位置当次级键。反过来也会碰到：有时一列恰恰不能跟 Excel 的顺序，比如零件号，X-100 和 X100 是两个不同的编码，理应按码点排。TXLSXWorksheet.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;

提供自定义比较器时，HotXLS 跳过自己的空白处理，把原始键值直接递过来，所以比较器必须处理 Null。降序键时 HotXLS 把比较器的返回值取反，这同样会把空白顶到最上面，除非比较器自己料到了。记住，这样排过序的列不再处于 Excel 近似 VLOOKUP 或二分查找 XLOOKUP 期待的顺序；这些模式在异序数据上的坑，XLOOKUP 与 XMATCH 二分查找模式指南里有讲

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

同一个工作簿在另一台机器上可能排出不同结果，因为 Excel 的文本顺序跟着 Windows 用户区域设置走，而 HotXLS 刻意追随这种依赖。单词排序因语言而异：比如瑞典语排序把 ä 放在 z 后面，英语和德语却让它挨着 a。Excel 从自己运行其下的区域设置继承这一点，所以斯德哥尔摩同事重算的工作簿，COUNTIF(...,">y") 可能与芝加哥一台桌面上的同一文件不同。HotXLS 传 LOCALE_USER_DEFAULT，为的是同一台机器上结果与 Excel 相等；任何固定区域都会让 HotXLS 在每台设置不同的机器上与 Excel 顶牛

对服务端生成来说，这带出三个实际后果：

• 说了算的区域设置是进程运行账户的那个。Windows 服务或 IIS 应用程序池的区域格式可能与开发者桌面不同，IDE 里观察到的结果不会自动等于生产环境算出的
• 写进文件的缓存公式结果反映生成机器的区域设置。Excel 用自己的区域重算，文件在别处打开重算时值可能变；那是 Excel 的行为，不是 HotXLS 的伪影
• 各区域设置的分歧大多在带重音字母、被某些语言当单个字母的字母组合以及非拉丁文字上，所以只拿普通英文单词做的测试数据暴露不了问题

平台边界很简单。HotXLS 是 Windows 库，用 Delphi 和 C++Builder 建 Win32 与 Win64，用 Lazarus 和 Free Pascal 建 win32 / win64 目标，所有这些构建调的都是同一个 CompareStringW。没有单独的非 Windows 排序路径。唯一的回退是给失败的 API 调用留的：CompareStringW 返回 0 时，XlsCompareText 改按代码单元比较转成大写的字符串，而不是在重算中途抛异常——计算保住了，但 Excel 的顺序不再有保证

速查：HotXLS 里的 Excel 文本比较

• 规则：用户区域单词排序加 NORM_IGNORECASE，不要 SORT_STRINGSORT，不要 NORM_IGNOREWIDTH，HotXLS 自 v2.384.67 起
• - 和 ' 只破平局：="a-b">"ab" 为 TRUE，="a-b"="ab" 为 FALSE
• 其余标点排在数字前，数字排在字母前：="a~b"<"ab" 和 ="a0"<"ab" 均为 TRUE
• 大小写永不参与：="ABC"="abc" 为 TRUE，VLOOKUP("ABC",...) 找得到 abc
• 覆盖的路径：比较运算符、数组比较、> / < 条件、VLOOKUP / HLOOKUP、动态数组排序、两个引擎的 SortRange
• 本规则不管的：混合类型（number < text < boolean）与通配符条件，它们各有各的规则
• Delphi 代码里：CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...)，先测 0，再减 CSTR_EQUAL；结果必须与 Excel 一致时，避开 CompareText、CompareStr 和 TComparer<string>.Default
• 结果取决于运行代码的账户的区域设置，Excel 与 HotXLS 皆然

普通单词在任何规则下排得都一样，只有带连字符的编码、标点和带重音的名字才会暴露错误的排序规则。XLS 与 XLSX 两个引擎里，HotXLS 如今对它们全部给出 Excel 的答案。授权、支持的 Delphi 与 C++Builder 版本及试用下载，见 HotXLS Delphi Excel 组件页