Confronto testo in HotXLS: word sort di Excel in Delphi

HotXLS Delphi Component confronta due valori testuali come fa Excel 16 dalla v2.384.67: senza distinguere maiuscole e minuscole, nell'ordine "word sort" del locale utente Windows, che è ciò che CompareStringW restituisce con il flag NORM_IGNORECASE. Trattini e apostrofi vengono saltati al primo passaggio e rompono solo i pareggi, quindi ="a-b">"ab" è TRUE, mentre l'altra punteggiatura ordina prima delle cifre e delle lettere, così ="a~b"<"ab" è TRUE anch'esso. Lo stesso ordine ora governa gli operatori di confronto, i criteri > / <, l'ordinamento degli intervalli e VLOOKUP

Nessuno apre un bug intitolato "mancata combacianza della collation". Le segnalazioni dicono che COUNTIF(A:A,">M") conta due righe in più sul server che in Excel, che un listino prezzi ordinato dal servizio di reporting mette X-100 dove Excel non lo metterebbe, o che VLOOKUP("ABC",...) restituisce #N/A benché la colonna contenga chiaramente abc. Tutte e tre vengono dalla stessa domanda: quando entrambi gli operandi sono testo, quale dei due è più piccolo? Excel ha una risposta precisa, non è quella che la maggior parte del codice Delphi dà, e prima della v2.384.67 HotXLS dava tre risposte diverse a seconda del percorso di codice che chiedeva

Quale regola usa Excel per confrontare due stringhe di testo?

Excel confronta il testo con il word sort del locale utente, ignorando il maiuscolo/minuscolo. Il word sort è la collation predefinita delle funzioni di confronto NLS di Windows: le lettere si confrontano per il loro ordine linguistico anziché per i loro code point, le lettere accentate stanno accanto alla loro lettera base, e due caratteri ricevono un trattamento speciale. Il trattino - e l'apostrofo ' vengono ignorati al primo passaggio, quindi co-op e coop atterrano uno accanto all'altro, e solo quando il resto delle stringhe pareggia la loro presenza decide l'ordine. Ogni altro segno di punteggiatura è significativo e ordina prima delle cifre, e le cifre ordinano prima delle lettere

La tabella mostra cosa significa in pratica, accanto ai due confronti a cui uno sviluppatore Delphi arriva più probabilmente per primo. La colonna Excel contiene i verdetti che Excel 16 ha restituito per IF(A<B,...), che HotXLS riproduce dalla v2.384.67

A vs B	Excel 16 / HotXLS	CompareStr (ordinale)	CompareText
"a-b" vs "ab"	maggiore	minore	minore
"a'b" vs "ab"	maggiore	minore	minore
"a~b" vs "ab"	minore	maggiore	maggiore
"a_b" vs "ab"	minore	minore	maggiore
"ab" vs "AB"	uguale	maggiore	uguale
"é" vs "f"	minore	maggiore	maggiore
"Z" vs "f"	maggiore	minore	maggiore

Due conseguenze sono facili da perdere. Primo, il ruolo di spareggio del trattino significa che ="a-b"="ab" è FALSE: le stringhe sono vicine di casa nell'ordinamento, eppure non sono uguali. Secondo, l'uguaglianza ignora completamente il maiuscolo/minuscolo, quindi ab, AB e Ab sono la stessa chiave per quanto riguarda qualsiasi confronto. Ordinando le 20 parole di test con Range.Sort di Excel si ottiene 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; dentro il gruppo ab, la posizione del carattere ignorato decide

Come è stato fissato l'ordine testuale di Excel?

L'ordine testuale di Excel è stato individuato per misura, non per documentazione, perché la documentazione di Excel non nomina la collation. Il test ha generato 4.000 coppie di stringhe casuali da punteggiatura ASCII, cifre, entrambi i casi delle lettere, spazi, é, ß, ä, caratteri cinesi, forme full-width e spazio unificatore, con lunghezze da 0 a 4 e metà delle coppie costruite come quasi-riscontri l'una dell'altra. Excel 16 ha valutato IF(A<B,-1,IF(A=B,0,1)) per ogni coppia, e i verdetti sono stati incrociati con l'API di confronto Windows con diversi insiemi di flag

• NORM_IGNORECASE da solo (word sort predefinito, locale utente): nessun disallineamento genuino. Le uniche 7 differenze erano celle il cui intero contenuto era ', che Excel consuma come carattere prefisso di testo, quindi erano artefatti di campionamento anziché differenze di collation
• NORM_IGNORECASE con SORT_STRINGSORT: 41 disallineamenti. Lo string sort tratta il trattino e l'apostrofo come simboli ordinari, che è esattamente il comportamento che Excel non ha
• Aggiungere NORM_IGNOREWIDTH: sbagliato in modo diverso, perché rende uguali al confronto le forme full-width e half-width della stessa lettera, ed Excel le tiene distinte

Un secondo controllo, scelto a mano, ha confrontato tutte le 190 coppie estratte da 20 parole insidiose con il risultato di Range.Sort di Excel sulla stessa colonna. Entrambi sono andati d'accordo con il semplice word sort NORM_IGNORECASE, e quei 190 verdetti più l'ordinamento ottenuto sono ora parte della suite di regressioni HotXLS, fatta girare sia sul motore classico TXLSWorkbook sia sul motore nativo XLSX TXLSXWorkbook

Perché CompareText e il confronto ordinale sbagliano?

CompareText e il confronto ordinale prendono l'ordine di Excel per il fianco sbagliato perché confrontano code unit UTF-16, e l'ordine per code point mette la punteggiatura in posti arbitrari rispetto alle lettere. Il trattino è U+002D e l'apostrofo U+0027, entrambi sotto ogni lettera, quindi un confronto ordinale dichiara "a-b" più piccolo di "ab" invece di trattare il trattino come spareggio. La tilde U+007E sta sopra ogni lettera, quindi "a~b" esce più grande, il contrario di Excel. CompareText nella RTL Delphi riporta solo a..z a maiuscolo e poi confronta code unit, il che aggiunge una seconda distorsione: l'underscore U+005F sta tra le lettere maiuscole e minuscole, quindi il riporto al maiuscolo sposta "a_b" da sotto "ab" a sopra di essa. Nessuna delle due funzioni sa che é sta tra e e f

I soliti strumenti Delphi cadono da entrambi i lati della linea:

• CompareStr, l'operatore stringa < e TComparer<string>.Default (che chiama CompareStr) sono ordinali e sensibili al maiuscolo/minuscolo, quindi TArray.Sort<string> senza un comparer mette Z prima di f
• CompareText e SameText sono ordinali dopo un ripporto al maiuscolo solo ASCII
• AnsiCompareText e WideCompareText nella RTL Delphi su Windows chiamano CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), la stessa chiamata che combacia con Excel. Una TStringList ordinata con i suoi default (UseLocale True, CaseSensitive False) passa per AnsiCompareText e quindi va d'accordo anche con Excel
• Sui target POSIX la RTL Delphi instrada AnsiCompareText attraverso un collator ICU, che è un algoritmo diverso con regole di punteggiatura diverse, e l'AnsiCompareText di Free Pascal su Windows chiama CompareStringA dopo la conversione alla code page ANSI, che perde qualsiasi carattere quella pagina non possa rappresentare

Quindi le funzioni RTL consapevoli del locale vanno bene su Windows per implementazione, non per contratto, e il codice che ha bisogno dell'ordine di Excel fa meglio a fare la chiamata API esplicitamente. Anche HotXLS aveva lo stesso mix internamente. Gli operatori di confronto riportavano entrambe le stringhe al maiuscolo e confrontavano code point, i rami > / < delle funzioni di criteri usavano il confronto Variant di Delphi sensibile al maiuscolo/minuscolo, e VLOOKUP / HLOOKUP confrontavano il testo con quel confronto Variant sensibile al maiuscolo/minuscolo, ecco perché VLOOKUP("ABC",A1:A20,1,FALSE) non poteva trovare abc. L'ordinamento degli intervalli usava già WideCompareText. Tre percorsi, tre ordini

Che cosa è cambiato in HotXLS nella v2.384.67?

Dalla v2.384.67 i confronti testo-contro-testo nei percorsi di calcolo e ordinamento HotXLS passano per un'unica funzione, XlsCompareText in lxStandard.pas, che chiama CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) e sottrae CSTR_EQUAL. I chiamanti sono i sei operatori di confronto, i confronti elemento per elemento nelle formule array, i rami >, <, >= e <= dei criteri in stile COUNTIF e delle funzioni di database, VLOOKUP e HLOOKUP (esatto e approssimato), gli helper di ordinamento dietro le funzioni dynamic array e XLOOKUP / XMATCH, e l'ordinamento degli intervalli di entrambi i motori. Instradare l'ordinamento degli intervalli attraverso la stessa funzione garantisce che ordine di ordinamento e ordine di confronto non possano più divergere, il che conta perché il VLOOKUP approssimato su testo ha senso solo quando la colonna era stata ordinata nell'ordine in cui il lookup confronta

uses
  System.Variants, lxHandleX;

var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Sheets.Add('Data');  // Calculate valuta sul foglio attivo
    Writeln(VarToStr(Book.Calculate('="a-b">"ab"')));   // True: il trattino rompe solo i pareggi
    Writeln(VarToStr(Book.Calculate('="a-b"="ab"')));   // False: pareggio rotto, non uguali
    Writeln(VarToStr(Book.Calculate('="a~b"<"ab"')));   // True: punteggiatura prima
    Writeln(VarToStr(Book.Calculate('="ABC"="abc"')));  // True: maiuscole ignorate
  finally
    Book.Free;
  end;
end;

I confronti tra tipi diversi sono una regola a parte e non sono cambiati: ogni numero sta sotto ogni valore testuale e ogni valore testuale sta sotto ogni booleano, come descritto in l'articolo su catene di confronto, operandi vuoti e SUMIF. Il word sort si applica solo quando entrambi gli operandi sono testo. Anche la corrispondenza con wildcard è a parte: un criterio come "a*" o "=ab" è un pattern o un test di uguaglianza, coperto in la guida alle wildcard Excel in COUNTIF, MATCH e DSUM, e la collation discussa qui decide solo gli operatori di ordinamento

L'esempio successivo carica le 20 parole di test in una colonna, la ordina con TXLSXWorksheet.SortRange, e verifica un conteggio per criteri e un lookup. I conteggi sono quelli che Excel 16 ha restituito per la stessa colonna

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 sulla stessa colonna: 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")')));

    // Era #N/A prima della v2.384.67: il lookup confrontava col maiuscolo sensibile
    Sheet.Cells[1, 3].Formula := '=VLOOKUP("ABC",A1:A20,1,FALSE)';
    Book.Recalculate;
    Writeln(VarToStr(Sheet.Cells[1, 3].Value));                // abc

    // Una colonna chiave, crescente: 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 usa un merge sort stabile, quindi ab, AB e Ab, che confrontano uguali, conservano l'ordine relativo che avevano prima dell'ordinamento. Le celle vuote vanno in fondo in entrambe le direzioni, come in Excel

Come riproduco l'ordine di ordinamento di Excel nel mio codice Delphi?

Per riprodurre l'ordine testuale di Excel nel tuo codice Delphi, chiama CompareStringW con LOCALE_USER_DEFAULT e NORM_IGNORECASE, e non aggiungere SORT_STRINGSORT né NORM_IGNOREWIDTH. Il valore restituito non è un risultato di confronto con segno: l'API restituisce CSTR_LESS_THAN (1), CSTR_EQUAL (2) o CSTR_GREATER_THAN (3), e 0 quando la chiamata fallisce. Sottrai 2 per avere la consueta convenzione negativo / zero / positivo, e testa prima lo 0, perché un fallimento scambiato per un risultato diventa -2, un silenzioso "minore"

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

// L'ordine testuale di Excel: word sort del locale utente, senza maiuscole sensibili
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 è un fallimento, non un risultato di confronto
  Result := R - CSTR_EQUAL;    // 1/2/3 diventano -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 (uguali, in qualsiasi ordine), a-b, -ab, abc
end;

TArray.Sort non è stabile, quindi le chiavi che confrontano uguali, come ab e AB, possono uscire in qualsiasi ordine; se l'ordine originale delle chiavi uguali conta, ordina un array di indici con la posizione originale come chiave secondaria. Vale anche il caso opposto: a volte una colonna non deve seguire l'ordine di Excel, per esempio i codici parte dove X-100 e X100 sono codici distinti e devono ordinare per code point. TXLSXWorksheet.SortRange ha un overload che prende un TXLSSortCompareEvent, un metodo con la firma function(const Left, Right: Variant): Integer of object, e lo usa al posto del confronto integrato

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 comparer personalizzato riceve anche celle vuote (come Null): disponile tu
  if VarIsNull(Left) or VarIsNull(Right) then
    Exit(Ord(VarIsNull(Left)) - Ord(VarIsNull(Right)));
  Result := CompareStr(VarToStr(Left), VarToStr(Right));   // ordinale, col maiuscolo sensibile
end;

var
  Sheet: TXLSXWorksheet;   // un foglio riempito, righe 2..501, colonne A..D
  Order: TPartNumberOrder;
begin
  // ...
  Order := TPartNumberOrder.Create;
  try
    // con chiave sulla colonna A, crescente
    Sheet.SortRange(2, 1, 501, 4, [1], [False], xlsSortByRows,
      xlsSortExcelLike, Order.Compare);
  finally
    Order.Free;
  end;
end;

Quando viene fornito un comparer personalizzato, HotXLS salta la propria gestione dei vuoti e passa i valori chiave grezzi, quindi il comparer deve saper gestire Null. Per una chiave decrescente HotXLS ribalta di segno ciò che il comparer restituisce, il che sposta anche i vuoti in cima a meno che il comparer non ne tenga conto. Ricorda che una colonna ordinata in questo modo non è più nell'ordine che il VLOOKUP approssimato di Excel o un XLOOKUP a ricerca binaria si aspettano; le insidie di quelle modalità su dati ordinati in un ordine diverso sono coperte in la guida alle modalità di ricerca binaria di XLOOKUP e XMATCH

Perché la stessa cartella di lavoro può ordinare diversamente su un'altra macchina?

La stessa cartella di lavoro può ordinare diversamente su un'altra macchina perché l'ordine testuale di Excel dipende dal locale utente Windows, e HotXLS segue deliberatamente quella dipendenza. Il word sort è specifico per lingua: la collation svedese, per esempio, colloca ä dopo z, dove l'inglese e il tedesco la tengono accanto alla a. Excel lo eredita dal locale sotto cui gira, quindi una cartella di lavoro ricalcolata da un collega di Stoccolma può restituire un COUNTIF(...,">y") diverso dallo stesso file su un desktop di Chicago. HotXLS passa LOCALE_USER_DEFAULT così che i suoi risultati eguagliano quelli di Excel sulla stessa macchina; qualsiasi locale fisso farebbe divergere HotXLS ed Excel su ogni macchina con un'impostazione diversa

Tre conseguenze pratiche discendono per la generazione lato server:

• Il locale che conta è quello dell'account sotto cui gira il processo. Un servizio Windows o un application pool IIS può usare formati regionali diversi dal desktop dello sviluppatore, quindi i risultati osservati nell'IDE non sono automaticamente ciò che la produzione calcola
• I risultati delle formule in cache scritti nel file riflettono il locale della macchina che genera. Excel ricalcola con il proprio locale, quindi un valore può cambiare quando il file viene aperto altrove e ricalcolato; è il comportamento di Excel, non un artefatto HotXLS
• I locali divergono soprattutto sulle lettere accentate, sulle combinazioni di lettere che alcune lingue trattano come un'unica lettera, e sulle scritture non latine, quindi dati di test limitati a parole inglesi semplici non rivelano il problema

Il confine di piattaforma è semplice. HotXLS è una libreria Windows, costruita per Win32 e Win64 con Delphi e C++Builder e per i target win32 / win64 con Lazarus e Free Pascal, e tutte queste build chiamano la stessa CompareStringW. Non esiste un percorso di collation separato per il non-Windows. L'unico fallback è per una chiamata API fallita: se CompareStringW restituisce 0, XlsCompareText confronta le stringhe riportate al maiuscolo per code unit anziché sollevare un'eccezione in mezzo a un ricalcolo, il che tiene vivo il calcolo ma non garantisce più l'ordine di Excel

Riferimento rapido: confronto testuale Excel in HotXLS

• Regola: word sort del locale utente con NORM_IGNORECASE, niente SORT_STRINGSORT, niente NORM_IGNOREWIDTH, in HotXLS dalla v2.384.67
• - e ' rompono solo i pareggi: ="a-b">"ab" è TRUE e ="a-b"="ab" è FALSE
• L'altra punteggiatura ordina prima delle cifre, le cifre prima delle lettere: ="a~b"<"ab" e ="a0"<"ab" sono TRUE
• Il maiuscolo/minuscolo non conta mai: ="ABC"="abc" è TRUE e VLOOKUP("ABC",...) trova abc
• Percorsi coperti: operatori di confronto, confronti array, criteri > / <, VLOOKUP / HLOOKUP, ordinamento dynamic array, SortRange in entrambi i motori
• Non coperti da questa regola: tipi misti (numero < testo < booleano) e criteri wildcard, che hanno le loro regole
• Nel codice Delphi: CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), controlla lo 0, sottrai CSTR_EQUAL; evita CompareText, CompareStr e TComparer<string>.Default quando il risultato deve andare d'accordo con Excel
• I risultati dipendono dal locale dell'account che esegue il codice, in Excel e in HotXLS allo stesso modo

Le parole comuni ordinano allo stesso modo sotto ogni regola, quindi solo i codici con trattini, la punteggiatura e i nomi accentati espongono una collation sbagliata. HotXLS ora dà la risposta di Excel su tutti in entrambi i motori XLS e XLSX. I dettagli su licenze, versioni Delphi e C++Builder supportate e download di prova sono sulla pagina del componente Excel Delphi HotXLS