HotXLS:n tekstivertailu: Excelin sanajärjestys Delphissä

HotXLS Delphi Component vertaa kahta tekstiarvoa samaan tapaan kuin Excel 16 versiosta v2.384.67 alkaen: kirjainkokoa huomioimatta, Windowsin käyttäjän maa-asetuksen "sanajärjestys"-järjestyksessä, joka on se, mitä CompareStringW palauttaa NORM_IGNORECASE-flagilla. Väliviivat ja heittomerkit ohitetaan ensimmäisellä kierroksella ja ratkaisevat vain tasapelit, joten ="a-b">"ab" on TRUE, kun taas muut välimerkit lajitellaan lukujen ja kirjainten eteen, joten myös ="a~b"<"ab" on TRUE. Sama järjestys ohjaa nykyään vertailuoperaattoreita, > / <-ehtoja, alueiden lajittelua ja VLOOKUPia

Kukaan ei kirjaa bugia otsikolla "lajittelujärjestyksen ristiriita". Raportit kertovat, että COUNTIF(A:A,">M") laskee palvelimella kaksi riviä enemmän kuin Excelissä, että raportointipalvelun lajittelema hinnasto laittaa X-100:n jonnekin, mihin Excel ei sitä laittaisi, tai että VLOOKUP("ABC",...) palauttaa #N/A:n, vaikka sarake sisältää selvästi arvon abc. Kaikki kolme johtuvat samasta kysymyksestä: kun molemmat operandit ovat tekstiä, kumpi on pienempi? Excelillä on tarkka vastaus, se ei ole se, jonka useimmat Delphi-koodit antavat, ja ennen versiota v2.384.67 HotXLS antoi kolme eri vastausta riippuen siitä, mikä koodipolku kysyi

Mitä sääntöä Excel käyttää kahden tekstimerkkijonon vertailuun?

Excel vertaa tekstiä käyttäjän maa-asetuksen sanajärjestyksellä, kirjainkoosta välittämättä. Sanajärjestys on Windowsin NLS-vertailufunktioiden oletuslajittelujärjestys: kirjaimet vertautuvat kielellisessä järjestyksessään eivät koodipisteidensä mukaan, aksenttimerkilliset kirjaimet istuvat peruskirjaimensa vieressä, ja kaksi merkkiä saa erikoiskohtelun. Väliviiva - ja heittomerkki ' ohitetaan ensimmäisellä kierroksella, joten co-op ja coop asettuvat vierekkäin, ja vasta kun merkkijonojen loput täsmäävät, niiden läsnäolo ratkaisee järjestyksen. Jokainen muu välimerkki on merkitsevä ja lajitellaan lukujen eteen, ja luvut lajitellaan kirjainten eteen

Taulukko näyttää, mitä se tarkoittaa käytännössä, rinnakkain kahden vertailun kanssa, joihin Delphi-kehittäjä todennäköisimmin tarttuu. Excel-sarake sisältää tuomiot, jotka Excel 16 palautti kaavalle IF(A<B,...) ja jotka HotXLS toistaa versiosta v2.384.67 alkaen

A vs B	Excel 16 / HotXLS	CompareStr (ordinaali)	CompareText
"a-b" vs "ab"	suurempi	pienempi	pienempi
"a'b" vs "ab"	suurempi	pienempi	pienempi
"a~b" vs "ab"	pienempi	suurempi	suurempi
"a_b" vs "ab"	pienempi	pienempi	suurempi
"ab" vs "AB"	yhtä suuri	suurempi	yhtä suuri
"é" vs "f"	pienempi	suurempi	suurempi
"Z" vs "f"	suurempi	pienempi	suurempi

Kaksi seurausta on helppo ohittaa. Ensinnäkin väliviivan tasapelinratkaisijan rooli tarkoittaa, että ="a-b"="ab" on FALSE: merkkijonot ovat lajittelussa läheisiä naapureita, eivätkä silti ole yhtä suuria. Toiseksi yhtä suuruus ohittaa kirjainkodon täysin, joten ab, AB ja Ab ovat sama avain minkä tahansa vertailun kannalta. Kahdenkymmenen testisanan lajittelu Excelin Range.Sortilla antaa järjestyksen 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; ab-ryhmän sisällä ohitetun merkin paikka ratkaisee

Miten Excelin tekstijärjestys selvitettiin?

Excelin tekstijärjestys selvitettiin mittaamalla, ei dokumentaatiosta, sillä Excelin dokumentaatio ei nimeä lajittelujärjestystä. Testi tuotti 4 000 satunnaista merkkijonoparia ASCII-välimerkeistä, luvuista, molemmista kirjainkodoista, välilyönneistä, é:stä, ß:stä, ä:stä, kiinan kielen merkeistä, full-width-muodoista ja katkeamattomasta välilyönnistä, pituuksilla 0–4, ja puolet pareista rakennettiin toistensa lähes osuviksi. Excel 16 laski IF(A<B,-1,IF(A=B,0,1)) jokaiselle parille, ja tuomiot sovitettiin Windowsin vertailu-API:a vasten eri flagiyhdistelmillä

• NORM_IGNORECASE yksinään (oletussanajärjestys, käyttäjän maa-asetus): ei aitoja ristiriitoja. Ainoat 7 eroa olivat soluja, joiden koko sisältö oli ', jotka Excel nielaisee tekstin etuliitemerkkinä, joten ne olivat otannan artefakteja eivätkä lajittelujärjestyksen eroja
• NORM_IGNORECASE ja SORT_STRINGSORT: 41 ristiriitaa. Merkkijonojärjestys käsittelee väliviivan ja heittomerkin tavallisina symboleina, mikä on täsmälleen se käytös, jota Excelillä ei ole
• NORM_IGNOREWIDTHn lisääminen: väärin toisella tavalla, sillä se tekee saman kirjaimen full-width- ja half-width-muodoista yhtä suuret, ja Excel pitää ne erillään

Toinen, käsin poimittu tarkistus vertasi kaikki 190 paria, jotka oli poimittu 20 ovelan sanan joukosta, ja Excelin Range.Sortin tulosta samalle sarakkeelle. Molemmat sopivat puhtaaseen NORM_IGNORECASE-sanajärjestykseen, ja kyseiset 190 tuomiota plus lajiteltu järjestys ovat nyt osa HotXLS:n regressiotestiä, joka ajetaan sekä klassisen TXLSWorkbook-moottorin että natiivin XLSX-TXLSXWorkbook-moottorin läpi

Miksi CompareText ja ordinaalivertailu erehtyvät?

CompareText ja ordinaalivertailu saavat Excelin järjestyksen väärin, koska ne vertaavat UTF-16-koodiyksiköitä, ja koodipistejärjestys laittaa välimerkit satunnaisiin paikkoihin kirjaimiin nähden. Väliviiva on U+002D ja heittomerkki U+0027, molemmat jokaisen kirjaimen alapuolella, joten ordinaalivertailu sanoo "a-b":n pienemmäksi kuin "ab" sen sijaan, että käsittelisi väliviivaa tasapelinratkaisijana. Tilde U+007E istuu jokaisen kirjaimen yläpuolella, joten "a~b" tulee suuremmaksi, päinvastoin kuin Excelissä. Delphi-RTL:n CompareText taittaa vain a..z-kirjaimet isoiksi ja vertaa sitten koodiyksiköitä, mikä lisää toisen vääristymän: alaviiva U+005F makaa iso- ja pienaakkosten välissä, joten isoksi taittaminen siirtää "a_b":n arvon "ab" alta sen yläpuolelle. Kumpikaan funktio ei tiedä, että é kuuluu e:n ja f:n väliin

Tavalliset Delphi-työkalut asettuvat molemmin puolin rajaa:

• CompareStr, merkkijonojen <-operaattori ja TComparer<string>.Default (joka kutsuu funktiota CompareStr) ovat ordinaalisia ja kirjainkoon erottavia, joten TArray.Sort<string> ilman vertailijaa laittaa Z:n ennen f:ää
• CompareText ja SameText ovat ordinaalisia vain ASCII-kirjaimia taittavan muunnoksen jälkeen
• Delphi-RTL:n AnsiCompareText ja WideCompareText Windowsissa kutsuvat funktiota CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), samaa kutsua, joka täsmää Excelin kanssa. Lajiteltu TStringList oletusasetuksillaan (UseLocale True, CaseSensitive False) kulkee AnsiCompareTextin kautta ja sopii siksi myös Excelin kanssa
• POSIX-kohteissa Delphi-RTL ohjaa AnsiCompareTextin ICU-vertailijan läpi, mikä on eri algoritmi eri välimerkkisäännöillä, ja Free Pascal:n AnsiCompareText Windowsissa kutsuu funktiota CompareStringA muunnettuaan ANSI-koodisivulle, mikä menettää jokaisen merkin, jota kyseinen sivu ei pysty esittämään

Localea hyödyntävät RTL-funktiot ovat siis oikein Windowsissa toteutuksen, eivät sopimuksen nojalla, ja koodin, joka tarvitsee Excelin järjestyksen, kannattaa tehdä API-kutsu eksplisiittisesti. HotXLS:llä oli sisäisesti sama sekoitus. Vertailuoperaattorit tekivät molemmista merkkijonoista isolokirjaimiset ja vertasivat koodipisteitä, ehtofunktioiden > / <-haarat käyttivät Delphin kirjainkoon erottavaa Variant-vertailua, ja VLOOKUP / HLOOKUP sovitti tekstin samalla kirjainkoon erottavalla Variant-vertailulla, minkä vuoksi VLOOKUP("ABC",A1:A20,1,FALSE) ei löytänyt arvoa abc. Alueiden lajittelu käytti jo funktiota WideCompareText. Kolme polkua, kolme järjestystä

Mitä muuttui HotXLS v2.384.67:ssä?

Versiosta v2.384.67 alkaen teksti–teksti-vertailut HotXLS:n laskenta- ja lajittelupoluissa kulkevat yhden funktion, lxStandard.pas-tiedoston XlsCompareTextin kautta, joka kutsuu funktiota CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) ja vähentää arvon CSTR_EQUAL. Kutsujat ovat kuusi vertailuoperaattoria, elementti elementiltä -vertailut taulukkokaavoissa, COUNTIF-tyylisten ehtojen ja tietokantafunktioiden >-, <-, >=- ja <=-haarat, VLOOKUP ja HLOOKUP (tarkka ja approksimaattinen), dynaamisten taulukkofunktioiden ja XLOOKUP / XMATCH:n takana olevat järjestysapurit sekä kummankin moottorin aluelajittelu. Aluelajittelun ohjaaminen saman funktion kautta takaa, etteivät lajittelu- ja vertailujärjestys voi enää loitontua toisistaan, mikä on tärkeää, sillä tekstin approksimaattinen VLOOKUP on mielekäs vain, kun sarake on lajiteltu siinä järjestyksessä, jossa haku vertaa

uses
  System.Variants, lxHandleX;

var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Sheets.Add('Data');  // Calculate laskee aktiivista taulukkoa vasten
    Writeln(VarToStr(Book.Calculate('="a-b">"ab"')));   // True: väliviiva ratkaisee vain tasapelit
    Writeln(VarToStr(Book.Calculate('="a-b"="ab"')));   // False: tasapeli ratkaistu, eivät ole yhtä suuria
    Writeln(VarToStr(Book.Calculate('="a~b"<"ab"')));   // True: välimerkit ensin
    Writeln(VarToStr(Book.Calculate('="ABC"="abc"')));  // True: kirjainkoko ohitetaan
  finally
    Book.Free;
  end;
end;

Erityyppisten arvojen vertailut ovat erillinen sääntö eivätkä muuttuneet: jokainen luku on jokaisen tekstiarvon alapuolella ja jokainen tekstiarvo jokaisen totuusarvon alapuolella, kuten artikkelissa vertailuketjut, tyhjät operandit ja SUMIF kuvataan. Sanajärjestys astuu voimaan vasta, kun molemmat operandit ovat tekstiä. Myös jokerimerkkien sovitus on erillinen asia: ehto kuten "a*" tai "=ab" on malli- tai yhtäsuuruustesti, johon tutustutaan oppaassa Excelin jokerimerkit COUNTIFissa, MATCHissa ja DSUMissa, ja tässä käsitelty lajittelujärjestys ratkaisee vain järjestysoperaattorit

Seuraava esimerkki lataa 20 testisanaa sarakkeeseen, lajittelee sen funktiolla TXLSXWorksheet.SortRange ja tarkistaa ehtolaskennan ja haun. Lukumäärät ovat ne, jotka Excel 16 palautti samalle sarakkeelle

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 samalle sarakkeelle: 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")')));

    // Oli #N/A ennen versiota v2.384.67: haku vertasi kirjainkokoa erottaen
    Sheet.Cells[1, 3].Formula := '=VLOOKUP("ABC",A1:A20,1,FALSE)';
    Book.Recalculate;
    Writeln(VarToStr(Sheet.Cells[1, 3].Value));                // abc

    // Yksi avainsarake, nouseva: 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 käyttää vakaata lomituslajittelua, joten ab, AB ja Ab, jotka vertautuvat yhtä suuriksi, säilyttävät keskinäisen järjestyksensä lajittelua edeltäneenä. Tyhjät solut menevät loppuun molempiin suuntiin, kuten Excelissä

Miten saan oman Delphi-koodini noudattamaan Excelin lajittelujärjestystä?

Saadaksesi oman Delphi-koodisi noudattamaan Excelin tekstijärjestystä, kutsu funktiota CompareStringW arvoilla LOCALE_USER_DEFAULT ja NORM_IGNORECASE äläkä lisää flagia SORT_STRINGSORT eikä NORM_IGNOREWIDTH. Paluuarvo ei ole etumerkillinen vertailutulos: API palauttaa arvon CSTR_LESS_THAN (1), CSTR_EQUAL (2) tai CSTR_GREATER_THAN (3), ja 0:n, kun kutsu epäonnistuu. Vähennä luku 2 saadaksesi tavallisen negatiivisen / nollan / positiivisen konvention, ja testaa ensin nolla, sillä tulokseksi luullut epäonnistuminen muuttuu arvoksi -2, hiljainen "pienempi"

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

// Excelin tekstijärjestys: käyttäjän maa-asetuksen sanajärjestys, kirjainkoko ohitetaan
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 on virhe, ei vertailutulos
  Result := R - CSTR_EQUAL;    // 1/2/3 muuttuvat arvoiksi -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 (yhtä suuret, kumpi tahansa järjestys), a-b, -ab, abc
end;

TArray.Sort ei ole vakaa, joten yhtä suureksi vertautuvat avaimet, kuten ab ja AB, voivat tulla ulos kummassa tahansa järjestyksessä; jos yhtä suurten avainten alkuperäinen järjestys merkitsee, lajittele indeksitaulukko alkuperäisellä paikalla toissijaisena avaimena. Vastakohtakin tulee vastaan: joskus sarakkeen ei pidä noudattaa Excelin järjestystä, esimerkiksi osanumeroissa, joissa X-100 ja X100 ovat erillisiä koodeja ja pitäisi lajitella koodipisteittäin. TXLSXWorksheet.SortRangeilla on overload, joka ottaa vastaan TXLSSortCompareEvent-tapahtuman, metodin, jonka signature on function(const Left, Right: Variant): Integer of object, ja käyttää sitä sisäänrakennetun vertailun sijaan

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
  // Oma vertailija saa myös tyhjät solut (Null-arvona): sijoita ne itse
  if VarIsNull(Left) or VarIsNull(Right) then
    Exit(Ord(VarIsNull(Left)) - Ord(VarIsNull(Right)));
  Result := CompareStr(VarToStr(Left), VarToStr(Right));   // ordinaali, kirjainkoko erottaen
end;

var
  Sheet: TXLSXWorksheet;   // täytetty taulukko, rivit 2..501, sarakkeet A..D
  Order: TPartNumberOrder;
begin
  // ...
  Order := TPartNumberOrder.Create;
  try
    // avaimena sarake A, nouseva
    Sheet.SortRange(2, 1, 501, 4, [1], [False], xlsSortByRows,
      xlsSortExcelLike, Order.Compare);
  finally
    Order.Free;
  end;
end;

Kun oma vertailija on annettu, HotXLS ohittaa oman tyhjien solujen käsittelynsä ja välittää raakavaimen arvot, joten vertailijan on käsiteltävä Null. Laskevalle avaimelle HotXLS kääntää merkin sille, minkä vertailija palauttaa, mikä siirtää myös tyhjät ylös, ellei vertailija ota sitä huomioon. Muista, ettei tällä tavoin lajiteltu sarake ole enää siinä järjestyksessä, jonka Excelin approksimaattinen VLOOKUP tai binäärihakuinen XLOOKUP odottaa; kyseisten tilojen sudenkuopat toisin lajitellulla datalla on käsitelty oppaassa XLOOKUPin ja XMATCHin binäärihakutilat

Miksi sama työkirja voi lajitella toisin toisella koneella?

Sama työkirja voi lajitella toisin toisella koneella, koska Excelin tekstijärjestys riippuu Windowsin käyttäjän maa-asetuksesta, ja HotXLS seuraa tuota riippuvuutta tahallaan. Sanajärjestys on kielikohtainen: ruotsalainen lajittelujärjestys sijoittaa esimerkiksi ä:n z:n jälkeen, kun taas englanti ja saksa pitävät sen a:n vieressä. Excel perii tuon localesta, jonka alla se ajaa, joten Tukholman kollegan uudelleenlaskema työkirja voi palauttaa eri tuloksen kaavalle COUNTIF(...,">y") kuin sama tiedosto Chicagon työpöydällä. HotXLS välittää arvon LOCALE_USER_DEFAULT, jotta sen tulokset ovat yhtä suuret kuin Excelin samalla koneella; mikä tahansa kiinteä locale tekisi HotXLS:stä eri mieltä kuin Excel jokaisella koneella, jolla asetus on eri

Palvelinpuolen tuotannolle seuraa kolme käytännön seurausta:

• Ratkaiseva locale on sen tilin, jonka alla prosessi ajaa. Windows-palvelu tai IIS-sovelluspooli voi käyttää eri aluekohtaista muotoa kuin kehittäjän työpöytä, joten IDE:ssä havaitut tulokset eivät automaattisesti ole se, mitä tuotanto laskee
• Tiedostoon kirjoitetut välimuistitetut kaavojen tulokset heijastavat tuottavan koneen localea. Excel laskee uudelleen omalla localellaan, joten arvo voi muuttua, kun tiedosto avataan muualla ja lasketaan uudelleen; kyseessä on Excelin käytös, ei HotXLS:n artefakti
• Localet eriävät eniten aksenttimerkillisistä kirjaimista, kirjainyhdistelmistä, joita jotkin kielet käsittelevät yhtenä kirjaimena, ja ei-latinalaisista kirjaimistoista, joten pelkkiin tavallisiin englanninkielisiin sanoihin rajoittuva testidata ei paljasta ongelmaa

Alustaraja on yksinkertainen. HotXLS on Windows-kirjasto, rakennettu Win32- ja Win64-versioille Delphillä ja C++Builderilla sekä win32 / win64 -kohteille Lazaruksella ja Free Pascalilla, ja kaikki nämä käännökset kutsuvat samaa funktiota CompareStringW. Erillistä Windowsin ulkopuolista lajittelupolkua ei ole. Ainoa varapolku on epäonnistuneelle API-kutsulle: jos CompareStringW palauttaa 0:n, XlsCompareText vertaa isolokirjaimisiksi tehdyt merkkijonot koodiyksiköittäin sen sijaan, että nostaisi poikkeuksen uudelleenlaskennan keskellä, mikä pitää laskennan käynnissä mutta ei enää takaa Excelin järjestystä

Pikaopas: Excelin tekstivertailu HotXLS:ssä

• Sääntö: käyttäjän maa-asetuksen sanajärjestys flagilla NORM_IGNORECASE, ei SORT_STRINGSORTia, ei NORM_IGNOREWIDTHia, HotXLS:ssä versiosta v2.384.67 alkaen
• - ja ' ratkaisevat vain tasapelit: ="a-b">"ab" on TRUE ja ="a-b"="ab" on FALSE
• Muut välimerkit lajitellaan lukujen eteen, luvut kirjainten eteen: ="a~b"<"ab" ja ="a0"<"ab" ovat TRUE
• Kirjainkoko ei koskaan ratkaise: ="ABC"="abc" on TRUE ja VLOOKUP("ABC",...) löytää arvon abc
• Katetut polut: vertailuoperaattorit, taulukkovertailut, > / <-ehdot, VLOOKUP / HLOOKUP, dynaamisten taulukoiden järjestys, SortRange kummassakin moottorissa
• Ei tämän säännön piirissä: sekatyypit (luku < teksti < totuusarvo) ja jokerimerkkiehdot, joilla on omat sääntönsä
• Delphi-koodissa: CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), testaa 0, vähennä CSTR_EQUAL; vältä funktioita CompareText, CompareStr ja TComparer<string>.Default, kun tuloksen on sovittava Excelin kanssa
• Tulokset riippuvat koodia ajavan tilin localesta, Excelissä ja HotXLS:ssä yhtä lailla

Tavalliset sanat lajitellaan samoin kaikilla säännöillä, joten vain väliviivaiset koodit, välimerkit ja aksenttimerkilliset nimet paljastavat väärän lajittelujärjestyksen. HotXLS antaa nykyään Excelin vastauksen kaikkiin näihin sekä XLS- että XLSX-moottorissa. Lisensointi-, tuettujen Delphi- ja C++Builder-versioiden sekä kokeilulatauksen yksityiskohdat ovat HotXLS Delphi Excel -komponentin sivulla