HotXLS Tekstsammenligning: Excels Word Sort i Delphi

HotXLS Delphi Component sammenligner to tekstværdier, som Excel 16 gør det, siden v2.384.67: uden hensyn til store/små bogstaver, i Windows-brugerlokalets "word sort"-orden, altså det, CompareStringW returnerer med flaget NORM_IGNORECASE. Bindestreger og apostroffer springes over i første gennemløb og udligner kun lige, så ="a-b">"ab" er TRUE, mens anden tegnsætning sorteres før cifre og bogstaver, så ="a~b"<"ab" også er TRUE. Den samme orden driver nu sammenligningsoperatorerne, > / <-kriterier, område-sortering og VLOOKUP

Ingen opretter en bug med titlen "collation mismatch". Rapporterne lyder, at COUNTIF(A:A,">M") tæller to rækker mere på serveren end i Excel, at en prisliste sorteret af rapporteringstjenesten placerer X-100 et sted, Excel ikke ville, eller at VLOOKUP("ABC",...) returnerer #N/A, selv om kolonnen åbenlyst indeholder abc. Alle tre udspringer af det samme spørgsmål: når begge operander er tekst, hvilken er så mindst? Excel har et præcist svar, det er ikke det, som det meste Delphi-kode giver, og før v2.384.67 gav HotXLS tre forskellige svar afhængigt af, hvilken kodevej der spurgte

Hvilken regel bruger Excel til at sammenligne to tekststrenge?

Excel sammenligner tekst med brugerlokalets word sort uden hensyn til versaler. Word sort er default-collationen af Windows' NLS-sammenligningsfunktioner: bogstaver sammenlignes efter deres lingvistiske orden frem for deres kodepunkter, accentbårne bogstaver ligger ved deres grundbogstav, og to tegn får særlig behandling. Bindestregen - og apostroffen ' ignoreres i første gennemløb, så co-op og coop lander side om side, og først når resten af strengene er lige, afgør deres tilstedeværelse ordenen. Ethvert andet tegnsætningstegn tæller med og sorteres før cifre, og cifre sorteres før bogstaver

Tabellen viser, hvad det betyder i praksis, ved siden af de to sammenligninger, en Delphi-udvikler oftest griber efter. Excel-kolonnen rummer de domme, Excel 16 returnerede for IF(A<B,...), som HotXLS gengiver siden v2.384.67

A vs B	Excel 16 / HotXLS	CompareStr (ordinal)	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"	lig	større	lig
"é" vs "f"	mindre	større	større
"Z" vs "f"	større	mindre	større

To konsekvenser er lette at overse. For det første betyder bindestregens tie-breaker-rolle, at ="a-b"="ab" er FALSE: strengene er nære naboer i sorteringen, men ikke ens. For det andet ignorerer lighed versaler fuldstændigt, så ab, AB og Ab er samme nøgle, så snart der sammenlignes. Sorterer man de 20 testord med Excels Range.Sort, fås 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; inden for ab-gruppen afgør det ignorerede tegns placering

Hvordan blev Excels tekstorden fastlagt?

Excels tekstorden blev identificeret ved måling, ikke ved dokumentation, fordi Excels dokumentation ikke nævner collationen. Testen genererede 4.000 tilfældige strengpar ud fra ASCII-tegnsætning, cifre, begge kasus, mellemrum, é, ß, ä, kinesiske tegn, full-width-former og det hårdte mellemrum, med længder fra 0 til 4 og halvdelen af parrene bygget som near-miss af hinanden. Excel 16 evaluerede IF(A<B,-1,IF(A=B,0,1)) for hvert par, og dommene blev matchet mod Windows' sammenlignings-API med forskellige flagsæt

• NORM_IGNORECASE alene (default word sort, brugerlokalet): ingen ægte mismatch. De eneste 7 forskelle var celler, hvis hele indhold var ', som Excel opsluger som tekst-præfikstegnet, så de var samplingartefakter frem for collation-forskelle
• NORM_IGNORECASE med SORT_STRINGSORT: 41 mismatches. String sort behandler bindestreg og apostrof som almindelige symboler, hvilket er præcis den opførsel, Excel ikke har
• Tilføjes NORM_IGNOREWIDTH: forkert på en anden måde, fordi det får full-width- og half-width-former af samme bogstav til at sammenlignes som ens, og Excel holder dem adskilt

Et andet, håndplukket tjek sammenlignede alle 190 par trukket fra 20 besværlige ord og resultatet af Excels Range.Sort på samme kolonne. Begge var enige med ren NORM_IGNORECASE-word sort, og de 190 domme plus sorteringsordenen er nu en del af HotXLS' regressionssuite, kørt gennem både den klassiske TXLSWorkbook-motor og den XLSX-native TXLSXWorkbook-motor

Hvorfor tager CompareText og ordinal sammenligning fejl?

CompareText og ordinal sammenligning tager Excels orden fejl, fordi de sammenligner UTF-16 kodeenheder, og kodepunkt-orden placerer tegnsætning vilkårligt i forhold til bogstaver. Bindestregen er U+002D, og apostroffen U+0027, begge under hvert bogstav, så en ordinal sammenligning kalder "a-b" mindre end "ab" i stedet for at behandle bindestregen som tie-breaker. Tilden U+007E ligger over hvert bogstav, så "a~b" kommer ud som større, det modsatte af Excel. CompareText i Delphi RTL folder kun a..z til versaler og sammenligner derefter kodeenheder, hvilket tilføjer en anden forvrængning: understregen U+005F ligger mellem versal- og minuskelbogstaverne, så foldning til versaler flytter "a_b" fra under "ab" til over den. Ingen af funktionerne ved, at é hører hjemme mellem e og f

De sædvanlige Delphi-værktøjer rammer på begge sider af linjen:

• CompareStr, streng-<-operatoren og TComparer<string>.Default (som kalder CompareStr) er ordinære og versalfølsomme, så TArray.Sort<string> uden en comparer sætter Z før f
• CompareText og SameText er ordinære efter ASCII-only versalfoldning
• AnsiCompareText og WideCompareText i Delphi RTL på Windows kalder CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), samme kald, der matcher Excel. En sorteret TStringList med dens defaults (UseLocale True, CaseSensitive False) går gennem AnsiCompareText og er derfor også enig med Excel
• På POSIX-mål ruter Delphi RTL AnsiCompareText gennem en ICU-collator, en anden algoritme med andre tegnsætningsregler, og Free Pascals AnsiCompareText på Windows kalder CompareStringA efter konvertering til ANSI-codepage, hvilket mister hvert tegn, den side ikke kan repræsentere

Altså er de locale-aware RTL-funktioner rigtige på Windows af implementering, ikke af kontrakt, og kode, der skal have Excels orden, gør klogest i at kalde API'et eksplicit. HotXLS havde den samme blanding internt. Sammenligningsoperatorerne versalede begge strenge og sammenlignede kodepunkter, > / <-grenene af kriteriefunktionerne brugte Delphis versalfølsomme Variant-sammenligning, og VLOOKUP / HLOOKUP matchede tekst med den samme versalfølsomme Variant-sammenligning, hvilket er grunden til, at VLOOKUP("ABC",A1:A20,1,FALSE) ikke kunne finde abc. Område-sorteringen brugte allerede WideCompareText. Tre veje, tre ordener

Hvad ændrede sig i HotXLS v2.384.67?

Siden v2.384.67 går tekst-mod-tekst-sammenligningerne i HotXLS' beregnings- og sorteringsveje gennem én funktion, XlsCompareText i lxStandard.pas, som kalder CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) og subtraherer CSTR_EQUAL. Kalderne er de seks sammenligningsoperatorer, elementvise sammenligninger i array-formler, >-, <-, >=- og <=-grenene af COUNTIF-agtige kriterier og databasefunktionerne, VLOOKUP og HLOOKUP (eksakt og approximativ), ordeningshjælperne bag dynamic-array-funktionerne og XLOOKUP / XMATCH samt område-sorteringen i begge motorer. At rute område-sorteringen gennem samme funktion garanterer, at sorterings- og sammenligningsorden ikke kan drive fra hinanden igen, hvilket tæller, fordi approximativ VLOOKUP på tekst kun giver mening, når kolonnen var sorteret i den orden, opslaget sammenligner i

uses
  System.Variants, lxHandleX;

var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Sheets.Add('Data');  // Calculate evaluerer mod det aktive ark
    Writeln(VarToStr(Book.Calculate('="a-b">"ab"')));   // True: bindestreg udligner kun lige
    Writeln(VarToStr(Book.Calculate('="a-b"="ab"')));   // False: ligheden brudt, ikke ens
    Writeln(VarToStr(Book.Calculate('="a~b"<"ab"')));   // True: tegnsætning først
    Writeln(VarToStr(Book.Calculate('="ABC"="abc"')));  // True: store/små bogstaver ignoreres
  finally
    Book.Free;
  end;
end;

Tværgående typesammenligninger er en separat regel og ændredes ikke: hvert tal er under hver tekstværdi, og hver tekstværdi er under hver boolean, som beskrevet i artiklen om sammenligningskæder, tomme operander og SUMIF. Word sort gælder først, når begge operander er tekst. Wildcard-matching er også separat: et kriterium som "a*" eller "=ab" er et mønster eller en lighedstest, dækket i guiden til Excel-wildcards i COUNTIF, MATCH og DSUM, og den collation, der drøftes her, afgør kun ordeningsoperatorerne

Det næste eksempel indlæser de 20 testord i en kolonne, sorterer den med TXLSXWorksheet.SortRange og tjekker et kriterietal og et opslag. Tallene er dem, Excel 16 returnerede 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: opslaget sammenlignede med versalfølsomhed
    Sheet.Cells[1, 3].Formula := '=VLOOKUP("ABC",A1:A20,1,FALSE)';
    Book.Recalculate;
    Writeln(VarToStr(Sheet.Cells[1, 3].Value));                // abc

    // Én nøglekolonne, 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 bruger en stabil merge sort, så ab, AB og Ab, som sammenlignes lige, beholder den relative rækkefølge, de havde før sorteringen. Tomme celler går til sidst i begge retninger, som i Excel

Hvordan matcher jeg Excels sorteringsorden i min egen Delphi-kode?

For at matche Excels tekstorden i din egen Delphi-kode, kald CompareStringW med LOCALE_USER_DEFAULT og NORM_IGNORECASE, og tilføj ikke SORT_STRINGSORT eller NORM_IGNOREWIDTH. Returværdien er ikke et signeret sammenligningsresultat: API'et returnerer CSTR_LESS_THAN (1), CSTR_EQUAL (2) eller CSTR_GREATER_THAN (3), og 0, når kaldet fejler. Subtrahér 2 for at nå den sædvanlige negativ/nul/positiv-konvention, og test for 0 først, fordi en fejl, der forveksles med et resultat, bliver til -2, et lydløst "mindre end"

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

// Excels tekstorden: word sort i brugerlokalet, uden versalfølsomhed
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 fejl, ikke et sammenligningsresultat
  Result := R - CSTR_EQUAL;    // 1/2/3 bliver til -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 (lige, vilkårlig rækkefølge), a-b, -ab, abc
end;

TArray.Sort er ikke stabil, så nøgler, der sammenlignes lige, som ab og AB, kan komme ud i vilkårlig rækkefølge; betyder de lige nøglers oprindelige rækkefølge noget, så sorter et indeksarray med originalpositionen som sekundær nøgle. Det omvendte tilfælde dukker også op: nogle gange må en kolonne ikke følge Excels orden, for eksempel varenumre, hvor X-100 og X100 er adskillelige koder og skal sorteres efter kodepunkt. TXLSXWorksheet.SortRange har en overload, der tager en TXLSSortCompareEvent, en metode med signaturen function(const Left, Right: Variant): Integer of object, og bruger den i stedet for den indbyggede sammenligning

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 tilpasset comparer modtager også tomme celler (som Null): placer dem selv
  if VarIsNull(Left) or VarIsNull(Right) then
    Exit(Ord(VarIsNull(Left)) - Ord(VarIsNull(Right)));
  Result := CompareStr(VarToStr(Left), VarToStr(Right));   // ordinal, versalfølsom
end;

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

Leveres en tilpasset comparer, springer HotXLS sin egen håndtering af tomme celler over og sender de rå nøgleværdier videre, så comparer'en selv må klare Null. For en faldende nøgle negater HotXLS, hvad end comparer'en returnerer, hvilket også flytter tomme celler til toppen, medmindre comparer'en tager højde for det. Husk, at en kolonne, der er sorteret sådan, ikke længere står i den orden, Excels approximative VLOOKUP eller en binær-søgning-XLOOKUP forventer; faldgruberne ved de tilstande på data sorteret i en anden orden er dækket i guiden til XLOOKUP- og XMATCH-binær søgning

Hvorfor kan den samme workbook sortere forskelligt på en anden maskine?

Den samme workbook kan sortere forskelligt på en anden maskine, fordi Excels tekstorden afhænger af Windows-brugerlokalet, og HotXLS følger bevidst den afhængighed. Word sort er sprogafhængig: den svenske collation placerer for eksempel ä efter z, hvor engelsk og tysk holder den ved a. Excel arver den fra det locale, det kører under, så en workbook, en kollega i Stockholm genberegner, kan returnere et andet COUNTIF(...,">y") end samme fil på en desktop i Chicago. HotXLS sender LOCALE_USER_DEFAULT, så dets resultater er lig med Excels på samme maskine; et fastlåst locale ville få HotXLS til at tage fejl af Excel på hver maskine med en anden indstilling

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

• Det locale, der tæller, er det for den konto, processen kører under. En Windows-service eller en IIS-applikationspool kan bruge et andet regionalt format end udviklerens desktop, så resultater observeret i IDE'en er ikke automatisk dem, produktionen beregner
• Cachede formelresultater skrevet ind i filen afspejler genereringsmaskinens locale. Excel genberegner med sit eget locale, så en værdi kan ændre sig, når filen åbnes et andet sted og genberegnes; det er Excels opførsel, ikke et HotXLS-artefakt
• Locales er uenige mest om accentbårne bogstaver, om bogstavkombinationer, nogle sprog behandler som ét bogstav, og om ikke-latinske skrifter, så testdata begrænset til almindelige engelske ord afslører ikke problemet

Platformgrænsen er enkel. HotXLS er et Windows-bibliotek, bygget til Win32 og Win64 med Delphi og C++Builder og til win32-/win64-mål med Lazarus og Free Pascal, og alle disse builds kalder den samme CompareStringW. Der findes ingen separat ikke-Windows-collation-vej. Den eneste fallback er et fejlet API-kald: returnerer CompareStringW 0, sammenligner XlsCompareText de versalede strenge efter kodeenhed frem for at kaste en exception midt i en genberegning, hvilket holder beregningen kørende, men ikke længere garanterer Excels orden

Hurtig reference: Tekstsammenligning i Excel-stil i HotXLS

• Regel: word sort i brugerlokalet med NORM_IGNORECASE, ingen SORT_STRINGSORT, ingen NORM_IGNOREWIDTH, i HotXLS siden v2.384.67
• - og ' udligner kun lige: ="a-b">"ab" er TRUE, og ="a-b"="ab" er FALSE
• Anden tegnsætning sorteres før cifre, cifre før bogstaver: ="a~b"<"ab" og ="a0"<"ab" er TRUE
• Store/små bogstaver tæller aldrig: ="ABC"="abc" er TRUE, og VLOOKUP("ABC",...) finder abc
• Dækkede veje: sammenligningsoperatorer, arraysammenligninger, > / <-kriterier, VLOOKUP / HLOOKUP, dynamic-array-orden, SortRange i begge motorer
• Ikke dækket af denne regel: blandede typer (number < text < boolean) og wildcard-kriterier, som har deres egne regler
• I Delphi-kode: CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), tjek for 0, subtrahér CSTR_EQUAL; undgå CompareText, CompareStr og TComparer<string>.Default, når resultatet skal stemme med Excel
• Resultaterne afhænger af kontots locale, der kører koden, i Excel lige så vel som i HotXLS

Almindelige ord sorteres ens under enhver regel, så kun koder med bindestreg, tegnsætning og accentbårne navne afslører en forkert collation. HotXLS giver nu Excels svar på alle dem i både XLS- og XLSX-motoren. Detaljer om licensering, understøttede Delphi- og C++Builder-versioner og trial-download står på siden om HotXLS Delphi Excel-komponenten