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 B | Excel / 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 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
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_IGNORECASEda solo, conLOCALE_USER_DEFAULT, è il word sort puro e quello che Excel segue- Aggiungere
SORT_STRINGSORTpassa allo string sort, dove il trattino e l'apostrofo diventano simboli ordinari a pieno peso, così ogni coppiaa-b/abesce al contrario - Aggiungere
NORM_IGNOREWIDTHrende 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
I soliti strumenti Delphi cadono da entrambi i lati della linea:
CompareStr, l'operatore<su stringhe eTComparer<string>.Defaultsono ordinali e case-sensitive, cosìTArray.Sort<string>senza comparer metteZprima difCompareTexteSameTextsono ordinali dopo il ripiegamento del solo ASCIIAnsiCompareTexteWideCompareTextnella RTL Delphi su Windows chiamanoCompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), la chiamata che combacia con Excel. UnaTStringListordinata con le impostazioni predefinite (UseLocaleTrue,CaseSensitiveFalse) passa perAnsiCompareTexte combacia con Excel anche lei- Sui target POSIX la RTL Delphi instrada
AnsiCompareTextattraverso un collatore ICU, un algoritmo diverso con regole di punteggiatura diverse.AnsiCompareTextdi Free Pascal su Windows converte alla code page ANSI e chiamaCompareStringA, 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
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ömeÖ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