HotXLS usporedba teksta: Excel word-sort poredak u Delphiju

HotXLS Delphi Component uspoređuje dvije tekstovne vrijednosti onako kako Excel 16 od v2.384.67: bez razlikovanja velikih i malih slova, u "word sort" poretku Windows user localea, što je ono što CompareStringW vraća sa zastavicom NORM_IGNORECASE. Crtice i apostrofi prvi prolaz preskaču i samo razrješavaju neriješeno, pa je ="a-b">"ab" TRUE, dok se ostala interpunkcija slaže prije znamenki i slova, pa je i ="a~b"<"ab" TRUE. Istim redoslijedom sada upravljaju operatori usporedbe, kriteriji > / <, sortiranje raspona i VLOOKUP

Nitko ne prijavljuje bug pod naslovom "nepoklapanje collationa". Izvještaji kažu da COUNTIF(A:A,">M") na serveru broji dva reda više nego u Excelu, da cjenik sortiran od izvještajnog servisa stavlja X-100 tamo gdje Excel ne bi, ili da VLOOKUP("ABC",...) vraća #N/A iako stupac očito sadrži abc. Sva tri dolaze iz istog pitanja: kad su oba operanda tekst, koji je manji? Excel ima precizan odgovor, nije onaj koji daje većina Delphi koda, a prije v2.384.67 HotXLS je davao tri različita odgovora ovisno o tome koji je put u kodu pitao

Kojim pravilom Excel uspoređuje dva tekstovna niza?

Excel tekst uspoređuje word sortom user localea, ignorirajući velika i mala slova. Word sort je zadani collation Windows NLS funkcija usporedbe: slova se uspoređuju po svom lingvističkom redu, a ne po kodnim točkama, slova s akcentima sjede uz svoje osnovno slovo, a dva znaka dobivaju poseban tretman. Crtica - i apostrof ' u prvom prolazu se ignoriraju, pa co-op i coop završe jedan uz drugi, i tek kad ostatak nizova neriješi, njihova prisutnost odlučuje o redu. Svaki drugi interpunkcijski znak bitan je i slaže se prije znamenki, a znamenke se slažu prije slova

Tablica pokazuje što to znači u praksi, uz dvije usporedbe do kojih će se Delphi developer najčešće posegnuti. Excel stupac drži presude koje je Excel 16 vratio za IF(A<B,...), a HotXLS ih reproducira od v2.384.67

A vs B	Excel 16 / HotXLS	CompareStr (ordinal)	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

Dvije posljedice lako je promašiti. Prvo, uloga crtice kao razrješivača znači da je ="a-b"="ab" FALSE: nizovi su u sortiranju bliski susjedi, a ipak nisu jednaki. Drugo, jednakost potpuno ignorira velika i mala slova, pa su ab, AB i Ab isti ključ što se bilo koje usporedbe tiče. Sortiranje 20 testnih riječi Excelovim Range.Sort 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 ab grupe odlučuje pozicija ignoriranog znaka

Kako je utvrđen Excelov tekstovni poredak?

Excelov tekstovni poredak utvrđen je mjerenjem, ne dokumentacijom, jer Excelova dokumentacija ne imenuje collation. Test je generirao 4.000 slučajnih parova nizova iz ASCII interpunkcije, znamenki, oba slučaja slova, razmaka, é, ß, ä, kineskih znakova, full-width oblika i neprelomivog razmaka, s duljinama od 0 do 4, a pola parova građeno kao međusobne gotovo-jednake. Excel 16 evaluirao je IF(A<B,-1,IF(A=B,0,1)) za svaki par, a presude su uspoređene s Windows API-jem usporedbe s raznim skupovima zastavica

• NORM_IGNORECASE sam (zadani word sort, user locale): nijedno pravo nepoklapanje. Jedinih 7 razlika bile su ćelije čiji je cijeli sadržaj bio ', koje Excel konzumira kao prefiksni znak teksta, pa su to bili artefakti uzorkovanja, a ne razlike collationa
• NORM_IGNORECASE sa SORT_STRINGSORT: 41 nepoklapanje. String sort tretira crticu i apostrof kao obične simbole, što je upravo ponašanje koje Excel nema
• Dodavanje NORM_IGNOREWIDTH: krivo na drugi način, jer full-width i half-width oblike istog slova čini jednakima u usporedbi, a Excel ih drži odvojene

Druga, ručno odabrana provjera usporedila je svih 190 parova izvučenih iz 20 lukavih riječi i rezultat Excelova Range.Sort na istom stupcu. Oboje se slagalo s običnim NORM_IGNORECASE word sortom, a tih 190 presuda plus sortirani redoslijed sada su dio HotXLS regresijskog seta, pokretanog kroz klasični TXLSWorkbook motor i XLSX-nativni TXLSXWorkbook motor

Zašto CompareText i ordinalna usporedba pogađaju krivo?

CompareText i ordinalna usporedba Excelov red krivo pogađaju jer uspoređuju UTF-16 kodne jedinice, a kodnotočni poredak interpunkciju stavlja na proizvoljna mjesta u odnosu na slova. Crtica je U+002D, a apostrof U+0027, oboje ispod svakog slova, pa ordinalna usporedba "a-b" proglašava manjim od "ab" umjesto da crticu tretira kao razrješivača neriješenog. Tilda U+007E stoji iznad svakog slova, pa "a~b" izlazi kao veće, suprotno od Excela. CompareText u Delphi RTL-u na velika slova pretvara samo a..z i zatim uspoređuje kodne jedinice, što dodaje još jedno izobličenje: donja crta U+005F leži između velikih i malih slova, pa pretvorba u velika slova "a_b" pomiče s mjesta ispod "ab" na iznad njega. Nijedna funkcija ne zna da é pripada između e i f

Uobičajeni Delphi alati padaju na obje strane granice:

• CompareStr, string operator < i TComparer<string>.Default (koji zove CompareStr) ordinalni su i razlikuju velika i mala slova, pa TArray.Sort<string> bez comparera stavlja Z ispred f
• CompareText i SameText ordinalni su nakon ASCII-only pretvorbe slučaja
• 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 zadanim postavkama (UseLocale True, CaseSensitive False) prolazi kroz AnsiCompareText i zato se također slaže s Excelom
• Na POSIX ciljevima Delphi RTL AnsiCompareText usmjerava kroz ICU collator, što je drugi algoritam s drugim pravilima interpunkcije, a Free Pascalov AnsiCompareText na Windowsu zove CompareStringA nakon pretvorbe u ANSI code page, koja gubi svaki znak koji ta stranica ne može prikazati

Dakle locale-osviještene RTL funkcije na Windowsu su točne po implementaciji, ne po ugovoru, pa je kod koji treba Excelov red bolje da izričito zove API. HotXLS je interno imao istu mješavinu. Operatori usporedbe prebacivali su oba niza u velika slova i uspoređivali kodne točke, > / < grane funkcija kriterija koristile su Delphi Variant usporedbu koja razlikuje velika i mala slova, a VLOOKUP / HLOOKUP tekst su poklapali tom istom Variant usporedbom koja razlikuje velika i mala slova, pa VLOOKUP("ABC",A1:A20,1,FALSE) nije mogao naći abc. Sortiranje raspona već je koristilo WideCompareText. Tri puta, tri redoslijeda

Što je promijenjeno u HotXLS v2.384.67?

Od v2.384.67 tekst-prema-tekstu usporedbe u HotXLS putevima izračuna i sortiranja idu kroz jednu funkciju, XlsCompareText u lxStandard.pas, koja zove CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) i oduzima CSTR_EQUAL. Pozivatelji su šest operatora usporedbe, usporedbe po elementu u formulama polja, >, <, >= i <= grane kriterija stila COUNTIF i funkcija baze, VLOOKUP i HLOOKUP (točan i približan), pomoćne funkcije poredka iza funkcija dinamičkog polja i XLOOKUP / XMATCH, te sortiranje raspona oba motora. Usmjeravanje sortiranja raspona kroz istu funkciju jamči da se redoslijed sortiranja i redoslijed usporedbe više ne mogu razdvojiti, a to je bitno jer je približni VLOOKUP nad tekstom smislen samo kad je stupac sortiran u poretku u kojem lookup uspoređuje

uses
  System.Variants, lxHandleX;

var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Sheets.Add('Data');  // Calculate evaluira nad aktivnim listom
    Writeln(VarToStr(Book.Calculate('="a-b">"ab"')));   // True: crtica samo razrješava neriješeno
    Writeln(VarToStr(Book.Calculate('="a-b"="ab"')));   // False: neriješeno razriješ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 ignoriraju
  finally
    Book.Free;
  end;
end;

Usporedbe različitih tipova zasebno su pravilo i nisu se mijenjale: svaki je broj ispod svake tekstovne vrijednosti, a svaka tekstovna vrijednost ispod svakog logičkog, kako je opisano u članku o lancima usporedbi, praznim operandima i SUMIF-u. Word sort primjenjuje se tek kad su oba operanda tekst. Wildcard poklapanje također je zasebno: kriterij poput "a*" ili "=ab" jest test uzorka ili jednakosti, pokriven u vodiču kroz Excel wildcarde u COUNTIF-u, MATCH-u i DSUM-u, a collation o kojem ovdje govore odlučuje samo o operatorima poredka

Sljedeći primjer učitava 20 testnih riječi u stupac, sortira ga s TXLSXWorksheet.SortRange i provjerava jedno kriterijsko brojanje i jedan lookup. Brojanja su ona koja je Excel 16 vratio za isti stupac

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 nad istim stupcem: 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 prije v2.384.67: lookup je uspoređivao razlikujući 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

    // Jedan ključni stupac, uzlazno: 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 stabilno merge sortiranje, pa ab, AB i Ab, koji se uspoređuju kao jednaki, zadržavaju relativni redoslijed koji su imali prije sortiranja. Prazne ćelije idu na kraj u oba smjera, kao u Excelu

Kako poklopiti Excelov redoslijed sortiranja u vlastitom Delphi kodu?

Da poklopite Excelov tekstovni red u vlastitom Delphi kodu, zovite CompareStringW s LOCALE_USER_DEFAULT i NORM_IGNORECASE, a ne dodajte SORT_STRINGSORT ni NORM_IGNOREWIDTH. Povratna vrijednost nije predznačeni rezultat usporedbe: API vraća CSTR_LESS_THAN (1), CSTR_EQUAL (2) ili CSTR_GREATER_THAN (3), a 0 kad poziv padne. Oduzmite 2 za uobičajenu negativno / nula / pozitivno konvenciju i prvo testirajte 0, jer greška zamijenjena za rezultat postaje -2, tiho "manje"

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

// Excelov tekstovni poredak: word sort user localea, bez velikih i 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 usporedbe
  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 redoslijed), a-b, -ab, abc
end;

TArray.Sort nije stabilan, pa ključevi koji se uspoređuju kao jednaki, poput ab i AB, mogu izaći u bilo kojem redoslijedu; ako je redoslijed jednakih ključeva bitan, sortirajte polje indeksa s izvornom pozicijom kao sekundarnim ključem. Javlja se i suprotni slučaj: ponekad stupac ne smije slijediti Excelov red, na primjer šifre dijelova gdje su X-100 i X100 različiti kodovi i trebalo bi ih sortirati po kodnoj točki. TXLSXWorksheet.SortRange ima overload koji prima TXLSSortCompareEvent, metodu s potpisom function(const Left, Right: Variant): Integer of object, i koristi je umjesto ugrađene usporedbe

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
  // Vlastiti comparer dobiva i prazne ćelije (kao Null): smjestite 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 i mala slova
end;

var
  Sheet: TXLSXWorksheet;   // popunjena lista, redovi 2..501, stupci A..D
  Order: TPartNumberOrder;
begin
  // ...
  Order := TPartNumberOrder.Create;
  try
    // ključ po stupcu A, uzlazno
    Sheet.SortRange(2, 1, 501, 4, [1], [False], xlsSortByRows,
      xlsSortExcelLike, Order.Compare);
  finally
    Order.Free;
  end;
end;

Kad je dan vlastiti comparer, HotXLS preskače vlastito rukovanje praznim ćelijama i predaje sirove vrijednosti ključeva, pa comparer mora snaci se s Null. Za silazni ključ HotXLS negira sve što comparer vrati, što prazne ćelije također tjera na vrh osim ako comparer to ne računa. Imajte na umu da stupac sortiran ovako više nije u poretku kojega očekuje Excelov približni VLOOKUP ili binarno-pretrage XLOOKUP; zamke tih načina na podacima sortiranima u drugom redu pokriva vodič kroz XLOOKUP i XMATCH načine binarnog pretraživanja

Zašto ista radna knjiga može sortirati drugačije na drugom računalu?

Ista radna knjiga može sortirati drugačije na drugom računalu jer Excelov tekstovni red ovisi o Windows user localeu, a HotXLS tu ovisnost slijedi namjerno. Word sort ovisi o jeziku: švedski collation, na primjer, stavlja ä iza z, dok ga engleski i njemački drže uz a. Excel to nasljeđuje od localea pod kojim radi, pa radnu knjigu preračunana od kolege iz Stockholma može zadesiti drugačiji COUNTIF(...,">y") nego istu datoteku na desktopu u Chicagu. HotXLS prosljeđuje LOCALE_USER_DEFAULT da mu rezultati na istom računalu budu jednaki Excelovima; svaki fiksni locale natjerao bi HotXLS da se s Excelom ne slaže na svakom računalu s drugačijom postavkom

Za generiranje na strani servera slijede tri praktične posljedice:

• Locale koji se računa jest onaj računa pod kojim proces radi. Windows servis ili IIS aplikacijski bazen mogu koristiti drugačiji regionalni format od developerova desktopa, pa rezultati viđeni u IDE-u nisu automatski ono što produkcija računa
• Predmemorirani rezultati formula upisani u datoteku odražavaju locale računala koje je generiralo. Excel preračunava sa svojim localeom, pa se vrijednost može promijeniti kad se datoteka otvori negdje drugdje i preračuna; to je Excelovo ponašanje, ne HotXLS artefakt
• Localei se ne slažu najviše oko slova s akcentima, oko kombinacija slova koje neki jezici tretiraju kao jedno slovo i oko nelatinskih pisama, pa testni podaci ograničeni na obične engleske riječi neće otkriti problem

Granicu platforme jednostavna je. HotXLS je Windows biblioteka, građena za Win32 i Win64 s Delphijem i C++Builderom i za win32 / win64 ciljeve s Lazarusom i Free Pascalom, i svi ti buildovi zovu isti CompareStringW. Nema zasebnog ne-Windows puta collationa. Jedini fallback je za pali API poziv: ako CompareStringW vrati 0, XlsCompareText uspoređuje nizove prebačene u velika slova po kodnoj jedinici, umjesto da podigne iznimku usred preračunavanja, što izračun drži u pogonu, ali više ne jamči Excelov red

Brza referenca: Excel usporedba teksta u HotXLS-u

• Pravilo: word sort user localea sa NORM_IGNORECASE, bez SORT_STRINGSORT, bez NORM_IGNOREWIDTH, u HotXLS-u od v2.384.67
• - i ' samo razrješavaju neriješeno: ="a-b">"ab" je TRUE, a ="a-b"="ab" je FALSE
• Ostala interpunkcija slaže se prije znamenki, a znamenke prije slova: ="a~b"<"ab" i ="a0"<"ab" TRUE su
• Velika i mala slova nikad nisu bitna: ="ABC"="abc" je TRUE, a VLOOKUP("ABC",...) nalazi abc
• Pokriveni putevi: operatori usporedbe, usporedbe polja, kriteriji > / <, VLOOKUP / HLOOKUP, poredak dinamičkih polja, SortRange u oba motora
• Nije pokriveno ovim pravilom: miješani tipovi (number < text < boolean) i wildcard kriteriji, koji imaju vlastita pravila
• U Delphi kodu: CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), provjerite 0, oduzmite CSTR_EQUAL; izbjegavajte CompareText, CompareStr i TComparer<string>.Default kad rezultat mora odgovarati Excelu
• Rezultati ovise o localeu računa pod kojim kod radi, u Excelu jednako kao i u HotXLS-u

Obične riječi sortiraju se isto pod svakim pravilom, pa samo šifre s crticom, interpunkcija i imena s akcentima izlažu krivi collation. HotXLS sada daje Excelov odgovor na sve njih u oba motora, XLS i XLSX. Detalji o licenciranju, podržanim verzijama Delphija i C++Buildera i probno preuzimanje na stranici HotXLS Delphi Excel komponente