Articolo tecnico

Confronto testo in HotXLS: word sort di Excel in Delphi

In Excel ="a-b">"ab" restituisce TRUE, e ="a~b"<"ab" restituisce TRUE anch'esso. Passate le stesse coppie al CompareText di Delphi e entrambe le risposte si invertono, perché il trattino sta sotto le lettere nell'ordine dei code point e la tilde sta sopra. Excel confronta il testo con il "word sort" case-insensitive del locale utente Windows, dove trattini e apostrofi servono solo a spareggiare e ogni altro segno di punteggiatura ordina prima delle cifre e delle lettere

I sintomi parlano raramente di collation. Una COUNTIF(A:A,">M") calcolata sul server conta due righe in più della stessa formula in Excel. Un listino prezzi ordinato da un servizio di reporting mette X-100 in una posizione dove Excel non lo metterebbe mai. VLOOKUP("ABC",...) restituisce #N/A anche se la colonna contiene chiaramente abc. Tutti e tre i casi si riducono a una domanda: quando entrambi gli operandi sono testo, quale dei due è minore? Excel ha una risposta precisa, e quasi mai è quella che il codice Delphi dà per impostazione predefinita

Quale regola usa Excel per confrontare due stringhe di testo?

Excel confronta due valori testuali con il word sort del locale utente Windows corrente, ignorando il maiuscolo/minuscolo. Il word sort è la collation predefinita delle funzioni di confronto NLS di Windows: le lettere si confrontano in ordine linguistico e non per code point, così é cade tra e e f, e maiuscole e minuscole si sovrappongono. Due caratteri ricevono un trattamento speciale. Il trattino - e l'apostrofo ' vengono saltati al primo passaggio, il che mette co-op accanto a coop; solo quando i caratteri rimanenti pareggiano la loro presenza, e la loro posizione, decide l'ordine. Ogni altro segno di punteggiatura conta a pieno peso e ordina prima delle cifre, e le cifre ordinano prima delle lettere

La tabella mette a confronto il verdetto di Excel per IF(A<B,...) con i due confronti che uno sviluppatore Delphi scrive per primo

A vs BExcel / HotXLSCompareStr (ordinale)CompareText
"a-b" vs "ab"maggioreminoreminore
"a'b" vs "ab"maggioreminoreminore
"a~b" vs "ab"minoremaggioremaggiore
"a_b" vs "ab"minoreminoremaggiore
"ab" vs "AB"ugualemaggioreuguale
"é" vs "f"minoremaggioremaggiore
"Z" vs "f"maggioreminoremaggiore

Due conseguenze prendono in contropiede. Poiché il trattino serve solo a spareggiare, ="a-b"="ab" è FALSE: le due stringhe sono vicine di casa in un ordinamento e restano comunque diverse. E poiché il maiuscolo/minuscolo sparisce del tutto, ab, AB e Ab sono un'unica chiave per ogni operatore di confronto. Con un locale utente inglese, una colonna di 20 parole insidiose si ordina in 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 decide la posizione del carattere saltato: trattino finale prima, trattino iniziale per ultimo

Diagramma word sort di HotXLS che classifica le 20 parole di test da a b, a.b, a_b e a~b passando per a0 e a1b, poi il gruppo ab con AB e Ab, le varianti con trattino e apostrofo come a-b e a'b, fino a abc, b, e, é, f e Z, con punteggiatura prima delle cifre e cifre prima delle lettere e maiuscolo/minuscolo ignorato
La punteggiatura e lo spazio ordinano prima delle cifre e le cifre prima delle lettere, il maiuscolo/minuscolo sparisce, e trattino e apostrofo servono solo a spareggiare; ecco perché a-b cade accanto ad ab e resta comunque maggiore

Verificare la collation contro Excel di persona

La documentazione di Excel non nomina mai la collation che usa, quindi il modo affidabile per confermarla sulla tua macchina è far giudicare a Excel un lotto di coppie di stringhe e confrontare i suoi verdetti con CompareStringW sotto diverse combinazioni di flag. Metti le coppie in due colonne, fai valutare a Excel =IF(A1<B1,-1,IF(A1=B1,0,1)) per ogni riga, e passa le stesse coppie all'API. Le coppie che vale la pena generare mescolano punteggiatura ASCII, cifre, entrambi i casi delle lettere, spazi, lettere accentate come é, ß e ä, caratteri CJK, forme full-width e lo spazio unificatore, con parecchi quasi-scontri che differiscono per un solo carattere. Parole a caso prese da un dizionario sono un campione povero, perché le lettere semplici ordinano allo stesso modo sotto ogni regola trattata in questo articolo

Le combinazioni di flag portano a risultati chiaramente diversi:

  • NORM_IGNORECASE da solo, con LOCALE_USER_DEFAULT, è il word sort puro e quello che Excel segue
  • Aggiungere SORT_STRINGSORT passa allo string sort, dove il trattino e l'apostrofo diventano simboli ordinari a pieno peso, così ogni coppia a-b / ab esce al contrario
  • Aggiungere NORM_IGNOREWIDTH rende una lettera full-width uguale alla sua forma half-width, e Excel invece le tiene distinte

Una trappola sta nell'harness di test stesso. Excel tratta l'apostrofo iniziale digitato in una cella come prefisso "memorizza come testo", quindi una cella il cui unico contenuto è ' finisce vuota, e ogni coppia costruita così riporta un mismatch che con la collation non c'entra nulla. Costruisci queste coppie con letterali stringa nella formula (="'"), oppure saltale

Perché CompareText e il confronto ordinale sbagliano?

CompareText e il confronto ordinale perdono l'ordine di Excel perché confrontano unità di codice UTF-16, e l'ordine per code point sparge la punteggiatura attorno all'alfabeto. Il trattino è U+002D e l'apostrofo U+0027, entrambi sotto ogni lettera, quindi un confronto ordinale dichiara "a-b" minore di "ab" dove Excel usa il trattino come spareggio. La tilde è U+007E, sopra ogni lettera, il che rende "a~b" maggiore, il contrario di Excel. CompareText aggiunge una seconda distorsione tutta sua: ripiega solo a..z in maiuscolo e poi confronta unità di codice, e poiché l'underscore (U+005F) sta tra l'intervallo maiuscolo e quello minuscolo, il ripiegamento sposta "a_b" da sotto "ab" a sopra. Nessuna delle due funzioni ha la minima idea che é stia tra e e f

Diagramma di confronto HotXLS che contrappone l'ordine per code point al word sort di Excel: il confronto ordinale mette apostrofo, trattino e underscore a 0x27, 0x2D e 0x5F attorno alle lettere, così a-b contro ab esce minore, mentre il word sort sposta la punteggiatura prima delle cifre e delle lettere e usa solo trattino e apostrofo come spareggi
I code point spargono la punteggiatura attorno alle lettere, così i confronti ordinali e con ripiegamento ASCII invertono i verdetti; il word sort sposta la punteggiatura davanti alle cifre e degrada trattino e apostrofo a semplici spareggi

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

  • CompareStr, l'operatore < su stringhe e TComparer<string>.Default sono ordinali e case-sensitive, così TArray.Sort<string> senza comparer mette Z prima di f
  • CompareText e SameText sono ordinali dopo il ripiegamento del solo ASCII
  • AnsiCompareText e WideCompareText nella RTL Delphi su Windows chiamano CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), la chiamata che combacia con Excel. Una TStringList ordinata con le impostazioni predefinite (UseLocale True, CaseSensitive False) passa per AnsiCompareText e combacia con Excel anche lei
  • Sui target POSIX la RTL Delphi instrada AnsiCompareText attraverso un collatore ICU, un algoritmo diverso con regole di punteggiatura diverse. AnsiCompareText di Free Pascal su Windows converte alla code page ANSI e chiama CompareStringA, che perde ogni carattere che quella code page non sa rappresentare

Quindi le funzioni RTL consapevoli del locale vanno bene su Windows per come sono implementate oggi, non per contratto. Il codice che deve combaciare con Excel dovrebbe fare la chiamata API in modo esplicito e dichiararlo. Il problema più grosso affiora in qualsiasi motore di formule dove ogni percorso di codice prende il confronto più a portata di mano: maiuscolo e confronto per code point negli operatori = e <, un confronto Variant case-sensitive nel ramo > / < dei criteri di COUNTIF, un altro dentro VLOOKUP (ed è esattamente così che VLOOKUP("ABC",A1:A20,1,FALSE) non trova abc), e una chiamata al locale nell'ordinamento dell'intervallo. Preso singolarmente ogni percorso si può difendere. Insieme producono tre ordini diversi sulla stessa colonna

Un solo confronto dietro formule, lookup e ordinamento in HotXLS

HotXLS instrada ogni confronto testo-contro-testo nel codice di calcolo e ordinamento attraverso un'unica funzione, XlsCompareText in lxStandard.pas, che chiama CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) e sottrae CSTR_EQUAL. La usano i sei operatori di confronto, i confronti elemento per elemento nelle formule di matrice, i rami >, <, >= e <= dei criteri in stile COUNTIF e le funzioni di database, VLOOKUP e HLOOKUP in modo esatto e approssimato, l'ordinamento dietro le funzioni a matrice dinamica e XLOOKUP / XMATCH, e SortRange sia su TXLSWorksheet che su TXLSXWorksheet. Mettere l'ordinamento dell'intervallo sulla stessa funzione è stato il punto di design decisivo. Un VLOOKUP approssimato sul testo dà risposte sensate solo se la colonna era ordinata nell'ordine in cui il lookup confronta, e condividere una funzione rende impossibile che quei due ordini divergano

Diagramma di instradamento HotXLS che mostra ogni percorso di confronto testuale, dai sei operatori di confronto e dai criteri in stile COUNTIF fino a VLOOKUP, HLOOKUP, XLOOKUP e all'ordinamento dell'intervallo di entrambi i motori, convergendo su XlsCompareText, che chiama CompareStringW con LOCALE_USER_DEFAULT e NORM_IGNORECASE e mappa 1, 2, 3 in -1, 0, 1
Operatori, criteri, lookup e ordinamento condividono una funzione, quindi l'ordine che vede Excel e quello con cui ordina HotXLS non possono divergere; l'API restituisce 1, 2 o 3, e zero significa fallimento, non minore di

Il modo più rapido per vedere la regola da Delphi è TXLSXWorkbook.Calculate, che valuta una stringa di formula sul foglio attivo e restituisce il risultato come Variant

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 serve solo a spareggiare
    Writeln(VarToStr(Book.Calculate('="a-b"="ab"')));   // False: spareggio risolto, non uguali
    Writeln(VarToStr(Book.Calculate('="a~b"<"ab"')));   // True: prima la punteggiatura
    Writeln(VarToStr(Book.Calculate('="ABC"="abc"')));  // True: maiuscolo/minuscolo ignorato
  finally
    Book.Free;
  end;
end;

Il word sort si applica solo quando entrambi gli operandi sono testo. I tipi misti seguono una gerarchia fissa in cui 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. I criteri con caratteri jolly come "a*" o "=ab" sono test di pattern e di uguaglianza con regole loro, trattati nella guida ai caratteri jolly di Excel in COUNTIF, MATCH e DSUM; la collation di cui qui si parla decide gli operatori di ordinamento

L'esempio successivo carica le 20 parole nella colonna A di un foglio, controlla tre conteggi con criteri e un lookup, poi ordina la colonna con TXLSXWorksheet.SortRange. I conteggi attesi discendono direttamente dall'ordine mostrato prima: undici parole ordinano dopo il gruppo ab, undici prima di a-b, e i tre membri del gruppo ab si aggiungono agli undici per >=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]);

    // Attesi dall'ordine 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")')));

    // Trova abc: il lookup confronta ignorando il maiuscolo/minuscolo, come Excel
    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;

SortRange usa un merge sort stabile, così ab, AB e Ab, che confrontano uguali, conservano l'ordine relativo che avevano prima dell'ordinamento. Le celle vuote vanno in fondo sia con chiave crescente che decrescente, che è dove le mette Excel

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

Per riprodurre l'ordine testuale di Excel fuori da una cartella di lavoro, chiama CompareStringW con LOCALE_USER_DEFAULT e NORM_IGNORECASE, e tralascia SORT_STRINGSORT e NORM_IGNOREWIDTH. Attenzione al valore di ritorno. L'API non restituisce un risultato con segno; restituisce CSTR_LESS_THAN (1), CSTR_EQUAL (2) o CSTR_GREATER_THAN (3), e 0 quando la chiamata fallisce. Sottraendo 2 si riottiene la consueta convenzione negativo / zero / positivo, ma solo dopo aver gestito il caso 0, altrimenti una chiamata fallita diventa -2 e si legge come "minore di"

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

// L'ordine testuale di Excel: word sort del locale utente, senza distinzione maiuscole/minuscole
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;

Se HotXLS è già nella uses, lxStandard.XlsCompareText è pubblico e restituisce lo stesso ordine. Le due funzioni differiscono solo su una chiamata API fallita: la funzione qui sopra lancia un'eccezione, mentre XlsCompareText ripiega sul confronto per unità di codice delle stringhe in maiuscolo, così che un ricalcolo non venga mai interrotto a metà. Per un ordinamento standalone lanciare l'eccezione è la scelta migliore, perché un ordine silenziosamente sbagliato è più difficile da ricostruire di un'eccezione

TArray.Sort non è stabile, quindi chiavi che confrontano uguali come ab e AB possono uscire in qualsiasi ordine. Quando l'ordine originale delle chiavi uguali conta, ordina un array di indici e usa la posizione originale come chiave secondaria

A volte una colonna deve deliberatamente ignorare l'ordine di Excel. I codici parte sono il caso classico: X-100 e X100 sono codici diversi e dovrebbero ordinarsi per code point. SortRange ha un overload che accetta un TXLSSortCompareEvent, dichiarato come 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 le celle vuote (come Null): sistemale tu
  if VarIsNull(Left) or VarIsNull(Right) then
    Exit(Ord(VarIsNull(Left)) - Ord(VarIsNull(Right)));
  Result := CompareStr(VarToStr(Left), VarToStr(Right));   // ordinale, case-sensitive
end;

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

Con un comparer personalizzato, SortRange passa i valori chiave grezzi e lascia a te la sistemazione dei vuoti, ed è per questo che il comparer qui sopra gestisce Null per primo. Per una chiave decrescente l'ordinamento nega ciò che il comparer restituisce, quindi in quella direzione i vuoti finiscono in cima a meno che il comparer non ne tenga conto. Una colonna ordinata così non è più nell'ordine che un VLOOKUP approssimato o un XLOOKUP con ricerca binaria si aspettano; cosa va storto in quelle modalità su dati ordinati diversamente è trattato nella guida alle modalità di ricerca binaria di XLOOKUP e XMATCH

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

Una cartella di lavoro può ordinare diversamente su un'altra macchina perché l'ordine testuale di Excel dipende dal locale utente Windows, e HotXLS segue quella dipendenza di proposito. Il word sort è specifico per lingua. La collation svedese, per esempio, mette ä dopo z, mentre inglese e tedesco la tengono accanto alla a, così un file ricalcolato da un collega a Stoccolma può restituire un COUNTIF(...,">y") diverso dallo stesso file su un desktop a Chicago. HotXLS passa LOCALE_USER_DEFAULT così che le sue risposte eguagliano quelle di Excel sulla stessa macchina. Bloccare un locale fisso sembrerebbe più ordinato e farebbe sbagliare HotXLS rispetto a Excel su ogni macchina configurata diversamente

Per la generazione lato server questo ha tre conseguenze pratiche:

  • Il locale che conta è quello dell'account sotto cui gira il processo. Un servizio Windows o un application pool IIS può avere impostazioni internazionali diverse dal desktop dello sviluppatore, quindi i risultati visti nell'IDE non sono automaticamente quelli che la produzione calcola
  • I risultati delle formule in cache scritti nel file riflettono il locale della macchina che li ha generati. Excel ricalcola con il suo locale, quindi un valore può cambiare quando il file viene aperto e ricalcolato altrove; Excel si comporta allo stesso modo con i suoi file
  • I locali discrepano soprattutto sulle lettere accentate, sulle coppie di lettere che alcune lingue trattano come lettera singola e sugli alfabeti non latini. Dati di test fatti di parole inglesi semplici non mostreranno mai la differenza, quindi metti nel campione qualche nome come Ångström e Öberg

Il percorso che combacia con Excel è quello Windows: ogni build Win32 e Win64, sia compilata con Delphi, C++Builder, sia con Lazarus e Free Pascal, chiama la stessa CompareStringW. Quando una build punta a una piattaforma non Windows, XlsCompareText usa la WideCompareText della RTL, il cui ordine viene dalla libreria di collation della piattaforma

Le parole comuni ordinano allo stesso modo sotto ogni regola, ed è per questo che una collation sbagliata sopravvive ai test finché non arrivano codici parte con trattini, punteggiatura o nomi di clienti accentati. HotXLS dà la risposta di Excel su quegli input in entrambi i motori XLS e XLSX, dagli operatori di confronto fino a SortRange. Le versioni supportate di Delphi e C++Builder, le licenze e il download della trial sono elencati nella pagina del componente Excel Delphi HotXLS