HotXLS-tekstvergelijking: Excels woordsortering in Delphi

De HotXLS Delphi Component vergelijkt twee tekstwaarden sinds v2.384.67 zoals Excel 16 dat doet: hoofdletterongevoelig, in de woordsortering van de Windows-gebruikerslocale, wat precies is wat CompareStringW teruggeeft met de vlag NORM_IGNORECASE. Koppeltekens en apostrofes worden in de eerste passe overgeslagen en breken alleen gelijkspel, dus ="a-b">"ab" is TRUE, terwijl andere leestekens vóór cijfers en letters sorteren, dus ="a~b"<"ab" is ook TRUE. Dezelfde volgorde drijft nu de vergelijkingsoperators, de criteria > / <, het sorteren van bereiken en VLOOKUP

Niemand dient een bug in met de titel collation mismatch. De meldingen zeggen dat COUNTIF(A:A,">M") op de server twee rijen meer telt dan in Excel, dat een prijslijst die de rapportservice sorteert X-100 ergens neerzet waar Excel het niet zou doen, of dat VLOOKUP("ABC",...) #N/A teruggeeft terwijl de kolom alleraardigst abc bevat. Alle drie komen uit dezelfde vraag: als beide operands tekst zijn, welke is dan kleiner? Excel heeft een precies antwoord, het is niet het antwoord dat de meeste Delphi-code geeft, en vóór v2.384.67 gaf HotXLS drie verschillende antwoorden afhankelijk van welk codepad vroeg

Welke regel gebruikt Excel om twee tekststrings te vergelijken?

Excel vergelijkt tekst met de woordsortering van de gebruikerslocale, hoofdletterongevoelig. Woordsortering is de default collation van de NLS-vergelijkingsfuncties van Windows: letters vergelijken op hun linguïstische volgorde in plaats van op hun code points, letters met accenten staan naast hun basisletter, en twee tekens krijgen een speciale behandeling. Het koppelteken - en de apostrof ' worden in de eerste passe genegeerd, dus co-op en coop belanden naast elkaar, en pas als de rest van de strings gelijk eindigt beslist hun aanwezigheid over de volgorde. Elk ander leesteken telt mee en sorteert vóór cijfers, en cijfers sorteren vóór letters

De tabel laat zien wat dat in de praktijk betekent, naast de twee vergelijkingen waar een Delphi-ontwikkelaar het vaakst naar grijpt. De Excel-kolom bevat de uitspraken die Excel 16 gaf voor IF(A<B,...), en die HotXLS sinds v2.384.67 reproduceert

A vs B	Excel 16 / HotXLS	CompareStr (ordinaal)	CompareText
"a-b" vs "ab"	groter	kleiner	kleiner
"a'b" vs "ab"	groter	kleiner	kleiner
"a~b" vs "ab"	kleiner	groter	groter
"a_b" vs "ab"	kleiner	kleiner	groter
"ab" vs "AB"	gelijk	groter	gelijk
"é" vs "f"	kleiner	groter	groter
"Z" vs "f"	groter	kleiner	groter

Twee gevolgen zijn makkelijk te missen. Ten eerste betekent de gelijkspel-brekende rol van het koppelteken dat ="a-b"="ab" FALSE is: de strings zijn buurtgenoten in de sortering, maar niet gelijk. Ten tweede negeert gelijkheid hoofdletters volledig, dus ab, AB en Ab zijn voor elke vergelijking dezelfde sleutel. Twintig testwoorden sorteren met de Range.Sort van Excel geeft 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; binnen de groep ab beslist de positie van het genegeerde teken

Hoe is de tekstvolgorde van Excel vastgesteld?

De tekstvolgorde van Excel is vastgesteld door meten, niet door documentatie, want de documentatie van Excel noemt de collation niet. De test genereerde 4.000 willekeurige stringparen uit ASCII-leestekens, cijfers, beide letterkasten, spaties, é, ß, ä, Chinese tekens, full-width-vormen en de vaste spatie, met lengtes van 0 tot 4 en de helft van de paren als near-misses van elkaar. Excel 16 evalueerde IF(A<B,-1,IF(A=B,0,1)) voor elk paar, en de uitspraken werden vergeleken met de Windows-vergelijkings-API onder verschillende vlaggensets

• NORM_IGNORECASE alleen (default woordsortering, gebruikerslocale): geen echte mismatch. De enige 7 verschillen waren cellen waarvan de volledige inhoud ' was, die Excel opneemt als het prefixteken voor tekst, dus dat waren sampling-artefacts in plaats van collation-verschillen
• NORM_IGNORECASE met SORT_STRINGSORT: 41 mismatches. String sort behandelt het koppelteken en de apostrof als gewone symbolen, en dat is precies het gedrag dat Excel niet heeft
• NORM_IGNOREWIDTH erbij: op een andere manier fout, want hij laat de full-width- en half-width-vormen van dezelfde letter als gelijk vergelijken, en Excel houdt ze uit elkaar

Een tweede, met de hand samengestelde controle vergeleek alle 190 paren uit 20 lastige woorden met het resultaat van de Range.Sort van Excel op dezelfde kolom. Beiden bevestigden de kale NORM_IGNORECASE-woordsortering, en die 190 uitspraken plus de gesorteerde volgorde maken nu deel uit van de HotXLS-regressiesuite, gedraaid door zowel de klassieke TXLSWorkbook-engine als de XLSX-native TXLSXWorkbook-engine

Waarom zitten CompareText en ordinale vergelijking ernaast?

CompareText en ordinale vergelijking zitten naast de volgorde van Excel omdat ze UTF-16 code units vergelijken, en code-point-volgorde zet leestekens op willekeurige plekken ten opzichte van letters. Het koppelteken is U+002D en de apostrof U+0027, beide onder elke letter, dus een ordinale vergelijking noemt "a-b" kleiner dan "ab" in plaats van het koppelteken als gelijkspelbreker te behandelen. De tilde U+007E zit boven elke letter, dus "a~b" komt er groter uit, het omgekeerde van Excel. CompareText in de Delphi-RTL vouwt alleen a..z naar hoofdletters en vergelijkt dan code units, wat een tweede vervorming toevoegt: het laag streepje U+005F ligt tussen de hoofd- en de kleine letters, dus het vouwen naar hoofdletters schuift "a_b" van onder "ab" naar erboven. Geen van beide functies weet dat é tussen e en f hoort

De gebruikelijke Delphi-tools vallen aan beide kanten van de lijn:

• CompareStr, de string-operator < en TComparer<string>.Default (die CompareStr aanroept) zijn ordinaal en hoofdlettergevoelig, dus TArray.Sort<string> zonder comparer zet Z vóór f
• CompareText en SameText zijn ordinaal na alléén-ASCII-hoofdlettervouwing
• AnsiCompareText en WideCompareText in de Delphi-RTL op Windows roepen CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) aan, dezelfde aanroep die bij Excel past. Een gesorteerde TStringList met zijn defaults (UseLocale True, CaseSensitive False) loopt via AnsiCompareText en is het dus ook met Excel eens
• Op POSIX-doelen leidt de Delphi-RTL AnsiCompareText via een ICU-collator, een ander algoritme met andere leestekenregels, en de AnsiCompareText van Free Pascal op Windows roept CompareStringA aan na conversie naar de ANSI-codepagina, waarbij elk teken verloren gaat dat die pagina niet kan representeren

De locale-bewuste RTL-functies zitten op Windows dus om implementatieredennen goed, niet contractueel, en code die de volgorde van Excel nodig heeft doet er verstandig aan de API-aanroep expliciet te maken. HotXLS had intern dezelfde mix. De vergelijkingsoperators zetten beide strings om naar hoofdletters en vergeleken code points, de > / <-takken van criteriumfuncties gebruikten de hoofdlettergevoelige Variantvergelijking van Delphi, en VLOOKUP / HLOOKUP matchten tekst eveneens met die hoofdlettergevoelige Variantvergelijking, wat de reden is dat VLOOKUP("ABC",A1:A20,1,FALSE) abc niet kon vinden. De bereiksortering gebruikte al WideCompareText. Drie paden, drie volgordes

Wat is er veranderd in HotXLS v2.384.67?

Sinds v2.384.67 lopen de tekst-tegen-tekst-vergelijkingen in de berekenings- en sorteerpaden van HotXLS door één functie, XlsCompareText in lxStandard.pas, die CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) aanroept en er CSTR_EQUAL van aftrekt. De aanroepers zijn de zes vergelijkingsoperators, elementsgewijze vergelijkingen in arrayformules, de takken >, <, >= en <= van criteria in COUNTIF-stijl en de databasefuncties, VLOOKUP en HLOOKUP (exact en benaderend), de ordeningshelpers achter de dynamic-arrayfuncties en XLOOKUP / XMATCH, en de bereiksortering van beide engines. De bereiksortering door dezelfde functie leiden garandeert dat sorteervolgorde en vergelijkingsvolgorde niet opnieuw uit elkaar kunnen drijven, wat ertoe doet omdat benaderende VLOOKUP op tekst alleen zin heeft wanneer de kolom was gesorteerd in de volgorde waarin de lookup vergelijkt

uses
  System.Variants, lxHandleX;

var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Sheets.Add('Data');  // Calculate evalueert tegen het actieve werkblad
    Writeln(VarToStr(Book.Calculate('="a-b">"ab"')));   // True: het koppelteken breekt alleen gelijkspel
    Writeln(VarToStr(Book.Calculate('="a-b"="ab"')));   // False: gelijkspel gebroken, niet gelijk
    Writeln(VarToStr(Book.Calculate('="a~b"<"ab"')));   // True: leestekens eerst
    Writeln(VarToStr(Book.Calculate('="ABC"="abc"')));  // True: hoofdletters genegeerd
  finally
    Book.Free;
  end;
end;

Vergelijkingen over typen heen zijn een aparte regel en zijn niet veranderd: elk getal staat onder elke tekstwaarde en elke tekstwaarde onder elke boolean, zoals beschreven in het artikel over vergelijkingsketens, lege operands en SUMIF. De woordsortering geldt pas zodra beide operands tekst zijn. Wildcard-matching is ook apart: een criterium als "a*" of "=ab" is een patroon- of gelijkheidstest, behandeld in de gids over Excel-wildcards in COUNTIF, MATCH en DSUM, en de collation die hier wordt besproken beslist alleen over de ordeningsoperators

Het volgende voorbeeld laadt de 20 testwoorden in een kolom, sorteert die met TXLSXWorksheet.SortRange en controleert een criteriumtelling en een lookup. De tellingen zijn degene die Excel 16 voor dezelfde kolom teruggaf

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 op dezelfde kolom: 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")')));

    // Was #N/A vóór v2.384.67: de lookup vergeleek hoofdlettergevoelig
    Sheet.Cells[1, 3].Formula := '=VLOOKUP("ABC",A1:A20,1,FALSE)';
    Book.Recalculate;
    Writeln(VarToStr(Sheet.Cells[1, 3].Value));                // abc

    // Eén sleutelkolom, oplopend: 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 gebruikt een stabiele merge sort, dus ab, AB en Ab, die als gelijk vergelijken, houden de relatieve volgorde die ze vóór de sortering hadden. Lege cellen gaan in beide richtingen naar het einde, zoals in Excel

Hoe match ik de sorteervolgorde van Excel in mijn eigen Delphi-code?

Om de tekstvolgorde van Excel in uw eigen Delphi-code te matchen, roept u CompareStringW aan met LOCALE_USER_DEFAULT en NORM_IGNORECASE, en voegt u geen SORT_STRINGSORT of NORM_IGNOREWIDTH toe. De retourwaarde is geen getekend vergelijkingsresultaat: de API geeft CSTR_LESS_THAN (1), CSTR_EQUAL (2) of CSTR_GREATER_THAN (3) terug, en 0 als de aanroep faalt. Trek 2 af voor de gebruikelijke conventie negatief / nul / positief, en test eerst op 0, want een falen dat voor een resultaat wordt aangezien wordt -2, een stilletjes kleiner dan

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

// De tekstvolgorde van Excel: woordsortering van de gebruikerslocale, hoofdletterongevoelig
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 is een falen, geen vergelijkingsresultaat
  Result := R - CSTR_EQUAL;    // 1/2/3 worden -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 (gelijk, willekeurige volgorde), a-b, -ab, abc
end;

TArray.Sort is niet stabiel, dus sleutels die als gelijk vergelijken, zoals ab en AB, kunnen in willekeurige volgorde uit de bus komen; doet de originele volgorde van gelijke sleutels ertoe, sorteer dan een indexarray met de oorspronkelijke positie als secundaire sleutel. Het omgekeerde geval speelt ook: soms mag een kolom niet de volgorde van Excel volgen, bijvoorbeeld onderdeelnummers waar X-100 en X100 verschillende codes zijn en op code point moeten sorteren. TXLSXWorksheet.SortRange heeft een overload die een TXLSSortCompareEvent neemt, een methode met de signatuur function(const Left, Right: Variant): Integer of object, en gebruikt die in plaats van de ingebouwde vergelijking

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
  // Een eigen comparer krijgt ook lege cellen (als Null): plaats ze zelf
  if VarIsNull(Left) or VarIsNull(Right) then
    Exit(Ord(VarIsNull(Left)) - Ord(VarIsNull(Right)));
  Result := CompareStr(VarToStr(Left), VarToStr(Right));   // ordinaal, hoofdlettergevoelig
end;

var
  Sheet: TXLSXWorksheet;   // een gevuld werkblad, rijen 2..501, kolommen A..D
  Order: TPartNumberOrder;
begin
  // ...
  Order := TPartNumberOrder.Create;
  try
    // gesleuteld op kolom A, oplopend
    Sheet.SortRange(2, 1, 501, 4, [1], [False], xlsSortByRows,
      xlsSortExcelLike, Order.Compare);
  finally
    Order.Free;
  end;
end;

Wanneer een eigen comparer wordt meegegeven, slaat HotXLS zijn eigen afhandeling van lege cellen over en geeft de rauwe sleutelwaarden door, dus de comparer moet zelf met Null omgaan. Voor een aflopende sleutel maakt HotXLS negatief wat de comparer teruggeeft, wat lege cellen ook naar de top schuift tenzij de comparer daar rekening mee houdt. Bedenk daarbij dat een kolom die zo is gesorteerd niet meer in de volgorde staat die de benaderende VLOOKUP van Excel of een binary-search XLOOKUP verwacht; de valkuilen van die modi op data die in een andere volgorde is gesorteerd staan bij de gids over de binary-search-modi van XLOOKUP en XMATCH

Waarom kan hetzelfde workbook op een andere machine anders sorteren?

Hetzelfde workbook kan op een andere machine anders sorteren omdat de tekstvolgorde van Excel afhangt van de Windows-gebruikerslocale, en HotXLS volgt die afhankelijkheid met opzet. Woordsortering is taalspecifiek: de Zweedse collation zet ä bijvoorbeeld na z, waar het Engels en het Duits haar naast a houden. Excel erft dat van de locale waaronder hij draait, dus een workbook dat een collega in Stockholm herberekent kan een andere COUNTIF(...,">y") teruggeven dan hetzelfde bestand op een desktop in Chicago. HotXLS geeft LOCALE_USER_DEFAULT door zodat zijn resultaten op dezelfde machine gelijk zijn aan die van Excel; elke vaste locale zou HotXLS op elke machine met een andere instelling laten afwijken van Excel

Voor generatie aan de serverkant volgen daaruit drie praktische gevolgen:

• De locale die telt is die van het account waaronder het proces draait. Een Windows-service of IIS-application pool gebruikt misschien een andere regionale instelling dan de desktop van de ontwikkelaar, dus resultaten die in de IDE worden waargenomen zijn niet automatisch wat productie berekent
• Gecachte formuleresultaten die in het bestand worden weggeschreven weerspiegelen de locale van de genererende machine. Excel herberekent met zijn eigen locale, dus een waarde kan veranderen zodra het bestand elders wordt geopend en herberekend; dat is het gedrag van Excel, geen artefact van HotXLS
• Locales verschillen vooral over letters met accenten, over lettercombinaties die sommige talen als één letter behandelen en over niet-Latijnse schriften, dus testdata beperkt tot kale Engelse woorden brengen het probleem niet aan het licht

De platformgrens is simpel. HotXLS is een Windows-library, gebouwd voor Win32 en Win64 met Delphi en C++Builder en voor win32 / win64-doelen met Lazarus en Free Pascal, en al deze builds roepen dezelfde CompareStringW aan. Er is geen apart niet-Windows-pad voor collation. De enige fallback is voor een mislukte API-aanroep: geeft CompareStringW 0 terug, dan vergelijkt XlsCompareText de naar hoofdletters gevouwen strings op code unit in plaats van midden in een herberekening een exception te gooien, wat de berekening draaiende houdt maar de volgorde van Excel niet meer garandeert

Snelnaslag: tekstvergelijking volgens Excel in HotXLS

• Regel: woordsortering van de gebruikerslocale met NORM_IGNORECASE, geen SORT_STRINGSORT, geen NORM_IGNOREWIDTH, in HotXLS sinds v2.384.67
• - en ' breken alleen gelijkspel: ="a-b">"ab" is TRUE en ="a-b"="ab" is FALSE
• Andere leestekens sorteren vóór cijfers, cijfers vóór letters: ="a~b"<"ab" en ="a0"<"ab" zijn TRUE
• Hoofdletters spelen nooit een rol: ="ABC"="abc" is TRUE en VLOOKUP("ABC",...) vindt abc
• Gedekte paden: vergelijkingsoperators, arrayvergelijkingen, criteria > / <, VLOOKUP / HLOOKUP, ordening van dynamic arrays, SortRange in beide engines
• Niet gedekt door deze regel: gemengde typen (getal < tekst < boolean) en wildcard-criteria, die hun eigen regels hebben
• In Delphi-code: CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), testen op 0, er CSTR_EQUAL van aftrekken; vermijd CompareText, CompareStr en TComparer<string>.Default zodra het resultaat met Excel moet overeenkomen
• Resultaten hangen af van de locale van het account dat de code draait, in Excel net zo goed als in HotXLS

Gewone woorden sorteren onder elke regel hetzelfde, dus alleen codes met koppeltekens, leestekens en namen met accenten leggen een verkeerde collation bloot. HotXLS geeft nu op alle punten het antwoord van Excel, in zowel de XLS- als de XLSX-engine. Details over licenties, ondersteunde Delphi- en C++Builder-versies en de proefdownload staan op de HotXLS Delphi Excel component-pagina