HotXLS-Textvergleich: Excel-Wortsortierung in Delphi

Die HotXLS Delphi Component vergleicht zwei Textwerte seit v2.384.67 so wie Excel 16: ohne Groß-/Kleinschreibung, in der „word sort“-Ordnung der Windows-User-Locale, also dem, was CompareStringW mit dem Flag NORM_IGNORECASE zurückgibt. Bindestrich und Apostroph überspringt der erste Durchlauf und sie brechen nur Gleichstände, ="a-b">"ab" ist also TRUE, während andere Satzeichen vor Ziffern und Buchstaben sortieren, ="a~b"<"ab" also ebenfalls TRUE ist. Dieselbe Ordnung treibt jetzt die Vergleichsoperatoren, die >- / <-Kriterien, die Bereichssortierung und VLOOKUP

Niemand meldet einen Bug mit dem Titel „Collation-Mismatch“. Die Meldungen lauten: COUNTIF(A:A,">M") zählt auf dem Server zwei Zeilen mehr als in Excel, eine vom Reporting-Dienst sortierte Preisliste bringt X-100 an eine Stelle, an die Excel sie nie legen würde, oder VLOOKUP("ABC",...) liefert #N/A, obwohl die Sparte nachweislich abc enthält. Alle drei kommen von derselben Frage: Wenn beide Operanden Text sind, welcher ist dann der kleinere? Excel hat eine präzise Antwort, es ist nicht die, die der meiste Delphi-Code gibt, und vor v2.384.67 gab HotXLS je nach Codepfad drei verschiedene Antworten

Nach welcher Regel vergleicht Excel zwei Textstrings?

Excel vergleicht Text mit der Wortsortierung der User-Locale, ohne Groß-/Kleinschreibung. Wortsortierung ist die Default-Collation der Windows-NLS-Vergleichsfunktionen: Buchstaben vergleichen sich nach ihrer sprachlichen Ordnung statt nach ihren Codepunkten, akzentuierte Buchstaben sitzen neben ihrem Basisbuchstaben, und zwei Zeichen bekommen eine Sonderbehandlung. Der Bindestrich - und der Apostroph ' werden im ersten Durchlauf ignoriert, co-op und coop landen also nebeneinander, und erst wenn der Rest der Strings gleichzieht, entscheidet ihre Präsenz über die Reihenfolge. Jedes andere Satzeichen zählt und sortiert vor den Ziffern, und Ziffern sortieren vor den Buchstaben

Die Tabelle zeigt, was das in der Praxis bedeutet, neben den zwei Vergleichen, nach denen ein Delphi-Entwickler am ehesten greift. Die Excel-Spalte hält die Urteile, die Excel 16 für IF(A<B,...) geliefert hat und die HotXLS seit v2.384.67 reproduziert

A vs. B	Excel 16 / HotXLS	CompareStr (ordinal)	CompareText
"a-b" vs. "ab"	größer	kleiner	kleiner
"a'b" vs. "ab"	größer	kleiner	kleiner
"a~b" vs. "ab"	kleiner	größer	größer
"a_b" vs. "ab"	kleiner	kleiner	größer
"ab" vs. "AB"	gleich	größer	gleich
"é" vs. "f"	kleiner	größer	größer
"Z" vs. "f"	größer	kleiner	größer

Zwei Konsequenzen sind leicht zu übersehen. Erstens bedeutet die Gleichstand-Rolle des Bindestrichs, dass ="a-b"="ab" FALSE ist: Die Strings liegen in der Sortierung eng beieinander, sind aber nicht gleich. Zweitens ignoriert die Gleichheit die Groß-/Kleinschreibung komplett, ab, AB und Ab sind also für jeden Vergleich derselbe Schlüssel. Sortiert man 20 Testwörter mit Excels Range.Sort, ergibt sich 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; innerhalb der ab-Gruppe entscheidet die Position des ignorierten Zeichens

Wie wurde Excels Textordnung eingekreist?

Excels Textordnung wurde durch Messung identifiziert, nicht durch Dokumentation, denn Excels Dokumentation benennt die Collation nicht. Der Test erzeugte 4.000 zufällige Stringpaare aus ASCII-Satzeichen, Ziffern, beiden Schreibweisen, Leerzeichen, é, ß, ä, chinesischen Zeichen, Fullwidth-Formen und dem geschützten Leerzeichen, mit Längen von 0 bis 4 und der Hälfte der Paare als Near-Miss-Paare. Excel 16 wertete für jedes Paar IF(A<B,-1,IF(A=B,0,1)) aus, und die Urteile wurden gegen die Windows-Vergleichs-API mit verschiedenen Flag-Sets gematcht

• NORM_IGNORECASE allein (Default-Wortsortierung, User-Locale): kein echter Mismatch. Die einzigen 7 Differenzen waren Zellen, deren gesamter Inhalt ein ' war, die Excel als Text-Präfixzeichen schluckt, es handelte sich also um Sampling-Artefakte statt um Collation-Differenzen
• NORM_IGNORECASE mit SORT_STRINGSORT: 41 Mismatches. String-Sort behandelt Bindestrich und Apostroph wie gewöhnliche Symbole, genau das Verhalten, das Excel nicht hat
• NORM_IGNOREWIDTH zusätzlich: falsch auf andere Weise, denn es lässt Fullwidth- und Halfwidth-Formen desselben Buchstabens gleich vergleichen, und Excel hält sie auseinander

Eine zweite, handverlesene Prüfung verglich alle 190 Paare aus 20 tückischen Wörtern mit dem Ergebnis von Excels Range.Sort auf derselben Spalte. Beide stimmten mit der schlichten NORM_IGNORECASE-Wortsortierung überein, und diese 190 Urteile plus die sortierte Reihenfolge sind jetzt Teil der HotXLS-Regressionssuite, gefahren durch sowohl die klassische TXLSWorkbook-Engine als auch die XLSX-native TXLSXWorkbook-Engine

Warum liegen CompareText und ordinaler Vergleich daneben?

CompareText und der ordinale Vergleich liegen neben Excels Ordnung, weil sie UTF-16-Codeeinheiten vergleichen, und die Codepunkt-Ordnung setzt Satzeichen an willkürliche Stellen relativ zu den Buchstaben. Der Bindestrich ist U+002D und der Apostroph U+0027, beide unter jedem Buchstaben, ein ordinaler Vergleich nennt "a-b" also kleiner als "ab", statt den Bindestrich als Gleichstand-Brecher zu behandeln. Die Tilde U+007E liegt über jedem Buchstaben, "a~b" kommt also größer heraus, das Gegenteil von Excel. CompareText in der Delphi-RTL faltet nur a..z in Großbuchstaben und vergleicht dann Codeeinheiten, was eine zweite Verzerrung hinzufügt: Der Unterstrich U+005F liegt zwischen den Groß- und Kleinbuchstaben, das Falten in Großbuchstaben verschiebt "a_b" also von unterhalb "ab" nach oberhalb. Keine der Funktionen weiß, dass é zwischen e und f gehört

Die üblichen Delphi-Werkzeuge fallen auf beiden Seiten der Linie:

• CompareStr, der String-<-Operator und TComparer<string>.Default (der CompareStr aufruft) sind ordinal und case-sensitiv, TArray.Sort<string> ohne Comparer setzt Z also vor f
• CompareText und SameText sind ordinal nach reiner ASCII-Faltung
• AnsiCompareText und WideCompareText in der Delphi-RTL unter Windows rufen CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) auf, denselben Aufruf, der zu Excel passt. Eine sortierte TStringList mit ihren Defaults (UseLocale True, CaseSensitive False) läuft über AnsiCompareText und stimmt deshalb ebenfalls mit Excel
• Auf POSIX-Zielen route die Delphi-RTL AnsiCompareText durch einen ICU-Collator, einen anderen Algorithmus mit anderen Satzeichen-Regeln, und Free Pascals AnsiCompareText unter Windows ruft CompareStringA nach Konvertierung in die ANSI-Codepage auf, was jedes Zeichen verliert, das diese Seite nicht darstellen kann

Die locale-aware-RTL-Funktionen sind unter Windows also der Implementierung nach richtig, nicht dem Vertrag nach, und Code, der Excels Ordnung braucht, fährt besser, wenn er die API explizit aufruft. HotXLS hatte intern denselben Mix. Die Vergleichsoperatoren wandelten beide Strings in Großbuchstaben und verglichen Codepunkte, die >- / <-Zweige der Kriterienfunktionen nutzten Delphis case-sensitive Variant-Vergleich, und VLOOKUP / HLOOKUP matchten Text mit ebendiesem case-sensitive Variant-Vergleich, weshalb VLOOKUP("ABC",A1:A20,1,FALSE) abc nicht finden konnte. Die Bereichssortierung nutzte bereits WideCompareText. Drei Pfade, drei Ordnungen

Was hat sich in HotXLS v2.384.67 geändert?

Seit v2.384.67 laufen die Text-gegen-Text-Vergleiche in den Berechnungs- und Sortierpfaden von HotXLS durch eine Funktion, XlsCompareText in lxStandard.pas, die CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) aufruft und CSTR_EQUAL abzieht. Die Aufrufer sind die sechs Vergleichsoperatoren, die elementweisen Vergleiche in Array-Formeln, die >-, <-, >=- und <=-Zweige der COUNTIF-artigen Kriterien und der Datenbankfunktionen, VLOOKUP und HLOOKUP (exakt und approximativ), die Ordnungs-Helfer hinter den Dynamic-Array-Funktionen und XLOOKUP / XMATCH sowie die Bereichssortierung beider Engines. Die Bereichssortierung durch dieselbe Funktion zu routen garantiert, dass Sortierordnung und Vergleichsordnung nicht wieder auseinanderdriften, was zählt, weil ein approximativer VLOOKUP auf Text nur dann Sinn ergibt, wenn die Spalte in der Ordnung sortiert wurde, in der der Lookup vergleicht

uses
  System.Variants, lxHandleX;

var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Sheets.Add('Data');  // Calculate wertet gegen das aktive Blatt aus
    Writeln(VarToStr(Book.Calculate('="a-b">"ab"')));   // True: der Bindestrich bricht nur Gleichstände
    Writeln(VarToStr(Book.Calculate('="a-b"="ab"')));   // False: Gleichstand gebrochen, nicht gleich
    Writeln(VarToStr(Book.Calculate('="a~b"<"ab"')));   // True: Satzeichen zuerst
    Writeln(VarToStr(Book.Calculate('="ABC"="abc"')));  // True: Groß-/Kleinschreibung ignoriert
  finally
    Book.Free;
  end;
end;

Quer-Typ-Vergleiche sind eine eigene Regel und haben sich nicht geändert: Jede Zahl liegt unter jedem Textwert, und jeder Textwert unter jedem Boolean, wie in dem Artikel zu Vergleichsketten, leeren Operanden und SUMIF beschrieben. Die Wortsortierung greift erst, wenn beide Operanden Text sind. Wildcard-Matching ist ebenfalls separat: Ein Kriterium wie "a*" oder "=ab" ist ein Muster- oder Gleichheitstest, behandelt in der Anleitung zu Excel-Wildcards in COUNTIF, MATCH und DSUM, und die hier besprochene Collation entscheidet nur über die Ordnungsoperatoren

Das nächste Beispiel lädt die 20 Testwörter in eine Spalte, sortiert sie mit TXLSXWorksheet.SortRange und prüft eine Kriterien-Zählung und einen Lookup. Die Zählungen sind die, die Excel 16 für dieselbe Spalte zurückgab

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 auf derselben Spalte: 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")')));

    // Vor v2.384.67 #N/A: der Lookup verglich case-sensitiv
    Sheet.Cells[1, 3].Formula := '=VLOOKUP("ABC",A1:A20,1,FALSE)';
    Book.Recalculate;
    Writeln(VarToStr(Sheet.Cells[1, 3].Value));                // abc

    // Eine Schlüsselspalte, aufsteigend: 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 benutzt einen stabilen Merge-Sort, ab, AB und Ab, die gleich vergleichen, behalten also ihre relative Reihenfolge von vor der Sortierung. Leere Zellen rutschen in beide Richtungen ans Ende, wie in Excel

Wie treffe ich Excels Sortierordnung in meinem eigenen Delphi-Code?

Um Excels Textordnung im eigenen Delphi-Code zu treffen, rufen Sie CompareStringW mit LOCALE_USER_DEFAULT und NORM_IGNORECASE auf, ohne SORT_STRINGSORT oder NORM_IGNOREWIDTH hinzuzufügen. Der Rückgabewert ist kein signiertes Vergleichsergebnis: Die API liefert CSTR_LESS_THAN (1), CSTR_EQUAL (2) oder CSTR_GREATER_THAN (3), und 0, wenn der Aufruf scheitert. Ziehen Sie 2 ab, um zur gewohnten negativ / null / positiv-Konvention zu kommen, und testen Sie zuerst auf 0, denn ein Fehler, der für ein Ergebnis gehalten wird, wird zu -2, einem stillen „kleiner“

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

// Excels Textordnung: Wortsortierung der User-Locale, ohne Groß-/Kleinschreibung
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 ist ein Fehler, kein Vergleichsergebnis
  Result := R - CSTR_EQUAL;    // 1/2/3 werden -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 (gleich, in beliebiger Reihenfolge), a-b, -ab, abc
end;

TArray.Sort ist nicht stabil, Schlüssel, die gleich vergleichen, wie ab und AB, kommen also in beliebiger Reihenfolge heraus; liegt Ihnen die ursprüngliche Reihenfolge gleicher Schlüssel am Herzen, sortieren Sie ein Index-Array mit der ursprünglichen Position als Sekundärschlüssel. Der umgekehrte Fall kommt auch vor: Manchmal darf eine Spalte gerade nicht Excels Ordnung folgen, etwa Teilenummern, bei denen X-100 und X100 verschiedene Codes sind und nach Codepunkt sortieren sollen. TXLSXWorksheet.SortRange hat einen Overload, der ein TXLSSortCompareEvent nimmt, eine Methode mit der Signatur function(const Left, Right: Variant): Integer of object, und sie statt des eingebauten Vergleichs benutzt

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
  // Ein eigener Comparer bekommt auch leere Zellen (als Null): selbst einsortieren
  if VarIsNull(Left) or VarIsNull(Right) then
    Exit(Ord(VarIsNull(Left)) - Ord(VarIsNull(Right)));
  Result := CompareStr(VarToStr(Left), VarToStr(Right));   // ordinal, case-sensitiv
end;

var
  Sheet: TXLSXWorksheet;   // ein gefülltes Blatt, Zeilen 2..501, Spalten A..D
  Order: TPartNumberOrder;
begin
  // ...
  Order := TPartNumberOrder.Create;
  try
    // keyed auf Spalte A, aufsteigend
    Sheet.SortRange(2, 1, 501, 4, [1], [False], xlsSortByRows,
      xlsSortExcelLike, Order.Compare);
  finally
    Order.Free;
  end;
end;

Wenn ein eigener Comparer geliefert wird, überspringt HotXLS seine eigene Behandlung leerer Zellen und reicht die rohen Schlüsselwerte durch, der Comparer muss sich also um Null kümmern. Für einen absteigenden Schlüssel negiert HotXLS, was der Comparer liefert, was die Leerzellen mit an den Anfang schiebt, sofern der Comparer dem nicht Rechnung trägt. Denken Sie daran, dass eine so sortierte Spalte nicht mehr in der Ordnung liegt, die Excels approximativer VLOOKUP oder ein Binary-Search-XLOOKUP erwartet; die Fallstricke dieser Modi auf anders sortierten Daten behandelt die Anleitung zu den Binary-Search-Modi von XLOOKUP und XMATCH

Warum kann dieselbe Arbeitsmappe auf einer anderen Maschine anders sortieren?

Dieselbe Arbeitsmappe kann auf einer anderen Maschine anders sortieren, weil Excels Textordnung von der Windows-User-Locale abhängt und HotXLS dieser Abhängigkeit bewusst folgt. Wortsortierung ist sprachspezifisch: Die schwedische Collation etwa stellt ä hinter z, wo Englisch und Deutsch es neben a behalten. Excel erbt das von der Locale, unter der es läuft, eine von der Stockholmer Kollegin neu berechnete Arbeitsmappe kann also ein anderes COUNTIF(...,">y") liefern als dieselbe Datei auf einem Desktop in Chicago. HotXLS reicht LOCALE_USER_DEFAULT durch, damit seine Ergebnisse auf derselben Maschine denen von Excel gleichen; jede festgezurrte Locale würde HotXLS auf jeder Maschine mit einer anderen Einstellung von Excel abweichen lassen

Drei praktische Konsequenzen ergeben sich für die serverseitige Generierung:

• Die Locale, die zählt, ist die des Accounts, unter dem der Prozess läuft. Ein Windows-Dienst oder IIS-Application-Pool nutzt unter Umständen ein anderes Regionsformat als der Entwickler-Desktop, Beobachtetes in der IDE ist also nicht automatisch das, was die Produktion berechnet
• Zwischengespeicherte Formelergebnisse in der Datei spiegeln die Locale der generierenden Maschine. Excel berechnet mit seiner eigenen Locale neu, ein Wert kann sich also ändern, wenn die Datei anderswo geöffnet und neu berechnet wird; das ist Excels Verhalten, kein HotXLS-Artefakt
• Locales uneinig sind sie vor allem bei akzentuierten Buchstaben, bei Buchstabenkombinationen, die manche Sprachen als einen einzigen Buchstaben behandeln, und bei nicht-lateinischen Schriften, Testdaten aus schlichten englischen Wörtern decken das Problem also nicht auf

Die Plattform-Grenze ist schlicht. HotXLS ist eine Windows-Bibliothek, gebaut für Win32 und Win64 mit Delphi und C++Builder und für win32- / win64-Ziele mit Lazarus und Free Pascal, und alle diese Builds rufen dasselbe CompareStringW auf. Einen separaten Nicht-Windows-Collation-Pfad gibt es nicht. Der einzige Fallback gilt für einen fehlgeschlagenen API-Aufruf: Liefert CompareStringW 0, vergleicht XlsCompareText die großgeschriebenen Strings nach Codeeinheit, statt mitten in einer Neuberechnung eine Exception zu werfen, was die Berechnung am Laufen hält, Excels Ordnung aber nicht mehr garantiert

Kurzreferenz: Excel-Textvergleich in HotXLS

• Regel: Wortsortierung der User-Locale mit NORM_IGNORECASE, kein SORT_STRINGSORT, kein NORM_IGNOREWIDTH, in HotXLS seit v2.384.67
• - und ' brechen nur Gleichstände: ="a-b">"ab" ist TRUE und ="a-b"="ab" ist FALSE
• Andere Satzeichen sortieren vor Ziffern, Ziffern vor Buchstaben: ="a~b"<"ab" und ="a0"<"ab" sind TRUE
• Die Groß-/Kleinschreibung zählt nie: ="ABC"="abc" ist TRUE und VLOOKUP("ABC",...) findet abc
• Abgedeckte Pfade: Vergleichsoperatoren, Array-Vergleiche, > / <-Kriterien, VLOOKUP / HLOOKUP, Dynamic-Array-Ordnung, SortRange in beiden Engines
• Nicht von dieser Regel abgedeckt: gemischte Typen (Zahl < Text < Boolean) und Wildcard-Kriterien, die eigene Regeln haben
• In Delphi-Code: CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), auf 0 prüfen, CSTR_EQUAL abziehen; meiden Sie CompareText, CompareStr und TComparer<string>.Default, wenn das Ergebnis mit Excel übereinstimmen muss
• Die Ergebnisse hängen von der Locale des Accounts ab, unter dem der Code läuft, in Excel wie in HotXLS

Gewöhnliche Wörter sortieren unter jeder Regel gleich, nur Bindestrich-Codes, Satzeichen und akzentuierte Namen legen eine falsche Collation offen. HotXLS liefert jetzt auf allen Excels Antwort, in beiden Engines, XLS wie XLSX. Details zu Lizenzierung, unterstützten Delphi- und C++Builder-Versionen und dem Testdownload finden Sie auf der HotXLS Delphi Excel component page