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 B | Excel / HotXLS | CompareStr (ordinal) | 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 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
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_IGNORECASEzLOCALE_USER_DEFAULTto zwykły word sort i ten, którego trzyma się Excel - Dodanie
SORT_STRINGSORTprzełącza na string sort, gdzie myślnik i apostrof stają się zwykłymi symbolami z pełną wagą, więc każda para w stylua-b/abwychodzi na odwrót - Dodanie
NORM_IGNOREWIDTHczyni 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
Zwykłe narzędzia Delphi lądują po obu stronach tej linii:
CompareStr, operator<na napisach iTComparer<string>.Defaultsą porządkowe i z rozróżnianiem wielkości liter, więcTArray.Sort<string>bez komparatora stawiaZprzedfCompareTextiSameTextsą porządkowe po składaniu wielkości liter tylko w zakresie ASCIIAnsiCompareTextiWideCompareTextw RTL Delphi na Windows wołająCompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...)— wywołanie zgodne z Excelem. PosortowanyTStringListz domyślnymi ustawieniami (UseLocaleTrue,CaseSensitiveFalse) idzie przezAnsiCompareTexti też zgadza się z Excelem- Na celach POSIX RTL Delphi kieruje
AnsiCompareTextprzez collator ICU — inny algorytm z innymi regułami interpunkcji.AnsiCompareTextFree Pascala na Windows konwertuje napisy do strony kodowej ANSI i wołaCompareStringA, 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
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ömiÖ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