HotXLS 文字比較：Delphi 裡的 Excel 字組排序

從 v2.384.67 起，HotXLS Delphi Component 比較兩個文字值的方式與 Excel 16 一致：不分大小寫，按 Windows 使用者地區設定的「字組排序」，也就是 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（ordinal）	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 與 ordinal 比較為什麼會錯？

CompareText 與 ordinal 比較之所以對不上 Excel 的順序，是因為它們比的是 UTF-16 碼元，而碼位順序把標點放在字母之間任意的地方。連字號是 U+002D、撇號 U+0027，都低於所有字母，ordinal 比較因此判 "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）是 ordinal 且分大小寫，不帶比較器的 TArray.Sort<string> 會把 Z 排在 f 前面
• CompareText 與 SameText 是 ASCII 摺疊大小寫後的 ordinal 比較
• Windows 上 Delphi RTL 的 AnsiCompareText 與 WideCompareText 呼叫 CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...)，正是對上 Excel 的那一個呼叫。用預設值（UseLocale True、CaseSensitive False）的排序 TStringList 走 AnsiCompareText，所以也與 Excel 一致
• POSIX 目標上，Delphi RTL 把 AnsiCompareText 轉交 ICU 定序器，那是標點規則不同的另一套演算法；Windows 上 Free Pascal 的 AnsiCompareText 則先轉到 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 的文字順序，就呼叫 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;

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));   // ordinal，分大小寫
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
• 不歸這條規則管：混合型別（數字 < 文字 < 布林）與萬用字元準則，各自有各自的規則
• 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 component 產品頁