HotXLS-tekstsammenligning: Excels ordsortering i Delphi

HotXLS Delphi Component sammenligner to tekstverdier slik Excel 16 gjør det siden v2.384.67: uten hensyn til store og små bokstaver, i «word sort»-rekkefølgen til brukerens Windows-locale, som er det CompareStringW returnerer med flagget NORM_IGNORECASE. Bindestrek og apostrof hoppes over i første runde og bryter bare likheter, så ="a-b">"ab" er TRUE, mens annen tegnsetting sorteres foran siffer og bokstaver, så ="a~b"<"ab" også er TRUE. Samme rekkefølge driver nå sammenligningsoperatorene, > / <-kriteriene, områdesorteringen og VLOOKUP

Ingen registrerer en feilrapport med tittelen «kollasjonsmismatch». Rapportene sier at COUNTIF(A:A,">M") teller to rader flere på serveren enn i Excel, at en prisliste sortert av rapporteringstjenesten legger X-100 et sted Excel ikke ville, eller at VLOOKUP("ABC",...) returnerer #N/A selv om kolonnen åpenbart inneholder abc. Alle tre kommer fra samme spørsmål: når begge operandene er tekst, hvilken er minst? Excel har et presist svar, det er ikke det mest Delphi-kode gir, og før v2.384.67 ga HotXLS tre ulike svar avhengig av hvilken kodesti som spurte

Hvilken regel bruker Excel for å sammenligne to tekststrenger?

Excel sammenligner tekst med brukerens locale sin ordsortering, uten hensyn til store og små bokstaver. Ordsortering er standardkollasjonen til Windows NLS-sammenligningsfunksjoner: bokstaver sammenlignes etter sin språklige rekkefølge snarere enn sine kodepunkter, aksentbokstaver ligger ved siden av grunnbokstaven sin, og to tegn får spesiell behandling. Bindestreken - og apostrofen ' ignoreres i første runde, så co-op og coop havner ved siden av hverandre, og bare når resten av strengene er like, avgjør deres tilstedeværelse rekkefølgen. Alle andre tegn for tegnsetting er signifikante og sorteres foran siffer, og siffer sorteres foran bokstaver

Tabellen viser hva det betyr i praksis, ved siden av de to sammenligningene en Delphi-utvikler er mest tilbøyelig til å gripe etter. Excel-kolonnen inneholder dommene Excel 16 ga for IF(A<B,...), som HotXLS gjenskaper siden v2.384.67

A mot B	Excel 16 / HotXLS	CompareStr (ordinær)	CompareText
"a-b" vs "ab"	større	mindre	mindre
"a'b" vs "ab"	større	mindre	mindre
"a~b" vs "ab"	mindre	større	større
"a_b" vs "ab"	mindre	mindre	større
"ab" vs "AB"	lik	større	lik
"é" vs "f"	mindre	større	større
"Z" vs "f"	større	mindre	større

To konsekvenser er lette å overse. For det første betyr bindestrekens likhetsbryter-rolle at ="a-b"="ab" er FALSE: strengene er nære naboer i sorteringen, men ikke like. For det andre ignorerer likheten store og små bokstaver fullstendig, så ab, AB og Ab er samme nøkkel så langt enhver sammenligning er opptatt. Sorterer du 20 testord med Excels Range.Sort, får du 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; innenfor ab-gruppen avgjør posisjonen til det ignorerte tegnet

Hvordan ble Excels tekstrekkefølge fastslått?

Excels tekstrekkefølge ble fastslått ved måling, ikke ved dokumentasjon, fordi Excels dokumentasjon ikke navngir kollasjonen. Testen genererte 4 000 tilfeldige strengpar fra ASCII-tegnsetting, siffer, begge bokstavkasus, mellomrom, é, ß, ä, kinesiske tegn, fullbrede former og det harde mellomrommet, med lengder fra 0 til 4 og halvparten av parene bygget som nesten-treff av hverandre. Excel 16 evaluerte IF(A<B,-1,IF(A=B,0,1)) for hvert par, og dommene ble matchet mot Windows sammenlignings-API med ulike flaggsett

• NORM_IGNORECASE alene (standard ordsortering, brukerens locale): ingen ekte mismatch. De eneste 7 forskjellene var celler hvis hele innhold var ', som Excel spiser som tekst-prefikstegnet, så de var uttaksartefakter snarere enn kollasjonsforskjeller
• NORM_IGNORECASE med SORT_STRINGSORT: 41 mismatches. String sort behandler bindestreken og apostrofen som ordinære symboler, noe som er nøyaktig den atferden Excel ikke har
• Å legge til NORM_IGNOREWIDTH: feil på en annen måte, fordi det gjør fullbrede og halvbrede former av samme bokstav like, og Excel holder dem atskilt

En andre, håndplukket sjekk sammenlignet alle 190 parene trukket fra 20 lumske ord med resultatet av Excels Range.Sort på samme kolonne. Begge var enige med ren NORM_IGNORECASE-ordsortering, og de 190 dommene pluss den sorterte rekkefølgen er nå en del av HotXLS regresjonssuite, kjørt gjennom både den klassiske TXLSWorkbook-motoren og den XLSX-native TXLSXWorkbook-motoren

Hvorfor tar CompareText og ordinær sammenligning feil?

CompareText og ordinær sammenligning tar Excels rekkefølge feil fordi de sammenligner UTF-16 kodeenheter, og kodepunktrekkefølgen plasserer tegnsetting på vilkårlige steder i forhold til bokstaver. Bindestreken er U+002D og apostrofen U+0027, begge under enhver bokstav, så en ordinær sammenligning kaller "a-b" mindre enn "ab" i stedet for å behandle bindestreken som en likhetsbryter. Tilden U+007E ligger over enhver bokstav, så "a~b" kommer ut større, det motsatte av Excel. CompareText i Delphi RTL bretter bare a..z til store bokstaver og sammenligner så kodeenheter, noe som legger til en ny forvrengning: understreken U+005F ligger mellom store og små bokstaver, så bretting til store bokstaver flytter "a_b" fra under "ab" til over den. Ingen av funksjonene vet at é hører hjemme mellom e og f

De vanlige Delphi-verktøyene havner på begge sider av streken:

• CompareStr, streng-<-operatoren og TComparer<string>.Default (som kaller CompareStr) er ordinære og kasussensitive, så TArray.Sort<string> uten en comparer legger Z foran f
• CompareText og SameText er ordinære etter ASCII-bare kasusbretting
• AnsiCompareText og WideCompareText i Delphi RTL på Windows kaller CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), samme kall som matcher Excel. En sortert TStringList med standardinnstillingene (UseLocale True, CaseSensitive False) går gjennom AnsiCompareText og er derfor også enig med Excel
• På POSIX-mål ruter Delphi RTL AnsiCompareText gjennom en ICU-kollator, som er en annen algoritme med andre regler for tegnsetting, og Free Pascals AnsiCompareText på Windows kaller CompareStringA etter konvertering til ANSI-kodesiden, noe som mister ethvert tegn den siden ikke kan representere

Så de locale-bevisste RTL-funksjonene er riktige på Windows av implementasjon, ikke av kontrakt, og kode som trenger Excels rekkefølge bør heller kalle API-et eksplisitt. HotXLS hadde samme miks internt. Sammenligningsoperatorene gjorde begge strengene om til store bokstaver og sammenlignet kodepunkter, > / <-grenene av kriteriefunksjonene brukte Delphis kasussensitive Variant-sammenligning, og VLOOKUP / HLOOKUP matchet tekst med den samme kasussensitive Variant-sammenligningen, noe som er grunnen til at VLOOKUP("ABC",A1:A20,1,FALSE) ikke fant abc. Områdesorteringen brukte allerede WideCompareText. Tre stier, tre rekkefølger

Hva endret seg i HotXLS v2.384.67?

Siden v2.384.67 går tekst-mot-tekst-sammenligningene i HotXLS beregnings- og sorteringsstier gjennom én funksjon, XlsCompareText i lxStandard.pas, som kaller CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) og trekker fra CSTR_EQUAL. Kallerne er de seks sammenligningsoperatorene, elementvise sammenligninger i matriseformler, >-, <-, >=- og <=-grenene av COUNTIF-aktige kriterier og databasefunksjonene, VLOOKUP og HLOOKUP (eksakt og tilnærmet), sorteringshjelperne bak de dynamiske matrisefunksjonene og XLOOKUP / XMATCH, og områdesorteringen i begge motorer. Å rute områdesorteringen gjennom samme funksjon garanterer at sorteringsrekkefølge og sammenligningsrekkefølge ikke kan skille lag igjen, noe som betyr noe fordi tilnærmet VLOOKUP på tekst bare er meningsfull når kolonnen ble sortert i den rekkefølgen oppslaget sammenligner i

uses
  System.Variants, lxHandleX;

var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Sheets.Add('Data');  // Calculate evaluerer mot det aktive arket
    Writeln(VarToStr(Book.Calculate('="a-b">"ab"')));   // True: bindestreken bryter bare likheter
    Writeln(VarToStr(Book.Calculate('="a-b"="ab"')));   // False: likheten er brutt, ikke like
    Writeln(VarToStr(Book.Calculate('="a~b"<"ab"')));   // True: tegnsetting først
    Writeln(VarToStr(Book.Calculate('="ABC"="abc"')));  // True: kasus ignoreres
  finally
    Book.Free;
  end;
end;

Kryss-type-sammenligninger er en egen regel og endret seg ikke: hvert tall ligger under enhver tekstverdi og enhver tekstverdi under enhver boolean, som beskrevet i artikkelen om sammenligningskjeder, tomme operander og SUMIF. Ordsorteringen gjelder bare når begge operandene er tekst. Jokertegn-matching er også adskilt: et kriterium som "a*" eller "=ab" er et mønster eller en likhetstest, dekket i guiden om Excel-jokertegn i COUNTIF, MATCH og DSUM, og kollasjonen som diskuteres her avgjør bare rekkefølgeoperatorene

Det neste eksemplet laster de 20 testordene inn i en kolonne, sorterer den med TXLSXWorksheet.SortRange, og sjekker et kriterietall og et oppslag. Tallene er de Excel 16 returnerte for samme kolonne

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 på samme kolonne: 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")')));

    // Var #N/A før v2.384.67: oppslaget sammenlignet kasussensitivt
    Sheet.Cells[1, 3].Formula := '=VLOOKUP("ABC",A1:A20,1,FALSE)';
    Book.Recalculate;
    Writeln(VarToStr(Sheet.Cells[1, 3].Value));                // abc

    // Én nøkkelkolonne, stigende: 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 bruker en stabil merge sort, så ab, AB og Ab, som sammenlignes like, beholder den relative rekkefølgen de hadde før sorteringen. Tomme celler går til slutten i begge retninger, som i Excel

Hvordan matcher jeg Excels sorteringsrekkefølge i min egen Delphi-kode?

For å matche Excels tekstrekkefølge i din egen Delphi-kode, kall CompareStringW med LOCALE_USER_DEFAULT og NORM_IGNORECASE, og ikke legg til SORT_STRINGSORT eller NORM_IGNOREWIDTH. Returverdien er ikke et signert sammenligningsresultat: API-et returnerer CSTR_LESS_THAN (1), CSTR_EQUAL (2) eller CSTR_GREATER_THAN (3), og 0 når kallet feiler. Trekk fra 2 for å få den vanlige negativ / null / positiv-konvensjonen, og test for 0 først, fordi en feil som forveksles med et resultat blir -2, en stille «mindre enn»

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

// Excels tekstrekkefølge: ordsortering i brukerens locale, uten hensyn til store/små bokstaver
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 er en feil, ikke et sammenligningsresultat
  Result := R - CSTR_EQUAL;    // 1/2/3 blir -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 (like, vilkårlig rekkefølge), a-b, -ab, abc
end;

TArray.Sort er ikke stabil, så nøkler som sammenlignes like, som ab og AB, kan komme ut i vilkårlig rekkefølge; betyr den opprinnelige rekkefølgen av like nøkler noe, sorterer du en indeksmatrise med den opprinnelige posisjonen som sekundær nøkkel. Det motsatte tilfellet dukker også opp: noen ganger skal en kolonne ikke følge Excels rekkefølge, for eksempel varenumre der X-100 og X100 er ulike koder og bør sorteres etter kodepunkt. TXLSXWorksheet.SortRange har en overload som tar imot en TXLSSortCompareEvent, en metode med signaturen function(const Left, Right: Variant): Integer of object, og bruker den i stedet for den innebygde sammenligningen

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
  // En egen comparer mottar også tomme celler (som Null): plasser dem selv
  if VarIsNull(Left) or VarIsNull(Right) then
    Exit(Ord(VarIsNull(Left)) - Ord(VarIsNull(Right)));
  Result := CompareStr(VarToStr(Left), VarToStr(Right));   // ordinær, kasussensitiv
end;

var
  Sheet: TXLSXWorksheet;   // et fylt ark, rader 2..501, kolonner A..D
  Order: TPartNumberOrder;
begin
  // ...
  Order := TPartNumberOrder.Create;
  try
    // nøkkel på kolonne A, stigende
    Sheet.SortRange(2, 1, 501, 4, [1], [False], xlsSortByRows,
      xlsSortExcelLike, Order.Compare);
  finally
    Order.Free;
  end;
end;

Når en egen comparer er oppgitt, hopper HotXLS over sin egen tomhåndtering og sender de rå nøkkelverdiene, så comparer-en må håndtere Null. For en synkende nøkkel negerer HotXLS det comparer-en returnerer, noe som også flytter tomme celler til toppen med mindre comparer-en tar høyde for det. Husk at en kolonne sortert slik ikke lenger ligger i den rekkefølgen Excels tilnærmete VLOOKUP eller en binærsøk-XLOOKUP forventer; fallgruvene i de modene på data sortert i en annen rekkefølge er dekket i guiden om XLOOKUP- og XMATCH-binærsøkmoduser

Hvorfor kan samme arbeidsbok sortere annerledes på en annen maskin?

Samme arbeidsbok kan sortere annerledes på en annen maskin fordi Excels tekstrekkefølge avhenger av brukerens Windows-locale, og HotXLS følger den avhengigheten med vilje. Ordsortering er språkspesifikk: den svenske kollasjonen plasserer for eksempel ä etter z, der engelsk og tysk holder den ved siden av a. Excel arver det fra locale-en den kjører under, så en arbeidsbok rekalkulert av en kollega i Stockholm kan returnere en annen COUNTIF(...,">y") enn samme fil på en stasjonær maskin i Chicago. HotXLS sender LOCALE_USER_DEFAULT slik at resultatene blir like med Excel på samme maskin; en fast locale ville gjort HotXLS uenig med Excel på hver maskin med en annen innstilling

Tre praktiske konsekvenser følger for generering på serversiden:

• Locale-en som teller, er den til kontoen prosessen kjører under. En Windows-tjeneste eller en IIS-applikasjonspool kan bruke et annet regionalt format enn utviklerens stasjonære maskin, så resultater observert i IDE-en er ikke automatisk det produksjonen beregner
• Bufrede formelresultater skrevet inn i filen speiler genereringsmaskinens locale. Excel rekalkulerer med sin egen locale, så en verdi kan endre seg når filen åpnes et annet sted og rekalkuleres; det er Excels atferd, ikke et HotXLS-artefakt
• Locales er uenige mest om aksentbokstaver, om bokstavkombinasjoner noen språk behandler som én bokstav, og om ikke-latinske skript, så testdata begrenset til vanlige engelske ord avslører ikke problemet

Plattformgrensen er enkel. HotXLS er et Windows-bibliotek, bygget for Win32 og Win64 med Delphi og C++Builder og for win32 / win64-mål med Lazarus og Free Pascal, og alle disse byggene kaller samme CompareStringW. Det finnes ingen separat ikke-Windows-kollasjonssti. Eneste fallback er et feilet API-kall: returnerer CompareStringW 0, sammenligner XlsCompareText de store-bokstav-versjonene av strengene etter kodeenhet i stedet for å reise et unntak midt i en rekalkulering, noe som holder beregningen i gang men ikke lenger garanterer Excels rekkefølge

Hurtigreferanse: Tekstsammenligning i Excel-stil i HotXLS

• Regel: ordsortering i brukerens locale med NORM_IGNORECASE, ingen SORT_STRINGSORT, ingen NORM_IGNOREWIDTH, i HotXLS siden v2.384.67
• - og ' bryter bare likheter: ="a-b">"ab" er TRUE og ="a-b"="ab" er FALSE
• Annen tegnsetting sorteres foran siffer, siffer foran bokstaver: ="a~b"<"ab" og ="a0"<"ab" er TRUE
• Kasus betyr aldri noe: ="ABC"="abc" er TRUE og VLOOKUP("ABC",...) finner abc
• Dekkede stier: sammenligningsoperatorer, matrisesammenligninger, > / <-kriterier, VLOOKUP / HLOOKUP, dynamisk matrise-rekkefølge, SortRange i begge motorer
• Ikke dekket av denne regelen: blandede typer (tall < tekst < boolean) og jokertegn-kriterier, som har sine egne regler
• I Delphi-kode: CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), sjekk for 0, trekk fra CSTR_EQUAL; unngå CompareText, CompareStr og TComparer<string>.Default når resultatet må stemme med Excel
• Resultatene avhenger av locale-en til kontoen som kjører koden, i Excel og i HotXLS alike

Vanlige ord sorteres likt under enhver regel, så bare koder med bindestrek, tegnsetting og aksenterte navn avslører feil kollasjon. HotXLS gir nå Excels svar på alle dem i både XLS- og XLSX-motoren. Detaljer om lisensiering, støttede Delphi- og C++Builder-versjoner og prøveversjonen finner du på produktsiden for HotXLS Delphi Excel-komponent