HotXLS poređenje teksta: Excel word sort red u Delphiju

HotXLS Delphi Component poredi dve tekstualne vrednosti na način Excela 16 od v2.384.67: bez razlikovanja velikih i malih slova, u „word sort“ redu Windows lokala korisnika, što je ono što CompareStringW vraća sa zastavicom NORM_IGNORECASE. Crtice i apostrofi se u prvom prolazu preskaču i samo rešavaju izjednačenje, pa je ="a-b">"ab" TRUE, dok se ostala interpunkcija sortira pre cifara i slova, pa je i ="a~b"<"ab" TRUE. Isti red sada vodi poredeće operatore, kriterijume > / <, sortiranje opsega i VLOOKUP

Niko ne prijavljuje bag pod naslovom „nepoklapanje kolacije“. Prijave kažu da COUNTIF(A:A,">M") broji dva reda više na serveru nego u Excelu, da cenovnik sortiran od reporting servisa stavi X-100 nekamo gde Excel ne bi, ili da VLOOKUP("ABC",...) vraća #N/A iako kolona očigledno sadrži abc. Sve tri dolaze od istog pitanja: kad su oba operanda tekst, koji je manji? Excel ima precizan odgovor, nije onaj koji većina Delphi koda daje, a pre v2.384.67 je HotXLS davao tri različita odgovora zavisno od toga koji je put koda pitao

Koje pravilo Excel koristi da uporedi dva tekstualna stringa?

Excel poredi tekst s word sort-om lokaliteta korisnika, ignorišući velika i mala slova. Word sort je podrazumevana kolacija Windows NLS funkcija za poređenje: slova se porede po svom jezičkom redu, a ne po code points, akcentovana slova sede uz svoje osnovno slovo, i dva znaka dobijaju poseban tretman. Crtica - i apostrof ' ignorišu se u prvom prolazu, pa se co-op i coop nađu jedan uz drugi, i tek kad ostatak stringova izjednači, njihovo prisustvo odlučuje o redu. Svaki drugi interpunkcijski znak je značajan i sortira se pre cifara, a cifre se sortiraju pre slova

Tabela pokazuje šta to znači u praksi, uz dva poređenja za koja će se Delphi developer najčešće posegnuti. Kolona Excel drži presude koje je Excel 16 vratio za IF(A<B,...), a HotXLS ih reprodukuje od v2.384.67

A vs B	Excel 16 / HotXLS	CompareStr (ordinalno)	CompareText
"a-b" vs "ab"	veće	manje	manje
"a'b" vs "ab"	veće	manje	manje
"a~b" vs "ab"	manje	veće	veće
"a_b" vs "ab"	manje	manje	veće
"ab" vs "AB"	jednako	veće	jednako
"é" vs "f"	manje	veće	veće
"Z" vs "f"	veće	manje	veće

Dve posledice lako promaknu. Prvo, uloga crtice kao razrešivača izjednačenja znači da je ="a-b"="ab" FALSE: stringovi su bliski susedi u sortiranju, a ipak nisu jednaki. Drugo, jednakost potpuno ignoriše velika i mala slova, pa su ab, AB i Ab isti ključ koliko god poređenje gledali. Sortiranje 20 probnih reči s Excelovim Range.Sort-om 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; unutar grupe ab o poziciji ignorisanog znaka odlučuje mesto

Kako je Excelov tekstualni red utvrđen?

Excelov tekstualni red utvrđen je merenjem, ne dokumentacijom, jer Excelova dokumentacija ne imenuje kolaciju. Test je generisao 4.000 slučajnih parova stringova iz ASCII interpunkcije, cifara, oba tipa slova, razmaka, é, ß, ä, kineskih znakova, full-width oblika i nerazmaknice, s dužinama od 0 do 4 i s polovinom parova građenih kao bliski promašaji jednih drugih. Excel 16 je za svaki par izračunao IF(A<B,-1,IF(A=B,0,1)), a presude su upoređene s Windows API-jem za poređenje s različitim skupovima zastavica

• NORM_IGNORECASE sam (podrazumevani word sort, lokal korisnika): nijedno pravo nepoklapanje. Jedinih 7 razlika bile su ćelije čiji je ceo sadržaj ', koje Excel troši kao prefiksni znak teksta, pa su to bili artefakti uzorkovanja, a ne razlike kolacije
• NORM_IGNORECASE s SORT_STRINGSORT: 41 nepoklapanje. String sort tretira crticu i apostrof kao obične simbole, a to je tačno ponašanje koje Excel nema
• Dodavanje NORM_IGNOREWIDTH: pogrešno na drugi način, jer izjednačava full-width i half-width oblike istog slova, a Excel ih drži razdvojene

Druga, ručno birana provera uporedila je svih 190 parova iz 20 podmuklih reči i rezultat Excelovog Range.Sort-a na istoj koloni. Oboje se poklopilo s običnim NORM_IGNORECASE word sort-om, i tih 190 presuda plus sortirani red sada su deo HotXLS regresionog seta, koji se izvršava kroz oba engine-a, klasični TXLSWorkbook i XLSX-nativni TXLSXWorkbook

Zašto se CompareText i ordinalno poređenje ovo pogrešno?

CompareText i ordinalno poređenje daju Excelov red pogrešno jer poredaju UTF-16 code units, a code-point red stavlja interpunkciju na proizvoljna mesta u odnosu na slova. Crtica je U+002D, a apostrof U+0027, oboje ispod svakog slova, pa ordinalno poređenje proglašava "a-b" manjim od "ab" umesto da crticu tretira kao razrešivača izjednačenja. Tilda U+007E sedi iznad svakog slova, pa "a~b" ispadne veće, suprotno od Excela. CompareText u Delphi RTL-u savija samo a..z u velika slova pa poredi code units, što dodaje drugo izobličenje: donja crta U+005F leži između velikih i malih slova, pa savijanje u velika slova pomera "a_b" iz položaja ispod "ab" iznad njega. Ni jedna od te dve funkcije ne zna da é pripada između e i f

Uobičajeni Delphi alati padaju s obe strane linije:

• CompareStr, string operator < i TComparer<string>.Default (koji zove CompareStr) su ordinalni i osetljivi na velika i mala slova, pa TArray.Sort<string> bez comparer-a stavlja Z ispred f
• CompareText i SameText su ordinalni posle ASCII-only savijanja velikih i malih slova
• AnsiCompareText i WideCompareText u Delphi RTL-u na Windowsu zovu CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), isti poziv koji se poklapa s Excelom. Sortirani TStringList sa svojim podrazumevanim vrednostima (UseLocale True, CaseSensitive False) ide kroz AnsiCompareText i zato se takođe slaže s Excelom
• Na POSIX ciljevima Delphi RTL usmerava AnsiCompareText kroz ICU kolator, što je drugi algoritam s drugim pravilima za interpunkciju, a Free Pascalov AnsiCompareText na Windowsu zove CompareStringA posle konverzije u ANSI code page, čime gubi svaki znak koji ta strana ne može da predstavi

Znači, RTL funkcije svestne lokala su na Windowsu ispravne po implementaciji, ne po ugovoru, i kod kojem treba Excelov red bolje je da pozove API eksplicitno. HotXLS je imao istu mešavinu interno. Poredeći operatori su ispisivali oba stringa velikim slovima pa poredali code points, > / < grane funkcija kriterijuma koristile su Delphi Variant poređenje osetljivo na velika i mala slova, i VLOOKUP / HLOOKUP su tekst poklapali tim istim Variant poređenjem, pa VLOOKUP("ABC",A1:A20,1,FALSE) nije mogao da nađe abc. Sortiranje opsega je već koristilo WideCompareText. Tri puta, tri reda

Šta je promenjeno u HotXLS v2.384.67?

Od v2.384.67 poređenja tekst-naspram-teksta u HotXLS putevima računanja i sortiranja idu kroz jednu funkciju, XlsCompareText u lxStandard.pas, koja zove CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) i oduzima CSTR_EQUAL. Pozivači su šest poredećih operatora, poređenja po elementima u array formulama, >, <, >= i <= grane COUNTIF-stila kriterijuma i database funkcija, VLOOKUP i HLOOKUP (egzaktni i približni), pomoćnici za poređenje iza dinamičko-nizovskih funkcija i XLOOKUP / XMATCH, i sortiranje opsega oba engine-a. Vođenje sortiranja opsega kroz istu funkciju garantuje da se red sortiranja i red poređenja više ne mogu razdvojiti, a to je važno jer je približni VLOOKUP na tekstu smislen samo kad je kolona sortirana u redu u kojem lookup poredi

uses
  System.Variants, lxHandleX;

var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Sheets.Add('Data');  // Calculate računa nad aktivnim listom
    Writeln(VarToStr(Book.Calculate('="a-b">"ab"')));   // True: crtica samo rešava izjednačenje
    Writeln(VarToStr(Book.Calculate('="a-b"="ab"')));   // False: izjednačenje rešeno, nisu jednaki
    Writeln(VarToStr(Book.Calculate('="a~b"<"ab"')));   // True: interpunkcija prva
    Writeln(VarToStr(Book.Calculate('="ABC"="abc"')));  // True: velika i mala slova se ignorišu
  finally
    Book.Free;
  end;
end;

Poređenja različitih tipova su posebno pravilo i nisu se menjala: svaki broj je ispod svake tekstualne vrednosti, a svaka tekstualna vrednost ispod svakog booleana, kako je opisano u tekstu o lancima poređenja, praznim operandima i SUMIF-u. Word sort važi tek kad su oba operanda tekst. Podudaranje džoker znakova je takođe posebno: kriterijum poput "a*" ili "=ab" je test obrasca ili jednakosti, pokriven u vodiču o Excel džoker znakovima u COUNTIF, MATCH i DSUM, a kolacija o koroj je ovde reč odlučuje samo o operatorima poređenja

Sledeći primer učitava 20 probnih reči u kolonu, sortira je s TXLSXWorksheet.SortRange i proverava jedno brojanje po kriterijumu i jedan lookup. Brojevi su oni koje je Excel 16 vratio za istu kolonu

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 istoj koloni: 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")')));

    // Bilo je #N/A pre v2.384.67: lookup je razlikovao velika i mala slova
    Sheet.Cells[1, 3].Formula := '=VLOOKUP("ABC",A1:A20,1,FALSE)';
    Book.Recalculate;
    Writeln(VarToStr(Sheet.Cells[1, 3].Value));                // abc

    // Jedna kolona ključa, rastuće: 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 koristi stabilan merge sort, pa ab, AB i Ab, koji se porede kao jednaki, zadržavaju relativni red koji su imali pre sortiranja. Prazne ćelije idu na kraj u oba smera, kao u Excelu

Kako da poklopim Excelov red sortiranja u sopstvenom Delphi kodu?

Da poklopite Excelov tekstualni red u sopstvenom Delphi kodu, zovite CompareStringW s LOCALE_USER_DEFAULT i NORM_IGNORECASE, i ne dodajte SORT_STRINGSORT ni NORM_IGNOREWIDTH. Povratna vrednost nije označeni rezultat poređenja: API vraća CSTR_LESS_THAN (1), CSTR_EQUAL (2) ili CSTR_GREATER_THAN (3), a 0 kad poziv padne. Oduzmite 2 da dobijete uobičajenu konvenciju negativno / nula / pozitivno, i prvo testirajte na 0, jer greška zamoljena za rezultat postaje -2, tiho „manje od“

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

// Excelov tekstualni red: word sort korisničkog lokala, bez razlikovanja velikih/malih slova
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 je greška, ne rezultat poređenja
  Result := R - CSTR_EQUAL;    // 1/2/3 postaju -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 (jednaki, bilo koji red), a-b, -ab, abc
end;

TArray.Sort nije stabilan, pa ključevi koji se porede kao jednaki, poput ab i AB, mogu ispasti u bilo kojem redu; ako red originala jednakih ključeva znači, sortirajte indeksni niz s originalnom pozicijom kao sekundarnim ključem. Obrnuti slučaj takođe iskače: ponekad kolona ne sme da prati Excelov red, na primer delovi gde su X-100 i X100 različiti kodovi i treba da se sortiraju po code pointu. TXLSXWorksheet.SortRange ima overload koji prima TXLSSortCompareEvent, metodu sa potpisom function(const Left, Right: Variant): Integer of object, i koristi je umesto ugrađenog poređenja

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
  // Prilagođeni comparer dobija i prazne ćelije (kao Null): rasporedite ih sami
  if VarIsNull(Left) or VarIsNull(Right) then
    Exit(Ord(VarIsNull(Left)) - Ord(VarIsNull(Right)));
  Result := CompareStr(VarToStr(Left), VarToStr(Right));   // ordinalno, razlikuje velika/mala slova
end;

var
  Sheet: TXLSXWorksheet;   // popunjen list, redovi 2..501, kolone A..D
  Order: TPartNumberOrder;
begin
  // ...
  Order := TPartNumberOrder.Create;
  try
    // ključ po koloni A, rastuće
    Sheet.SortRange(2, 1, 501, 4, [1], [False], xlsSortByRows,
      xlsSortExcelLike, Order.Compare);
  finally
    Order.Free;
  end;
end;

Kad je dat prilagođeni comparer, HotXLS preskače svoju obradu praznih i predaje sirove vrednosti ključeva, pa comparer mora da se snađe s Null-om. Za opadajući ključ HotXLS negira sve što comparer vrati, što prazne takođe pomera na vrh osim ako comparer to ne uzme u obzir. Imajte na umu da kolona sortirana ovako više nije u redu koji Excelov približni VLOOKUP ili binarno-pretraživački XLOOKUP očekuju; zamke tih režima na podacima sortiranim drugačije pokrivene su u vodiču o binarnim režimima pretrage XLOOKUP i XMATCH

Zašto ista radna sveska može da se sortira drugačije na drugoj mašini?

Ista radna sveska može da se sortira drugačije na drugoj mašini jer Excelov tekstualni red zavisi od Windows lokala korisnika, i HotXLS tu zavisnost namerno prati. Word sort je jezički: švedska kolacija, recimo, stavlja ä posle z, gde je engleski i nemački drže uz a. Excel to nasleđuje od lokala pod kojim radi, pa radna sveska preračunata od kolege iz Stokholma može da vrati drugačiji COUNTIF(...,">y") nego isti fajl na desktopu u Čikagu. HotXLS prosleđuje LOCALE_USER_DEFAULT da mu rezultati budu jednaki Excelovim na istoj mašini; svaki fiksni lokal učinio bi da se HotXLS razilazi s Excelom na svakoj mašini s drugačijim podešavanjem

Tri praktične posledice slede za generisanje na strani servera:

• Lokal koji se računa je onaj naloga pod kojim proces radi. Windows servis ili IIS application pool može koristiti drugačiji regionalni format od desktopa developera, pa rezultati viđeni u IDE-u nisu automatski ono što produkcija izračuna
• Keširani rezultati formula upisani u fajl odražavaju lokal mašine koja je generisala. Excel preračunava svojim lokalom, pa vrednost može da se promeni kad se fajl otvori negde drugde i preračuna; to je Excelovo ponašanje, ne HotXLS artefakt
• Lokali se najviše razilaze na akcentovanim slovima, na kombinacijama slova koje neki jezici tretiraju kao jedno slovo, i na nelatinskim pismima, pa probni podaci ograničeni na obične engleske reči neće otkriti problem

Granična linija platforme je prosta. HotXLS je Windows biblioteka, građena za Win32 i Win64 s Delphi-om i C++Builder-om i za win32 / win64 ciljeve s Lazarus-om i Free Pascal-om, i svi ti buildovi zovu isti CompareStringW. Ne postoji odvojen ne-Windows put kolacije. Jedini fallback je za pali API poziv: ako CompareStringW vrati 0, XlsCompareText poredi velikim slovima ispisane stringove po code unitu umesto da baci izuzetak usred preračunavanja, čime računanje nastavlja da radi, ali više ne garantuje Excelov red

Brzi podsetnik: poređenje teksta u Excelu u HotXLS-u

• Pravilo: word sort korisničkog lokala s NORM_IGNORECASE, bez SORT_STRINGSORT, bez NORM_IGNOREWIDTH, u HotXLS-u od v2.384.67
• - i ' samo rešavaju izjednačenja: ="a-b">"ab" je TRUE i ="a-b"="ab" je FALSE
• Ostala interpunkcija se sortira pre cifara, cifre pre slova: ="a~b"<"ab" i ="a0"<"ab" su TRUE
• Velika i mala slova nikad nisu važna: ="ABC"="abc" je TRUE i VLOOKUP("ABC",...) nalazi abc
• Pokriveni putevi: poredeći operatori, array poređenja, kriterijumi > / <, VLOOKUP / HLOOKUP, poređenje dinamičkih nizova, SortRange u oba engine-a
• Ovo pravilo ne pokriva: mešani tipovi (number < text < boolean) i kriterijumi s džoker znakovima, koji imaju svoja pravila
• U Delphi kodu: CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), proverite 0, oduzmite CSTR_EQUAL; izbegavajte CompareText, CompareStr i TComparer<string>.Default kad rezultat mora da se poklopi s Excelom
• Rezultati zavise od lokala naloga pod kojim se kod izvršava, u Excelu jednako kao u HotXLS-u

Obične reči se sortiraju isto pod svakim pravilom, pa pogrešnu kolaciju izlažu samo kodovi s crticom, interpunkcija i akcentovana imena. HotXLS sada daje Excelov odgovor za sve njih u oba engine-a, XLS i XLSX. Detalji o licenciranju, podržanim verzijama Delphi i C++Buildera i probnom preuzimanju su na stranici HotXLS Delphi Excel component