No Excel, ="a-b">"ab" devolve TRUE, e ="a~b"<"ab" também devolve TRUE. Entregue os mesmos pares ao CompareText do Delphi e as duas respostas se invertem, porque o hífen fica abaixo das letras na ordem de code point e o til fica acima. O Excel compara texto com o word sort do locale de usuário do Windows, sem diferenciar maiúsculas, em que hífenes e apóstrofos só desempatam e todo o restante da pontuação ordena antes de dígitos e letras
Os sintomas raramente mencionam collation. Um COUNTIF(A:A,">M") calculado no servidor conta duas linhas a mais que a mesma fórmula no Excel. Uma lista de preços ordenada por um serviço de relatórios coloca X-100 num lugar em que o Excel não colocaria. VLOOKUP("ABC",...) devolve #N/A embora a coluna claramente contenha abc. Os três casos se reduzem a uma pergunta: quando os dois operandos são texto, qual é o menor? O Excel tem uma resposta precisa, e raramente é a que o código Delphi dá por padrão
Que regra o Excel usa para comparar duas strings de texto?
O Excel compara dois valores de texto com o word sort do locale de usuário atual do Windows, sem diferenciar maiúsculas. O word sort é o collation padrão das funções de comparação NLS do Windows: as letras comparam em ordem linguística em vez de por code point, então é cai entre e e f, e maiúsculas e minúsculas se dobram juntas. Dois caracteres recebem tratamento especial. O hífen - e o apóstrofo ' são pulados na primeira passada, o que põe co-op colado a coop; só quando os caracteres restantes empatam a presença deles, e a posição deles, decide a ordem. Todo o restante da pontuação conta com peso total e ordena antes dos dígitos, e os dígitos ordenam antes das letras
A tabela confronta o veredito do Excel para IF(A<B,...) com as duas comparações que um desenvolvedor Delphi provavelmente escreveria primeiro
| A vs B | Excel / HotXLS | CompareStr (ordinal) | CompareText |
|---|---|---|---|
"a-b" vs "ab" | maior | menor | menor |
"a'b" vs "ab" | maior | menor | menor |
"a~b" vs "ab" | menor | maior | maior |
"a_b" vs "ab" | menor | menor | maior |
"ab" vs "AB" | igual | maior | igual |
"é" vs "f" | menor | maior | maior |
"Z" vs "f" | maior | menor | maior |
Duas consequências pegam as pessoas de surpresa. Como o hífen só desempata, ="a-b"="ab" é FALSE: as duas strings são vizinhas imediatas numa ordenação e ainda assim desiguais. E como maiúsculas se dobram por completo, ab, AB e Ab são uma chave só para todo operador de comparação. Num locale de usuário inglês, uma coluna de 20 palavras espinhosas ordena em 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 do grupo ab a posição do caractere pulado decide: hífen no final primeiro, hífen no início por último
Conferindo o collation contra o Excel você mesmo
A documentação do Excel nunca diz o nome do collation que usa, então a forma confiável de confirmá-lo na sua própria máquina é deixar o Excel julgar um lote de pares de strings e comparar os vereditos dele com a CompareStringW sob diferentes conjuntos de flags. Ponha os pares em duas colunas, faça o Excel avaliar =IF(A1<B1,-1,IF(A1=B1,0,1)) para cada linha e rode os mesmos pares pela API. Os pares que valem a pena gerar misturam pontuação ASCII, dígitos, os dois casos de letra, espaços, letras acentuadas como é, ß e ä, caracteres CJK, formas full-width e o espaço sem quebra, com bastante quase-acertos que diferem num único caractere. Palavras aleatórias de um dicionário são uma amostra ruim, porque letras simples ordenam igual sob todas as regras deste artigo
Os conjuntos de flags levam a resultados claramente diferentes:
NORM_IGNORECASEsozinha, comLOCALE_USER_DEFAULT, é o word sort puro e o que o Excel segue- Adicionar
SORT_STRINGSORTmuda para o string sort, em que o hífen e o apóstrofo viram símbolos comuns com peso total, então todo par estiloa-b/absai ao contrário - Adicionar
NORM_IGNOREWIDTHfaz uma letra full-width valer sua forma half-width, e o Excel mantém as duas separadas
Uma armadilha fica no próprio harness de teste. O Excel trata um apóstrofo digitado no início de uma célula como o prefixo "guardar como texto", então uma célula cuja entrada inteira é ' acaba vazia, e qualquer par montado assim reporta um mismatch que não tem nada a ver com collation. Monte esses pares com literais de string na fórmula (="'"), ou pule-os
Por que CompareText e a comparação ordinal erram?
CompareText e a comparação ordinal erram a ordem do Excel porque comparam unidades de código UTF-16, e a ordem por code point espalha a pontuação em volta do alfabeto. O hífen é U+002D e o apóstrofo U+0027, ambos abaixo de qualquer letra, então uma comparação ordinal diz que "a-b" é menor que "ab", enquanto o Excel trata o hífen como desempatador. O til é U+007E, acima de qualquer letra, o que faz "a~b" maior, o oposto do Excel. O CompareText acrescenta uma segunda distorção própria: ele dobra apenas a..z para maiúsculas e depois compara unidades de código, e como o underscore (U+005F) fica entre as faixas de maiúsculas e minúsculas, a dobra move "a_b" de abaixo de "ab" para acima dele. Nenhuma das duas funções tem a menor ideia de que é pertence entre e e f
As ferramentas Delphi de sempre caem dos dois lados da linha:
CompareStr, o operador<de string eTComparer<string>.Defaultsão ordinais e diferenciam maiúsculas, entãoTArray.Sort<string>sem um comparer põeZantes defCompareTexteSameTextsão ordinais após a dobra ASCII de maiúsculasAnsiCompareTexteWideCompareTextna RTL do Delphi no Windows chamamCompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), a chamada que coincide com o Excel. UmaTStringListordenada com os padrões dela (UseLocaleTrue,CaseSensitiveFalse) passa pelaAnsiCompareTexte também concorda com o Excel- Em alvos POSIX a RTL do Delphi roteia a
AnsiCompareTextpor um collator ICU, um algoritmo diferente com regras de pontuação diferentes. AAnsiCompareTextdo Free Pascal no Windows converte para a code page ANSI e chamaCompareStringA, o que perde todo caractere que essa code page não representa
Ou seja, as funções da RTL cientes de locale por acaso estão certas no Windows por causa de como são implementadas hoje. Código que precisa coincidir com o Excel deve fazer a chamada de API explicitamente e dizer isso. O problema mais difícil aparece em qualquer motor de fórmulas em que cada caminho de código pega a comparação mais próxima: maiúsculas e compara por code points nos operadores = e <, uma comparação de Variant case-sensitive no ramo > / < dos critérios do COUNTIF, outra dentro do VLOOKUP (que é exatamente como VLOOKUP("ABC",A1:A20,1,FALSE) não acha abc), e uma chamada de locale na ordenação do intervalo. Cada caminho é defensável sozinho. Juntos produzem três ordens diferentes sobre a mesma coluna
Uma comparação por trás de fórmulas, lookups e ordenação no HotXLS
O HotXLS manda toda comparação texto-contra-texto no código de cálculo e de ordenação dele por uma única função, a XlsCompareText em lxStandard.pas, que chama CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) e subtrai CSTR_EQUAL. Os seis operadores de comparação a usam, assim como comparações elemento a elemento em fórmulas de matriz, os ramos >, <, >= e <= de critérios estilo COUNTIF e as funções de banco de dados, VLOOKUP e HLOOKUP em modo exato e aproximado, a ordenação por trás das funções de dynamic array e do XLOOKUP / XMATCH, e a SortRange tanto na TXLSWorksheet quanto na TXLSXWorksheet. Pôr a ordenação de intervalos na mesma função foi o ponto decisivo de design. O VLOOKUP aproximado sobre texto só dá respostas sensíveis quando a coluna foi ordenada na ordem em que o lookup compara, e compartilhar uma função torna impossível essas duas ordens se separarem
O jeito mais rápido de ver a regra a partir do Delphi é a TXLSXWorkbook.Calculate, que avalia uma string de fórmula contra a planilha ativa e devolve o resultado como Variant
uses
System.Variants, lxHandleX;
var
Book: TXLSXWorkbook;
begin
Book := TXLSXWorkbook.Create;
try
Book.Sheets.Add('Data'); // Calculate avalia contra a planilha ativa
Writeln(VarToStr(Book.Calculate('="a-b">"ab"'))); // True: hífen só desempata
Writeln(VarToStr(Book.Calculate('="a-b"="ab"'))); // False: desempate aplicado, não é igual
Writeln(VarToStr(Book.Calculate('="a~b"<"ab"'))); // True: pontuação primeiro
Writeln(VarToStr(Book.Calculate('="ABC"="abc"'))); // True: maiúsculas ignoradas
finally
Book.Free;
end;
end;
O word sort só se aplica quando os dois operandos são texto. Tipos mistos seguem um ranking fixo em que todo número está abaixo de todo valor de texto e todo valor de texto abaixo de todo boolean, como descrito em o artigo sobre cadeias de comparação, operandos vazios e SUMIF. Critérios com wildcard como "a*" ou "=ab" são testes de padrão e igualdade com regras próprias, cobertos em o guia de wildcards do Excel em COUNTIF, MATCH e DSUM; o collation discutido aqui decide os operadores de ordenação
O exemplo seguinte carrega as 20 palavras na coluna A de uma planilha, verifica três contagens de critérios e um lookup, e depois ordena a coluna com TXLSXWorksheet.SortRange. As contagens esperadas seguem direto da ordem ordenada mostrada antes: onze palavras ordenam depois do grupo ab, onze ordenam antes de a-b, e os três membros do grupo ab somam-se aos onze para >=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]);
// Esperado pela ordem do 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")')));
// Acha abc: o lookup compara sem diferenciar maiúsculas, como o Excel
Sheet.Cells[1, 3].Formula := '=VLOOKUP("ABC",A1:A20,1,FALSE)';
Book.Recalculate;
Writeln(VarToStr(Sheet.Cells[1, 3].Value)); // abc
// Uma coluna de chave, ascendente: 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;
A SortRange roda um merge sort estável, então ab, AB e Ab, que comparam iguais, mantêm a ordem relativa que tinham antes da ordenação. Células vazias vão para o fundo, seja a chave ascendente ou descendente, que é onde o Excel as põe
Como reproduzir a ordem de classificação do Excel no meu próprio código Delphi?
Para reproduzir a ordem de texto do Excel fora de um workbook, chame a CompareStringW com LOCALE_USER_DEFAULT e NORM_IGNORECASE, e deixe de fora SORT_STRINGSORT e NORM_IGNOREWIDTH. Fique de olho no valor de retorno. A API não devolve um resultado com sinal; ela devolve CSTR_LESS_THAN (1), CSTR_EQUAL (2) ou CSTR_GREATER_THAN (3), e 0 quando a chamada falha. Subtrair 2 dá a convenção usual de negativo / zero / positivo, mas só depois do caso 0 tratado, senão uma chamada falha vira -2 e lê como "menor que"
uses
Winapi.Windows, System.SysUtils, System.Generics.Defaults,
System.Generics.Collections;
// Ordem de texto do Excel: word sort do locale do usuário, sem diferenciar maiúsculas
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 é falha, não um resultado de comparação
Result := R - CSTR_EQUAL; // 1/2/3 viram -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 (iguais, em qualquer ordem), a-b, -ab, abc
end;
Se o HotXLS já está no uses clause, a lxStandard.XlsCompareText é pública e devolve a mesma ordem. As duas diferem só numa chamada de API que falhe: a função acima lança exceção, enquanto a XlsCompareText cai para comparar as strings em maiúsculas por unidade de código para que um recálculo nunca seja interrompido no meio. Numa ordenação isolada, lançar exceção é a melhor escolha, porque uma ordem silenciosamente errada é mais difícil de rastrear que uma exceção
A TArray.Sort não é estável, então chaves iguais como ab e AB podem sair em qualquer ordem. Quando a ordem original das chaves iguais importa, ordene um array de índices e use a posição original como chave secundária
Às vezes uma coluna precisa deliberadamente ignorar a ordem do Excel. Números de peça são o caso clássico: X-100 e X100 são códigos diferentes e devem ordenar por code point. A SortRange tem uma sobrecarga que recebe um TXLSSortCompareEvent, declarado como function(const Left, Right: Variant): Integer of object, e o usa no lugar da comparação embutida
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
// Um comparer customizado também recebe células vazias (como Null): posicione-as você mesmo
if VarIsNull(Left) or VarIsNull(Right) then
Exit(Ord(VarIsNull(Left)) - Ord(VarIsNull(Right)));
Result := CompareStr(VarToStr(Left), VarToStr(Right)); // ordinal, diferencia maiúsculas
end;
var
Sheet: TXLSXWorksheet; // uma planilha preenchida, linhas 2..501, colunas A..D
Order: TPartNumberOrder;
begin
// ...
Order := TPartNumberOrder.Create;
try
// chave pela coluna A, ascendente
Sheet.SortRange(2, 1, 501, 4, [1], [False], xlsSortByRows,
xlsSortExcelLike, Order.Compare);
finally
Order.Free;
end;
end;
Com um comparer customizado, a SortRange entrega os valores de chave crus e deixa o posicionamento de vazios com você, por isso o comparer acima trata Null primeiro. Para uma chave descendente a ordenação nega o que o comparer devolve, então nessa direção os vazios vão para o topo a menos que o comparer leve isso em conta. Uma coluna ordenada assim deixa de estar na ordem que um VLOOKUP aproximado ou um XLOOKUP por busca binária espera; o que dá errado nesses modos com dados ordenados numa ordem diferente está coberto em o guia dos modos de busca binária do XLOOKUP e XMATCH
Por que o mesmo workbook pode ordenar diferente em outra máquina?
Um workbook pode ordenar diferente em outra máquina porque a ordem de texto do Excel depende do locale de usuário do Windows, e o HotXLS segue essa dependência de propósito. O word sort é específico por idioma. O collation sueco, por exemplo, põe ä depois de z, enquanto inglês e alemão a mantêm junto de a, então um arquivo recalculado por um colega em Estocolmo pode devolver um COUNTIF(...,">y") diferente do mesmo arquivo num desktop em Chicago. O HotXLS passa LOCALE_USER_DEFAULT para que as respostas dele igualem as do Excel na mesma máquina. Fixar um locale qualquer pareceria mais arrumado e faria o HotXLS discordar do Excel em toda máquina configurada diferente
Para geração no lado do servidor, isso tem três consequências práticas:
- O locale que conta é o da conta sob a qual o processo roda. Um serviço do Windows ou um application pool do IIS pode ter configurações regionais diferentes do desktop do desenvolvedor, então os resultados vistos na IDE não são automaticamente o que a produção calcula
- Resultados de fórmula em cache gravados no arquivo refletem o locale da máquina geradora. O Excel recalcula com o locale dele, então um valor pode mudar quando o arquivo é aberto e recalculado em outro lugar; o Excel se comporta igual com os próprios arquivos
- Locales na maior parte divergem em letras acentuadas, em pares de letras que alguns idiomas tratam como uma letra única, e em escritas não latinas. Dados de teste feitos de palavras inglesas simples nunca expõem a diferença, então ponha alguns nomes como
ÅngströmeÖbergna amostra
O caminho que iguala o Excel é o do Windows: todo build Win32 e Win64, seja compilado com Delphi, C++Builder, ou Lazarus e Free Pascal, chama a mesma CompareStringW. Quando um build mira uma plataforma não Windows, a XlsCompareText usa a WideCompareText da RTL, cuja ordem vem da biblioteca de collation da própria plataforma
Palavras comuns ordenam igual sob todas as regras, e é por isso que um collation errado tende a sobreviver aos testes até chegarem códigos de peça com hífen, pontuação ou nomes de cliente acentuados. O HotXLS dá a resposta do Excel nessas entradas nos dois motores, XLS e XLSX, dos operadores de comparação até a SortRange. Versões suportadas de Delphi e C++Builder, licenciamento e o download de trial estão na página do componente Excel Delphi HotXLS