HotXLS teksto palyginimas: Excel žodžių rikiavimas Delphi

HotXLS Delphi Component nuo v2.384.67 lygina dvi teksto reikšmes taip, kaip Excel 16: neatsižvelgdamas į registrą, pagal Windows naudotojo vietovės „word sort“ tvarką, kurią grąžina CompareStringW su NORM_IGNORECASE vėliavėle. Brūkšneliai ir apostrofai pirmame etape praleidžiami ir tik išlygina lygiąsias, tad ="a-b">"ab" yra TRUE, o kita skyryba rikiuojasi prieš skaitmenis ir raides, tad ir ="a~b"<"ab" yra TRUE. Ta tvarka dabar varo palyginimo operatorius, > / < kriterijus, diapazono rikiavimą ir VLOOKUP

Niekas nefilia klaidos, pavadintos „collation mismatch“. Pranešimai sako, kad COUNTIF(A:A,">M") serveryje suskaičiuoja dviem eilutėm daugiau negu Excel, kad ataskaitų tarnybos surikiuotas kainynas X-100 padeda ten, kur Excel nebepadėtų, arba kad VLOOKUP("ABC",...) grąžina #N/A, nors stulpelyje akivaizdžiai yra abc. Visi trys atsako į tą patį klausimą: kai abu operandai tekstas, kuris mažesnis? Excel turi tikslų atsakymą, jis ne tas, kurį duoda dauguma Delphi kodo, o iki v2.384.67 HotXLS atsakydavo trimis skirtingais atsakymais, priklausomai nuo to, koks kelias klausė

Kokia taisykle Excel lygina dvi teksto eilutes?

Excel tekstą lygina naudotojo vietovės word sort tvarka, neskirdamas registro. Word sort yra numatytoji Windows NLS palyginimo funkcijų rikiavimo taisyklė: raidės lyginamos pagal savo lingvistinę tvarką, o ne pagal kodo taškus, kirčiuotos raidės stovi šalia savo pagrindinės raidės, o du simboliai sulaukia ypatingo elgesio. Brūkšnelis - ir apostrofas ' pirmame etape ignoruojami, tad co-op ir coop nusileidžia vienas šalia kito, ir tik kai likusi eilučių dalis lygi, jų buvimas nulemia tvarką. Kiekvienas kitas skyrybos ženklas reikšmingas ir rikiuojasi prieš skaitmenis, o skaitmenys – prieš raides

Lentelė rodo, ką tai reiškia praktikoje, šalia dviejų palyginimų, prie kurių Delphi kūrėjas labiausiai linksta. Excel stulpelyje – Excel 16 verdiktai formulėms IF(A<B,...), kuriuos HotXLS atkuria nuo v2.384.67

A prieš B	Excel 16 / HotXLS	CompareStr (ordinarinė)	CompareText
"a-b" vs "ab"	didesnė	mažesnė	mažesnė
"a'b" vs "ab"	didesnė	mažesnė	mažesnė
"a~b" vs "ab"	mažesnė	didesnė	didesnė
"a_b" vs "ab"	mažesnė	mažesnė	didesnė
"ab" vs "AB"	lygu	didesnė	lygu
"é" vs "f"	mažesnė	didesnė	didesnė
"Z" vs "f"	didesnė	mažesnė	didesnė

Dvi išvadas lengva pražiopsoti. Pirma, brūkšnelio lygiąsias laužiantis vaidmuo reiškia, kad ="a-b"="ab" yra FALSE: eilutės rikiavime kaimynės, bet ne lygios. Antra, lygybė registro ignoruoja visiškai, tad ab, AB ir Ab yra tas pats raktas, kol kalba apie bet kokį palyginimą. Surikiavus 20 bandomųjų žodžių su Excel Range.Sort gaunama 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 grupėje nulemia ignoruojamo simbolio pozicija

Kaip buvo nustatyta Excel teksto tvarka?

Excel teksto tvarka nustatyta matavimu, o ne dokumentacija, nes Excel dokumentacija rikiavimo taisyklės neįvardija. Testas sugeneravo 4 000 atsitiktinių eilučių porų iš ASCII skyrybos, skaitmenų, abiejų registrų raidžių, tarpų, é, ß, ä, kinų simbolių, full-width formų ir nerijos tarpo, kurių ilgiai nuo 0 iki 4, o pusė porų sudaryta kaip beveik sutampančios. Excel 16 kiekvienai porai įvertino IF(A<B,-1,IF(A=B,0,1)), o verdiktai sulyginti su Windows palyginimo API su skirtingais vėliavėlių rinkiniais

• Vien NORM_IGNORECASE (numatytasis word sort, naudotojo vietovė): jokių tikrų nesutapimų. Vieninteliai 7 skirtumai buvo langeliai, kurių visas turinys – ', kuriuos Excel suvalgo kaip teksto priešdėlio simbolį, tad tai buvo ėmimo atsitiktinumai, o ne rikiavimo skirtumai
• NORM_IGNORECASE su SORT_STRINGSORT: 41 nesutapimas. String sort brūkšnelį ir apostrofą laiko paprastais simboliais, o būtent tokio elgesio Excel neturi
• Pridėjus NORM_IGNOREWIDTH: klaidinga kitaip, nes jis tos pačios raidės full-width ir half-width formas lygina kaip lygias, o Excel jas skiria

Antra, rankomis parinkta patikra sulygino visus 190 porų, paimtų iš 20 klastingų žodžių, ir Excel Range.Sort rezultatą tame pačiame stulpelyje. Abu sutapo su paprastu NORM_IGNORECASE word sort, o tie 190 verdiktų kartu su surikiuota tvarka dabar yra HotXLS regresijos rinkinio dalis, leidžiama ir per klasikinį TXLSWorkbook variklį, ir per XLSX natyvų TXLSXWorkbook variklį

Kodėl CompareText ir ordinarinis palyginimas klysta?

CompareText ir ordinarinis palyginimas Excel tvarką klysta, nes lygina UTF-16 kodo vienetus, o kodo taškų tvarka skyrybą raidžių atžvilgiu išmėčiusi atsitiktinai. Brūkšnelis yra U+002D, apostrofas U+0027, abu žemiau kiekvienos raidės, tad ordinarinis palyginimas "a-b" paskelbia mažesne už "ab", vietoj to, kad brūkšnelį traktuotų kaip lygiąsių laužėją. Tildė U+007E stovi aukščiau kiekvienos raidės, tad "a~b" išeina didesnė – atvirkščiai nei Excel. CompareText Delphi RTL sulenkia tik a..z į didžiąsias ir tada lygina kodo vienetus, kas prideda antrą iškraipymą: pabraukimas U+005F guli tarp didžiųjų ir mažųjų raidžių, tad sulenkimas į didžiąsias "a_b" pakelia iš po "ab" virš jo. Nė viena funkcija nežino, kad é priklauso tarp e ir f

Įprasti Delphi įrankiai išsidėsto abiejose linijos pusėse:

• CompareStr, eilutės < operatorius ir TComparer<string>.Default (kviečiantis CompareStr) yra ordinariniai ir jautrūs registrui, tad TArray.Sort<string> be lygintuvo Z padeda prieš f
• CompareText ir SameText ordinariniai po tik ASCII registro sulenkimo
• AnsiCompareText ir WideCompareText Delphi RTL Windows aplinkoje kviečia CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) – tą patį kvietimą, kuris sutampa su Excel. Surikuota TStringList su numatytosiomis reikšmėmis (UseLocale True, CaseSensitive False) eina per AnsiCompareText ir todėl su Excel irgi sutampa
• POSIX tiksluose Delphi RTL AnsiCompareText nukreipia per ICU rikiuotuvą – kitą algoritmą su kitomis skyrybos taisyklėmis, o Free Pascal AnsiCompareText Windows aplinkoje kviečia CompareStringA po konvertavimo į ANSI kodavimo puslapį, kuris praranda bet kurį simbolį, kurio tas puslapis negali užrašyti

Taigi vietovės sąmoningos RTL funkcijos Windows aplinkoje teisingos pagal implementaciją, o ne pagal sutartį, tad kodui, kuriam reikia Excel tvarkos, geriau aiškiai kviesti API. HotXLS viduje turėjo tą patį kokteilį. Palyginimo operatoriai abejas eilutes padarydavo didžiosiomis ir lygindavo kodo taškus, kriterijų funkcijų > / < šakos naudodavo Delphi registro jautrų Variant palyginimą, o VLOOKUP / HLOOKUP tekstą sutapdindavo tą pačiu registro jautriu Variant palyginimu, todėl VLOOKUP("ABC",A1:A20,1,FALSE) negalėjo rasti abc. Diapazono rikiavimas jau naudojo WideCompareText. Trys keliai, trys tvarkos

Kas pasikeitė HotXLS v2.384.67?

Nuo v2.384.67 tekstas-su-tekstu palyginimai HotXLS skaičiavimo ir rikiavimo keliuose eina per vieną funkciją, XlsCompareText iš lxStandard.pas, kuri kviečia CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) ir atima CSTR_EQUAL. Kviestėjai – šeši palyginimo operatoriai, palyginimai po elementą masyvo formulėse, COUNTIF stiliaus kriterijų ir duomenų bazės funkcijų >, <, >= ir <= šakos, VLOOKUP ir HLOOKUP (tikslūs ir apytiksliai), dinaminio masyvo funkcijų ir XLOOKUP / XMATCH užkulisių tvarkos pagalbininkai bei abiejų variklių diapazono rikiavimas. Diapazono rikiavimas per tą pačią funkciją garantuoja, kad rikiavimo ir palyginimo tvarkos vėl nebeatsiskirs, o tai svarbu, nes apytikslis VLOOKUP tekste prasmingas tik tada, kai stulpelis surikiuotas ta tvarka, kuria paieška lygina

uses
  System.Variants, lxHandleX;

var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Sheets.Add('Data');  // Calculate vertina prieš aktyvųjį lapą
    Writeln(VarToStr(Book.Calculate('="a-b">"ab"')));   // True: brūkšnelis tik išlygina lygiąsias
    Writeln(VarToStr(Book.Calculate('="a-b"="ab"')));   // False: lygiąsios išlaužtos, nelygu
    Writeln(VarToStr(Book.Calculate('="a~b"<"ab"')));   // True: skyryba pirmiau
    Writeln(VarToStr(Book.Calculate('="ABC"="abc"')));  // True: registro nepaisoma
  finally
    Book.Free;
  end;
end;

Kryžminių tipų palyginimai – atskira taisyklė ir nepasikeitė: kiekvienas skaičius žemiau kiekvienos tekstinės reikšmės, o kiekviena tekstinė reikšmė žemiau kiekvieno Boolean, kaip aprašyta straipsnyje palyginimo grandinės, tušti operandai ir SUMIF. Word sort taikomas tik kai abu operandai tekstas. Pakaitos simbolių atitikimas taip pat atskiras: kriterijus kaip "a*" arba "=ab" yra šablono arba lygybės testas, dengiamas vadove Excel pakaitos simboliai COUNTIF, MATCH ir DSUM, o čia aptarta rikiavimo taisyklė sprendžia tik rikiavimo operatorius

Kitas pavyzdys įkelia 20 bandomųjų žodžių į stulpelį, surikiuoja jį su TXLSXWorksheet.SortRange ir patikrina kriterijaus skaičiavimą bei paiešką. Skaičiai – tie, kuriuos to paties stulpelio atveju grąžino Excel 16

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 tame pačiame stulpelyje: 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")')));

    // Iki v2.384.67 buvo #N/A: paieška lygino jautriai registrui
    Sheet.Cells[1, 3].Formula := '=VLOOKUP("ABC",A1:A20,1,FALSE)';
    Book.Recalculate;
    Writeln(VarToStr(Sheet.Cells[1, 3].Value));                // abc

    // Vienas rakto stulpelis, didėjimo tvarka: 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 naudoja stabilųjį suliejimo rikiavimą, tad ab, AB ir Ab, lyginami kaip lygūs, išlaiko santykinę tvarką, kurią turėjo iki rikiavimo. Tušti langeliai abejomis kryptimis keliauja į galą, kaip ir Excel

Kaip savo Delphi kode pasiekti Excel rikiavimo tvarką?

Kad savo Delphi kode gautumėte Excel teksto tvarką, kvieskite CompareStringW su LOCALE_USER_DEFAULT ir NORM_IGNORECASE, nepridėdami SORT_STRINGSORT ar NORM_IGNOREWIDTH. Grąžinama reikšmė nėra ženklintas palyginimo rezultatas: API grąžina CSTR_LESS_THAN (1), CSTR_EQUAL (2) arba CSTR_GREATER_THAN (3), o nesėkmės atveju 0. Atėmę 2 gausite įprastąją neigiama / nulis / teigiama konvenciją, ir pirmiausia patikrinkite nulį, nes nesėkmė, apsiklaidžiusi kaip rezultatas, virsta -2 – tyliu „mažiau už“

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

// Excel teksto tvarka: naudotojo vietovės word sort, be registro skirtumų
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 yra nesėkmė, o ne palyginimo rezultatas
  Result := R - CSTR_EQUAL;    // 1/2/3 tampa -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 (lygios, bet kokia tvarka), a-b, -ab, abc
end;

TArray.Sort nestabilus, tad lygūs raktai, pavyzdžiui ab ir AB, gali iškilti bet kuria tvarka; jei lygių raktų pradinė tvarka svarbi, rikiuokite indeksų masyvą su pradine pozicija kaip antriniu raktu. Pasitaiko ir atvirkščias atvejis: kartais stulpelis neturi sekti Excel tvarkos, pavyzdžiui detalių numeriai, kur X-100 ir X100 yra skirtingi kodai ir turėtų rikiuotis pagal kodo taškus. TXLSXWorksheet.SortRange turi perkrovimą, imantį TXLSSortCompareEvent – metodą su parašu function(const Left, Right: Variant): Integer of object – ir naudoja jį vietoje įtaisytojo palyginimo

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
  // Pasirinktinis lygintuvas taip pat gauna tuščius langelius (kaip Null): sudėliokite juos pats
  if VarIsNull(Left) or VarIsNull(Right) then
    Exit(Ord(VarIsNull(Left)) - Ord(VarIsNull(Right)));
  Result := CompareStr(VarToStr(Left), VarToStr(Right));   // ordinarinis, jautrus registrui
end;

var
  Sheet: TXLSXWorksheet;   // užpildytas lapas, eilutės 2..501, stulpeliai A..D
  Order: TPartNumberOrder;
begin
  // ...
  Order := TPartNumberOrder.Create;
  try
    // rikiuojama pagal A stulpelį, didėjimo tvarka
    Sheet.SortRange(2, 1, 501, 4, [1], [False], xlsSortByRows,
      xlsSortExcelLike, Order.Compare);
  finally
    Order.Free;
  end;
end;

Kai pateikiamas pasirinktinis lygintuvas, HotXLS praleidžia savą tuščių langelių apdorojimą ir perduoda neapdorotas rakto reikšmes, tad lygintuvas turi susitvarkyti su Null. Mažėjančiam raktui HotXLS pakeičia ženklą tai, ką grąžina lygintuvas, o tai tuščius langelius irgi pakelia į viršų, nebent lygintuvas tuo pasirūpintų. Turėkite omenyje, kad tokiu būdu surikuotas stulpelis nebūna tokioje tvarkoje, kokios tikisi Excel apytikslis VLOOKUP ar dvejetainės paieškos XLOOKUP; šių režimų spąstai ant kita tvarka surikuotų duomenų aprašyti vadove XLOOKUP ir XMATCH dvejetainės paieškos režimai

Kodėl ta pati darbaknygė kitoje mašinoje gali rikiuotis kitaip?

Ta pati darbaknygė kitoje mašinoje gali rikiuotis kitaip, nes Excel teksto tvarka priklauso nuo Windows naudotojo vietovės, ir HotXLS šios priklausomybės sąmoningai laikosi. Word sort priklauso nuo kalbos: švedų rikiavimo taisyklė, pavyzdžiui, ä pastato po z, nors anglų ir vokiečių laiko ją šalia a. Excel tai paveldi iš vietovės, kurioje veikia, tad Stokholmo kolegos perskaičiuota darbaknygė gali grąžinti kitokį COUNTIF(...,">y") negu tas pats failas Čikagoje staliniame kompiuteryje. HotXLS perduoda LOCALE_USER_DEFAULT, kad jo rezultatai toje pačioje mašinoje prilygtų Excel rezultatams; bet kokia fiksuota vietovė HotXLS nuo Excel atskirtų kiekvienoje mašinoje su kita nuostata

Serveriniam generavimui iš to seka trys praktinės išvados:

• Svarbi ta vietovė, kuria veikia paskyra, kurioje sukasi procesas. Windows tarnyba ar IIS taikymų baseinas gali naudoti kitą regiono formatą negu kūrėjo darbalaukis, tad IDE stebėti rezultatai nebūtinai tokie, kokius apskaičiuos produkcija
• Į failą įrašyti podėlyje laikomi formulės rezultatai atspindi generuojančios mašinos vietovę. Excel perskaičiuoja savo vietove, tad reikšmė gali pasikeisti, kai failas atveriamas kitoje vietoje ir perskaičiuojamas; tai Excel elgsena, o ne HotXLS artefaktas
• Vietovės nesutaria daugiausia dėl kirčiuotų raidžių, dėl raidžių junginių, kuriuos kai kurios kalbos laiko viena raide, ir dėl nelotyniškų raštų, tad bandomieji duomenys, apsiribojantys paprastais anglų žodžiais, problemos neatskleis

Platformų riba paprasta. HotXLS yra Windows biblioteka, kuriama Win32 ir Win64 su Delphi ir C++Builder bei win32 / win64 tikslams su Lazarus ir Free Pascal, ir visi šie variantai kviečia tą patį CompareStringW. Atskiros ne Windows rikiavimo atšakos nėra. Vienintelė atsarginė vėliava – nepavykusiam API kvietimui: jei CompareStringW grąžina 0, XlsCompareText didžiosiomis raidėmis parašytas eilutes lygina pagal kodo vienetus, vietoj to, kad perskaičiavimo viduryje mestų išimtį – skaičiavimas tęsiasi, bet Excel tvarka nebūna garantuota

Trumpa atmintinė: Excel teksto palyginimas HotXLS

• Taisyklė: naudotojo vietovės word sort su NORM_IGNORECASE, be SORT_STRINGSORT, be NORM_IGNOREWIDTH, HotXLS nuo v2.384.67
• - ir ' tik išlygina lygiąsias: ="a-b">"ab" yra TRUE, o ="a-b"="ab" – FALSE
• Kita skyryba rikiuojasi prieš skaitmenis, skaitmenys – prieš raides: ="a~b"<"ab" ir ="a0"<"ab" yra TRUE
• Registras niekada nesvarbus: ="ABC"="abc" yra TRUE, o VLOOKUP("ABC",...) randa abc
• Dengiami keliai: palyginimo operatoriai, masyvų palyginimai, > / < kriterijai, VLOOKUP / HLOOKUP, dinaminio masyvo tvarkymas, SortRange abiejuose varikliuose
• Ši taisyklė nedengia: mišrių tipų (skaičius < tekstas < Boolean) ir pakaitos simbolių kriterijų, kurie turi savas taisykles
• Delphi kode: CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), patikrinkite 0, atimkite CSTR_EQUAL; vengite CompareText, CompareStr ir TComparer<string>.Default, kai rezultatas turi sutapti su Excel
• Rezultatai priklauso nuo kodą vykdančios paskyros vietovės – ir Excel, ir HotXLS

Paprasti žodžiai po kiekviena taisykle rikiuojasi vienodai, tad netinkamą rikiavimo taisyklę atmegsta tik su brūkšneliais kodai, skyryba ir kirčiuoti vardai. HotXLS dabar visais jų atvejais duoda Excel atsakymą abiejuose varikliuose – XLS ir XLSX. Licencijavimo, palaikomų Delphi ir C++Builder versijų bei bandomosios versijos detalės yra HotXLS Delphi Excel komponento puslapyje