Technisch artikel

HotXLS-tekstvergelijking: Excels woordsortering in Delphi

In Excel levert ="a-b">"ab" TRUE op, en ="a~b"<"ab" levert ook TRUE op. Geef dezelfde paren aan CompareText van Delphi en beide antwoorden draaien om, want het koppelteken zit in code-point-volgorde onder de letters en de tilde erboven. Excel vergelijkt tekst met de hoofdletterongevoelige "woordsortering" van de Windows-gebruikerslocale, waarin koppeltekens en apostrofes alleen gelijkspel breken en elk ander leesteken vóór cijfers en letters sorteert

De symptommen hebben zelden het woord collation in de titel. Een COUNTIF(A:A,">M") die op de server wordt berekend telt twee rijen meer dan dezelfde formule in Excel. Een prijslijst die een rapportservice sorteert zet X-100 ergens neer waar Excel het niet zou doen. VLOOKUP("ABC",...) geeft #N/A terug terwijl de kolom heus abc bevat. Alle drie komen op dezelfde vraag uit: als beide operands tekst zijn, welke is dan kleiner? Excel heeft een precies antwoord, en dat is zelden wat Delphi-code standaard geeft

Welke regel gebruikt Excel om twee tekststrings te vergelijken?

Excel vergelijkt twee tekstwaarden met de woordsortering van de huidige Windows-gebruikerslocale, hoofdletterongevoelig. Woordsortering is de default collation van de NLS-vergelijkingsfuncties van Windows: letters vergelijken op hun linguïstische volgorde in plaats van op code points, dus é landt tussen e en f, en hoofd- en kleine letters vallen samen. Twee tekens krijgen een speciale behandeling. Het koppelteken - en de apostrof ' worden in de eerste passe overgeslagen, waardoor co-op direct naast coop belandt; pas als de overige tekens gelijk eindigen, beslist hun aanwezigheid, en hun positie, over de volgorde. Elk ander leesteken telt vol mee en sorteert vóór de cijfers, en de cijfers sorteren vóór de letters

De tabel zet de uitspraak van Excel voor IF(A<B,...) tegen de twee vergelijkingen waar een Delphi-ontwikkelaar het vaakst als eerste naar grijpt

A vs BExcel / HotXLSCompareStr (ordinaal)CompareText
"a-b" vs "ab"groterkleinerkleiner
"a'b" vs "ab"groterkleinerkleiner
"a~b" vs "ab"kleinergrotergroter
"a_b" vs "ab"kleinerkleinergroter
"ab" vs "AB"gelijkgrotergelijk
"é" vs "f"kleinergrotergroter
"Z" vs "f"groterkleinergroter

Twee gevolgen worden vaak gemist. Omdat het koppelteken alleen gelijkspel breekt, is ="a-b"="ab" FALSE: de twee strings zijn in een sortering directe buren en toch niet gelijk. En omdat hoofdletters volledig wegvallen, zijn ab, AB en Ab voor elke vergelijkingsoperator één sleutel. Onder een Engelse gebruikerslocale sorteert een kolom met 20 lastige woorden als 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 overgeslagen teken: koppelteken achteraan eerst, koppelteken vooraan laatst

HotXLS-diagram van de woordsortering dat alle 20 testwoorden rangschikt, van a b, a.b, a_b en a~b via a0 en a1b, dan de ab-groep met AB en Ab, koppelteken- en apostrofvarianten zoals a-b en a'b, tot aan abc, b, e, e met accent, f en Z, met leestekens vóór cijfers vóór letters en hoofdletters genegeerd
Leestekens en spatie sorteren vóór cijfers en cijfers vóór letters, hoofdletters vallen weg, en het koppelteken met de apostrof breken alleen gelijkspel; daarom landt a-b naast ab maar vergelijkt toch als groter

Zelf de collation tegen Excel toetsen

De documentatie van Excel noemt de gebruikte collation nergens bij naam, dus de betrouwbare manier om dat op uw eigen machine te bevestigen is Excel een batch stringparen laten oordelen en die uitspraken vergelijken met CompareStringW onder verschillende vlaggensets. Zet paren in twee kolommen, laat Excel per rij =IF(A1<B1,-1,IF(A1=B1,0,1)) evalueren en draai dezelfde paren door de API. Paren die de moeite waard zijn mengen ASCII-leestekens, cijfers, beide letterkasten, spaties, letters met accenten zoals é, ß en ä, CJK-tekens, full-width-vormen en de vaste spatie, met genoeg near-misses die op één teken verschillen. Willekeurige woorden uit een woordenlijst zijn een slecht sample, want kale letters sorteren onder elke regel in dit artikel identiek

De vlaggensets geven duidelijk verschillende resultaten:

  • Alleen NORM_IGNORECASE, met LOCALE_USER_DEFAULT, is de kale woordsortering en de regel die Excel volgt
  • Voeg SORT_STRINGSORT toe en u schakelt over naar string sort, waarin het koppelteken en de apostrof gewone symbolen met vol gewicht zijn, zodat elk paar in de stijl van a-b / ab de andere kant op uitvalt
  • Voeg NORM_IGNOREWIDTH toe en een full-width-letter wordt gelijk aan zijn half-width-vorm, terwijl Excel die twee uit elkaar houdt

Eén valkuil zit in de testopzet zelf. Excel ziet een apostrof die u vooraan in een cel typt als het prefix "opslaan als tekst", dus een cel waarvan de hele invoer ' is eindigt leeg, en elk paar dat zo wordt gebouwd meldt een mismatch die niets met collation te maken heeft. Bouw zulke paren met stringliterals in de formule (="'"), of sla ze over

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 strooit leestekens rond het alfabet. Het koppelteken is U+002D en de apostrof U+0027, beide onder elke letter, dus een ordinale vergelijking noemt "a-b" kleiner dan "ab" terwijl Excel het koppelteken als gelijkspelbreker behandelt. De tilde is U+007E, boven elke letter, waardoor "a~b" groter uitvalt, het omgekeerde van Excel. CompareText voegt daar nog een eigen vervorming aan toe: de functie vouwt alleen a..z naar hoofdletters en vergelijkt dan code units, en omdat het laag streepje (U+005F) tussen de hoofdletter- en kleinletterranges ligt, schuift dat vouwen "a_b" van onder "ab" naar erboven. Geen van beide functies heeft enig besef dat é tussen e en f hoort

HotXLS-vergelijkingsdiagram dat de code-point-volgorde tegenover de woordsortering van Excel zet: ordinale vergelijking plaatst de apostrof, het koppelteken en het laag streepje op 0x27, 0x2D en 0x5F rond de letters, waardoor a-b tegenover ab als kleiner uitvalt, terwijl woordsortering leestekens vóór cijfers en letters duwt en alleen het koppelteken en de apostrof als gelijkspelbrekers behandelt
Code points strooien leestekens rond de letters, dus ordinale en ASCII-vouwende vergelijkingen draaien de uitspraken om; woordsortering zet leestekens vóór de cijfers en degradeert het koppelteken en de apostrof tot gelijkspelbrekers

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

  • CompareStr, de string-operator < en TComparer<string>.Default 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. De AnsiCompareText van Free Pascal op Windows converteert naar de ANSI-codepagina en roept CompareStringA aan, waarbij elk teken verloren gaat dat die codepagina niet kan representeren

De locale-bewuste RTL-functies zitten op Windows dus bij toeval goed, puur door hoe ze vandaag geïmplementeerd zijn. Code die met Excel moet overeenkomen, maakt de API-aanroep expliciet en zegt dat er ook bij. Het lastigere probleem verschijnt in elke formule-engine waar elk codepad grijpt wat toevallig het dichtstbij is: omzetten naar hoofdletters en code points vergelijken in de operatoren = en <, een hoofdlettergevoelige Variantvergelijking in de tak > / < van COUNTIF-criteria, weer een andere binnen VLOOKUP (precies de reden waarom VLOOKUP("ABC",A1:A20,1,FALSE) abc niet vindt), en een locale-aanroep in de bereiksortering. Elk pad is op zichzelf verdedigbaar. Samen leveren ze drie verschillende volgordes op over dezelfde kolom

Eén vergelijking achter formules, lookups en sorteren in HotXLS

HotXLS stuurt elke tekst-tegen-tekst-vergelijking in zijn berekenings- en sorteercode door één functie, XlsCompareText in lxStandard.pas, die CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) aanroept en er CSTR_EQUAL van aftrekt. De zes vergelijkingsoperators gebruiken hem, net als elementsgewijze vergelijkingen in arrayformules, de takken >, <, >= en <= van criteria in COUNTIF-stijl en de databasefuncties, VLOOKUP en HLOOKUP in exacte en benaderende modus, de ordening achter de dynamic-arrayfuncties en XLOOKUP / XMATCH, en SortRange op zowel TXLSWorksheet als TXLSXWorksheet. De bereiksortering op dezelfde functie zetten was het beslissende ontwerppunt. Benaderende VLOOKUP op tekst geeft alleen zinvolle antwoorden als de kolom gesorteerd was in de volgorde waarin de lookup vergelijkt, en doordat één functie wordt gedeeld kunnen die twee volgordes niet meer uit elkaar drijven

HotXLS-routeringsdiagram dat elk pad voor tekstvergelijking toont, van de zes vergelijkingsoperators en criteria in COUNTIF-stijl via VLOOKUP, HLOOKUP, XLOOKUP en de bereiksortering van beide engines, convergerend op XlsCompareText, dat CompareStringW aanroept met LOCALE_USER_DEFAULT en NORM_IGNORECASE en 1, 2, 3 afbeeldt op -1, 0, 1
Operators, criteria, lookups en sortering delen één functie, dus de volgorde die Excel ziet en de volgorde waarmee HotXLS sorteert kunnen niet meer uiteen drijven; de API geeft 1, 2 of 3 terug, en nul betekent falen, niet kleiner dan

De snelste manier om de regel vanuit Delphi te zien is TXLSXWorkbook.Calculate, dat een formulestring tegen het actieve werkblad evalueert en het resultaat als Variant teruggeeft

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;

Woordsortering gaat pas gelden zodra beide operands tekst zijn. Gemengde typen volgen een vaste rangschikking waarin elk getal onder elke tekstwaarde staat en elke tekstwaarde onder elke boolean, zoals beschreven in het artikel over vergelijkingsketens, lege operands en SUMIF. Wildcard-criteria zoals "a*" of "=ab" zijn patroon- en gelijkheidstesten met eigen regels, behandeld in de gids over Excel-wildcards in COUNTIF, MATCH en DSUM; de collation die hier wordt besproken bepaalt de ordeningsoperators

Het volgende voorbeeld laadt de 20 woorden in kolom A van een werkblad, controleert drie criteriumtellingen en een lookup, en sorteert daarna de kolom met TXLSXWorksheet.SortRange. De verwachte tellingen volgen direct uit de eerder getoonde sorteervolgorde: elf woorden sorteren na de groep ab, elf sorteren vóór a-b, en de drie leden van de groep ab komen bij die elf voor >=AB

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]);

    // Verwacht uit de woordsorteringsvolgorde: 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")')));

    // Vindt abc: de lookup vergelijkt hoofdletterongevoelig, zoals Excel dat doet
    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;

SortRange draait 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 naar de onderkant, of de sleutel nu oplopend of aflopend is, en dat is precies waar Excel ze zet

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

Om de tekstvolgorde van Excel buiten een workbook te reproduceren, roept u CompareStringW aan met LOCALE_USER_DEFAULT en NORM_IGNORECASE, en laat u SORT_STRINGSORT en NORM_IGNOREWIDTH weg. Let op de retourwaarde. De API geeft geen getekend resultaat terug; hij geeft CSTR_LESS_THAN (1), CSTR_EQUAL (2) of CSTR_GREATER_THAN (3) terug, en 0 als de aanroep faalt. Twee aftrekken geeft de gebruikelijke conventie negatief / nul / positief, maar pas nadat het geval 0 is afgehandeld, anders wordt een mislukte aanroep -2 en leest als "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;

Staat HotXLS al in de uses-clausule, dan is lxStandard.XlsCompareText publiek en geeft dezelfde volgorde terug. De twee verschillen alleen bij een mislukte API-aanroep: de functie hierboven gooit een exception, terwijl XlsCompareText terugvalt op het vergelijken van de naar hoofdletters gevouwen strings op code unit, zodat een herberekening nooit halverwege wordt afgebroken. Voor een losse sortering is een exception de betere keuze, want een stilletjes verkeerde volgorde is lastiger terug te vinden

TArray.Sort is niet stabiel, dus gelijke sleutels zoals ab en AB kunnen in willekeurige volgorde uitkomen. Doet de oorspronkelijke volgorde van gelijke sleutels ertoe, sorteer dan een indexarray en gebruik de oorspronkelijke positie als secundaire sleutel

Soms moet een kolom opzettelijk níét de volgorde van Excel volgen. Onderdeelnummers zijn het klassieke geval: X-100 en X100 zijn verschillende codes en moeten op code point sorteren. SortRange heeft een overload die een TXLSSortCompareEvent neemt, gedeclareerd als 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;

Met een eigen comparer geeft SortRange de rauwe sleutelwaarden door en laat de plaatsing van lege cellen aan u over, vandaar dat de comparer hierboven eerst Null afhandelt. Voor een aflopende sleutel maakt de sortering het retour van de comparer negatief, dus in die richting schuiven lege cellen naar de top tenzij de comparer daar rekening mee houdt. Een kolom die zo gesorteerd is, staat niet meer in de volgorde die benaderende VLOOKUP of een binary-search XLOOKUP verwacht; wat er in die modi misgaat op data die in een andere volgorde is gesorteerd, staat in de gids over de binary-search-modi van XLOOKUP en XMATCH

Waarom kan hetzelfde workbook op een andere machine anders sorteren?

Een 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, terwijl Engels en Duits haar naast a houden, dus een bestand 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 antwoorden op dezelfde machine gelijk zijn aan die van Excel. Een vaste locale vastpinnen oogt netter en zou HotXLS op elke anders geconfigureerde machine 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; Excel doet met zijn eigen bestanden precies hetzelfde
  • Locales verschillen vooral op letters met accenten, op letterparen die sommige talen als één letter behandelen en op niet-Latijnse schriften. Testdata van kale Engelse woorden leggen het verschil nooit bloot, dus zet een paar namen als Ångström en Öberg in het sample

Het pad dat Excel volgt, is het Windows-pad: elke Win32- en Win64-build, of die nu met Delphi, C++Builder, of Lazarus en Free Pascal is gecompileerd, roept dezelfde CompareStringW aan. Richt een build zich op een niet-Windows-platform, dan gebruikt XlsCompareText de WideCompareText van de RTL, waarvan de volgorde uit de eigen collation-bibliotheek van dat platform komt

Gewone woorden sorteren onder elke regel hetzelfde, waardoor een verkeerde collation de test doorgaans overleeft totdat codes met koppeltekens, leestekens of klantnamen met accenten opduiken. HotXLS geeft op die invoer het antwoord van Excel, in zowel de XLS- als de XLSX-engine, van de vergelijkingsoperators tot aan SortRange. Ondersteunde Delphi- en C++Builder-versies, licenties en de proefdownload staan op de HotXLS Delphi Excel component-pagina