Technischer Artikel

HotXLS-Textvergleich: Excel-Wortsortierung in Delphi

In Excel liefert ="a-b">"ab" TRUE, und ="a~b"<"ab" liefert ebenfalls TRUE. Übergibt man dieselben Paare an Delphis CompareText, kippen beide Antworten, weil der Bindestrich in der Codepunkt-Ordnung unter den Buchstaben liegt und die Tilde darüber. Excel vergleicht Text mit der case-insensitiven „word sort“ der Windows-User-Locale, in der Bindestrich und Apostroph nur Gleichstände brechen und jedes andere Satzeichen vor Ziffern und Buchstaben sortiert

Die Symptome erwähnen Collation selten. Ein COUNTIF(A:A,">M") auf dem Server zählt zwei Zeilen mehr als dieselbe Formel in Excel. Eine vom Reporting-Dienst sortierte Preisliste bringt X-100 an eine Stelle, an die Excel sie nie legen würde. VLOOKUP("ABC",...) liefert #N/A, obwohl die Spalte nachweislich abc enthält. Alle drei laufen auf dieselbe Frage hinaus: Wenn beide Operanden Text sind, welcher ist dann der kleinere? Excel hat eine präzise Antwort, und es ist selten die, die Delphi-Code per Voreinstellung gibt

Nach welcher Regel vergleicht Excel zwei Textstrings?

Excel vergleicht zwei Textwerte mit der Wortsortierung der aktuellen Windows-User-Locale, ohne Groß-/Kleinschreibung. Wortsortierung ist die Default-Collation der Windows-NLS-Vergleichsfunktionen: Buchstaben vergleichen in sprachlicher Ordnung statt nach Codepunkt, é landet also zwischen e und f, und Groß- und Kleinschreibung fallen zusammen. Zwei Zeichen bekommen eine Sonderbehandlung. Der Bindestrich - und der Apostroph ' werden im ersten Durchlauf übersprungen, wodurch co-op direkt neben coop landet; erst wenn die übrigen Zeichen gleichziehen, entscheiden ihre Präsenz und ihre Position über die Reihenfolge. Jedes andere Satzeichen zählt mit vollem Gewicht und sortiert vor den Ziffern, und die Ziffern sortieren vor den Buchstaben

Die Tabelle stellt Excels Urteil für IF(A<B,...) neben die zwei Vergleiche, die ein Delphi-Entwickler höchstwahrscheinlich zuerst schreibt

A vs. BExcel / HotXLSCompareStr (ordinal)CompareText
"a-b" vs. "ab"größerkleinerkleiner
"a'b" vs. "ab"größerkleinerkleiner
"a~b" vs. "ab"kleinergrößergrößer
"a_b" vs. "ab"kleinerkleinergrößer
"ab" vs. "AB"gleichgrößergleich
"é" vs. "f"kleinergrößergrößer
"Z" vs. "f"größerkleinergrößer

Zwei Konsequenzen werden regelmäßig übersehen. Weil der Bindestrich nur Gleichstände bricht, ist ="a-b"="ab" FALSE: Die beiden Strings liegen in einer Sortierung direkt nebeneinander und sind trotzdem ungleich. Und weil die Groß-/Kleinschreibung komplett wegfällt, sind ab, AB und Ab für jeden Vergleichsoperator ein einziger Schlüssel. Unter einer englischen User-Locale sortiert eine Spalte mit 20 Widerlingswörtern so: 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 übersprungenen Zeichens: Bindestrich am Ende zuerst, Bindestrich am Anfang zuletzt

HotXLS-Wortsortierungs-Diagramm, das alle 20 Testwörter ordnet, von a b, a.b, a_b und a~b über a0 und a1b, dann die ab-Gruppe mit AB und Ab, Bindestrich- und Apostroph-Varianten wie a-b und a'b, bis hinauf zu abc, b, e, e-acute, f und Z, mit Satzeichen vor Ziffern vor Buchstaben und ignorierten Groß-/Kleinschreibung
Satzeichen und Leerzeichen sortieren vor Ziffern, Ziffern vor Buchstaben, die Groß-/Kleinschreibung fällt weg, und Bindestrich mit Apostroph brechen nur Gleichstände; deshalb landet a-b neben ab und vergleicht sich trotzdem als größer

Die Collation selbst gegen Excel prüfen

Excels Dokumentation nennt die verwendete Collation nirgends, deshalb ist der verlässliche Weg, das auf der eigenen Maschine zu bestätigen, Excel über einen Schwung Stringpaare urteilen zu lassen und seine Urteile mit CompareStringW unter verschiedenen Flag-Sets zu vergleichen. Die Paare kommen in zwei Spalten, Excel wertet für jede Zeile =IF(A1<B1,-1,IF(A1=B1,0,1)) aus, und dieselben Paare laufen durch die API. Erzeugenswerte Paare mischen ASCII-Satzeichen, Ziffern, beide Schreibweisen, Leerzeichen, akzentuierte Buchstaben wie é, ß und ä, CJK-Zeichen, Fullwidth-Formen und das geschützte Leerzeichen, mit reichlich Near-Misses, die sich in genau einem Zeichen unterscheiden. Zufallswörter aus einem Wörterbuch sind eine schlechte Stichprobe, denn schlichte Buchstaben sortieren unter jeder Regel dieses Artikels identisch

Die Flag-Sets führen zu klar unterschiedlichen Ergebnissen:

  • NORM_IGNORECASE allein, mit LOCALE_USER_DEFAULT, ist die schlichte Wortsortierung und die, der Excel folgt
  • SORT_STRINGSORT zusätzlich schaltet auf String-Sort um, wo Bindestrich und Apostroph zu gewöhnlichen Symbolen mit vollem Gewicht werden, so dass jedes Paar im Stil von a-b / ab genau umgekehrt herauskommt
  • NORM_IGNOREWIDTH zusätzlich macht einen Fullwidth-Buchstaben gleich seinem Halfwidth-Pendant, und Excel hält die beiden auseinander

Eine Falle sitzt im Testharness selbst. Excel behandelt einen führenden Apostroph in einer Zelle als „Als Text speichern“-Präfix, eine Zelle, deren gesamte Eingabe ein ' ist, endet also leer, und jedes so gebaute Paar meldet einen Mismatch, der mit Collation nichts zu tun hat. Solche Paare baut man mit String-Literalen in der Formel (="'") oder überspringt sie

Warum liegen CompareText und ordinaler Vergleich daneben?

CompareText und der ordinale Vergleich verfehlen Excels Ordnung, weil sie UTF-16-Codeeinheiten vergleichen und die Codepunkt-Ordnung Satzeichen quer über das Alphabet streut. Der Bindestrich ist U+002D und der Apostroph U+0027, beide unter jedem Buchstaben, ein ordinaler Vergleich nennt "a-b" also kleiner als "ab", wo Excel den Bindestrich als Gleichstand-Brecher behandelt. Die Tilde ist U+007E, über jedem Buchstaben, wodurch "a~b" größer herauskommt, das Gegenteil von Excel. CompareText fügt eine zweite Verzerrung eigener Regie hinzu: Es faltet nur a..z in Großbuchstaben und vergleicht dann Codeeinheiten, und weil der Unterstrich (U+005F) zwischen den Groß- und Kleinbuchstaben-Bereichen liegt, verschiebt das Falten "a_b" von unterhalb "ab" nach oberhalb. Keine der beiden Funktionen hat auch nur eine Ahnung, dass é zwischen e und f gehört

HotXLS-Vergleichsdiagramm mit Codepunkt-Ordnung gegen Excel-Wortsortierung: Der ordinale Vergleich setzt Apostroph, Bindestrich und Unterstrich bei 0x27, 0x2D und 0x5F um die Buchstaben herum ab, a-b gegen ab kommt also als kleiner heraus, während die Wortsortierung Satzeichen vor Ziffern und Buchstaben schiebt und nur Bindestrich und Apostroph als Gleichstand-Brecher behandelt
Codepunkte verstreuen Satzeichen um die Buchstaben, ordinale und ASCII-faltende Vergleiche kippen also die Urteile; die Wortsortierung rückt Satzeichen vor die Ziffern und degradiert Bindestrich und Apostroph zu Gleichstand-Brechern

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

  • CompareStr, der String-<-Operator und TComparer<string>.Default 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, den 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. Free Pascals AnsiCompareText unter Windows konvertiert in die ANSI-Codepage und ruft CompareStringA auf, was jedes Zeichen verliert, das diese Codepage nicht darstellen kann

Die locale-aware-RTL-Funktionen sind unter Windows also zufällig richtig, einfach wegen ihrer heutigen Implementierung. Code, der mit Excel übereinstimmen muss, sollte den API-Aufruf explizit machen und das auch dazu sagen. Das härtere Problem zeigt sich in jeder Formel-Engine, in der jeder Codepfad greift, was gerade am nächsten liegt: Großbuchstaben und Codepunkt-Vergleich in den =- und <-Operatoren, ein case-sensitiver Variant-Vergleich im >- / <-Zweig der COUNTIF-Kriterien, noch einer in VLOOKUP (genau deshalb findet VLOOKUP("ABC",A1:A20,1,FALSE) abc nicht) und ein Locale-Aufruf in der Bereichssortierung. Jeder Pfad ist für sich vertretbar. Zusammen erzeugen sie drei verschiedene Ordnungen über dieselbe Spalte

Ein Vergleich hinter Formeln, Lookups und Sortierung in HotXLS

HotXLS schickt jeden Text-gegen-Text-Vergleich in seinem Berechnungs- und Sortiercode durch eine einzige Funktion, XlsCompareText in lxStandard.pas, die CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) aufruft und CSTR_EQUAL abzieht. Die sechs Vergleichsoperatoren nutzen sie, ebenso die elementweisen Vergleiche in Array-Formeln, die >-, <-, >=- und <=-Zweige der COUNTIF-artigen Kriterien und der Datenbankfunktionen, VLOOKUP und HLOOKUP im exakten wie im approximativen Modus, die Ordnung hinter den Dynamic-Array-Funktionen und XLOOKUP / XMATCH sowie SortRange auf TXLSWorksheet wie auf TXLSXWorksheet. Die Bereichssortierung auf dieselbe Funktion zu setzen war der entscheidende Design-Punkt. Ein approximativer VLOOKUP auf Text liefert nur dann brauchbare Antworten, wenn die Spalte in der Ordnung sortiert wurde, in der der Lookup vergleicht, und eine gemeinsame Funktion macht es unmöglich, dass diese beiden Ordnungen auseinanderdriften

HotXLS-Routing-Diagramm mit jedem Textvergleichspfad, von den sechs Vergleichsoperatoren und COUNTIF-artigen Kriterien über VLOOKUP, HLOOKUP, XLOOKUP und die Bereichssortierung beider Engines, alles laufend auf XlsCompareText, das CompareStringW mit LOCALE_USER_DEFAULT und NORM_IGNORECASE aufruft und 1, 2, 3 auf -1, 0, 1 abbildet
Operatoren, Kriterien, Lookups und Sortierung teilen sich eine Funktion, sodass die Ordnung, die Excel sieht, und die Ordnung, mit der HotXLS sortiert, nicht auseinanderdriften können; die API liefert 1, 2 oder 3, und null bedeutet Fehler, nicht kleiner

Am schnellsten sieht man die Regel aus Delphi an TXLSXWorkbook.Calculate, das einen Formel-String gegen das aktive Blatt auswertet und das Ergebnis als Variant zurückgibt

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;

Die Wortsortierung greift erst, wenn beide Operanden Text sind. Gemischte Typen folgen einer festen Rangordnung, in der jede Zahl unter jedem Textwert und jeder Textwert unter jedem Boolean liegt, wie in dem Artikel zu Vergleichsketten, leeren Operanden und SUMIF beschrieben. Wildcard-Kriterien wie "a*" oder "=ab" sind Muster- und Gleichheitstests mit eigenen Regeln, behandelt in der Anleitung zu Excel-Wildcards in COUNTIF, MATCH und DSUM; die hier besprochene Collation entscheidet über die Ordnungsoperatoren

Das nächste Beispiel lädt die 20 Wörter in Spalte A eines Blatts, prüft drei Kriterien-Zählungen und einen Lookup und sortiert dann die Spalte mit TXLSXWorksheet.SortRange. Die erwarteten Zählungen folgen direkt aus der weiter oben gezeigten Sortierfolge: elf Wörter sortieren hinter der ab-Gruppe, elf vor a-b, und die drei Mitglieder der ab-Gruppe kommen für >=AB zu den elf dazu

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

    // Erwartet aus der Wortsortier-Reihenfolge: 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")')));

    // Findet abc: der Lookup vergleicht case-insensitiv wie Excel
    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;

SortRange fährt einen stabilen Merge-Sort, ab, AB und Ab, die gleich vergleichen, behalten also ihre relative Reihenfolge von vor der Sortierung. Leere Zellen rutschen bei aufsteigendem wie bei absteigendem Schlüssel ans Ende, genau dorthin, wo Excel sie ablegt

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

Um Excels Textordnung außerhalb einer Arbeitsmappe zu reproduzieren, ruft man CompareStringW mit LOCALE_USER_DEFAULT und NORM_IGNORECASE auf und lässt SORT_STRINGSORT und NORM_IGNOREWIDTH weg. Ein Blick auf den Rückgabewert lohnt sich. Die API liefert kein signiertes Ergebnis, sondern CSTR_LESS_THAN (1), CSTR_EQUAL (2) oder CSTR_GREATER_THAN (3), und 0, wenn der Aufruf scheitert. Abzüglich 2 ergibt das die gewohnte negativ / null / positiv-Konvention, aber erst, nachdem der 0-Fall behandelt wurde, sonst wird aus einem fehlgeschlagenen Aufruf -2, ein stilles „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;

Wenn HotXLS ohnehin in der uses-Klausel steht, ist lxStandard.XlsCompareText öffentlich und liefert dieselbe Ordnung. Die beiden unterscheiden sich nur beim fehlgeschlagenen API-Aufruf: Die Funktion oben wirft eine Exception, während XlsCompareText auf einen Vergleich der großgeschriebenen Strings nach Codeeinheit ausweicht, damit eine Neuberechnung nie mittendrin abbricht. Für eine eigenständige Sortierung ist das Werfen die bessere Wahl, denn eine still falsche Ordnung ist schwerer aufzuspüren als eine Exception

TArray.Sort ist nicht stabil, gleiche Schlüssel wie ab und AB können also in beliebiger Reihenfolge herauskommen. Wenn die ursprüngliche Reihenfolge gleicher Schlüssel zählt, sortiert man ein Index-Array und benutzt die ursprüngliche Position als Sekundärschlüssel

Manchmal soll eine Spalte Excels Ordnung bewusst ignorieren. Teilenummern sind der Klassiker: X-100 und X100 sind verschiedene Codes und sollten nach Codepunkt sortieren. SortRange hat einen Overload, der ein TXLSSortCompareEvent nimmt, deklariert als function(const Left, Right: Variant): Integer of object, und ihn an Stelle 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;

Bei einem eigenen Comparer reicht SortRange die rohen Schlüsselwerte durch und überlässt die Platzierung der Leerzellen einem selbst, genau deshalb kümmert sich der Comparer oben zuerst um Null. Für einen absteigenden Schlüssel negiert die Sortierung, was der Comparer liefert, in dieser Richtung rutschen die Leerzellen also an den Anfang, sofern der Comparer dem nicht Rechnung trägt. Eine so sortierte Spalte liegt nicht mehr in der Ordnung, die ein approximativer VLOOKUP oder ein Binary-Search-XLOOKUP erwartet; was in diesen Modi auf anders sortierten Daten schiefgeht, behandelt die Anleitung zu den Binary-Search-Modi von XLOOKUP und XMATCH

Warum kann dieselbe Arbeitsmappe auf einer anderen Maschine anders sortieren?

Eine 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, während Englisch und Deutsch es neben a behalten, eine von der Kollegin in Stockholm neu berechnete Datei kann also ein anderes COUNTIF(...,">y") liefern als dieselbe Datei auf einem Desktop in Chicago. HotXLS reicht LOCALE_USER_DEFAULT durch, damit seine Antworten auf derselben Maschine denen von Excel gleichen. Jede fest gepinnte Locale sähe ordentlicher aus und würde HotXLS auf jeder anders konfigurierten Maschine von Excel abweichen lassen

Für die serverseitige Generierung hat das drei praktische Konsequenzen:

  • Die Locale, die zählt, gehört zum Account, unter dem der Prozess läuft. Ein Windows-Dienst oder ein IIS-Application-Pool kann andere Regionaleinstellungen haben als der Entwickler-Desktop, was man in der IDE sieht, 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; Excel verhält sich bei seinen eigenen Dateien genauso
  • Locales uneinig sind sie vor allem bei akzentuierten Buchstaben, bei Buchstabenpaaren, die manche Sprachen als einen einzigen Buchstaben behandeln, und bei nicht-lateinischen Schriften. Testdaten aus schlichten englischen Wörtern decken den Unterschied nie auf, also ein paar Namen wie Ångström und Öberg in die Stichprobe

Der Excel-konforme Pfad ist der Windows-Pfad: Jeder Win32- und Win64-Build, ob kompiliert mit Delphi, C++Builder oder Lazarus und Free Pascal, ruft dasselbe CompareStringW auf. Zeigt ein Build auf eine Nicht-Windows-Plattform, nutzt XlsCompareText das WideCompareText der RTL, dessen Ordnung aus der Collation-Bibliothek der jeweiligen Plattform kommt

Gewöhnliche Wörter sortieren unter jeder Regel gleich, deshalb überlebt eine falsche Collation den Test oft, bis Teilenummern mit Bindestrich, Satzeichen oder akzentuierte Kundennamen auftauchen. HotXLS liefert Excels Antwort auf solche Eingaben in beiden Engines, XLS wie XLSX, von den Vergleichsoperatoren bis hin zu SortRange. Unterstützte Delphi- und C++Builder-Versionen, Lizenzierung und den Trial-Download listet die HotXLS-Delphi-Excel-Component-Seite