Primerjava besedil v HotXLS: besedni vrstni red Excela

Komponenta HotXLS Delphi primerja dve besedilni vrednosti od v2.384.67 tako, kot to dela Excel 16: brez ločevanja velikih in malih črk, v besednem vrstnem redu (word sort) uporabniškega locale Windows, kar vrača CompareStringW z zastavico NORM_IGNORECASE. Vezaji in opustki se v prvem prehodu preskočijo in le razrešujejo izenačenja, zato je ="a-b">"ab" TRUE, druga ločila pa se uredijo pred številkami in črkami, zato je tudi ="a~b"<"ab" TRUE. Isti vrstni red zdaj poganja operatorje primerjave, kriterije > / <, razvrščanje obsegov in VLOOKUP

Nihče ne prijavi hrošča z naslovom »neujemanje kolacije«. Poročila pravijo, da COUNTIF(A:A,">M") na strežniku prešteje dve vrstici več kot v Excelu, da cenik, razvrščen s storitvijo za poročila, postavi X-100 nekam, kamor Excel ne bi, ali pa da VLOOKUP("ABC",...) vrne #N/A, čeprav stolpec očitno vsebuje abc. Vsi trije izhajajo iz istega vprašanja: kadar sta oba operanda besedili, katera je manjša? Excel ima natančen odgovor, ni pa tisti, ki ga daje večina kode v Delphiju, HotXLS pa je pred v2.384.67 dal tri različne odgovore, odvisno od tega, katera kodaška pot je vprašala

Katero pravilo Excel uporabi za primerjavo dveh besedilnih nizov?

Excel primerja besedila z besednim vrstnim redom (word sort) uporabniškega locale, ne glede na velikost črk. Word sort je privzeta kolacija primerjalnih funkcij Windows NLS: črke se primerjajo po svojem jezikovnem vrstnem redu, ne po kodnih točkah, črke z diakritiko stojijo ob svoji osnovni črki, dva znaka pa dobita posebno obravnavo. Vezaj - in opustek ' se v prvem prehodu ignorirata, zato co-op in coop pristaneta drug ob drugem, njuna prisotnost pa odloči vrstni red šele, ko se preostanek nizov izenači. Vsako drugo ločilo je pomembno in se uredi pred številkami, številke pa pred črkami

Tabela pokaže, kaj to pomeni v praksi, ob dveh primerjavah, po katerih razvijalec v Delphiju poseže najraje. Stolpec Excel nosi sodbe, ki jih je Excel 16 vrnil za IF(A<B,...), HotXLS pa jih od v2.384.67 ponovi

A proti B	Excel 16 / HotXLS	CompareStr (ordinalno)	CompareText
"a-b" vs "ab"	večji	manjši	manjši
"a'b" vs "ab"	večji	manjši	manjši
"a~b" vs "ab"	manjši	večji	večji
"a_b" vs "ab"	manjši	manjši	večji
"ab" vs "AB"	enaki	večji	enaki
"é" vs "f"	manjši	večji	večji
"Z" vs "f"	večji	manjši	večji

Dve posledici sta lahko spregledani. Prvič, vloga vezaja pri razreševanju izenačenj pomeni, da je ="a-b"="ab" FALSE: niza sta v razvrščanju tesna soseda, a nista enaka. Drugič, enakost popolnoma ignorira velikost črk, zato so ab, AB in Ab za vsako primerjavo isti ključ. Razvrščanje 20 preskusnih besed z Excelovim Range.Sort da 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; znotraj skupine ab odloča položaj ignoriranega znaka

Kako je bil besedni vrstni red Excela sploh ugotovljen?

Besedni vrstni red Excela so določili z merjenjem, ne z dokumentacijo, ker Excelova dokumentacija kolacije ne poimenuje. Test je ustvaril 4000 naključnih parov nizov iz ločil ASCII, številk, obeh velikosti črk, presledkov, é, ß, ä, kitajskih znakov, polnoširinskih oblik in nedeljivega presledka, dolžin od 0 do 4, polovica parov pa je bila zgrajena kot skoraj enaka druga drugemu. Excel 16 je za vsak par ocenil IF(A<B,-1,IF(A=B,0,1)), sodbe pa so bile primerjane s primerjalnim API-jem Windows z različnimi nabori zastavic

• NORM_IGNORECASE sam (privzeti word sort, uporabniški locale): brez pravih neujemanj. Edinih 7 razlik so bile celice, katerih cela vsebina je bila ', ki jo Excel pogoltne kot znak besedilne predpone, zato so to bili vzorčni artefakti in ne razlike kolacije
• NORM_IGNORECASE z SORT_STRINGSORT: 41 neujemanj. String sort obravnava vezaj in opustek kot običajna simbola, kar je točno obnašanje, ki ga Excel nima
• Z dodanim NORM_IGNOREWIDTH: napačno drugače, ker polnoširinske in polovičnoširinske oblike iste črke primerja kot enaki, Excel pa ju loči

Drugo, ročno izbrano preverjanje je primerjalo vseh 190 parov, izvlečenih iz 20 zvijačnih besed, z rezultatom Excelovega Range.Sort na istem stolpcu. Oboje se je ujelo s preprostim word sortom NORM_IGNORECASE, teh 190 sodb plus razvrščeni vrstni red pa je zdaj del regresijske zbirke HotXLS, pognane skozi klasični pogon TXLSWorkbook in izvorno-XLSX pogon TXLSXWorkbook

Zakaj CompareText in ordinalna primerjava zgrešita?

CompareText in ordinalna primerjava zgrešita Excelov vrstni red, ker primerjata kodne enote UTF-16, vrstni red kodnih točk pa postavi ločila na poljubna mesta glede na črke. Vezaj je U+002D, opustek U+0027, oba pod vsako črko, zato ordinalna primerjava razglasi "a-b" za manjšega od "ab", namesto da bi vezaj obravnavala kot razreševalca izenačenj. Tilda U+007E stoji nad vsako črko, zato "a~b" pride kot večji, nasprotno od Excela. CompareText v Delphi RTL pretvori v velike črke samo a..z in nato primerja kodne enote, kar doda drugo popačenje: podčrtaj U+005F leži med velikimi in malimi črkami, zato pretvorba v velike črke premakne "a_b" izpod "ab" nad njega. Nobena od funkcij ne ve, da é pripada med e in f

Običajna orodja Delphija padajo na obeh straneh črte:

• CompareStr, operator niza < in TComparer<string>.Default (ki kliče CompareStr) so ordinalni in občutljivi na velikost, zato TArray.Sort<string> brez primerjalnika postavi Z pred f
• CompareText in SameText sta ordinalna po zlaganju velikosti samo iz ASCII
• AnsiCompareText in WideCompareText v Delphi RTL na Windows kličejo CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), isti klic, ki se ujema z Excelom. Razvrščen TStringList s privzetimi nastavitvami (UseLocale True, CaseSensitive False) gre skozi AnsiCompareText in se zato prav tako ujema z Excelom
• Na ciljih POSIX Delphi RTL usmerja AnsiCompareText skozi kolator ICU, kar je drugačen algoritem z drugačnimi pravili ločil, AnsiCompareText Free Pascala pa na Windows kliče CompareStringA po pretvorbi v kodno stran ANSI, s čimer izgubi vsak znak, ki te strani ne zna predstaviti

Funkcije RTL, ki poznajo locale, so na Windows torej prave po implementaciji, ne po pogodbi, koda, ki potrebuje Excelov vrstni red, pa je bolje, če klic API-ja naredi izrecno. HotXLS je imel interno isto mešanico. Operatorji primerjave sta oba niza pretvorila v velike črke in primerjala kodne točke, veji > / < kriterijskih funkcij so uporabile Delphi primerjavo Variant, občutljivo na velikost, VLOOKUP / HLOOKUP pa je besedila ujemal z isto Variant primerjavo, občutljivo na velikost, zato VLOOKUP("ABC",A1:A20,1,FALSE) ni mogel najti abc. Razvrščanje obsegov je že uporabljalo WideCompareText. Tri poti, trije vrstni redi

Kaj se je spremenilo v HotXLS v2.384.67?

Od v2.384.67 primerjave besedilo-proti-besedilu v računskih in razvrščevalnih poteh HotXLS gredo skozi eno funkcijo, XlsCompareText v lxStandard.pas, ki kliče CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) in odšteje CSTR_EQUAL. Klicatelji so šest operatorjev primerjave, primerjave po elementih v matričnih formulah, veji >, <, >= in <= kriterijev po vzoru COUNTIF ter funkcij baz podatkov, VLOOKUP in HLOOKUP (točno in približno), pomožniki razvrščanja za funkcijami dinamičnih matrik in XLOOKUP / XMATCH ter razvrščanje obsegov obeh pogonov. Usmerjanje razvrščanja obsegov skozi isto funkcijo jamči, da se vrstni red razvrščanja in vrstni red primerjave ne moreta spet raziti, kar šteje, ker je približni VLOOKUP na besedilu smiseln samo, kadar je bil stolpec razvrščen v vrstnem redu, v katerem iskanje primerja

uses
  System.Variants, lxHandleX;

var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Sheets.Add('Data');  // Calculate vrednoti na aktivnem listu
    Writeln(VarToStr(Book.Calculate('="a-b">"ab"')));   // True: vezaj le razrešuje izenačenja
    Writeln(VarToStr(Book.Calculate('="a-b"="ab"')));   // False: izenačenje razrešeno, nista enaka
    Writeln(VarToStr(Book.Calculate('="a~b"<"ab"')));   // True: ločila najprej
    Writeln(VarToStr(Book.Calculate('="ABC"="abc"')));  // True: velikost črk ignorirana
  finally
    Book.Free;
  end;
end;

Primerjave med tipi so ločeno pravilo in se niso spremenile: vsaka številka je pod vsako besedilno vrednostjo, vsaka besedilna vrednost pod vsako logično vrednostjo, kot opisuje članek o primerjalnih verigah, praznih operandih in SUMIF. Word sort velja šele, ko sta oba operanda besedili. Ujemanje z wildcardsi je prav tako ločeno: kriterij, kot je "a*" ali "=ab", je vzorec ali preizkus enakosti, pokrit v vodniku po Excelovih wildcardsih v COUNTIF, MATCH in DSUM, kolacija, o kateri je tu govora, pa odloča samo o operatorjih urejanja

Naslednji primer naloži 20 preskusnih besed v stolpec, razvrsti ga s TXLSXWorksheet.SortRange in preveri eno kriterijsko štetje ter eno iskanje. Štetja so tista, ki jih je Excel 16 vrnil za isti stolpec

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 istem stolpcu: 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")')));

    // Pred v2.384.67 je bilo #N/A: iskanje je primerjalo občutljivo na velikost
    Sheet.Cells[1, 3].Formula := '=VLOOKUP("ABC",A1:A20,1,FALSE)';
    Book.Recalculate;
    Writeln(VarToStr(Sheet.Cells[1, 3].Value));                // abc

    // En ključni stolpec, naraščajoč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 uporablja stabilno zlivanje (merge sort), zato ab, AB in Ab, ki se primerjajo kot enaka, obdržijo relativni vrstni red izpred razvrščanja. Prazne celice gredo v obe smeri na konec, kot v Excelu

Kako v svoji kodi Delphi dosegam Excelov vrstni red razvrščanja?

Če želite v svoji kodi Delphi dosegli Excelov besedni vrstni red, kličite CompareStringW z LOCALE_USER_DEFAULT in NORM_IGNORECASE, ne dodajte pa SORT_STRINGSORT ali NORM_IGNOREWIDTH. Vrnjena vrednost ni podpisani rezultat primerjave: API vrne CSTR_LESS_THAN (1), CSTR_EQUAL (2) ali CSTR_GREATER_THAN (3), ob spodletel klicu pa 0. Odštejte 2 za običajno konvencijo negativno / nič / pozitivno in najprej preizkusite 0, ker spodleteli klic, zamenjan za rezultat, postane -2, tiho »manj kot«

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

// Besedni vrstni red Excela: word sort uporabniškega locale, brez velikosti črk
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 napaka, ni rezultat primerjave
  Result := R - CSTR_EQUAL;    // 1/2/3 postanejo -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 (enaka, poljuben vrstni red), a-b, -ab, abc
end;

TArray.Sort ni stabilen, zato se lahko ključi, ki se primerjajo kot enaka, na primer ab in AB, izvrstijo v poljubnem vrstnem redu; če je izvirni vrstni red enakih ključev pomemben, razvrstite polje indeksov z izvirnim položajem kot drugim ključem. Nasprotni primer se tudi zgodi: včasih stolpec ne sme slediti Excelovemu vrstnemu redu, na primer številke delov, kjer sta X-100 in X100 različni kodi in se morata urediti po kodni točki. TXLSXWorksheet.SortRange ima preobremenitev, ki sprejme TXLSSortCompareEvent, metodo s podpisom function(const Left, Right: Variant): Integer of object, in jo uporabi namesto vgrajene primerjave

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
  // Primerjalnik po meri dobi tudi prazne celice (kot Null): razvrstite jih sami
  if VarIsNull(Left) or VarIsNull(Right) then
    Exit(Ord(VarIsNull(Left)) - Ord(VarIsNull(Right)));
  Result := CompareStr(VarToStr(Left), VarToStr(Right));   // ordinalno, občutljivo na velikost
end;

var
  Sheet: TXLSXWorksheet;   // zapolnjen list, vrstice 2..501, stolpci A..D
  Order: TPartNumberOrder;
begin
  // ...
  Order := TPartNumberOrder.Create;
  try
    // ključ na stolpcu A, naraščajoče
    Sheet.SortRange(2, 1, 501, 4, [1], [False], xlsSortByRows,
      xlsSortExcelLike, Order.Compare);
  finally
    Order.Free;
  end;
end;

Ko je primerjalnik po meri podan, HotXLS preskoči svojo obravnavo praznih celic in posreduje surove vrednosti ključev, zato se mora primerjalnik spoprijeti z Null. Za padajoči ključ HotXLS negira, kar koli primerjalnik vrne, kar prazne celice prestavi tudi na vrh, če se primerjalnik tega ne zaveda. Upoštevajte, da stolpec, razvrščen na ta način, ni več v vrstnem redu, ki ga pričakuje približni VLOOKUP Excela ali binarno iskanje XLOOKUP; pasti teh načinov na podatkih, razvrščenih v drugačnem vrstnem redu, so pokrite v vodniku po binarnih iskalnih načinih XLOOKUP in XMATCH

Zakaj se lahko isti delovni zvezek na drugem stroju razvrsti drugače?

Isti delovni zvezek se lahko na drugem stroju razvrsti drugače, ker besedni vrstni red Excela visi na uporabniškem locale Windows, HotXLS pa tej odvisnosti namenoma sledi. Word sort je odvisen od jezika: švedska kolacija na primer postavi ä za z, angleščina in nemščina pa jo držita ob a. Excel to podeduje od locale, pod katerim teče, zato lahko delovni zvezek, ki ga preračuna kolega iz Stockholma, vrne drugačen COUNTIF(...,">y") kot ista datoteka na namizju v Chicagu. HotXLS posreduje LOCALE_USER_DEFAULT, da so njegovi rezultati na istem stroju enaki Excelovim; vsak fiksni locale bi naredil, da bi se HotXLS na vsakem stroju z drugačno nastavitvijo razhajal z Excelom

Za generiranje na strani strežnika iz tega sledijo tri praktične posledice:

• Locale, ki šteje, je locale računa, pod katerim teče proces. Storitev Windows ali aplikacijski bazen IIS lahko uporablja drugačno regionalno obliko kot namizje razvijalca, zato rezultati, opaženi v IDE, niso samodejno tisto, kar izračuna produkcija
• Predpomnjeni rezultati formul, zapisani v datoteko, odražajo locale stroja, ki jo je generiral. Excel preračuna s svojim locale, zato se lahko vrednost spremeni, ko se datoteka odpre drugje in preračuna; to je obnašanje Excela, ne artefakt HotXLS
• Locale se najbolj razhajajo pri črkah z diakritiko, pri kombinacijah črk, ki jih nekateri jeziki obravnavajo kot eno samo črko, in pri ne-latinskih pisavah, zato preskusni podatki, omejeni na običajne angleške besede, težave ne bodo pokazali

Meja platforme je preprosta. HotXLS je knjižnica za Windows, zgrajena za Win32 in Win64 z Delphijem in C++Builderjem ter za cilje win32 / win64 z Lazarusom in Free Pascalom, vse te gradnje pa kličejo isti CompareStringW. Ločene poti kolacije za ne-Windows ni. Edina rezerva je za spodletel klic API-ja: če CompareStringW vrne 0, XlsCompareText primerja niza, pretvorjena v velike črke, po kodnih enotah, namesto da bi sredi preračuna sprožil izjemo — računanje teče naprej, Excelovega vrstnega reda pa ni več zajamčen

Hiter pregled: primerjava besedil Excel v HotXLS

• Pravilo: word sort uporabniškega locale z NORM_IGNORECASE, brez SORT_STRINGSORT, brez NORM_IGNOREWIDTH, v HotXLS od v2.384.67
• - in ' le razrešujeta izenačenja: ="a-b">"ab" je TRUE, ="a-b"="ab" pa FALSE
• Druga ločila se uredijo pred številkami, številke pred črkami: ="a~b"<"ab" in ="a0"<"ab" sta TRUE
• Velikost črk nikoli ne šteje: ="ABC"="abc" je TRUE, VLOOKUP("ABC",...) pa najde abc
• Pokrite poti: operatorji primerjave, matrične primerjave, kriteriji > / <, VLOOKUP / HLOOKUP, urejanje dinamičnih matrik, SortRange v obeh pogonih
• Nedotaknjeno s tem pravilom: mešani tipi (številka < besedilo < logična vrednost) in wildcard kriteriji, ki imata svoja pravila
• V kodi Delphi: CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), preizkus na 0, odštevanje CSTR_EQUAL; izogibajte se CompareText, CompareStr in TComparer<string>.Default, kadar se mora rezultat ujemati z Excelom
• Rezultati so odvisni od locale računa, pod katerim teče koda, enako v Excelu kot v HotXLS

Običajne besede se uredijo enako pod vsakim pravilom, zato napačno kolacijo izpostavijo samo kodirana imena z vezaji, ločila in imena z diakritiko. HotXLS zdaj da Excelov odgovor za vse njih v obeh pogonih, XLS in XLSX. Podrobnosti o licenciranju, podprtih različicah Delphi in C++Builder ter preizkusnem prenosu so na strani komponente HotXLS Delphi Excel