Dans Excel, ="a-b">"ab" renvoie TRUE, et ="a~b"<"ab" renvoie TRUE aussi. Confiez les mêmes paires au CompareText de Delphi et les deux réponses s’inversent, car le tiret se situe sous les lettres dans l’ordre des points de code et le tilde au-dessus. Excel compare le texte avec le « word sort » insensible à la casse de la locale utilisateur Windows, où tirets et apostrophes ne font que départager les égalités et où tout autre signe de ponctuation se trie avant les chiffres et les lettres
Les symptômes parlent rarement de collation. Un COUNTIF(A:A,">M") calculé sur le serveur compte deux lignes de plus que la même formule dans Excel. Une liste de prix triée par un service de reporting place X-100 là où Excel ne la mettrait pas. VLOOKUP("ABC",...) renvoie #N/A alors que la colonne contient manifestement abc. Les trois se ramènent à une seule question : quand les deux opérandes sont du texte, lequel est le plus petit ? Excel a une réponse précise, et ce n’est rarement celle que le code Delphi donne par défaut
Quelle règle Excel utilise-t-il pour comparer deux chaînes de texte ?
Excel compare deux valeurs texte avec le word sort de la locale utilisateur Windows courante, 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 par point de code, si bien que é se place entre e et f, et majuscules et minuscules se confondent. Deux caractères bénéficient d’un traitement spécial. Le tiret - et l’apostrophe ' sont sautés à la première passe, ce qui place co-op juste à côté de coop ; c’est seulement quand les caractères restants font égalité que leur présence, et leur position, décident de l’ordre. Tout autre signe de ponctuation compte à plein poids et se trie avant les chiffres, et les chiffres se trient avant les lettres
Le tableau confronte le verdict d’Excel pour IF(A<B,...) aux deux comparaisons qu’un développeur Delphi écrit le plus souvent en premier
| A vs B | Excel / 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 piègent pas mal de monde. Comme le tiret ne fait que départager, ="a-b"="ab" vaut FALSE : les deux chaînes sont voisines immédiates dans un tri et pourtant inégales. Et comme la casse disparaît entièrement, ab, AB et Ab ne forment qu’une seule clé pour tous les opérateurs de comparaison. Sous une locale utilisateur anglaise, une colonne de 20 mots piégeux se trie en 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 sauté qui décide : tiret final d’abord, tiret initial en dernier
Vérifier la collation face à Excel vous-même
La documentation d’Excel ne nomme jamais la collation qu’elle utilise, donc la façon fiable de le confirmer sur votre propre machine est de laisser Excel juger un lot de paires de chaînes et de comparer ses verdicts avec CompareStringW sous différents jeux de drapeaux. Placez les paires dans deux colonnes, faites évaluer =IF(A1<B1,-1,IF(A1=B1,0,1)) à Excel pour chaque ligne, et passez les mêmes paires par l’API. Les paires qui valent la peine d’être générées mêlent ponctuation ASCII, chiffres, les deux casses, espaces, lettres accentuées comme é, ß et ä, caractères CJK, formes à chasse pleine et espace insécable, avec plein de quasi coïncidences qui ne diffèrent que d’un caractère. Des mots pris au hasard dans un dictionnaire font un mauvais échantillon, car des lettres simples se trient à l’identique sous toutes les règles de cet article
Les jeux de drapeaux mènent à des résultats nettement différents :
NORM_IGNORECASEseul, avecLOCALE_USER_DEFAULT, est le word sort simple et celui qu’Excel suit- Ajouter
SORT_STRINGSORTbascule vers le string sort, où le tiret et l’apostrophe deviennent des symboles ordinaires à plein poids, si bien que chaque paire de typea-b/absort dans l’autre sens - Ajouter
NORM_IGNOREWIDTHrend une lettre à chasse pleine égale à sa forme à chasse demi, et Excel distingue les deux
Un piège se cache dans le harnais de test lui-même. Excel traite une apostrophe de tête saisie dans une cellule comme le préfixe « stocker comme texte », donc une cellule dont toute la saisie est ' finit vide, et toute paire construite ainsi remonte une divergence qui n’a rien à voir avec la collation. Construisez ces paires avec des littéraux chaîne dans la formule (="'"), ou passez-les
Pourquoi CompareText et la comparaison ordinale se trompent-elles ?
CompareText et la comparaison ordinale ratent l’ordre d’Excel parce qu’elles comparent des unités de code UTF-16, et l’ordre des points de code disperse la ponctuation autour de l’alphabet. 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" là où Excel traite le tiret comme un départageur. Le tilde est U+007E, au-dessus de toute lettre, ce qui rend "a~b" plus grand, l’inverse d’Excel. CompareText ajoute sa propre seconde distorsion : il ne bascule que a..z en majuscules puis compare des unités de code, et comme le tiret bas (U+005F) se trouve entre les plages majuscules et minuscules, 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 etTComparer<string>.Defaultsont ordinaux et sensibles à la casse, doncTArray.Sort<string>sans comparateur metZavantfCompareTextetSameTextsont ordinales après une bascule de casse limitée à l’ASCIIAnsiCompareTextetWideCompareTextdans la RTL Delphi sous Windows appellentCompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), l’appel qui colle à Excel. UneTStringListtriée avec ses réglages par défaut (UseLocaleTrue,CaseSensitiveFalse) passe parAnsiCompareTextet donne aussi raison à Excel- Sur les cibles POSIX, la RTL Delphi achemine
AnsiCompareTextpar un collateur ICU, un algorithme différent avec des règles de ponctuation différentes. LeAnsiCompareTextde Free Pascal sous Windows convertit vers la page de codes ANSI et appelleCompareStringA, ce qui perd tout caractère que cette page ne peut pas représenter
Les fonctions RTL conscientes de la locale se trouvent donc avoir raison sous Windows à cause de la façon dont elles sont implémentées aujourd’hui. Un code qui doit donner raison à Excel devrait faire l’appel API explicitement et le dire. Le problème le plus coriace surgit dans tout moteur de formules où chaque chemin de code attrape la comparaison la plus proche : majuscules et comparaison de points de code dans les opérateurs = et <, une comparaison Variant sensible à la casse dans la branche > / < des critères COUNTIF, une autre dans VLOOKUP (c’est exactement ainsi que VLOOKUP("ABC",A1:A20,1,FALSE) échoue à trouver abc), et un appel de locale dans le tri de plages. Chaque chemin se défend seul. Ensemble, ils produisent trois ordres différents sur la même colonne
Une seule comparaison derrière les formules, les recherches et le tri dans HotXLS
HotXLS fait passer chaque comparaison texte contre texte de son code de calcul et de tri par une seule fonction, XlsCompareText dans lxStandard.pas, qui appelle CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) et soustrait CSTR_EQUAL. Les six opérateurs de comparaison l’utilisent, de même que 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 en mode exact et approximatif, l’ordonnancement derrière les fonctions de tableau dynamique et XLOOKUP / XMATCH, et SortRange sur TXLSWorksheet comme sur TXLSXWorksheet. Placer le tri de plages sur la même fonction a été le point de conception décisif. Le VLOOKUP approximatif sur du texte ne donne des réponses sensées que si la colonne a été triée dans l’ordre où la recherche compare, et partager une seule fonction rend impossible toute divergence entre ces deux ordres
La façon la plus rapide de voir la règle depuis Delphi est TXLSXWorkbook.Calculate, qui évalue une chaîne de formule contre la feuille active et renvoie le résultat sous forme de Variant
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;
Le word sort ne s’applique qu’une fois les deux opérandes textuels. Les types mélangés suivent un classement fixe où tout nombre est sous toute valeur texte et toute valeur texte sous tout booléen, comme décrit dans l’article sur les chaînes de comparaison, les opérandes vides et SUMIF. Les critères à wildcards tels que "a*" ou "=ab" sont des tests de motif et d’égalité avec leurs propres règles, traités dans le guide des wildcards Excel dans COUNTIF, MATCH et DSUM ; la collation dont il est question ici décide des opérateurs d’ordre
L’exemple suivant charge les 20 mots dans la colonne A d’une feuille, vérifie trois comptages de critères et une recherche, puis trie la colonne avec TXLSXWorksheet.SortRange. Les comptages attendus se déduisent directement de l’ordre trié montré plus haut : onze mots se trient après le groupe ab, onze avant a-b, et les trois membres du groupe ab rejoignent les onze pour >=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]);
// Attendu d'après l'ordre word sort : 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")')));
// Trouve abc : la recherche compare sans sensibilité à la casse, comme Excel
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;
SortRange exécute 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 en bas que la clé soit ascendante ou descendante, c’est là qu’Excel les met
Comment reproduire l’ordre de tri d’Excel dans mon propre code Delphi ?
Pour reproduire l’ordre textuel d’Excel hors d’un classeur, appelez CompareStringW avec LOCALE_USER_DEFAULT et NORM_IGNORECASE, et laissez de côté SORT_STRINGSORT et NORM_IGNOREWIDTH. Surveillez la valeur de retour. L’API ne renvoie pas un résultat signé ; elle renvoie CSTR_LESS_THAN (1), CSTR_EQUAL (2) ou CSTR_GREATER_THAN (3), et 0 quand l’appel échoue. Soustraire 2 redonne la convention habituelle négatif / zéro / positif, mais seulement une fois le cas 0 traité, sinon un appel échoué devient -2 et se lit comme un « inférieur à »
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;
Si HotXLS figure déjà dans la clause uses, lxStandard.XlsCompareText est public et renvoie le même ordre. Les deux ne diffèrent que sur un appel API échoué : la fonction ci-dessus lève une exception, tandis que XlsCompareText retombe sur une comparaison des chaînes passées en majuscules par unité de code, pour qu’un recalcul ne soit jamais interrompu en plein milieu. Pour un tri autonome, lever une exception est le meilleur choix, car un ordre silencieusement faux est plus difficile à traquer qu’une exception
TArray.Sort n’est pas stable, donc des clés égales comme ab et AB peuvent sortir dans n’importe quel ordre. Quand l’ordre d’origine des clés égales compte, triez un tableau d’index et utilisez la position d’origine comme clé secondaire
Il arrive qu’une colonne doive délibérément ignorer l’ordre d’Excel. Les références de pièces sont le cas classique : X-100 et X100 sont des codes différents et doivent se trier par point de code. SortRange a une surcharge qui prend un TXLSSortCompareEvent, déclaré comme 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;
Avec un comparateur personnalisé, SortRange transmet les valeurs de clés brutes et vous laisse gérer le placement des vides, c’est pourquoi le comparateur ci-dessus traite d’abord Null. Pour une clé descendante, le tri nie ce que renvoie le comparateur, donc dans ce sens les vides remontent en haut sauf si le comparateur en tient compte. Une colonne triée ainsi n’est plus dans l’ordre qu’attendent le VLOOKUP approximatif ou un XLOOKUP par recherche dichotomique ; ce qui tourne mal dans ces modes sur des données triées dans un autre ordre est couvert 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 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 cette dépendance exprès. 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, si bien qu’un fichier 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éponses égalent celles d’Excel sur la même machine. Figer une locale quelconque semblerait plus propre et ferait diverger HotXLS d’Excel sur chaque machine configurée autrement
Pour la génération côté serveur, cela a trois conséquences pratiques :
- La locale qui compte appartient au compte sous lequel tourne le processus. Un service Windows ou un pool d’applications IIS peut avoir des paramètres régionaux différents du poste du développeur, donc les résultats vus 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 et recalculé ailleurs ; Excel se comporte de la même façon avec ses propres fichiers
- Les locales divergent surtout sur les lettres accentuées, sur les paires de lettres que certaines langues traitent comme une seule lettre, et sur les écritures non latines. Des données de test faites de mots anglais simples n’exposeront jamais la différence, donc mettez quelques noms comme
ÅngströmetÖbergdans l’échantillon
Le chemin qui colle à Excel est celui de Windows : chaque build Win32 et Win64, qu’il soit compilé avec Delphi, C++Builder, ou Lazarus et Free Pascal, appelle le même CompareStringW. Quand un build cible une plateforme non Windows, XlsCompareText utilise le WideCompareText de la RTL, dont l’ordre vient de la bibliothèque de collation propre à cette plateforme
Les mots ordinaires se trient pareil sous toutes les règles, c’est pourquoi une mauvaise collation survit en général aux tests jusqu’à l’arrivée de références à tirets, de ponctuation ou de noms de clients accentués. HotXLS donne la réponse d’Excel sur ces entrées dans les deux moteurs XLS et XLSX, des opérateurs de comparaison jusqu’à SortRange. Les versions Delphi et C++Builder prises en charge, les licences et le téléchargement d’essai sont listés sur la page du composant Delphi Excel HotXLS