技術文章

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(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 都是同一把鍵值。在英文使用者地區設定下,一欄 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 標點、數字、大小寫字母、空格、é、ß、ä 這類帶重音字母、中日韓字元、全形字與不換行空格,並且多放只差一個字元的近似對。字典裡隨手抓的單詞是壞樣本——本文談的每一套規則,普通字母排起來都一樣

不同旗標組合的結果截然不同:

  • 單用 NORM_IGNORECASE,配 LOCALE_USER_DEFAULT,就是純字組排序,也正是 Excel 走的那一套
  • 加上 SORT_STRINGSORT 就切到字串排序:連字號與撇號變成足重的普通符號,所有 a-b / ab 式的配對全部反過來
  • 加上 NORM_IGNOREWIDTH 會讓全形字母與半形比成相等,Excel 卻把兩者分開

有一個坑藏在測試工具本身。Excel 會把輸入到儲存格開頭的撇號當成「以文字儲存」前置字元,整格內容只有 ' 的儲存格最後是空的,這樣造出來的配對一律回報不一致,卻與定序毫無關係。這類配對請改用公式裡的字串文字來造(="'"),或者乾脆跳過

CompareText 與 ordinal 比較為什麼會錯?

CompareText 與 ordinal 比較之所以對不上 Excel 的順序,是因為它們比的是 UTF-16 碼元,而碼位順序把標點散落在字母之間。連字號是 U+002D、撇號 U+0027,都低於所有字母,ordinal 比較因此判 "a-b" 小於 "ab",Excel 卻把連字號當分平手的角色。波浪號 U+007E 高於所有字母,"a~b" 便顯得較大,與 Excel 相反。CompareText 還自帶第二層扭曲:它只把 a..z 摺成大寫再比碼元,而底線(U+005F)正好落在大寫與小寫字母區之間,一摺疊,"a_b" 就從 "ab" 下方挪到了上方。至於 é 該排在 e 與 f 之間,兩個函式都毫無概念

HotXLS 比較示意圖,對照碼位順序與 Excel 字組排序:ordinal 比較把撇號、連字號與底線放在字母周圍的 0x27、0x2D、0x5F,a-b 對 ab 因此判較小;字組排序則把標點推到數字與字母之前,只把連字號與撇號留作分平手
碼位把標點散落在字母四周,ordinal 與 ASCII 摺疊比較的判決於是翻轉;字組排序把標點挪到數字前面,連字號與撇號降級成只分平手

常見的 Delphi 工具兩邊都站:

  • CompareStr、字串 < 運算子與 TComparer<string>.Default 是 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 並把這件事說明白。更麻煩的問題出在任何一條程式路徑各抓一種最近便比較的公式引擎裡:= 與 < 運算子轉大寫比碼位,COUNTIF 準則的 > / < 分支用分大小寫的 Variant 比較,VLOOKUP 內部又是另一套(VLOOKUP("ABC",A1:A20,1,FALSE) 找不到 abc 正是這麼來的),範圍排序則走地區設定呼叫。每一條路徑單獨看都說得過去;合起來,同一欄資料就有了三種順序

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,0 代表失敗,不是「小於」

想從 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 群組的 3 個成員再與那 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));   // 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;

用自訂比較器時,SortRange 把原始鍵值直接遞過來,空白要放哪裡由你決定,上面的比較器才會先處理 Null。遞減鍵值時,排序會把比較器的回傳值變號,比較器沒把這件事算進去,空白在那個方向就會跑到頂端。這樣排出的欄位,不再處於近似 VLOOKUP 或二分搜尋 XLOOKUP 預期的順序;資料排序不同時這些模式會出什麼事,見XLOOKUP 與 XMATCH 二分搜尋模式指南

同一份活頁簿為什麼在別台機器上排序不同?

同一份活頁簿在另一台機器上可能排出不同結果,因為 Excel 的文字順序依賴 Windows 使用者地區設定,而 HotXLS 刻意沿用這份依賴。字組排序隨語言而異:瑞典語定序把 ä 放在 z 後面,英語與德語卻讓它緊挨著 a,所以斯德哥爾摩同事重新計算過的檔案,COUNTIF(...,">y") 的結果可能與芝加哥桌上同一份檔案不同。HotXLS 傳 LOCALE_USER_DEFAULT,就是為了讓它在同一台機器上的答案與 Excel 相等;改成釘死任何固定地區設定,看似整齊,代價是凡設定不同的機器,HotXLS 都與 Excel 對不上

對伺服器端產生檔案而言,這帶來三個實際的後果:

  • 算數的地區設定,是行程執行所用帳戶的那一份。Windows 服務或 IIS 應用程式集區的地區設定可能與開發者桌面不同,IDE 裡看到的結果,不一定就是生產環境會算出的
  • 寫進檔案的快取公式結果,反映產生機器的地區設定。Excel 會用自己的地區設定重新計算,檔案拿到別處開啟重算後,值可能變——Excel 對自己的檔案也是這副脾氣
  • 地區設定之間的分歧主要在帶重音的字母、某些語言視為單一字母的字母組合,以及非拉丁文字。測試資料只放普通英文單詞,永遠照不出差異,樣本裡請放幾個 Ångström、Öberg 這類名字

對齊 Excel 的路徑就是 Windows 那條:凡 Win32 與 Win64 建置,無論用 Delphi、C++Builder 還是 Lazarus 加 Free Pascal 編譯,都呼叫同一個 CompareStringW。建置目標是非 Windows 平台時,XlsCompareText 改用 RTL 的 WideCompareText,順序來自該平台自己的定序函式庫

普通單詞在哪套規則下都排得一樣,錯的定序因此往往能撐過測試,直到帶連字號的零件編號、標點或帶重音的客戶名稱出現。從比較運算子到 SortRange,XLS 與 XLSX 兩個引擎裡,HotXLS 對這些輸入都給出 Excel 的答案。支援的 Delphi 與 C++Builder 版本、授權與試用下載,見 HotXLS Delphi Excel component 產品頁