Perbandingan Teks HotXLS: Urutan Word Sort Excel di Delphi

HotXLS Delphi Component membandingkan dua nilai teks seperti yang dilakukan Excel 16 sejak v2.384.67: tanpa memedulikan huruf besar-kecil, dalam urutan "word sort" dari user locale Windows, yaitu apa yang dikembalikan CompareStringW dengan flag NORM_IGNORECASE. Tanda hubung dan apostrof dilewati di pass pertama dan hanya memutus seri, jadi ="a-b">"ab" TRUE, sementara tanda baca lain mengurut sebelum digit dan huruf, jadi ="a~b"<"ab" juga TRUE. Urutan yang sama kini menggerakkan operator perbandingan, kriteria > / <, pengurutan range, dan VLOOKUP

Tak ada yang melaporkan bug berjudul "collation mismatch". Laporannya berbunyi COUNTIF(A:A,">M") menghitung dua baris lebih banyak di server daripada di Excel, daftar harga yang diurutkan reporting service menaruh X-100 di tempat yang tak akan dipilih Excel, atau VLOOKUP("ABC",...) mengembalikan #N/A padahal kolomnya jelas berisi abc. Ketiganya bermuara ke pertanyaan yang sama: ketika kedua operand teks, mana yang lebih kecil? Excel punya jawaban presisi, dan itu bukan yang diberikan kebanyakan kode Delphi, sementara sebelum v2.384.67 HotXLS memberi tiga jawaban berbeda tergantung jalur kode mana yang bertanya

Aturan apa yang dipakai Excel untuk membandingkan dua string teks?

Excel membandingkan teks dengan word sort milik user locale, mengabaikan huruf besar-kecil. Word sort adalah collation default dari fungsi perbandingan Windows NLS: huruf dibandingkan menurut urutan linguistiknya alih-alih code point-nya, huruf beraksen duduk di samping huruf dasarnya, dan dua karakter mendapat perlakuan khusus. Tanda hubung - dan apostrof ' diabaikan di pass pertama, sehingga co-op dan coop mendarat bersebelahan, dan hanya ketika sisa string seri, kehadiran mereka yang menentukan urutan. Setiap tanda baca lain signifikan dan mengurut sebelum digit, dan digit mengurut sebelum huruf

Tabel berikut menunjukkan artinya dalam praktik, berdampingan dengan dua perbandingan yang paling mungkin dipakai developer Delphi. Kolom Excel memuat vonis yang dikembalikan Excel 16 untuk IF(A<B,...), yang direproduksi HotXLS sejak v2.384.67

A vs B	Excel 16 / HotXLS	CompareStr (ordinal)	CompareText
"a-b" vs "ab"	lebih besar	lebih kecil	lebih kecil
"a'b" vs "ab"	lebih besar	lebih kecil	lebih kecil
"a~b" vs "ab"	lebih kecil	lebih besar	lebih besar
"a_b" vs "ab"	lebih kecil	lebih kecil	lebih besar
"ab" vs "AB"	sama	lebih besar	sama
"é" vs "f"	lebih kecil	lebih besar	lebih besar
"Z" vs "f"	lebih besar	lebih kecil	lebih besar

Dua konsekuensi mudah terlewat. Pertama, peran tie-breaker dari tanda hubung berarti ="a-b"="ab" FALSE: kedua string bertetangga dekat dalam pengurutan, tapi tidak sama. Kedua, kesamaan mengabaikan huruf besar-kecil sepenuhnya, jadi ab, AB, dan Ab adalah key yang sama bagi perbandingan mana pun. Mengurutkan 20 kata uji dengan Range.Sort Excel menghasilkan 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; di dalam grup ab, posisi karakter yang diabaikan yang menentukan

Bagaimana urutan teks Excel dipastikan?

Urutan teks Excel diidentifikasi lewat pengukuran, bukan lewat dokumentasi, karena dokumentasi Excel tak menyebut collation-nya. Tesnya menghasilkan 4.000 pasangan string acak dari tanda baca ASCII, digit, kedua jenis huruf besar-kecil, spasi, é, ß, ä, karakter Tionghoa, bentuk full-width, dan non-breaking space, dengan panjang 0 sampai 4 dan setengah pasangan dibangun sebagai near-miss satu sama lain. Excel 16 mengevaluasi IF(A<B,-1,IF(A=B,0,1)) untuk setiap pasangan, dan vonisnya dicocokkan dengan API perbandingan Windows dengan set flag berbeda

• NORM_IGNORECASE saja (word sort default, user locale): tak ada ketidakcocokan sungguhan. Tujuh perbedaan yang ada semuanya sel yang seluruh isinya ', yang ditelan Excel sebagai karakter prefix teks, jadi itu artefak sampling dan bukan perbedaan collation
• NORM_IGNORECASE dengan SORT_STRINGSORT: 41 ketidakcocokan. String sort memperlakukan tanda hubung dan apostrof sebagai simbol biasa, persis perilaku yang tidak dimiliki Excel
• Menambahkan NORM_IGNOREWIDTH: salah dengan cara lain, karena ia membuat bentuk full-width dan half-width dari huruf yang sama terbanding sama, dan Excel memisahkannya

Pemeriksaan kedua yang dikurasi tangan membandingkan seluruh 190 pasangan dari 20 kata yang liku-liku dengan hasil Range.Sort Excel pada kolom yang sama. Keduanya sepakat dengan word sort NORM_IGNORECASE polos, dan 190 vonis itu plus urutan terurutnya kini jadi bagian dari regression suite HotXLS, dijalankan lewat engine classic TXLSWorkbook maupun engine native XLSX TXLSXWorkbook

Mengapa CompareText dan perbandingan ordinal keliru?

CompareText dan perbandingan ordinal menilai urutan Excel dengan salah karena keduanya membandingkan code unit UTF-16, dan urutan code point menaruh tanda baca di posisi arbitrer relatif terhadap huruf. Tanda hubung adalah U+002D dan apostrof U+0027, keduanya di bawah semua huruf, jadi perbandingan ordinal menyebut "a-b" lebih kecil dari "ab" alih-alih memperlakukan tanda hubung sebagai tie-breaker. Tilde U+007E duduk di atas semua huruf, jadi "a~b" keluar lebih besar, kebalikan dari Excel. CompareText di Delphi RTL hanya melipat a..z ke huruf besar lalu membandingkan code unit, yang menambah distorsi kedua: underscore U+005F terletak di antara huruf besar dan huruf kecil, sehingga pelipatan ke huruf besar memindahkan "a_b" dari bawah "ab" ke atasnya. Tak satu pun dari kedua fungsi itu tahu bahwa é letaknya di antara e dan f

Peralatan Delphi biasa jatuh di kedua sisi garis:

• CompareStr, operator < string, dan TComparer<string>.Default (yang memanggil CompareStr) bersifat ordinal dan case-sensitive, jadi TArray.Sort<string> tanpa comparer menaruh Z sebelum f
• CompareText dan SameText ordinal setelah pelipatan huruf besar-kecil khusus ASCII
• AnsiCompareText dan WideCompareText di Delphi RTL pada Windows memanggil CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), panggilan yang sama dengan yang cocok dengan Excel. TStringList terurut dengan default-nya (UseLocale True, CaseSensitive False) melewati AnsiCompareText dan karena itu juga sepakat dengan Excel
• Di target POSIX, Delphi RTL mengalirkan AnsiCompareText lewat ICU collator, algoritma berbeda dengan aturan tanda baca berbeda, dan AnsiCompareText milik Free Pascal di Windows memanggil CompareStringA setelah konversi ke ANSI code page, yang menghilangkan karakter apa pun yang tak terwakili di code page itu

Jadi fungsi RTL yang sadar locale benar di Windows karena implementasi, bukan karena kontrak, dan kode yang butuh urutan Excel lebih baik memanggil API-nya secara eksplisit. HotXLS punya campuran yang sama di dalam. Operator perbandingan mengubah kedua string ke huruf besar lalu membandingkan code point, cabang > / < dari fungsi kriteria memakai perbandingan Variant case-sensitive milik Delphi, dan VLOOKUP / HLOOKUP mencocokkan teks dengan perbandingan Variant case-sensitive itu juga, itulah kenapa VLOOKUP("ABC",A1:A20,1,FALSE) tak bisa menemukan abc. Sort range sudah memakai WideCompareText. Tiga jalur, tiga urutan

Apa yang berubah di HotXLS v2.384.67?

Sejak v2.384.67, perbandingan teks-lawan-teks di jalur kalkulasi dan pengurutan HotXLS melewati satu fungsi, XlsCompareText di lxStandard.pas, yang memanggil CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) dan mengurangkan CSTR_EQUAL. Pemanggilnya adalah enam operator perbandingan, perbandingan elemen-demi-elemen di array formula, cabang >, <, >=, dan <= dari kriteria ala COUNTIF dan fungsi database, VLOOKUP dan HLOOKUP (exact maupun approximate), helper pengurutan di balik fungsi dynamic array dan XLOOKUP / XMATCH, serta sort range kedua engine. Mengalirkan sort range lewat fungsi yang sama menjamin urutan sort dan urutan perbandingan tak bisa lagi terpisah, hal yang penting karena VLOOKUP approximate pada teks hanya bermakna ketika kolom diurutkan dalam urutan yang sama dengan yang dipakai lookup saat membandingkan

uses
  System.Variants, lxHandleX;

var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Sheets.Add('Data');  // Calculate mengevaluasi terhadap active sheet
    Writeln(VarToStr(Book.Calculate('="a-b">"ab"')));   // True: tanda hubung hanya memutus seri
    Writeln(VarToStr(Book.Calculate('="a-b"="ab"')));   // False: seri diputus, bukan sama
    Writeln(VarToStr(Book.Calculate('="a~b"<"ab"')));   // True: tanda baca dulu
    Writeln(VarToStr(Book.Calculate('="ABC"="abc"')));  // True: huruf besar-kecil diabaikan
  finally
    Book.Free;
  end;
end;

Perbandingan lintas tipe adalah aturan terpisah dan tidak berubah: setiap angka berada di bawah setiap nilai teks dan setiap nilai teks di bawah setiap boolean, seperti dijelaskan di artikel tentang comparison chain, operand kosong, dan SUMIF. Word sort hanya berlaku begitu kedua operand teks. Pencocokan wildcard juga terpisah: kriteria seperti "a*" atau "=ab" adalah uji pattern atau kesamaan, dibahas di panduan wildcard Excel di COUNTIF, MATCH, dan DSUM, dan collation yang dibahas di sini hanya menentukan operator pengurutan

Contoh berikut memuat 20 kata uji ke sebuah kolom, mengurutkannya dengan TXLSXWorksheet.SortRange, dan mengecek satu hitungan kriteria serta satu lookup. Hitungannya adalah yang dikembalikan Excel 16 untuk kolom yang sama

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 pada kolom yang sama: 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")')));

    // Dulunya #N/A sebelum v2.384.67: lookup membandingkan case-sensitive
    Sheet.Cells[1, 3].Formula := '=VLOOKUP("ABC",A1:A20,1,FALSE)';
    Book.Recalculate;
    Writeln(VarToStr(Sheet.Cells[1, 3].Value));                // abc

    // Satu kolom key, menaik: 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 memakai merge sort yang stabil, jadi ab, AB, dan Ab, yang terbanding sama, mempertahankan urutan relatif mereka sebelum sort. Sel kosong pergi ke akhir di kedua arah, seperti di Excel

Bagaimana mencocokkan urutan sort Excel di kode Delphi sendiri?

Untuk mencocokkan urutan teks Excel di kode Delphi Anda sendiri, panggil CompareStringW dengan LOCALE_USER_DEFAULT dan NORM_IGNORECASE, dan jangan menambahkan SORT_STRINGSORT atau NORM_IGNOREWIDTH. Nilai kembaliannya bukan hasil perbandingan bertanda: API mengembalikan CSTR_LESS_THAN (1), CSTR_EQUAL (2), atau CSTR_GREATER_THAN (3), dan 0 ketika panggilan gagal. Kurangkan 2 untuk mendapat konvensi negatif / nol / positif biasa, dan tes 0 lebih dulu, karena kegagalan yang salah dianggap hasil menjadi -2, sebuah "lebih kecil" yang diam-diam

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

// Urutan teks Excel: word sort user locale, case-insensitive
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 adalah kegagalan, bukan hasil perbandingan
  Result := R - CSTR_EQUAL;    // 1/2/3 menjadi -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 (sama, urutan mana pun), a-b, -ab, abc
end;

TArray.Sort tidak stabil, jadi key yang terbanding sama, seperti ab dan AB, bisa keluar dalam urutan mana pun; kalau urutan asli key-key yang sama penting, urutkan array index dengan posisi asli sebagai key sekunder. Kasus sebaliknya juga muncul: kadang sebuah kolom justru tidak boleh mengikuti urutan Excel, misalnya part number yang X-100 dan X100-nya adalah kode berbeda dan seharusnya diurutkan menurut code point. TXLSXWorksheet.SortRange punya overload yang menerima TXLSSortCompareEvent, sebuah method dengan signature function(const Left, Right: Variant): Integer of object, dan memakainya alih-alih perbandingan bawaan

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
  // Custom comparer juga menerima sel kosong (sebagai Null): tempatkan sendiri
  if VarIsNull(Left) or VarIsNull(Right) then
    Exit(Ord(VarIsNull(Left)) - Ord(VarIsNull(Right)));
  Result := CompareStr(VarToStr(Left), VarToStr(Right));   // ordinal, case-sensitive
end;

var
  Sheet: TXLSXWorksheet;   // sheet terisi, baris 2..501, kolom A..D
  Order: TPartNumberOrder;
begin
  // ...
  Order := TPartNumberOrder.Create;
  try
    // keyed di kolom A, menaik
    Sheet.SortRange(2, 1, 501, 4, [1], [False], xlsSortByRows,
      xlsSortExcelLike, Order.Compare);
  finally
    Order.Free;
  end;
end;

Ketika custom comparer diberikan, HotXLS melewati penanganan sel kosong miliknya dan meneruskan nilai key mentah, jadi comparer harus mengurus Null. Untuk key menurun, HotXLS menegasikan apa pun yang dikembalikan comparer, yang juga memindahkan sel kosong ke atas kecuali comparer mengakomodasinya. Ingat bahwa kolom yang diurutkan dengan cara ini tidak lagi berada dalam urutan yang diharapkan VLOOKUP approximate milik Excel atau XLOOKUP binary search; jebakan mode-mode itu pada data yang diurutkan dengan urutan berbeda dibahas di panduan mode binary search XLOOKUP dan XMATCH

Mengapa workbook yang sama bisa terurut berbeda di mesin lain?

Workbook yang sama bisa terurut berbeda di mesin lain karena urutan teks Excel bergantung pada user locale Windows, dan HotXLS sengaja mengikuti ketergantungan itu. Word sort spesifik per bahasa: collation Swedia, misalnya, menaruh ä setelah z, tempat bahasa Inggris dan Jerman menjaganya tetap di samping a. Excel mewarisi itu dari locale tempatnya berjalan, jadi workbook yang dihitung ulang oleh kolega di Stockholm bisa mengembalikan COUNTIF(...,">y") yang berbeda dari file yang sama di desktop Chicago. HotXLS meneruskan LOCALE_USER_DEFAULT agar hasilnya sama dengan Excel di mesin yang sama; locale tetap apa pun akan membuat HotXLS berselisih dengan Excel di setiap mesin yang settingnya berbeda

Tiga konsekuensi praktis menyusul untuk generasi sisi server:

• Locale yang berlaku adalah locale akun tempat proses berjalan. Windows service atau application pool IIS bisa memakai format regional yang berbeda dari desktop developer, jadi hasil yang teramati di IDE belum tentu yang dihitung production
• Hasil formula ter-cache yang ditulis ke file mencerminkan locale mesin pembangkitnya. Excel menghitung ulang dengan locale miliknya sendiri, jadi sebuah nilai bisa berubah ketika file dibuka di tempat lain dan dihitung ulang; itu perilaku Excel, bukan artefak HotXLS
• Locale berselisih terutama pada huruf beraksen, pada kombinasi huruf yang oleh sebagian bahasa diperlakukan sebagai satu huruf, dan pada skrip non-Latin, jadi data uji yang terbatas pada kata-kata Inggris polos tak akan mengungkap masalahnya

Batas platformnya sederhana. HotXLS adalah library Windows, dibangun untuk Win32 dan Win64 dengan Delphi dan C++Builder serta untuk target win32 / win64 dengan Lazarus dan Free Pascal, dan semua build itu memanggil CompareStringW yang sama. Tak ada jalur collation non-Windows terpisah. Satu-satunya fallback adalah untuk panggilan API yang gagal: kalau CompareStringW mengembalikan 0, XlsCompareText membandingkan string hasil pelipatan huruf besar menurut code unit alih-alih melempar exception di tengah penghitungan ulang, yang menjaga kalkulasi tetap jalan tapi tak lagi menjamin urutan Excel

Referensi cepat: perbandingan teks Excel di HotXLS

• Aturan: word sort user locale dengan NORM_IGNORECASE, tanpa SORT_STRINGSORT, tanpa NORM_IGNOREWIDTH, di HotXLS sejak v2.384.67
• - dan ' hanya memutus seri: ="a-b">"ab" TRUE dan ="a-b"="ab" FALSE
• Tanda baca lain mengurut sebelum digit, digit sebelum huruf: ="a~b"<"ab" dan ="a0"<"ab" TRUE
• Huruf besar-kecil tak pernah berpengaruh: ="ABC"="abc" TRUE dan VLOOKUP("ABC",...) menemukan abc
• Jalur yang tercakup: operator perbandingan, perbandingan array, kriteria > / <, VLOOKUP / HLOOKUP, pengurutan dynamic array, SortRange di kedua engine
• Tak tercakup aturan ini: tipe campuran (number < text < boolean) dan kriteria wildcard, yang punya aturan masing-masing
• Di kode Delphi: CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), cek 0, kurangkan CSTR_EQUAL; hindari CompareText, CompareStr, dan TComparer<string>.Default ketika hasilnya harus sepakat dengan Excel
• Hasil bergantung pada locale akun yang menjalankan kode, di Excel maupun di HotXLS

Kata-kata biasa terurut sama di bawah setiap aturan, jadi hanya kode bertanda hubung, tanda baca, dan nama beraksen yang membongkar collation yang salah. HotXLS kini memberi jawaban Excel untuk semuanya di kedua engine XLS dan XLSX. Detail lisensi, versi Delphi dan C++Builder yang didukung, serta unduhan trial ada di halaman komponen Excel Delphi HotXLS