Porównanie tekstu w HotXLS: kolejność word sort z Excela

HotXLS Delphi Component porównuje dwie wartości tekstowe tak, jak robi to Excel 16 od v2.384.67: bez rozróżniania wielkości liter, w kolejności „word sort” ustawień regionalnych użytkownika Windows, czyli taką, jaką zwraca CompareStringW z flagą NORM_IGNORECASE. Myślniki i apostrofy są pomijane w pierwszym przebiegu i tylko rozstrzygają remisy, więc ="a-b">"ab" daje TRUE, a pozostała interpunkcja sortuje się przed cyframi i literami, więc ="a~b"<"ab" również daje TRUE. Ta sama kolejność napędza teraz operatory porównania, kryteria > / <, sortowanie zakresów i VLOOKUP

Nikt nie zakłada buga pod tytułem „niezgodność collation”. Zgłoszenia mówią, że COUNTIF(A:A,">M") liczy na serwerze dwa wiersze więcej niż w Excelu, że cennik posortowany przez serwis raportujący wciska X-100 tam, gdzie Excel by go nie wcisnął, albo że VLOOKUP("ABC",...) zwraca #N/A, choć kolumna wprost zawiera abc. Wszystkie trzy sprowadzają się do tego samego pytania: gdy oba operandy to tekst, który jest mniejszy? Excel ma precyzyjną odpowiedź, nie jest nią ta, którą daje większość kodu w Delphi, a przed v2.384.67 HotXLS dawał trzy różne odpowiedzi, zależnie od tego, która ścieżka kodu pytała

Jaką regułą Excel porównuje dwa teksty?

Excel porównuje tekst według word sort ustawień regionalnych użytkownika, ignorując wielkość liter. Word sort to domyślna collation funkcji porównawczych NLS Windows: litery porównują się według porządku językowego, a nie punktów kodowych, litery z akcentami siedzą obok swojej litery bazowej, a dwa znaki dostają specjalne potraktowanie. Myślnik - i apostrof ' są ignorowane w pierwszym przebiegu, więc co-op i coop lądują obok siebie, a o kolejności decyduje ich obecność dopiero wtedy, gdy reszta tekstów zremisuje. Każdy inny znak interpunkcyjny się liczy i sortuje przed cyframi, a cyfry sortują się przed literami

Tabela pokazuje, co to znaczy w praktyce, obok dwóch porównań, po które deweloper Delphi sięga najczęściej. Kolumna Excela trzyma werdykty, jakie Excel 16 zwrócił dla IF(A<B,...), a HotXLS odtwarza je od v2.384.67

A vs B	Excel 16 / HotXLS	CompareStr (porządkowe)	CompareText
"a-b" vs "ab"	większe	mniejsze	mniejsze
"a'b" vs "ab"	większe	mniejsze	mniejsze
"a~b" vs "ab"	mniejsze	większe	większe
"a_b" vs "ab"	mniejsze	mniejsze	większe
"ab" vs "AB"	równe	większe	równe
"é" vs "f"	mniejsze	większe	większe
"Z" vs "f"	większe	mniejsze	większe

Dwie konsekwencje łatwo przeoczyć. Po pierwsze, rola myślnika jako rozstrzygacza remisów oznacza, że ="a-b"="ab" daje FALSE: teksty są w sortowaniu bliskimi sąsiadami, a jednak nie są równe. Po drugie, równość całkowicie ignoruje wielkość liter, więc ab, AB i Ab to dla każdego porównania ten sam klucz. Posortowanie 20 słów testowych przez Range.Sort Excela daje 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; wewnątrz grupy ab decyduje pozycja pominiętego znaku

Jak ustalono porządek tekstu w Excelu?

Porządek tekstu w Excelu ustalono pomiarem, a nie dokumentacją, bo dokumentacja Excela nie nazywa collation. Test wygenerował 4000 losowych par tekstów z interpunkcji ASCII, cyfr, obu wielkości liter, spacji, é, ß, ä, znaków chińskich, form pełnoszerokościowych i spacji niełamliwej, o długościach od 0 do 4, przy czym połowa par była zbudowana jako bliskie chybienia jednej drugiej. Excel 16 wyliczał IF(A<B,-1,IF(A=B,0,1)) dla każdej pary, a werdykty zestawiano z API porównawczym Windows z różnymi zestawami flag

• samo NORM_IGNORECASE (domyślny word sort, ustawienia regionalne użytkownika): żadnej prawdziwej niezgodności. Jedyne 7 różnic to komórki, których całą treścią był ', które Excel konsumuje jako znak prefiksu tekstu, więc były to artefakty próbkowania, a nie różnice collation
• NORM_IGNORECASE z SORT_STRINGSORT: 41 niezgodności. String sort traktuje myślnik i apostrof jako zwykłe symbole, a to dokładnie to zachowanie, którego Excel nie ma
• dodanie NORM_IGNOREWIDTH: złość w innym miejscu, bo uznaje pełnoszerokościową i półszerokościową formę tej samej litery za równą, a Excel trzyma je osobno

Drugie, ręcznie dobrane sprawdzenie porównało wszystkie 190 par z 20 podstępnych słów oraz wynik Range.Sort Excela na tej samej kolumnie. Oba zgodziły się z czystym word sort NORM_IGNORECASE, a te 190 werdyktów plus posortowana kolejność są teraz częścią regresji HotXLS, przepuszczanej zarówno przez klasyczny silnik TXLSWorkbook, jak i natywny dla XLSX silnik TXLSXWorkbook

Czemu CompareText i porównanie porządkowe się mylą?

CompareText i porównanie porządkowe mylą porządek Excela, bo porównują jednostki kodowe UTF-16, a porządek punktów kodowych wrzuca interpunkcję w arbitralne miejsca względem liter. Myślnik to U+002D, apostrof U+0027 — oba poniżej każdej litery, więc porównanie porządkowe uznaje "a-b" za mniejsze od "ab" zamiast traktować myślnik jako rozstrzygacza remisów. Tylda U+007E siedzi nad każdą literą, więc "a~b" wychodzi większe, odwrotnie niż w Excelu. CompareText w RTL Delphi podnosi do wielkich liter tylko a..z i potem porównuje jednostki kodowe, co dokłada drugie zniekształcenie: podkreślnik U+005F leży między literami wielkimi a małymi, więc podniesienie do wielkich przenosi "a_b" spod "ab" ponad nie. Żadna z tych funkcji nie wie, że é należy między e i f

Zwykłe narzędzia Delphi lądują po obu stronach tej linii:

• CompareStr, operator < na stringach i TComparer<string>.Default (który woła CompareStr) są porządkowe i czują wielkość liter, więc TArray.Sort<string> bez komparatora stawia Z przed f
• CompareText i SameText są porządkowe po składaniu wielkości liter tylko w zakresie ASCII
• AnsiCompareText i WideCompareText w RTL Delphi na Windows wołają CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) — to samo wywołanie, które zgadza się z Excelem. Posortowany TStringList z domyślnymi ustawieniami (UseLocale True, CaseSensitive False) idzie przez AnsiCompareText i dlatego też zgadza się z Excelem
• na celach POSIX RTL Delphi kieruje AnsiCompareText przez collator ICU, czyli inny algorytm z innymi regułami interpunkcji, a AnsiCompareText Free Pascala na Windows woła CompareStringA po konwersji na stronę kodową ANSI, co gubi każdy znak, którego ta strona nie umie przedstawić

Funkcje RTL uważające na ustawienia regionalne są więc na Windows dobre z implementacji, a nie z kontraktu, i kod potrzebujący porządku Excela lepiej robi wprost wywołanie API. HotXLS miał w środku tę samą mieszankę. Operatory porównania podnosiły oba teksty do wielkich liter i porównywały punkty kodowe, gałęzie > / < funkcji kryteriów używały czułego na wielkość liter porównania Variant z Delphi, a VLOOKUP / HLOOKUP dopasowywały tekst tym samym czułym na wielkość liter porównaniem Variant — dlatego VLOOKUP("ABC",A1:A20,1,FALSE) nie potrafiło znaleźć abc. Sortowanie zakresów już używało WideCompareText. Trzy ścieżki, trzy porządki

Co się zmieniło w HotXLS v2.384.67?

Od v2.384.67 porównania tekst–tekst na ścieżkach obliczeń i sortowania HotXLS przechodzą przez jedną funkcję, XlsCompareText w lxStandard.pas, która woła CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) i odejmuje CSTR_EQUAL. Wołają ją sześć operatorów porównania, porównania elementowe w formułach tablicowych, gałęzie >, <, >= i <= kryteriów w stylu COUNTIF oraz funkcji bazodanowych, VLOOKUP i HLOOKUP (dokładne i przybliżone), pomocniki porządkujące za funkcjami tablic dynamicznych oraz XLOOKUP / XMATCH, i sortowanie zakresów obu silników. Skierowanie sortowania zakresów przez tę samą funkcję gwarantuje, że kolejność sortowania i kolejność porównań nie rozjadą się znowu, co ma znaczenie, bo przybliżony VLOOKUP na tekście ma sens tylko wtedy, gdy kolumna była posortowana w kolejności, w jakiej lookup porównuje

uses
  System.Variants, lxHandleX;

var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Sheets.Add('Data');  // Calculate wylicza na aktywnym arkuszu
    Writeln(VarToStr(Book.Calculate('="a-b">"ab"')));   // True: myślnik tylko rozstrzyga remisy
    Writeln(VarToStr(Book.Calculate('="a-b"="ab"')));   // False: remis rozstrzygnięty, nie równe
    Writeln(VarToStr(Book.Calculate('="a~b"<"ab"')));   // True: interpunkcja najpierw
    Writeln(VarToStr(Book.Calculate('="ABC"="abc"')));  // True: wielkość liter ignorowana
  finally
    Book.Free;
  end;
end;

Porównania między typami to osobna reguła i się nie zmieniły: każda liczba jest poniżej każdej wartości tekstowej, a każda wartość tekstowa poniżej każdego boola — opisuje to artykuł o łańcuchach porównań, pustych operandach i SUMIF. Word sort wchodzi do gry dopiero, gdy oba operandy to tekst. Dopasowywanie wildcardów też jest osobne: kryterium takie jak "a*" albo "=ab" to test wzorca albo równości, opisany w przewodniku po wildcardach Excela w COUNTIF, MATCH i DSUM, a collation omawiana tutaj rozstrzyga tylko operatory porządkowania

Kolejny przykład wczytuje 20 słów testowych do kolumny, sortuje ją przez TXLSXWorksheet.SortRange i sprawdza licznik kryteriów oraz lookup. Liczby to te, które Excel 16 zwrócił dla tej samej kolumny

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 na tej samej kolumnie: 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")')));

    // Było #N/A przed v2.384.67: lookup porównywał z rozróżnianiem wielkości liter
    Sheet.Cells[1, 3].Formula := '=VLOOKUP("ABC",A1:A20,1,FALSE)';
    Book.Recalculate;
    Writeln(VarToStr(Sheet.Cells[1, 3].Value));                // abc

    // Jedna kolumna klucza, rosnąco: 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 używa stabilnego sortowania przez scalanie, więc ab, AB i Ab, porównujące się jako równe, zachowują względną kolejność sprzed sortowania. Puste komórki wędrują na koniec w obu kierunkach, jak w Excelu

Jak odwzorować kolejność sortowania Excela we własnym kodzie Delphi?

Żeby odwzorować porządek tekstu Excela we własnym kodzie Delphi, wołaj CompareStringW z LOCALE_USER_DEFAULT i NORM_IGNORECASE i nie dodawaj SORT_STRINGSORT ani NORM_IGNOREWIDTH. Wartość zwrotna nie jest podpisanym wynikiem porównania: API zwraca CSTR_LESS_THAN (1), CSTR_EQUAL (2) albo CSTR_GREATER_THAN (3), a 0, gdy wywołanie się nie uda. Odejmij 2, żeby dostać zwyczajową konwencję ujemne / zero / dodatnie, i najpierw testuj 0, bo porażka wzięta za wynik staje się -2, czyli po cichu „mniejsze”

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

// Porządek tekstu Excela: word sort ustawień użytkownika, bez wielkości liter
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 to porażka, nie wynik porównania
  Result := R - CSTR_EQUAL;    // 1/2/3 stają się -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 (równe, dowolna kolejność), a-b, -ab, abc
end;

TArray.Sort nie jest stabilne, więc klucze porównujące się jako równe, jak ab i AB, mogą wyjść w dowolnej kolejności; jeśli pierwotna kolejność równych kluczy ma znaczenie, sortuj tablicę indeksów z pierwotną pozycją jako kluczem drugorzędnym. Wchodzi też przypadek przeciwny: czasem kolumna nie ma iść w porządku Excela, na przykład numery części, gdzie X-100 i X100 to odrębne kody i powinny sortować się po punktach kodowych. TXLSXWorksheet.SortRange ma przeciążenie przyjmujące TXLSSortCompareEvent, metodę o sygnaturze function(const Left, Right: Variant): Integer of object, i używa go zamiast wbudowanego porównania

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
  // Własny komparator dostaje też puste komórki (jako Null): umieść je sam
  if VarIsNull(Left) or VarIsNull(Right) then
    Exit(Ord(VarIsNull(Left)) - Ord(VarIsNull(Right)));
  Result := CompareStr(VarToStr(Left), VarToStr(Right));   // porządkowe, z rozróżnianiem wielkości liter
end;

var
  Sheet: TXLSXWorksheet;   // wypełniony arkusz, wiersze 2..501, kolumny A..D
  Order: TPartNumberOrder;
begin
  // ...
  Order := TPartNumberOrder.Create;
  try
    // kluczowanie po kolumnie A, rosnąco
    Sheet.SortRange(2, 1, 501, 4, [1], [False], xlsSortByRows,
      xlsSortExcelLike, Order.Compare);
  finally
    Order.Free;
  end;
end;

Gdy dostarczysz własny komparator, HotXLS pomija własną obsługę pustych i podaje surowe wartości kluczy, więc komparator musi ogarnąć Null. Dla klucza malejącego HotXLS neguje to, co zwróci komparator, co przy okazji wypycha puste na szczyt, chyba że komparator to uwzględni. Miej na uwadze, że kolumna posortowana w ten sposób nie jest już w kolejności, jakiej oczekuje przybliżony VLOOKUP Excela ani XLOOKUP z wyszukiwaniem binarnym; o pułapkach tych trybów na danych posortowanych inaczej pisze przewodnik po trybach wyszukiwania binarnego XLOOKUP i XMATCH

Dlaczego ten sam skoroszyt może sortować się inaczej na innym komputerze?

Ten sam skoroszyt może sortować się inaczej na innym komputerze, bo porządek tekstu w Excelu zależy od ustawień regionalnych użytkownika Windows, a HotXLS celowo za tą zależnością idzie. Word sort jest zależny od języka: szwedzka collation na przykład stawia ä po z, tam gdzie angielska i niemiecka trzymają je obok a. Excel dziedziczy to z ustawień, na których działa, więc skoroszyt przeliczony przez kolegę ze Sztokholmu może zwrócić inne COUNTIF(...,">y") niż ten sam plik na biurku w Chicago. HotXLS podaje LOCALE_USER_DEFAULT, żeby jego wyniki na tej samej maszynie równały się wynikom Excela; każde sztywne ustawienie regionalne sprawiłoby, że HotXLS rozjechałby się z Excelem na każdej maszynie z inną konfiguracją

Dla generowania po stronie serwera wynikają z tego trzy praktyczne konsekwencje:

• Ustawienia regionalne, które się liczą, to te konta, na którym działa proces. Usługa Windows albo pula aplikacji IIS może używać innego formatu regionalnego niż biurko dewelopera, więc wyniki obserwowane w IDE nie są automatycznie tym, co policzy produkcja
• Zacache'owane wyniki formuł zapisane do pliku odzwierciedlają ustawienia maszyny generującej. Excel przelicza według własnych ustawień, więc wartość może się zmienić, gdy plik zostanie otwarty gdzie indziej i przeliczony; to zachowanie Excela, nie artefakt HotXLS
• Ustawienia regionalne różnią się głównie na literach z akcentami, na połączeniach liter traktowanych przez niektóre języki jako jedna litera i na pismach nielacińskich, więc dane testowe ograniczone do zwykłych angielskich słów problemu nie ujawnią

Granica platform jest prosta. HotXLS to biblioteka Windows, budowana dla Win32 i Win64 w Delphi i C++Builderze oraz dla celów win32 / win64 w Lazarusie i Free Pascalu, i wszystkie te buildy wołają to samo CompareStringW. Nie ma osobnej ścieżki collation poza Windows. Jedyny fallback dotyczy nieudanego wywołania API: gdy CompareStringW zwróci 0, XlsCompareText porównuje podniesione do wielkich liter teksty po jednostkach kodowych, zamiast rzucać wyjątkiem w środku przeliczania — obliczenia dalej działają, ale porządek Excela nie jest już gwarantowany

Ściąga: porównywanie tekstu jak Excel w HotXLS

• Reguła: word sort ustawień użytkownika z NORM_IGNORECASE, bez SORT_STRINGSORT, bez NORM_IGNOREWIDTH, w HotXLS od v2.384.67
• - i ' tylko rozstrzygają remisy: ="a-b">"ab" daje TRUE, a ="a-b"="ab" daje FALSE
• Pozostała interpunkcja sortuje się przed cyframi, cyfry przed literami: ="a~b"<"ab" i ="a0"<"ab" dają TRUE
• Wielkość liter nigdy się nie liczy: ="ABC"="abc" daje TRUE, a VLOOKUP("ABC",...) znajduje abc
• Objęte ścieżki: operatory porównania, porównania tablicowe, kryteria > / <, VLOOKUP / HLOOKUP, porządkowanie tablic dynamicznych, SortRange w obu silnikach
• Nieobjęte tą regułą: typy mieszane (liczba < tekst < boolean) i kryteria z wildcardami, które mają własne reguły
• W kodzie Delphi: CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), sprawdź 0, odejmij CSTR_EQUAL; unikaj CompareText, CompareStr i TComparer<string>.Default, gdy wynik ma zgadzać się z Excelem
• Wyniki zależą od ustawień regionalnych konta, na którym działa kod — tak w Excelu, jak i w HotXLS

Zwykłe słowa sortują się tak samo według każdej reguły, więc złą collation wystawiają dopiero kody z myślnikami, interpunkcja i nazwiska z akcentami. HotXLS daje teraz odpowiedź Excela na wszystkich z nich w obu silnikach, XLS i XLSX. Szczegóły licencjonowania, wspierane wersje Delphi i C++Buildera oraz pobranie wersji próbnej znajdziesz na stronie komponentu Excel HotXLS dla Delphi