HotXLS-textjämförelse: Excels ordsortering i Delphi

HotXLS Delphi Component jämför två textvärden som Excel 16 gör sedan v2.384.67: skiftlägesokänsligt, i ordningsschemat "word sort" för Windows-användarens locale, vilket är vad CompareStringW returnerar med flaggan NORM_IGNORECASE. Bindestreck och apostrofer hoppas över i första passet och bryter bara oavgjort, så ="a-b">"ab" är TRUE, medan annat skiljetecken sorteras före siffror och bokstäver, så ="a~b"<"ab" också är TRUE. Samma ordning driver nu jämförelseoperatorerna, > / <-kriterierna, områdessorteringen och VLOOKUP

Ingen anmäler en bugg med rubriken "kollationen stämmer inte". Rapporterna säger att COUNTIF(A:A,">M") räknar två rader fler på servern än i Excel, att en prislista som rapporttjänsten sorterat lägger X-100 på en plats Excel inte skulle välja, eller att VLOOKUP("ABC",...) returnerar #N/A fastän kolumnen uppenbart innehåller abc. Alla tre kommer från samma fråga: när båda operanderna är text, vilken är mindre? Excel har ett precist svar, det är inte det som den mesta Delphi-koden ger, och före v2.384.67 gav HotXLS tre olika svar beroende på vilken kodväg som frågade

Vilken regel använder Excel för att jämföra två textsträngar?

Excel jämför text med användarens locales word sort, med skiftläget ignorerat. Word sort är standardkollationen hos Windows NLS-jämförelsefunktioner: bokstäver jämförs efter sin språkliga ordning i stället för sina kodpunkter, accenterade bokstäver ligger bredvid sin grundbokstav, och två tecken får särbehandling. Bindestrecket - och apostrofen ' ignoreras i första passet, så co-op och coop hamnar bredvid varandra, och först när resten av strängarna är lika avgör deras närvaro ordningen. Alla andra skiljetecken betyder något och sorteras före siffror, och siffror sorteras före bokstäver

Tabellen visar vad det betyder i praktiken, bredvid de två jämförelser en Delphi-utvecklare mest sannolikt sträcker sig efter. Excel-kolumnen innehåller de domar Excel 16 returnerade för IF(A<B,...), vilka HotXLS reproducerar sedan v2.384.67

A mot B	Excel 16 / HotXLS	CompareStr (ordinal)	CompareText
"a-b" mot "ab"	större	mindre	mindre
"a'b" mot "ab"	större	mindre	mindre
"a~b" mot "ab"	mindre	större	större
"a_b" mot "ab"	mindre	mindre	större
"ab" mot "AB"	lika	större	lika
"é" mot "f"	mindre	större	större
"Z" mot "f"	större	mindre	större

Två konsekvenser är lätta att missa. För det första betyder bindestreckets roll som oavgjort-brytare att ="a-b"="ab" är FALSE: strängarna är nära grannar i sorteringen men ändå inte lika. För det andra ignorerar likhet skiftläget helt, så ab, AB och Ab är samma nyckel så vitt någon jämförelse anbelangar. Sorterar man 20 testord med Excels Range.Sort blir resultatet 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; inom ab-gruppen avgör positionen för det ignorerade tecknet

Hur fastställdes Excels textordning?

Excels textordning identifierades genom mätning, inte genom dokumentation, för Excels dokumentation namnger inte kollationen. Testet genererade 4 000 slumpmässiga strängpar av ASCII-skiljetecken, siffror, båda skiftlägena, mellanslag, é, ß, ä, kinesiska tecken, fullbreddsformer och hårda mellanslag, med längder från 0 till 4 och där hälften av parens byggts som nästidentiska varianter av varandra. Excel 16 utvärderade IF(A<B,-1,IF(A=B,0,1)) för varje par, och domarna matchades mot Windows-jämförelse-API:et med olika flagguppsättningar

• NORM_IGNORECASE ensamt (standard word sort, användarens locale): ingen genuin avvikelse. De enda 7 skillnaderna var celler vars hela innehåll var ', vilket Excel konsumerar som textprefixtecknet, så de var urvalsartefakter snarare än kollationsskillnader
• NORM_IGNORECASE med SORT_STRINGSORT: 41 avvikelser. String sort behandlar bindestrecket och apostrofen som vanliga symboler, vilket är exakt det beteende Excel inte har
• Med tillägg av NORM_IGNOREWIDTH: fel på ett annat sätt, för det får fullbredds- och halvbreddsformerna av samma bokstav att jämföras som lika, och Excel håller dem isär

En andra, handplockad kontroll jämförde alla 190 par tagna från 20 kluriga ord och resultatet av Excels Range.Sort på samma kolumn. Båda stämde med ren NORM_IGNORECASE-word sort, och de 190 domarna plus sorteringsordningen är nu del av HotXLS regressionssvit, körd genom både den klassiska TXLSWorkbook-motorn och den XLSX-nativa TXLSXWorkbook-motorn

Varför får CompareText och ordinell jämförelse det fel?

CompareText och ordinell jämförelse får Excels ordning fel eftersom de jämför UTF-16-kodenheter, och kodpunktsordningen placerar skiljetecken på godtyckliga ställen i förhållande till bokstäver. Bindestrecket är U+002D och apostrofen U+0027, båda under varje bokstav, så en ordinell jämförelse kallar "a-b" mindre än "ab" i stället för att behandla bindestrecket som en oavgjort-brytare. Tilden U+007E ligger över varje bokstav, så "a~b" kommer ut som större, motsatsen mot Excel. CompareText i Delphi RTL versaliserar bara a..z och jämför sedan kodenheter, vilket lägger till en andra distorsion: understrecket U+005F ligger mellan versal- och gemenbokstäverna, så versalisering flyttar "a_b" från under "ab" till över det. Ingen av funktionerna vet att é hör hemma mellan e och f

De vanliga Delphi-verktygen hamnar på båda sidor om linjen:

• CompareStr, strängoperatorn < och TComparer<string>.Default (som anropar CompareStr) är ordinella och skiftlägeskänsliga, så TArray.Sort<string> utan en comparer lägger Z före f
• CompareText och SameText är ordinella efter versalisering som bara täcker ASCII
• AnsiCompareText och WideCompareText i Delphi RTL på Windows anropar CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), samma anrop som stämmer med Excel. En sorterad TStringList med sina standardvärden (UseLocale True, CaseSensitive False) går genom AnsiCompareText och håller därför med Excel också
• På POSIX-mål dirigerar Delphi RTL AnsiCompareText genom en ICU-kollator, en annan algoritm med andra skiljeteckensregler, och Free Pascals AnsiCompareText på Windows anropar CompareStringA efter konvertering till ANSI-kodsidan, vilket tappar varje tecken den sidan inte kan representera

Så de locale-medvetna RTL-funktionerna är rätt på Windows av implementation, inte av kontrakt, och kod som behöver Excels ordning gör bättre i att göra API-anropet explicit. HotXLS hade samma mix internt. Jämförelseoperatorerna versaliserade båda strängarna och jämförde kodpunkter, > / <-grenarna i kriteriefunktionerna använde Delphis skiftlägeskänsliga Variant-jämförelse, och VLOOKUP / HLOOKUP matchade text med den skiftlägeskänsliga Variant-jämförelsen också, vilket är därför VLOOKUP("ABC",A1:A20,1,FALSE) inte kunde hitta abc. Områdessorteringen använde redan WideCompareText. Tre vägar, tre ordningar

Vad ändrades i HotXLS v2.384.67?

Sedan v2.384.67 går text-mot-text-jämförelserna i HotXLS beräknings- och sorteringsvägar genom en funktion, XlsCompareText i lxStandard.pas, som anropar CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) och subtraherar CSTR_EQUAL. Anroparna är de sex jämförelseoperatorerna, elementvisa jämförelser i matrisformler, >-, <-, >=- och <=-grenarna hos kriterier i COUNTIF-stil och databasfunktionerna, VLOOKUP och HLOOKUP (exakt och approximativ), ordningshjälparna bakom de dynamiska matrisfunktionerna och XLOOKUP / XMATCH, och båda motorernas områdessortering. Att dirigera områdessorteringen genom samma funktion garanterar att sorteringsordning och jämförelseordning inte kan glida isär igen, vilket spelar roll, för approximativ VLOOKUP på text är bara meningsfull när kolumnen sorterats i den ordning som uppslaget jämför i

uses
  System.Variants, lxHandleX;

var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Sheets.Add('Data');  // Calculate utvärderar mot det aktiva bladet
    Writeln(VarToStr(Book.Calculate('="a-b">"ab"')));   // True: bindestrecket bryter bara oavgjort
    Writeln(VarToStr(Book.Calculate('="a-b"="ab"')));   // False: oavgjort avgjort, alltså inte lika
    Writeln(VarToStr(Book.Calculate('="a~b"<"ab"')));   // True: skiljetecken först
    Writeln(VarToStr(Book.Calculate('="ABC"="abc"')));  // True: skiftläget ignoreras
  finally
    Book.Free;
  end;
end;

Jämförelser mellan typer är en separat regel och ändrades inte: varje tal är under varje textvärde och varje textvärde är under varje booleskt värde, som beskrivs i artikeln om jämförelsekedjor, tomma operanden och SUMIF. Word sort gäller först när båda operanderna är text. Jokerteckensmatchning är också separat: ett kriterium som "a*" eller "=ab" är ett mönster- eller likhetstest, som tas upp i guiden till Excel-jokertecken i COUNTIF, MATCH och DSUM, och kollationen som diskuteras här avgör bara ordningsoperatorerna

Nästa exempel läser in de 20 testorden i en kolumn, sorterar den med TXLSXWorksheet.SortRange och kontrollerar ett kriterieantal och ett uppslag. Antalen är de som Excel 16 returnerade för samma kolumn

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 på samma kolumn: 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")')));

    // Var #N/A före v2.384.67: uppslaget jämförde skiftlägeskänsligt
    Sheet.Cells[1, 3].Formula := '=VLOOKUP("ABC",A1:A20,1,FALSE)';
    Book.Recalculate;
    Writeln(VarToStr(Sheet.Cells[1, 3].Value));                // abc

    // En nyckelkolumn, stigande: 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 använder en stabil merge sort, så ab, AB och Ab, som jämförs som lika, behåller den relativa ordningen de hade före sorteringen. Tomma celler går till slutet i båda riktningarna, som i Excel

Hur matchar jag Excels sorteringsordning i min egen Delphi-kod?

För att matcha Excels textordning i din egen Delphi-kod, anropa CompareStringW med LOCALE_USER_DEFAULT och NORM_IGNORECASE, och lägg inte till SORT_STRINGSORT eller NORM_IGNOREWIDTH. Returvärdet är inte ett teckensatt jämförelseresultat: API:et returnerar CSTR_LESS_THAN (1), CSTR_EQUAL (2) eller CSTR_GREATER_THAN (3), och 0 när anropet misslyckas. Subtrahera 2 för att få den vanliga negativ / noll / positiv-konventionen, och testa för 0 först, för ett fel som misstas för ett resultat blir -2, en tyst "mindre än"

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

// Excels textordning: word sort för användarens locale, skiftlägesokänsligt
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 är ett fel, inte ett jämförelseresultat
  Result := R - CSTR_EQUAL;    // 1/2/3 blir -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 (lika, valfri ordning), a-b, -ab, abc
end;

TArray.Sort är inte stabil, så nycklar som jämförs som lika, som ab och AB, kan komma ut i valfri ordning; spelar den ursprungliga ordningen för lika nycklar roll, sortera en indexmatris med den ursprungliga positionen som sekundär nyckel. Det omvända fallet dyker också upp: ibland ska en kolumn inte följa Excels ordning, till exempel artikelnamn där X-100 och X100 är skilda koder och bör sorteras efter kodpunkt. TXLSXWorksheet.SortRange har en överlagring som tar en TXLSSortCompareEvent, en metod med signaturen function(const Left, Right: Variant): Integer of object, och använder den i stället för den inbyggda jämförelsen

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
  // En anpassad comparer får också tomma celler (som Null): placera dem själv
  if VarIsNull(Left) or VarIsNull(Right) then
    Exit(Ord(VarIsNull(Left)) - Ord(VarIsNull(Right)));
  Result := CompareStr(VarToStr(Left), VarToStr(Right));   // ordinal, skiftlägeskänslig
end;

var
  Sheet: TXLSXWorksheet;   // ett ifyllt blad, rader 2..501, kolumner A..D
  Order: TPartNumberOrder;
begin
  // ...
  Order := TPartNumberOrder.Create;
  try
    // nycklad på kolumn A, stigande
    Sheet.SortRange(2, 1, 501, 4, [1], [False], xlsSortByRows,
      xlsSortExcelLike, Order.Compare);
  finally
    Order.Free;
  end;
end;

Levereras en anpassad comparer hoppar HotXLS över sin egen tomcellshantering och skickar vidare råa nyckelvärden, så comparern måste hantera Null. För en fallande nyckel negerar HotXLS vad comparern returnerar, vilket också flytter tomma celler till toppen om inte comparern tar hänsyn till det. Tänk på att en kolumn sorterad så här inte längre ligger i den ordning Excels approximativa VLOOKUP eller en binärsökande XLOOKUP förväntar; fallgroparna för de lägena på data sorterade i en annan ordning tas upp i guiden till XLOOKUP- och XMATCH-binärsöklägen

Varför kan samma arbetsbok sorteras olika på en annan maskin?

Samma arbetsbok kan sorteras olika på en annan maskin för att Excels textordning beror på Windows-användarens locale, och HotXLS följer medvetet det beroendet. Word sort är språkspecifikt: den svenska kollationen placerar till exempel ä efter z, där engelskan och tyskan behåller den bredvid a. Excel ärver det från den locale det kör under, så en arbetsbok som en Stockholmskollega räknat om kan returnera ett annat COUNTIF(...,">y") än samma fil på en skrivbordsdator i Chicago. HotXLS skickar LOCALE_USER_DEFAULT så att dess resultat blir lika med Excels på samma maskin; vilken fast locale som helst skulle få HotXLS att avvika från Excel på varje maskin med en annan inställning

Tre praktiska konsekvenser följer för generering på serversidan:

• Den locale som spelar roll är den för kontot processen kör under. En Windows-tjänst eller IIS-programpool kan använda ett annat regionformat än utvecklarens skrivbord, så resultat observerade i IDE:n är inte automatiskt det som produktionen beräknar
• Cachade formelresultat skrivna in i filen speglar den genererande maskinens locale. Excel räknar om med sin egen locale, så ett värde kan ändras när filen öppnas någon annanstans och räknas om; det är Excels beteende, inte en HotXLS-artefakt
• Locales skiljer sig mest på accenterade bokstäver, på bokstavskombinationer som vissa språk behandlar som en enskild bokstav, och på icke-latinska skrifter, så testdata begränsade till vanliga engelska ord kommer inte att avslöja problemet

Plattformgränsen är enkel. HotXLS är ett Windows-bibliotek, byggt för Win32 och Win64 med Delphi och C++Builder och för win32 / win64-mål med Lazarus och Free Pascal, och alla de byggena anropar samma CompareStringW. Det finns ingen separat icke-Windows-kollationsväg. Den enda fallbacken är ett misslyckat API-anrop: returnerar CompareStringW 0 jämför XlsCompareText de versaliserade strängarna per kodenhet i stället för att kasta ett undantag mitt i en omräkning, vilket håller beräkningen igång men inte längre garanterar Excels ordning

Snabbreferens: textjämförelse enligt Excel i HotXLS

• Regel: word sort för användarens locale med NORM_IGNORECASE, ingen SORT_STRINGSORT, ingen NORM_IGNOREWIDTH, i HotXLS sedan v2.384.67
• - och ' bryter bara oavgjort: ="a-b">"ab" är TRUE och ="a-b"="ab" är FALSE
• Annat skiljetecken sorteras före siffror, siffror före bokstäver: ="a~b"<"ab" och ="a0"<"ab" är TRUE
• Skiftläget spelar aldrig roll: ="ABC"="abc" är TRUE och VLOOKUP("ABC",...) hittar abc
• Täckta vägar: jämförelseoperatorer, matrisjämförelser, > / <-kriterier, VLOOKUP / HLOOKUP, ordning för dynamiska matriser, SortRange i båda motorerna
• Inte täckt av den här regeln: blandade typer (tal < text < booleskt) och jokerteckenkriterier, som har egna regler
• I Delphi-kod: CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), kontrollera 0, subtrahera CSTR_EQUAL; undvik CompareText, CompareStr och TComparer<string>.Default när resultatet måste stämma med Excel
• Resultaten beror på kontots locale som kör koden, i Excel likväl som i HotXLS

Vanliga ord sorteras likadant under varje regel, så bara bindestreckskoder, skiljetecken och accenterade namn avslöjar en felaktig kollation. HotXLS ger nu Excels svar på alla dem i både XLS- och XLSX-motorerna. Detaljer om licensiering, stödda Delphi- och C++Builder-versioner och nedladdningen av testversionen finns på sidan för HotXLS Delphi Excel component