Comparație de text HotXLS: ordinea word sort Excel în Delphi

HotXLS Delphi Component compară două valori text așa cum face Excel 16 din v2.384.67: fără sens la majuscule, în ordinea „word sort” a locale-ului de utilizator Windows, ceea ce întoarce CompareStringW cu flag-ul NORM_IGNORECASE. Cratimele și apostrofurile sunt sărite în prima trecere și doar sparg egalurile, deci ="a-b">"ab" e TRUE, în timp ce restul punctuației se sortează înaintea cifrelor și literelor, deci ="a~b"<"ab" e tot TRUE. Aceeași ordine acționează acum operatorii de comparație, criteriile > / <, sortarea intervalelor și VLOOKUP

Nimeni nu trimite un bug intitulat „nepotrivire de collation”. Rapoartele spun că COUNTIF(A:A,">M") numără cu două rânduri mai mult pe server decât în Excel, că o listă de prețuri sortată de serviciul de raportare pune X-100 într-un loc unde Excel nu l-ar pune, sau că VLOOKUP("ABC",...) întoarce #N/A deși coloana conține evident abc. Toate trei vin din aceeași întrebare: când ambii operanzi sunt text, care e mai mic? Excel are un răspuns precis, el nu e cel pe care îl dă majoritatea codului Delphi, iar înainte de v2.384.67 HotXLS dădea trei răspunsuri diferite în funcție de calea de cod care întreba

Ce regulă folosește Excel pentru a compara două șiruri de text?

Excel compară textul cu word sort-ul locale-ului utilizatorului, ignorând majusculele. Word sort e collation-ul implicit al funcțiilor de comparație NLS din Windows: literele se compară după ordinea lor lingvistică, nu după code points, literele cu diacritice stau lângă litera lor de bază, iar două caractere primesc tratament special. Cratima - și apostroful ' sunt ignorate la prima trecere, deci co-op și coop aterizează unul lângă altul, iar doar când restul șirurilor egalează prezența lor decide ordinea. Orice alt semn de punctuație e semnificativ și se sortează înaintea cifrelor, iar cifrele se sortează înaintea literelor

Tabelul arată ce înseamnă asta în practică, lângă cele două comparații la care cel mai probabil apelază un developer Delphi. Coloana Excel conține verdictele pe care Excel 16 le-a întors pentru IF(A<B,...), pe care HotXLS le reproduce din v2.384.67

A vs B	Excel 16 / HotXLS	CompareStr (ordinal)	CompareText
"a-b" vs "ab"	mai mare	mai mic	mai mic
"a'b" vs "ab"	mai mare	mai mic	mai mic
"a~b" vs "ab"	mai mic	mai mare	mai mare
"a_b" vs "ab"	mai mic	mai mic	mai mare
"ab" vs "AB"	egal	mai mare	egal
"é" vs "f"	mai mic	mai mare	mai mare
"Z" vs "f"	mai mare	mai mic	mai mare

Două consecințe sunt ușor de ratat. În primul rând, rolul de departajator al cratimei înseamnă că ="a-b"="ab" e FALSE: șirurile sunt vecini apropiați în sortare, dar nu sunt egale. În al doilea rând, egalitatea ignoră complet majusculele, deci ab, AB și Ab sunt aceeași cheie din punctul de vedere al oricărei comparații. Sortarea a 20 de cuvinte de test cu Range.Sort al Excel-ului dă 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; în interiorul grupului ab, poziția caracterului ignorat decide

Cum a fost stabilită ordinea de text a Excel-ului?

Ordinea de text a Excel-ului a fost identificată prin măsurătoare, nu prin documentație, fiindcă documentația Excel-ului nu numește collation-ul. Testul a generat 4.000 de perechi de șiruri aleatoare din punctuație ASCII, cifre, ambele registre, spații, é, ß, ä, caractere chinezești, forme full-width și spațiul non-breaking, cu lungimi de la 0 la 4 și jumătate din perechi construite ca aproape-egale între ele. Excel 16 a evaluat IF(A<B,-1,IF(A=B,0,1)) pentru fiecare pereche, iar verdictele au fost potrivate cu API-ul de comparație Windows cu seturi diferite de flag-uri

• NORM_IGNORECASE singur (word sort implicit, locale-ul utilizatorului): nicio nepotrivire reală. Singurele 7 diferențe erau celule al căror întreg conținut era ', pe care Excel le consumă drept caracter de prefix text, deci erau artefacte de eșantionare, nu diferențe de collation
• NORM_IGNORECASE cu SORT_STRINGSORT: 41 de nepotriviri. String sort tratează cratima și apostroful ca simboluri obișnuite, ceea ce e exact comportamentul pe care Excel nu-l are
• Adăugând NORM_IGNOREWIDTH: greșit în alt fel, pentru că face formele full-width și half-width ale aceleiași litere să compare egale, iar Excel le ține separate

O a doua verificare, aleasă de mână, a comparat toate cele 190 de perechi trase din 20 de cuvinte capcanoase cu rezultatul lui Range.Sort al Excel-ului pe aceeași coloană. Ambele au fost de acord cu word sort simplu NORM_IGNORECASE, iar cele 190 de verdicte plus ordinea sortată fac acum parte din suita de regresie HotXLS, rulată prin ambele motoare, clasicul TXLSWorkbook și cel nativ XLSX TXLSXWorkbook

De ce greșesc CompareText și comparația ordinală?

CompareText și comparația ordinală greșesc ordinea Excel-ului pentru că compară unități de cod UTF-16, iar ordinea pe code points pune punctuația în locuri arbitrare față de litere. Cratima e U+002D și apostroful U+0027, ambele sub orice literă, deci o comparație ordinală declară "a-b" mai mic decât "ab" în loc să trateze cratima ca departajator. Tilda U+007E stă peste orice literă, deci "a~b" iese mai mare, opusul Excel-ului. CompareText în RTL-ul Delphi pliază doar a..z la majuscule și apoi compară unități de cod, ceea ce adaugă o a doua distorsiune: underscore-ul U+005F stă între literele mari și cele mici, deci plierea la majuscule mută "a_b" de sub "ab" deasupra lui. Nicio funcție nu știe că é aparține între e și f

Uneltele Delphi obișnuite cad pe ambele părți ai liniei:

• CompareStr, operatorul < pentru șiruri și TComparer<string>.Default (care apelează CompareStr) sunt ordinale și cu sens la majuscule, deci TArray.Sort<string> fără comparer pune Z înaintea lui f
• CompareText și SameText sunt ordinale după o pliere ASCII-only a registrelor
• AnsiCompareText și WideCompareText în RTL-ul Delphi pe Windows apelează CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), același apel care se potrivește cu Excel. Un TStringList sortat cu setările lui implicite (UseLocale True, CaseSensitive False) trece prin AnsiCompareText și deci e de acord și el cu Excel
• Pe ținte POSIX, RTL-ul Delphi dirijează AnsiCompareText printr-un collator ICU, care e un alt algoritm cu alte reguli de punctuație, iar AnsiCompareText din Free Pascal pe Windows apelează CompareStringA după conversia la pagina de cod ANSI, ceea ce pierde orice caracter pe care pagina respectivă nu-l poate reprezenta

Deci funcțiile RTL conștiente de locale sunt corecte pe Windows prin implementare, nu prin contract, iar codul care are nevoie de ordinea Excel-ului face mai bine să apeleze explicit API-ul. HotXLS avea același amestec intern. Operatorii de comparație treceau ambele șiruri la majuscule și comparau code points, ramurile > / < ale funcțiilor de criterii foloseau comparația Variant a Delphi cu sens la majuscule, iar VLOOKUP / HLOOKUP potriveau textul cu aceeași comparație Variant sensibilă la majuscule, de aceea VLOOKUP("ABC",A1:A20,1,FALSE) nu putea găsi abc. Sortarea intervalelor folosea deja WideCompareText. Trei căi, trei ordini

Ce s-a schimbat în HotXLS v2.384.67?

Din v2.384.67, comparațiile text-cu-text în căile de calcul și sortare HotXLS trec printr-o singură funcție, XlsCompareText în lxStandard.pas, care apelează CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) și scade CSTR_EQUAL. Apelanții sunt cei șase operatori de comparație, comparațiile element cu element din formulele array, ramurile >, <, >= și <= ale criteriilor în stil COUNTIF și ale funcțiilor de bază de date, VLOOKUP și HLOOKUP (exact și aproximativ), helper-ii de ordonare din spatele funcțiilor dynamic-array și ai lui XLOOKUP / XMATCH, și sortarea de intervale a ambelor motoare. Dirijarea sortării de intervale prin aceeași funcție garantează că ordinea de sortare și ordinea de comparație nu mai pot aluneca una de alta, ceea ce contează fiindcă VLOOKUP-ul aproximativ pe text are sens doar când coloana a fost sortată în ordinea în care cautarea compară

uses
  System.Variants, lxHandleX;

var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Sheets.Add('Data');  // Calculate evaluează peste sheet-ul activ
    Writeln(VarToStr(Book.Calculate('="a-b">"ab"')));   // True: cratima doar sparge egalurile
    Writeln(VarToStr(Book.Calculate('="a-b"="ab"')));   // False: egalul e spart, nu-s egale
    Writeln(VarToStr(Book.Calculate('="a~b"<"ab"')));   // True: punctuația întâi
    Writeln(VarToStr(Book.Calculate('="ABC"="abc"')));  // True: majusculele ignorate
  finally
    Book.Free;
  end;
end;

Comparațiile între tipuri diferite sunt o regulă separată și nu s-au schimbat: orice număr e sub orice valoare text, iar orice valoare text e sub orice boolean, cum e descris în articolul despre lanțurile de comparație, operanzii goi și SUMIF. Word sort-ul se aplică doar când ambii operanzi sunt text. Potrivirea cu wildcard-uri e de asemenea separată: un criteriu precum "a*" sau "=ab" e un test de tipar sau de egalitate, acoperit în ghidul pentru wildcard-uri Excel în COUNTIF, MATCH și DSUM, iar collation-ul discutat aici decide doar operatorii de ordonare

Exemplul următor încarcă cele 20 de cuvinte de test într-o coloană, o sortează cu TXLSXWorksheet.SortRange și verifică o numărare de criterii și o căutare. Contoarele sunt cele pe care Excel 16 le-a întors pentru aceeași coloană

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 pe aceeași coloană: 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")')));

    // Era #N/A înainte de v2.384.67: lookup-ul compara cu sens la majuscule
    Sheet.Cells[1, 3].Formula := '=VLOOKUP("ABC",A1:A20,1,FALSE)';
    Book.Recalculate;
    Writeln(VarToStr(Sheet.Cells[1, 3].Value));                // abc

    // O coloană cheie, crescător: 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 folosește un merge sort stabil, deci ab, AB și Ab, care compară egale, își păstrează ordinea relativă de dinaintea sortării. Celulele goale merg la final în ambele direcții, ca în Excel

Cum reproduc ordinea de sortare a Excel-ului în propriul meu cod Delphi?

Ca să reproduceți ordinea de text a Excel-ului în propriul cod Delphi, apelați CompareStringW cu LOCALE_USER_DEFAULT și NORM_IGNORECASE, și nu adăugați SORT_STRINGSORT sau NORM_IGNOREWIDTH. Valoarea întoarsă nu e un rezultat de comparație cu semn: API-ul întoarce CSTR_LESS_THAN (1), CSTR_EQUAL (2) sau CSTR_GREATER_THAN (3), iar 0 când apelul pice. Scădeți 2 ca să obțineți convenția obișnuită negativ / zero / pozitiv și testați întâi 0, fiindcă un eșec confundat cu un rezultat devine -2, un „mai mic” tăcut

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

// Ordinea de text a Excel-ului: word sort pe locale, fără majuscule
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 e un eșec, nu un rezultat de comparație
  Result := R - CSTR_EQUAL;    // 1/2/3 devin -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 (egale, orice ordine), a-b, -ab, abc
end;

TArray.Sort nu e stabil, deci cheile care compară egale, precum ab și AB, pot ieși în orice ordine; dacă ordinea originală a cheilor egale contează, sortați un array de index-uri cu poziția originală drept cheie secundară. Cazul opus apare și el: uneori o coloană nu trebuie să urmeze ordinea Excel-ului, de exemplu numere de piesă unde X-100 și X100 sunt coduri distincte și ar trebui sortate pe code point. TXLSXWorksheet.SortRange are un overload care primește un TXLSSortCompareEvent, o metodă cu semnătura function(const Left, Right: Variant): Integer of object, și îl folosește în locul comparației interne

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
  // Un comparer propriu primește și celule goale (ca Null): plasați-le voi
  if VarIsNull(Left) or VarIsNull(Right) then
    Exit(Ord(VarIsNull(Left)) - Ord(VarIsNull(Right)));
  Result := CompareStr(VarToStr(Left), VarToStr(Right));   // ordinal, cu sens la majuscule
end;

var
  Sheet: TXLSXWorksheet;   // un sheet umplut, rândurile 2..501, coloanele A..D
  Order: TPartNumberOrder;
begin
  // ...
  Order := TPartNumberOrder.Create;
  try
    // cheie pe coloana A, crescător
    Sheet.SortRange(2, 1, 501, 4, [1], [False], xlsSortByRows,
      xlsSortExcelLike, Order.Compare);
  finally
    Order.Free;
  end;
end;

Când e furnizat un comparer propriu, HotXLS își sare peste propria gestionare a golurilor și pasează valorile brute ale cheilor, deci comparer-ul trebuie să se descurce cu Null. Pentru o cheie descrescătoare HotXLS neagă orice întoarce comparer-ul, ceea ce mută și golurile sus, în afară de cazul în care comparer-ul ține cont de asta. Țineți minte că o coloană sortată așa nu mai e în ordinea în care se așteaptă VLOOKUP-ul aproximativ al Excel-ului sau un XLOOKUP cu căutare binară; capcanele acelor moduri pe date sortate într-o altă ordine sunt acoperite în ghidul modurilor de căutare binară XLOOKUP și XMATCH

De ce poate același workbook să se sorteze diferit pe altă mașină?

Același workbook se poate sorta diferit pe altă mașină fiindcă ordinea de text a Excel-ului depinde de locale-ul de utilizator Windows, iar HotXLS urmează deliberat dependența asta. Word sort e specific limbii: collation-ul suedez, de pildă, plasează ä după z, acolo unde engleza și germana îl țin lângă a. Excel moștenește asta de la locale-ul sub care rulează, deci un workbook recalculat de un coleg din Stockholm poate întoarce un alt COUNTIF(...,">y") decât același fișier pe un desktop din Chicago. HotXLS pasează LOCALE_USER_DEFAULT ca rezultatele lui să egaleze pe cele ale Excel-ului pe aceeași mașină; orice locale fix ar face HotXLS să contrazică Excel-ul pe fiecare mașină cu o setare diferită

Trei consecințe practice decurg pentru generarea pe server:

• Locale-ul care contează e cel al contului sub care rulează procesul. Un serviciu Windows sau un application pool IIS poate folosi un format regional diferit de cel al desktop-ului developer-ului, deci rezultatele observate în IDE nu sunt automat ce calculează producția
• Rezultatele de formule puse în cache în fișier reflectă locale-ul mașinii generatoare. Excel recalculează cu propriul lui locale, deci o valoare se poate schimba când fișierul e deschis în altă parte și recalculat; ăsta e comportamentul Excel-ului, nu un artefact HotXLS
• Locale-urile se contrazic mai ales la literele cu diacritice, la combinațiile de litere pe care unele limbi le tratează ca o literă unică și la scripturile non-latine, deci date de test limitate la cuvinte englezești simple nu vor dezvălui problema

Granița de platformă e simplă. HotXLS e o bibliotecă Windows, construită pentru Win32 și Win64 cu Delphi și C++Builder și pentru ținte win32 / win64 cu Lazarus și Free Pascal, iar toate aceste build-uri apelează același CompareStringW. Nu există o cale de collation separată pe non-Windows. Singurul fallback e pentru un apel API eșuat: dacă CompareStringW întoarce 0, XlsCompareText compară șirurile trecute la majuscule pe unități de cod în loc să ridice o excepție în mijlocul unei recalculari, ceea ce ține calculul în mișcare dar nu mai garantează ordinea Excel-ului

Referință rapidă: comparația de text Excel în HotXLS

• Regula: word sort pe locale-ul utilizatorului cu NORM_IGNORECASE, fără SORT_STRINGSORT, fără NORM_IGNOREWIDTH, în HotXLS din v2.384.67
• - și ' doar sparg egalurile: ="a-b">"ab" e TRUE și ="a-b"="ab" e FALSE
• Restul punctuației se sortează înaintea cifrelor, cifrele înaintea literelor: ="a~b"<"ab" și ="a0"<"ab" sunt TRUE
• Majusculele nu contează niciodată: ="ABC"="abc" e TRUE și VLOOKUP("ABC",...) găsește abc
• Căile acoperite: operatorii de comparație, comparațiile de array, criteriile > / <, VLOOKUP / HLOOKUP, ordonarea dynamic-array, SortRange în ambele motoare
• Neacoperite de regula asta: tipurile mixte (număr < text < boolean) și criteriile cu wildcard, care au propriile lor reguli
• În cod Delphi: CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), verificați 0, scădeți CSTR_EQUAL; evitați CompareText, CompareStr și TComparer<string>.Default când rezultatul trebuie să fie de acord cu Excel
• Rezultatele depind de locale-ul contului care rulează codul, în Excel și în HotXLS deopotrivă

Cuvintele obișnuite se sortează la fel sub orice regulă, deci doar codurile cu cratimă, punctuația și numele cu diacritice expun un collation greșit. HotXLS dă acum răspunsul Excel-ului pe toate, în ambele motoare, XLS și XLSX. Detalii despre licențiere, versiunile suportate de Delphi și C++Builder și descărcarea de probă sunt pe pagina componentei HotXLS Delphi Excel