Comparaison de texte HotXLS : tri word sort d’Excel

HotXLS Delphi Component compare deux valeurs texte comme le fait Excel 16 depuis v2.384.67 : sans distinction de casse, dans l’ordre « word sort » de la locale utilisateur Windows, c’est ce que renvoie CompareStringW avec le drapeau NORM_IGNORECASE. Les tirets et les apostrophes sont sautés à la première passe et ne départagent que les égalités, donc ="a-b">"ab" vaut TRUE, tandis que les autres signes de ponctuation se trient avant les chiffres et les lettres, donc ="a~b"<"ab" vaut TRUE également. Ce même ordre pilote désormais les opérateurs de comparaison, les critères > / <, le tri de plages et VLOOKUP

Personne ne remonte un bug intitulé « divergence de collation ». Les rapports disent que COUNTIF(A:A,">M") compte deux lignes de plus sur le serveur que dans Excel, qu’une liste de prix triée par le service de reporting place X-100 là où Excel ne la mettrait pas, ou que VLOOKUP("ABC",...) renvoie #N/A alors que la colonne contient manifestement abc. Les trois viennent de la même question : quand les deux opérandes sont du texte, lequel est le plus petit ? Excel a une réponse précise, ce n’est pas celle que donne la plupart du code Delphi, et avant v2.384.67 HotXLS en donnait trois différentes selon le chemin de code qui demandait

Quelle règle Excel utilise-t-il pour comparer deux chaînes de texte ?

Excel compare le texte avec le word sort de la locale utilisateur, en ignorant la casse. Le word sort est la collation par défaut des fonctions de comparaison NLS de Windows : les lettres se comparent selon leur ordre linguistique plutôt que selon leurs points de code, les lettres accentuées siègent à côté de leur lettre de base, et deux caractères bénéficient d’un traitement spécial. Le tiret - et l’apostrophe ' sont ignorés à la première passe, donc co-op et coop se retrouvent côte à côte, et c’est seulement quand le reste des chaînes fait égalité que leur présence décide de l’ordre. Tout autre signe de ponctuation est significatif et se trie avant les chiffres, et les chiffres se trient avant les lettres

Le tableau montre ce que cela donne en pratique, à côté des deux comparaisons qu’un développeur Delphi est le plus susceptible d’attraper. La colonne Excel contient les verdicts qu’Excel 16 a rendus pour IF(A<B,...), que HotXLS reproduit depuis v2.384.67

A vs B	Excel 16 / HotXLS	CompareStr (ordinal)	CompareText
"a-b" vs "ab"	supérieur	inférieur	inférieur
"a'b" vs "ab"	supérieur	inférieur	inférieur
"a~b" vs "ab"	inférieur	supérieur	supérieur
"a_b" vs "ab"	inférieur	inférieur	supérieur
"ab" vs "AB"	égal	supérieur	égal
"é" vs "f"	inférieur	supérieur	supérieur
"Z" vs "f"	supérieur	inférieur	supérieur

Deux conséquences sont faciles à rater. D’abord, le rôle de départage du tiret fait que ="a-b"="ab" vaut FALSE : les chaînes sont de proches voisines dans le tri, et pourtant pas égales. Ensuite, l’égalité ignore complètement la casse, donc ab, AB et Ab sont la même clé pour toute comparaison. Trier les 20 mots de test avec le Range.Sort d’Excel donne 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 ; dans le groupe ab, c’est la position du caractère ignoré qui décide

Comment l’ordre textuel d’Excel a-t-il été cerné ?

L’ordre textuel d’Excel a été identifié par la mesure, pas par la documentation, car la documentation d’Excel ne nomme pas la collation. Le test a généré 4 000 paires de chaînes aléatoires à partir de la ponctuation ASCII, des chiffres, des deux casses, des espaces, de é, ß, ä, de caractères chinois, des formes à chasse pleine et de l’espace insécable, avec des longueurs de 0 à 4 et la moitié des paires construites comme de quasi coïncidences. Excel 16 a évalué IF(A<B,-1,IF(A=B,0,1)) pour chaque paire, et les verdicts ont été confrontés à l’API de comparaison Windows avec différents jeux de drapeaux

• NORM_IGNORECASE seul (word sort par défaut, locale utilisateur) : aucune vraie divergence. Les seules 7 différences étaient des cellules dont tout le contenu était ', qu’Excel consomme comme caractère de préfixe de texte, donc c’étaient des artefacts d’échantillonnage plutôt que des différences de collation
• NORM_IGNORECASE avec SORT_STRINGSORT : 41 divergences. Le string sort traite le tiret et l’apostrophe comme des symboles ordinaires, exactement le comportement qu’Excel n’a pas
• Avec NORM_IGNOREWIDTH en plus : faux d’une autre façon, car il fait comparer comme égales les formes à chasse pleine et à chasse demi d’une même lettre, et Excel les distingue

Une seconde vérification, à la main cette fois, a comparé les 190 paires tirées de 20 mots piégeux et le résultat du Range.Sort d’Excel sur la même colonne. Les deux ont donné raison au simple word sort NORM_IGNORECASE, et ces 190 verdicts plus l’ordre trié font désormais partie de la suite de régression de HotXLS, exécutée à la fois sur le moteur classique TXLSWorkbook et sur le moteur natif XLSX TXLSXWorkbook

Pourquoi CompareText et la comparaison ordinale se trompent-elles ?

CompareText et la comparaison ordinale se trompent sur l’ordre d’Excel parce qu’elles comparent des unités de code UTF-16, et l’ordre des points de code place la ponctuation à des endroits arbitraires par rapport aux lettres. Le tiret est U+002D et l’apostrophe U+0027, tous deux sous toute lettre, donc une comparaison ordinale juge "a-b" plus petit que "ab" au lieu de traiter le tiret comme un départageur. Le tilde U+007E siège au-dessus de toute lettre, donc "a~b" sort plus grand, l’inverse d’Excel. CompareText dans la RTL Delphi ne bascule que a..z en majuscules puis compare des unités de code, ce qui ajoute une seconde distorsion : le tiret bas U+005F se trouve entre les lettres majuscules et minuscules, donc le passage en majuscules déplace "a_b" de sous "ab" à au-dessus de lui. Aucune des deux fonctions ne sait que é appartient entre e et f

Les outils Delphi habituels tombent des deux côtés de la frontière :

• CompareStr, l’opérateur < sur chaînes et TComparer<string>.Default (qui appelle CompareStr) sont ordinaux et sensibles à la casse, donc TArray.Sort<string> sans comparateur met Z avant f
• CompareText et SameText sont ordinales après une bascule de casse limitée à l’ASCII
• AnsiCompareText et WideCompareText dans la RTL Delphi sous Windows appellent CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), le même appel qui colle à Excel. Une TStringList triée avec ses réglages par défaut (UseLocale True, CaseSensitive False) passe par AnsiCompareText et donne donc aussi raison à Excel
• Sur les cibles POSIX, la RTL Delphi achemine AnsiCompareText par un collateur ICU, un algorithme différent avec des règles de ponctuation différentes, et le AnsiCompareText de Free Pascal sous Windows appelle CompareStringA après conversion vers la page de codes ANSI, ce qui perd tout caractère que cette page ne peut pas représenter

Les fonctions RTL conscientes de la locale ont donc raison sous Windows par implémentation, pas par contrat, et un code qui a besoin de l’ordre d’Excel a intérêt à faire l’appel API explicitement. HotXLS avait le même mélange en interne. Les opérateurs de comparaison passaient les deux chaînes en majuscules et comparaient des points de code, les branches > / < des fonctions de critères utilisaient la comparaison Variant sensible à la casse de Delphi, et VLOOKUP / HLOOKUP rapprochaient le texte avec cette même comparaison Variant sensible à la casse, d’où l’impossibilité pour VLOOKUP("ABC",A1:A20,1,FALSE) de trouver abc. Le tri de plages utilisait déjà WideCompareText. Trois chemins, trois ordres

Qu’est-ce qui a changé dans HotXLS v2.384.67 ?

Depuis v2.384.67, les comparaisons texte contre texte dans les chemins de calcul et de tri de HotXLS passent par une seule fonction, XlsCompareText dans lxStandard.pas, qui appelle CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) et soustrait CSTR_EQUAL. Les appelants sont les six opérateurs de comparaison, les comparaisons élément par élément dans les formules matricielles, les branches >, <, >= et <= des critères à la COUNTIF et des fonctions de base de données, VLOOKUP et HLOOKUP (exact et approximatif), les aides d’ordonnancement derrière les fonctions de tableau dynamique et XLOOKUP / XMATCH, et le tri de plages des deux moteurs. Faire passer le tri de plages par la même fonction garantit que l’ordre de tri et l’ordre de comparaison ne peuvent plus diverger, ce qui compte parce que le VLOOKUP approximatif sur du texte n’a de sens que si la colonne a été triée dans l’ordre où la recherche compare

uses
  System.Variants, lxHandleX;

var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Sheets.Add('Data');  // Calculate évalue contre la feuille active
    Writeln(VarToStr(Book.Calculate('="a-b">"ab"')));   // True : le tiret ne fait que départager
    Writeln(VarToStr(Book.Calculate('="a-b"="ab"')));   // False : égalité départagée, pas égales
    Writeln(VarToStr(Book.Calculate('="a~b"<"ab"')));   // True : ponctuation d’abord
    Writeln(VarToStr(Book.Calculate('="ABC"="abc"')));  // True : casse ignorée
  finally
    Book.Free;
  end;
end;

Les comparaisons entre types différents relèvent d’une autre règle et n’ont pas changé : tout nombre est sous toute valeur texte et toute valeur texte est sous tout booléen, comme décrit dans l’article sur les chaînes de comparaison, les opérandes vides et SUMIF. Le word sort ne s’applique qu’une fois les deux opérandes textuels. La correspondance par wildcards est elle aussi à part : un critère tel que "a*" ou "=ab" est un test de motif ou d’égalité, traité dans le guide des wildcards Excel dans COUNTIF, MATCH et DSUM, et la collation dont il est question ici ne décide que des opérateurs d’ordre

L’exemple suivant charge les 20 mots de test dans une colonne, la trie avec TXLSXWorksheet.SortRange, puis vérifie un comptage de critères et une recherche. Les comptages sont ceux qu’Excel 16 a renvoyés pour la même colonne

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 sur la même colonne : 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")')));

    // Était #N/A avant v2.384.67 : la recherche comparait avec sensibilité à la casse
    Sheet.Cells[1, 3].Formula := '=VLOOKUP("ABC",A1:A20,1,FALSE)';
    Book.Recalculate;
    Writeln(VarToStr(Sheet.Cells[1, 3].Value));                // abc

    // Une colonne clé, croissant : 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 utilise un tri fusion stable, donc ab, AB et Ab, qui comparent égaux, gardent l’ordre relatif qu’ils avaient avant le tri. Les cellules vides vont à la fin dans les deux sens, comme dans Excel

Comment reproduire l’ordre de tri d’Excel dans mon propre code Delphi ?

Pour reproduire l’ordre textuel d’Excel dans votre propre code Delphi, appelez CompareStringW avec LOCALE_USER_DEFAULT et NORM_IGNORECASE, sans ajouter SORT_STRINGSORT ni NORM_IGNOREWIDTH. La valeur de retour n’est pas un résultat de comparaison signé : l’API renvoie CSTR_LESS_THAN (1), CSTR_EQUAL (2) ou CSTR_GREATER_THAN (3), et 0 quand l’appel échoue. Soustrayez 2 pour retomber sur la convention habituelle négatif / zéro / positif, et testez d’abord 0, parce qu’un échec pris pour un résultat devient -2, un « inférieur à » silencieux

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

// Ordre textuel d’Excel : word sort de la locale utilisateur, sans casse
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 est un échec, pas un résultat de comparaison
  Result := R - CSTR_EQUAL;    // 1/2/3 deviennent -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 (égaux, ordre indifférent), a-b, -ab, abc
end;

TArray.Sort n’est pas stable, donc des clés qui comparent égales, comme ab et AB, peuvent sortir dans n’importe quel ordre ; si l’ordre d’origine des clés égales compte, triez un tableau d’index avec la position d’origine comme clé secondaire. Le cas inverse arrive aussi : il arrive qu’une colonne ne doive pas suivre l’ordre d’Excel, par exemple des références de pièces où X-100 et X100 sont des codes distincts et doivent se trier par point de code. TXLSXWorksheet.SortRange a une surcharge qui prend un TXLSSortCompareEvent, une méthode de signature function(const Left, Right: Variant): Integer of object, et l’utilise à la place de la comparaison intégrée

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
  // Un comparateur personnalisé reçoit aussi les cellules vides (en Null) : placez-les vous-même
  if VarIsNull(Left) or VarIsNull(Right) then
    Exit(Ord(VarIsNull(Left)) - Ord(VarIsNull(Right)));
  Result := CompareStr(VarToStr(Left), VarToStr(Right));   // ordinal, sensible à la casse
end;

var
  Sheet: TXLSXWorksheet;   // une feuille remplie, lignes 2..501, colonnes A..D
  Order: TPartNumberOrder;
begin
  // ...
  Order := TPartNumberOrder.Create;
  try
    // clé sur la colonne A, croissant
    Sheet.SortRange(2, 1, 501, 4, [1], [False], xlsSortByRows,
      xlsSortExcelLike, Order.Compare);
  finally
    Order.Free;
  end;
end;

Quand un comparateur personnalisé est fourni, HotXLS saute sa propre gestion des vides et passe les valeurs de clés brutes, donc le comparateur doit traiter Null. Pour une clé descendante, HotXLS nie ce que renvoie le comparateur, ce qui déplace aussi les vides en haut sauf si le comparateur en tient compte. Gardez en tête qu’une colonne triée ainsi n’est plus dans l’ordre qu’attendent le VLOOKUP approximatif d’Excel ou un XLOOKUP par recherche dichotomique ; les pièges de ces modes sur des données triées dans un autre ordre sont couverts dans le guide des modes de recherche dichotomique XLOOKUP et XMATCH

Pourquoi un même classeur peut-il se trier différemment sur une autre machine ?

Un même classeur peut se trier différemment sur une autre machine parce que l’ordre textuel d’Excel dépend de la locale utilisateur Windows, et HotXLS suit délibérément cette dépendance. Le word sort est propre à chaque langue : la collation suédoise, par exemple, place ä après z, là où l’anglais et l’allemand le gardent à côté du a. Excel l’hérite de la locale sous laquelle il tourne, donc un classeur recalculé par un collègue de Stockholm peut renvoyer un COUNTIF(...,">y") différent du même fichier sur un poste de bureau à Chicago. HotXLS passe LOCALE_USER_DEFAULT pour que ses résultats égalent ceux d’Excel sur la même machine ; toute locale figée ferait diverger HotXLS d’Excel sur chaque machine au réglage différent

Trois conséquences pratiques en découlent pour la génération côté serveur :

• La locale qui compte est celle du compte sous lequel tourne le processus. Un service Windows ou un pool d’applications IIS peut utiliser un format régional différent du poste du développeur, donc les résultats observés dans l’IDE ne sont pas automatiquement ce que la production calcule
• Les résultats de formules mis en cache dans le fichier reflètent la locale de la machine génératrice. Excel recalcule avec sa propre locale, donc une valeur peut changer quand le fichier est ouvert ailleurs et recalculé ; c’est le comportement d’Excel, pas un artefact de HotXLS
• Les locales divergent surtout sur les lettres accentuées, sur les combinaisons de lettres que certaines langues traitent comme une seule lettre, et sur les écritures non latines, donc des données de test limitées à des mots anglais simples ne révéleront pas le problème

La frontière de plateforme est simple. HotXLS est une bibliothèque Windows, compilée pour Win32 et Win64 avec Delphi et C++Builder et pour les cibles win32 / win64 avec Lazarus et Free Pascal, et tous ces builds appellent le même CompareStringW. Il n’existe pas de chemin de collation distinct hors Windows. Le seul repli concerne un appel API échoué : si CompareStringW renvoie 0, XlsCompareText compare les chaînes passées en majuscules par unité de code au lieu de lever une exception en pleine recalculation, ce qui laisse le calcul tourner mais ne garantit plus l’ordre d’Excel

Aide-mémoire : la comparaison de texte Excel dans HotXLS

• Règle : word sort de la locale utilisateur avec NORM_IGNORECASE, sans SORT_STRINGSORT, sans NORM_IGNOREWIDTH, dans HotXLS depuis v2.384.67
• - et ' ne font que départager : ="a-b">"ab" vaut TRUE et ="a-b"="ab" vaut FALSE
• Les autres ponctuations se trient avant les chiffres, les chiffres avant les lettres : ="a~b"<"ab" et ="a0"<"ab" valent TRUE
• La casse ne compte jamais : ="ABC"="abc" vaut TRUE et VLOOKUP("ABC",...) trouve abc
• Chemins couverts : opérateurs de comparaison, comparaisons de tableaux, critères > / <, VLOOKUP / HLOOKUP, ordonnancement des tableaux dynamiques, SortRange des deux moteurs
• Hors du champ de cette règle : les types mixtes (nombre < texte < booléen) et les critères à wildcards, qui ont leurs propres règles
• En code Delphi : CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), tester le 0, soustraire CSTR_EQUAL ; évitez CompareText, CompareStr et TComparer<string>.Default quand le résultat doit coller à Excel
• Les résultats dépendent de la locale du compte qui exécute le code, dans Excel comme dans HotXLS

Les mots ordinaires se trient pareil sous chaque règle, donc seuls les codes à tirets, la ponctuation et les noms accentués exposent une collation fausse. HotXLS donne désormais la réponse d’Excel sur tous, dans les moteurs XLS comme XLSX. Les détails de licence, les versions Delphi et C++Builder prises en charge et l’essai à télécharger sont sur la page du composant Excel HotXLS Delphi