Artykuł techniczny

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

W Excelu ="a-b">"ab" zwraca TRUE, i ="a~b"<"ab" też zwraca TRUE. Przekaż te same pary do CompareText w Delphi, a obie odpowiedzi się odwrócą, bo w porządku punktów kodowych myślnik leży pod literami, a tylda nad nimi. Excel porównuje teksty przy użyciu niewrażliwego na wielkość liter „word sort” z regionalnych ustawień użytkownika Windows, w którym myślniki i apostrofy tylko rozstrzygają remisy, a każdy inny znak interpunkcyjny sortuje się przed cyframi i literami

Objawy rzadko wspominają o collation. COUNTIF(A:A,">M") policzony na serwerze daje dwa wiersze więcej niż ta sama formuła w Excelu. Cennik posortowany przez serwis raportowy umieszcza X-100 tam, gdzie Excel by go nie umieścił. VLOOKUP("ABC",...) zwraca #N/A, choć kolumna wyraźnie zawiera abc. Wszystkie trzy sprowadzają się do jednego pytania: gdy oba operandy to tekst, który jest mniejszy? Excel ma precyzyjną odpowiedź i rzadko jest to ta, którą kod Delphi daje domyślnie

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

Excel porównuje dwa teksty word sortem bieżącej regionalnej ustawienia Windows, ignorując wielkość liter. Word sort to domyślna collacja funkcji porównawczych NLS Windows: litery porównują się w porządku językowym, a nie po punkcie kodu, więc é trafia między e i f, a wielkość liter się zlewa. Dwa znaki dostają specjalnego traktowania. Myślnik - i apostrof ' są pomijane w pierwszym przebiegu, co stawia co-op tuż obok coop; dopiero gdy pozostałe znaki remisują, o kolejności decyduje ich obecność i pozycja. Każdy inny znak interpunkcyjny liczy się z pełną wagą i sortuje przed cyframi, a cyfry przed literami

Tabela zestawia werdykt Excela dla IF(A<B,...) z dwoma porównaniami, które twórca Delphi najchętniej napisze na pierwszy rzut

A vs BExcel / HotXLSCompareStr (ordinal)CompareText
"a-b" vs "ab"większemniejszemniejsze
"a'b" vs "ab"większemniejszemniejsze
"a~b" vs "ab"mniejszewiększewiększe
"a_b" vs "ab"mniejszemniejszewiększe
"ab" vs "AB"równewiększerówne
"é" vs "f"mniejszewiększewiększe
"Z" vs "f"większemniejszewiększe

Dwie konsekwencje potrafią zaskoczyć. Ponieważ myślnik tylko rozstrzyga remisy, ="a-b"="ab" daje FALSE: dwa napisy są bezpośrednimi sąsiadami w sortowaniu i wciąż nie są równe. A ponieważ wielkość liter znika całkowicie, ab, AB i Ab to jeden klucz dla każdego operatora porównania. Przy angielskiej ustawie regionalnej kolumna 20 kapryśnych słów sortuje się w 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 o pozycji decyduje położenie pomijanego znaku: myślnik na końcu pierwszy, myślnik na początku ostatni

Diagram word sort w HotXLS porządkujący wszystkie 20 słów testowych: od a b, a.b, a_b i a~b, przez a0 i a1b, potem grupa ab z AB i Ab, warianty z myślnikiem i apostrofem jak a-b i a'b, aż po abc, b, e, e z akcentem, f i Z, pokazujący interpunkcję przed cyframi, cyfry przed literami i ignorowaną wielkość liter
interpunkcja i spacja sortują się przed cyframi, cyfry przed literami, wielkość liter znika, a myślnik z apostrofem tylko rozstrzygają remisy; dlatego a-b ląduje obok ab, a mimo to porównuje się jako większe

Samodzielne sprawdzenie collacji względem Excela

Dokumentacja Excela nigdy nie nazywa użytej collacji, więc pewnym sposobem potwierdzenia jej na własnej maszynie jest pozwolić Excelowi ocenić partię par napisów i zestawić jego werdykty z CompareStringW przy różnych zestawach flag. Włóż pary do dwóch kolumn, każ Excel policzyć =IF(A1<B1,-1,IF(A1=B1,0,1)) dla każdego wiersza i przepuść te same pary przez API. Warte wygenerowania pary mieszają interpunkcję ASCII, cyfry, obie wielkości liter, spacje, akcentowane litery typu é, ß i ä, znaki CJK, formy full-width i twardą spację, z dużą liczbą niemal trafień różniących się jednym znakiem. Losowe słowa ze słownika to słaba próbka, bo zwykłe litery sortują się identycznie pod każdą regułą z tego artykułu

Zestawy flag dają wyraźnie różne wyniki:

  • Samo NORM_IGNORECASE z LOCALE_USER_DEFAULT to zwykły word sort i ten, którego trzyma się Excel
  • Dodanie SORT_STRINGSORT przełącza na string sort, gdzie myślnik i apostrof stają się zwykłymi symbolami z pełną wagą, więc każda para w stylu a-b / ab wychodzi na odwrót
  • Dodanie NORM_IGNOREWIDTH czyni literę full-width równą jej formie half-width, a Excel te dwie formy rozróżnia

Jedna pułapka czeka w samym harnessie testowym. Excel traktuje apostrof wpisany na początku komórki jako prefiks „zapisz jako tekst”, więc komórka, której całą zawartością jest ', kończy pusta, a każda tak zbudowana para zgłasza niezgodność niezwiązaną z collacją. Takie pary buduj literałami napisów w formule (="'") albo je pomiń

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

CompareText i porównanie porządkowe gubią kolejność Excela, bo porównują jednostki kodu UTF-16, a porządek punktów kodowych rozrzuca interpunkcję wokół alfabetu. Myślnik to U+002D, a apostrof U+0027 — oba pod każdą literą, więc porównanie porządkowe uznaje "a-b" za mniejsze od "ab", podczas gdy Excel traktuje myślnik jako rozstrzygacz remisu. Tylda to U+007E, nad każdą literą, co czyni "a~b" większym — odwrotnie niż w Excelu. CompareText dokłada własne zniekształcenie: składa do wielkich liter tylko a..z i potem porównuje jednostki kodu, a ponieważ podkreślnik (U+005F) leży między zakresem wielkich i małych liter, składanie przesuwa "a_b" spod "ab" ponad nie. Żadna z tych funkcji nie ma pojęcia, że é należy między e i f

Diagram porównań HotXLS zestawiający porządek punktów kodowych z word sort Excela: porównanie porządkowe umieszcza apostrof, myślnik i podkreślnik na 0x27, 0x2D i 0x5F wśród liter, więc a-b versus ab wychodzi jako mniejsze, a word sort przesuwa interpunkcję przed cyfry i litery i za rozstrzygaczy remisów uznaje wyłącznie myślnik i apostrof
punkty kodowe rozrzucają interpunkcję wokół liter, więc porównania porządkowe i ze składaniem ASCII odwracają werdykty; word sort przestawia interpunkcję przed cyfry i degraduje myślnik z apostrofem do rozstrzygaczy remisów

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

  • CompareStr, operator < na napisach i TComparer<string>.Default są porządkowe i z rozróżnianiem wielkości 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, ...) — wywołanie zgodne z Excelem. Posortowany TStringList z domyślnymi ustawieniami (UseLocale True, CaseSensitive False) idzie przez AnsiCompareText i też zgadza się z Excelem
  • Na celach POSIX RTL Delphi kieruje AnsiCompareText przez collator ICU — inny algorytm z innymi regułami interpunkcji. AnsiCompareText Free Pascala na Windows konwertuje napisy do strony kodowej ANSI i woła CompareStringA, co gubi każdy znak, którego ta strona nie umie zapisać

Zatem funkcje RTL wrażliwe na ustawienia regionalne wychodzą na Windows trafnie przez to, jak są dziś zaimplementowane. Kod, który ma zgadzać się z Excelem, powinien wołać API wprost i powiedzieć o tym wprost. Trudniejszy problem pojawia się w każdym silniku formuł, gdzie każda ścieżka kodu bierze porównanie, które ma pod ręką: wielkie litery i punkty kodu w operatorach = i <, porównanie Variant z rozróżnianiem wielkości liter w gałęzi > / < kryteriów COUNTIF, kolejne wewnątrz VLOOKUP (dokładnie dlatego VLOOKUP("ABC",A1:A20,1,FALSE) nie znajduje abc) i wywołanie regionalne w sortowaniu zakresu. Każda ścieżka jest do obronienia osobno. Razem dają trzy różne porządki na tej samej kolumnie

Jedno porównanie za formułami, lookupami i sortowaniem w HotXLS

HotXLS przepuszcza każde porównanie tekst–tekst w kodzie wyliczeń i sortowania przez jedną funkcję, XlsCompareText w lxStandard.pas, która woła CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) i odejmuje CSTR_EQUAL. Używają jej sześć operatorów porównania, porównania element po elemencie w formułach tablicowych, gałęzie >, <, >= i <= kryteriów w stylu COUNTIF oraz funkcje bazodanowe, VLOOKUP i HLOOKUP w trybie dokładnym i przybliżonym, porządek za funkcjami tablic dynamicznych i XLOOKUP / XMATCH oraz SortRange na TXLSWorksheet i TXLSXWorksheet. Posadzenie sortowania zakresów na tej samej funkcji było rozstrzygającym punktem projektowym. Przybliżony VLOOKUP na tekście daje sensowne odpowiedzi tylko wtedy, gdy kolumna była posortowana w porządku, w jakim lookup porównuje, a wspólna funkcja uniemożliwia rozjechanie się tych dwóch porządków

Diagram trasowania HotXLS pokazujący każdą ścieżkę porównania tekstów: od sześciu operatorów porównania i kryteriów w stylu COUNTIF, przez VLOOKUP, HLOOKUP, XLOOKUP i sortowanie zakresów obu silników, aż po konwergencję w XlsCompareText, które woła CompareStringW z LOCALE_USER_DEFAULT i NORM_IGNORECASE i mapuje 1, 2, 3 na -1, 0, 1
operatory, kryteria, lookupy i sortowanie dzielą jedną funkcję, więc kolejność, którą widzi Excel, i kolejność, którą sortuje HotXLS, nie mogą się rozjechać; API zwraca 1, 2 albo 3, a zero oznacza porażkę, nie „mniejsze”

Najszybszy sposób, by zobaczyć regułę z Delphi, to TXLSXWorkbook.Calculate, który wylicza napis formuły na aktywnym arkuszu i zwraca wynik jako Variant

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;

Word sort działa dopiero, gdy oba operandy są tekstem. Typy mieszane podlegają stałemu rankingowi, w którym każda liczba jest pod każdym tekstem, a każdy tekst pod każdym boolem, co opisuje artykuł o łańcuchach porównań, pustych operandach i SUMIF. Kryteria z symbolami wieloznacznymi, takie jak "a*" czy "=ab", to testy wzorca i równości z własnymi regułami, omówione w przewodniku po symbolach wieloznacznych Excela w COUNTIF, MATCH i DSUM; collacja z tego artykułu rozstrzyga o operatorach porządkowania

Kolejny przykład wkłada 20 słów do kolumny A arkusza, sprawdza trzy liczniki kryteriów i lookup, a potem sortuje kolumnę przez TXLSXWorksheet.SortRange. Oczekiwane liczniki wynikają wprost z pokazanego wcześniej porządku: jedenaście słów sortuje się za grupą ab, jedenaście przed a-b, a trzy członkowie grupy ab dochodzą do jedenastu dla >=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]);

    // Oczekiwane z porządku word sort: 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")')));

    // Znajduje abc: lookup porównuje bez wielkości liter, jak Excel
    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;

SortRange robi stabilne sortowanie przez scalanie, więc ab, AB i Ab, porównywane jako równe, zachowują względną kolejność sprzed sortowania. Puste komórki lądują na dole niezależnie od kierunku klucza — tam, gdzie stawia je Excel

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

By odtworzyć porządek tekstowy Excela poza skoroszytem, wołaj CompareStringW z LOCALE_USER_DEFAULT i NORM_IGNORECASE, bez SORT_STRINGSORT i NORM_IGNOREWIDTH. Patrz na wartość zwrotną. API nie zwraca wyniku ze znakiem; zwraca CSTR_LESS_THAN (1), CSTR_EQUAL (2) albo CSTR_GREATER_THAN (3), a 0, gdy wywołanie się nie powiedzie. Odjęcie 2 daje zwyczajową konwencję ujemne / zero / dodatnie, ale dopiero po obsłużeniu przypadku 0, inaczej nieudane wywołanie zamienia się w -2 i wygląda jak „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;

Jeśli HotXLS już jest w uses, lxStandard.XlsCompareText jest publiczny i zwraca ten sam porządek. Różnią się tylko przy nieudanym wywołaniu API: funkcja powyżej rzuca wyjątek, a XlsCompareText schodzi do porównania wielkich napisów po jednostkach kodu, żeby przeliczenie nigdy nie urwało się w połowie. Dla samodzielnego sortowania rzucenie wyjątku jest lepszym wyborem, bo cicho zły porządek trafia się trudniej niż wyjątek

TArray.Sort nie jest stabilny, więc równe klucze, jak ab i AB, mogą wyjść w dowolnej kolejności. Gdy oryginalna kolejność równych kluczy ma znaczenie, sortuj tablicę indeksów i użyj oryginalnej pozycji jako klucza wtórnego

Czasem kolumna ma celowo ignorować porządek Excela. Numery części to klasyka: X-100 i X100 to różne kody i powinny sortować się po punkcie kodu. SortRange ma przeciążenie przyjmujące TXLSSortCompareEvent, zadeklarowane jako 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;

Przy własnym komparatorze SortRange podsuwa surowe wartości kluczy i zostawia rozmieszczenie pustych komórek tobie, dlatego komparator powyżej najpierw ogarnia Null. Dla klucza malejącego sortowanie neguje to, co zwróci komparator, więc w tym kierunku puste wędrują na górę, chyba że komparator to uwzględni. Kolumna posortowana tak nie jest już w porządku, jakiego oczekuje przybliżony VLOOKUP czy XLOOKUP przez wyszukiwanie binarne; co psuje się w tych trybach na danych posortowanych inaczej, opisuje przewodnik po trybach wyszukiwania binarnego XLOOKUP i XMATCH

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

Skoroszyt może sortować się inaczej na innym komputerze, bo porządek tekstowy Excela zależy od regionalnych ustawień użytkownika Windows i HotXLS celowo podąża za tą zależnością. Word sort jest zależny od języka. Collacja szwedzka stawia na przykład ä za z, podczas gdy angielska i niemiecka trzymają ją przy a, więc plik przeliczony przez kolegę ze Sztokholmu może dać inne COUNTIF(...,">y") niż ten sam plik na biurku w Chicago. HotXLS podaje LOCALE_USER_DEFAULT, by jego odpowiedzi równały się odpowiedziom Excela na tej samej maszynie. Przypięcie jednej stałej ustawy regionalnej wyglądałoby schludniej i sprawiłoby, że HotXLS rozmijałby się z Excelem na każdej maszynie skonfigurowanej inaczej

Dla generowania po stronie serwera płyną z tego trzy praktyczne konsekwencje:

  • O regionalnej, która się liczy, decyduje konto, na którym działa proces. Usługa Windows albo pula aplikacji IIS może mieć inne ustawienia regionalne niż biurko dewelopera, więc wyniki oglądane w IDE nie są automatycznie tym, co policzy produkcja
  • Zapisane w pliku cache'owane wyniki formuł odzwierciedlają regionalną maszyny generującej. Excel przelicza własną regionalną, więc wartość może się zmienić, gdy plik zostanie otwarty i przeliczony gdzie indziej; Excel zachowuje się tak samo ze swoimi plikami
  • Regionalne różnią się głównie na literach z akcentami, na parach liter, które niektóre języki traktują jako jedną literę, i na pismach niełacińskich. Dane testowe ze zwykłych angielskich słów nigdy nie odsłonią różnicy, więc wrzuć do próbki kilka nazwisk typu Ångström i Öberg

Ścieżka zgodna z Excelem jest ścieżką Windows: każda kompilacja Win32 i Win64, niezależnie czy z Delphi, C++Buildera, czy Lazarusa z Free Pascalem, woła ten sam CompareStringW. Gdy kompilacja celuje w platformę pozawindowsową, XlsCompareText używa WideCompareText z RTL, którego porządek pochodzi z własnej biblioteki collacji tej platformy

Zwykłe słowa sortują się tak samo pod każdą regułą, dlatego zła collacja potrafi przetrwać testy, aż dojdą numery części z myślnikami, interpunkcja albo akcentowane nazwiska klientów. HotXLS daje odpowiedź Excela na tych danych w obu silnikach, XLS i XLSX, od operatorów porównania po SortRange. Wspierane wersje Delphi i C++Buildera, licencjonowanie i pobranie wersji próbnej znajdziesz na stronie komponentu Excel dla Delphi HotXLS