So sánh text HotXLS: Word sort kiểu Excel trong Delphi

HotXLS Delphi Component so sánh hai giá trị text như Excel 16 kể từ v2.384.67: không phân biệt chữ hoa thường, theo thứ tự "word sort" của Windows user locale, tức thứ tự mà CompareStringW trả về với cờ NORM_IGNORECASE. Dấu gạch ngang và dấu nháy đơn bị bỏ qua ở lượt đầu và chỉ phá hòa, nên ="a-b">"ab" là TRUE, trong khi các dấu câu khác đứng trước chữ số và chữ cái, nên ="a~b"<"ab" cũng là TRUE. Cùng thứ tự đó giờ dẫn đường cho các toán tử so sánh, tiêu chí > / <, sort range và VLOOKUP

Chẳng ai đăng một bug mang tiêu đề "collation mismatch" cả. Các report nói rằng COUNTIF(A:A,">M") đếm nhiều hơn hai hàng trên server so với trong Excel, rằng một bảng giá do reporting service sort đặt X-100 ở một chỗ Excel không bao giờ đặt, hay rằng VLOOKUP("ABC",...) trả về #N/A dù cột rõ ràng có abc. Cả ba đều quy về cùng một câu hỏi: khi cả hai operand là text, cái nào nhỏ hơn? Excel có một câu trả lời chính xác, nó không phải cái phần lớn code Delphi đưa ra, và trước v2.384.67 HotXLS đã cho ba câu trả lời khác nhau tùy code path nào hỏi

Excel dùng luật gì để so sánh hai chuỗi text?

Excel so sánh text bằng word sort của user locale, bỏ qua chữ hoa thường. Word sort là collation mặc định của các hàm so sánh NLS trên Windows: chữ cái so theo trật tự ngôn ngữ học thay vì code point, chữ có dấu ngồi cạnh chữ gốc của nó, và có hai ký tự được đối xử đặc biệt. Dấu gạch ngang - và dấu nháy đơn ' bị bỏ qua ở lượt một, nên co-op và coop đổ về cạnh nhau, và chỉ khi phần còn lại của hai chuỗi hòa nhau thì sự hiện diện của chúng mới định thứ tự. Mọi dấu câu khác đều có nghĩa và đứng trước chữ số, còn chữ số đứng trước chữ cái

Bảng dưới cho thấy điều đó nghĩa là gì trong thực tế, cạnh hai phép so sánh mà một dev Delphi dễ với tới nhất. Cột Excel giữ các phán quyết Excel 16 trả về cho IF(A<B,...), và HotXLS tái tạo đúng từ v2.384.67

A vs B	Excel 16 / HotXLS	CompareStr (ordinal)	CompareText
"a-b" vs "ab"	lớn hơn	nhỏ hơn	nhỏ hơn
"a'b" vs "ab"	lớn hơn	nhỏ hơn	nhỏ hơn
"a~b" vs "ab"	nhỏ hơn	lớn hơn	lớn hơn
"a_b" vs "ab"	nhỏ hơn	nhỏ hơn	lớn hơn
"ab" vs "AB"	bằng	lớn hơn	bằng
"é" vs "f"	nhỏ hơn	lớn hơn	lớn hơn
"Z" vs "f"	lớn hơn	nhỏ hơn	lớn hơn

Hai hệ quả dễ bị bỏ sót. Thứ nhất, vai trò phá hòa của dấu gạch ngang nghĩa là ="a-b"="ab" là FALSE: hai chuỗi là hàng xóm thân thiết trong thứ tự sort, nhưng không bằng nhau. Thứ hai, phép bằng bỏ qua chữ hoa thường hoàn toàn, nên ab, AB và Ab là cùng một khóa dưới góc nhìn của mọi phép so sánh. Sort 20 từ thử nghiệm bằng Range.Sort của Excel cho ra 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; trong nhóm ab, vị trí của ký tự bị bỏ qua định chỗ đứng

Thứ tự text của Excel được ghim xuống thế nào?

Thứ tự text của Excel được xác định bằng đo đạc, không phải bằng tài liệu, vì tài liệu của Excel không nêu tên collation. Phép test sinh 4,000 cặp chuỗi ngẫu nhiên từ dấu câu ASCII, chữ số, cả hai dạng chữ, dấu cách, é, ß, ä, chữ Hán, dạng full-width và non-breaking space, với độ dài từ 0 tới 4 và nửa số cặp được dựng thành dạng gần trượt của nhau. Excel 16 đánh giá IF(A<B,-1,IF(A=B,0,1)) cho từng cặp, và các phán quyết được đối chiếu với API so sánh Windows với những bộ cờ khác nhau

• NORM_IGNORECASE một mình (word sort mặc định, user locale): không lệch nào thật sự. Bảy khác biệt duy nhất là các ô mà toàn bộ nội dung là ', thứ Excel tiêu hóa thành ký tự prefix text, nên chúng là tạp chất lấy mẫu chứ không phải khác biệt collation
• NORM_IGNORECASE với SORT_STRINGSORT: 41 lần lệch. String sort coi dấu gạch ngang và dấu nháy đơn là ký hiệu thường, chính là hành vi mà Excel không có
• Thêm NORM_IGNOREWIDTH: sai theo kiểu khác, vì nó khiến dạng full-width và half-width của cùng một chữ so ra bằng nhau, còn Excel thì tách chúng

Một phép kiểm tra thứ hai, tuyển tay, so tất cả 190 cặp rút từ 20 từ khó chịu với kết quả Range.Sort của Excel trên cùng cột. Cả hai đều khớp word sort NORM_IGNORECASE thuần, và 190 phán quyết đó cộng với thứ tự đã sort giờ nằm trong bộ regression của HotXLS, chạy qua cả engine kinh điển TXLSWorkbook lẫn engine XLSX thuần TXLSXWorkbook

Vì sao CompareText và so sánh ordinal làm sai?

CompareText và so sánh ordinal làm sai thứ tự của Excel vì chúng so các đơn vị code UTF-16, còn thứ tự code point đặt dấu câu ở những chỗ tùy hứng so với chữ cái. Dấu gạch ngang là U+002D và dấu nháy đơn U+0027, đều dưới mọi chữ cái, nên so sánh ordinal bảo "a-b" nhỏ hơn "ab" thay vì coi dấu gạch ngang là người phá hòa. Dấu ngã U+007E nằm trên mọi chữ cái, nên "a~b" lọt ra lớn hơn, ngược với Excel. CompareText trong Delphi RTL chỉ gộp a..z thành chữ hoa rồi so đơn vị code, thêm một điểm méo mó nữa: gạch dưới U+005F nằm giữa chữ hoa và chữ thường, nên gộp thành chữ hoa đẩy "a_b" từ dưới "ab" lên trên nó. Chẳng hàm nào biết é thuộc về giữa e và f

Những công cụ Delphi quen thuộc rơi vào cả hai phía của ranh giới:

• CompareStr, toán tử < cho chuỗi và TComparer<string>.Default (thứ gọi CompareStr) là ordinal và phân biệt chữ hoa thường, nên TArray.Sort<string> không kèm comparer sẽ đặt Z trước f
• CompareText và SameText là ordinal sau khi gộp chữ hoa thường kiểu ASCII
• AnsiCompareText và WideCompareText trong Delphi RTL trên Windows gọi CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), đúng lời gọi khớp với Excel. Một TStringList có sort với thiết lập mặc định (UseLocale True, CaseSensitive False) đi qua AnsiCompareText và vì thế cũng đồng ý với Excel
• Trên mục tiêu POSIX, Delphi RTL điều hướng AnsiCompareText qua một ICU collator, một thuật toán khác với luật dấu câu khác, còn AnsiCompareText của Free Pascal trên Windows gọi CompareStringA sau khi chuyển sang ANSI code page, thứ làm mất mọi ký tự trang đó không biểu diễn được

Nghĩa là các hàm RTL biết locale đúng trên Windows nhờ bản hiện thực, không phải nhờ hợp đồng, và code cần thứ tự của Excel thì nên tự gọi API một cách tường minh. HotXLS bên trong cũng từng là hỗn hợp như vậy. Các toán tử so sánh hoa hóa cả hai chuỗi rồi so code point, nhánh > / < của các hàm tiêu chí dùng so sánh Variant phân biệt chữ hoa thường của Delphi, và VLOOKUP / HLOOKUP khớp text bằng đúng phép so Variant phân biệt chữ hoa thường ấy, đó là lý do VLOOKUP("ABC",A1:A20,1,FALSE) không tìm thấy abc. Phép sort range đã dùng WideCompareText từ trước. Ba đường, ba thứ tự

HotXLS v2.384.67 đã đổi gì?

Kể từ v2.384.67, các phép so text với text trong đường tính toán và sort của HotXLS đi qua một hàm duy nhất, XlsCompareText trong lxStandard.pas, hàm gọi CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) rồi trừ CSTR_EQUAL. Những nơi gọi là sáu toán tử so sánh, so sánh từng phần tử trong array formula, các nhánh >, <, >= và <= của tiêu chí kiểu COUNTIF cùng các hàm database, VLOOKUP và HLOOKUP (chính xác lẫn xấp xỉ), các helper xếp thứ tự đằng sau các hàm dynamic-array và XLOOKUP / XMATCH, cùng phép sort range của cả hai engine. Điều hướng sort range qua cùng một hàm bảo đảm thứ tự sort và thứ tự so sánh không thể tách xa nhau lần nữa, điều quan trọng vì VLOOKUP xấp xỉ trên text chỉ có nghĩa khi cột được sort theo đúng thứ tự mà phép lookup so sánh

uses
  System.Variants, lxHandleX;

var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Sheets.Add('Data');  // Calculate đánh giá trên sheet active
    Writeln(VarToStr(Book.Calculate('="a-b">"ab"')));   // True: dấu gạch ngang chỉ phá hòa
    Writeln(VarToStr(Book.Calculate('="a-b"="ab"')));   // False: hòa bị phá, không bằng
    Writeln(VarToStr(Book.Calculate('="a~b"<"ab"')));   // True: dấu câu đứng trước
    Writeln(VarToStr(Book.Calculate('="ABC"="abc"')));  // True: bỏ qua chữ hoa thường
  finally
    Book.Free;
  end;
end;

So sánh chéo kiểu dữ liệu là một luật riêng và không đổi: mọi số nằm dưới mọi giá trị text và mọi giá trị text nằm dưới mọi boolean, như đã nói trong bài về chuỗi so sánh, operand rỗng và SUMIF. Word sort chỉ áp dụng khi cả hai operand đều là text. Khớp wildcard cũng riêng một chuyện: một tiêu chí như "a*" hay "=ab" là phép thử pattern hay phép thử bằng, được nói trong cẩm nang wildcard Excel trong COUNTIF, MATCH và DSUM, còn collation bàn ở đây chỉ định đoạt các toán tử xếp thứ tự

Ví dụ kế nạp 20 từ thử nghiệm vào một cột, sort bằng TXLSXWorksheet.SortRange, rồi thử một phép đếm tiêu chí và một phép lookup. Các con số đếm là những gì Excel 16 trả về cho cùng cột đó

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 trên cùng cột: 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")')));

    // Trước v2.384.67 là #N/A: phép lookup so phân biệt chữ hoa thường
    Sheet.Cells[1, 3].Formula := '=VLOOKUP("ABC",A1:A20,1,FALSE)';
    Book.Recalculate;
    Writeln(VarToStr(Sheet.Cells[1, 3].Value));                // abc

    // Một cột khóa, tăng dần: 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 dùng merge sort ổn định, nên ab, AB và Ab, những thứ so ra bằng nhau, giữ nguyên thứ tự tương đối trước khi sort. Ô trống đổ về cuối theo cả hai chiều, như trong Excel

Làm sao khớp thứ tự sort của Excel trong code Delphi của riêng tôi?

Để khớp thứ tự text của Excel trong code Delphi của bạn, hãy gọi CompareStringW với LOCALE_USER_DEFAULT và NORM_IGNORECASE, và đừng thêm SORT_STRINGSORT hay NORM_IGNOREWIDTH. Giá trị trả về không phải một kết quả so sánh có dấu: API trả về CSTR_LESS_THAN (1), CSTR_EQUAL (2) hay CSTR_GREATER_THAN (3), và 0 khi lời gọi thất bại. Trừ 2 để có quy ước âm / không / dương quen thuộc, và kiểm tra 0 trước, vì một thất bại bị nhầm thành kết quả sẽ thành -2, một "nhỏ hơn" âm thầm

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

// Thứ tự text của Excel: word sort theo user locale, không phân biệt hoa thường
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 là thất bại, không phải kết quả so sánh
  Result := R - CSTR_EQUAL;    // 1/2/3 trở thành -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 (bằng nhau, thứ tự nào cũng được), a-b, -ab, abc
end;

TArray.Sort không ổn định, nên những khóa so ra bằng nhau, như ab và AB, có thể lọt ra theo thứ tự nào cũng được; nếu thứ tự gốc của các khóa bằng nhau có ý nghĩa, hãy sort một mảng chỉ số với vị trí gốc làm khóa phụ. Trường hợp ngược lại cũng có: thỉnh thoảng một cột không được theo thứ tự của Excel, chẳng hạn part number nơi X-100 và X100 là hai mã khác nhau và nên sort theo code point. TXLSXWorksheet.SortRange có một overload nhận TXLSSortCompareEvent, một method với chữ ký function(const Left, Right: Variant): Integer of object, và dùng nó thay cho phép so sánh có sẵn

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
  // Comparer tùy chỉnh cũng nhận ô rỗng (dạng Null): tự xếp chúng
  if VarIsNull(Left) or VarIsNull(Right) then
    Exit(Ord(VarIsNull(Left)) - Ord(VarIsNull(Right)));
  Result := CompareStr(VarToStr(Left), VarToStr(Right));   // ordinal, phân biệt hoa thường
end;

var
  Sheet: TXLSXWorksheet;   // một sheet đã điền, hàng 2..501, cột A..D
  Order: TPartNumberOrder;
begin
  // ...
  Order := TPartNumberOrder.Create;
  try
    // khóa theo cột A, tăng dần
    Sheet.SortRange(2, 1, 501, 4, [1], [False], xlsSortByRows,
      xlsSortExcelLike, Order.Compare);
  finally
    Order.Free;
  end;
end;

Khi một comparer tùy chỉnh được đưa vào, HotXLS bỏ qua bộ xử lý ô trống của riêng nó và đưa thẳng các giá trị khóa thô, nên comparer phải tự xử Null. Với khóa giảm dần, HotXLS đảo dấu mọi thứ comparer trả về, điều cũng đẩy ô trống lên đỉnh trừ khi comparer đã tính đến chuyện đó. Nhớ rằng một cột được sort kiểu này không còn nằm trong thứ tự mà VLOOKUP xấp xỉ của Excel hay XLOOKUP binary search mong đợi; những cái bẫy của các chế độ đó trên dữ liệu sort theo thứ tự khác được nói trong cẩm nang chế độ binary search của XLOOKUP và XMATCH

Vì sao cùng một workbook có thể sort khác nhau trên một máy khác?

Cùng một workbook có thể sort khác nhau trên một máy khác vì thứ tự text của Excel phụ thuộc Windows user locale, và HotXLS cố tình đi theo sự phụ thuộc đó. Word sort là chuyện ngôn ngữ: collation Thụy Điển, chẳng hạn, đặt ä sau z, nơi tiếng Anh và tiếng Đức giữ nó cạnh a. Excel kế thừa điều đó từ locale nó chạy dưới, nên một workbook do đồng nghiệp ở Stockholm tính lại có thể trả một COUNTIF(...,">y") khác với cùng tệp trên một desktop ở Chicago. HotXLS đưa LOCALE_USER_DEFAULT để kết quả của nó bằng kết quả của Excel trên cùng một máy; một locale cố định nào khác sẽ khiến HotXLS bất đồng với Excel trên mọi máy có thiết lập khác

Ba hệ quả thực tế cho việc sinh phía server:

• Locale có giá trị là locale của account mà tiến trình chạy dưới. Một Windows service hay IIS application pool có thể dùng định dạng vùng khác với desktop của dev, nên kết quả quan sát trong IDE không tự động là thứ production tính ra
• Kết quả công thức được cache ghi vào tệp phản ánh locale của máy sinh ra nó. Excel tính lại với locale của chính nó, nên một giá trị có thể đổi khi tệp được mở ở nơi khác và tính lại; đó là hành vi của Excel, không phải tạp chất của HotXLS
• Các locale bất đồng chủ yếu ở chữ có dấu, ở các tổ hợp chữ mà vài ngôn ngữ coi là một chữ, và ở các hệ chữ phi Latin, nên dữ liệu test bó hẹp trong từ tiếng Anh trơn sẽ không lộ ra vấn đề

Ranh giới platform thì đơn giản. HotXLS là một thư viện Windows, dựng cho Win32 và Win64 với Delphi và C++Builder và cho mục tiêu win32 / win64 với Lazarus và Free Pascal, và tất cả các bản dựng ấy đều gọi cùng một CompareStringW. Không có đường collation phi Windows riêng nào. Fallback duy nhất là cho một lời gọi API thất bại: nếu CompareStringW trả về 0, XlsCompareText so các chuỗi đã hoa hóa theo đơn vị code thay vì ném exception giữa chừng một lần tính lại, cách này giữ phép tính chạy tiếp nhưng không còn bảo đảm thứ tự của Excel

Tra nhanh: so sánh text kiểu Excel trong HotXLS

• Luật: word sort theo user locale với NORM_IGNORECASE, không SORT_STRINGSORT, không NORM_IGNOREWIDTH, có trong HotXLS kể từ v2.384.67
• - và ' chỉ phá hòa: ="a-b">"ab" là TRUE và ="a-b"="ab" là FALSE
• Dấu câu khác sort trước chữ số, chữ số trước chữ cái: ="a~b"<"ab" và ="a0"<"ab" là TRUE
• Chữ hoa thường không bao giờ có nghĩa: ="ABC"="abc" là TRUE và VLOOKUP("ABC",...) tìm thấy abc
• Các đường được phủ: toán tử so sánh, so sánh mảng, tiêu chí > / <, VLOOKUP / HLOOKUP, xếp thứ tự dynamic-array, SortRange ở cả hai engine
• Không thuộc luật này: kiểu hỗn hợp (number < text < boolean) và tiêu chí wildcard, mỗi thứ có luật riêng
• Trong code Delphi: CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), kiểm tra 0, trừ CSTR_EQUAL; tránh CompareText, CompareStr và TComparer<string>.Default khi kết quả phải khớp Excel
• Kết quả phụ thuộc locale của account chạy code, ở Excel lẫn HotXLS như nhau

Những từ thông thường sort như nhau dưới mọi luật, nên chỉ các mã có gạch ngang, dấu câu và tên có dấu mới phơi ra một collation sai. HotXLS giờ cho câu trả lời kiểu Excel với tất cả chúng ở cả engine XLS lẫn XLSX. Chi tiết về license, các phiên bản Delphi và C++Builder được hỗ trợ cùng bản dùng thử nằm trên trang HotXLS Delphi Excel component