Artigo Técnico

Comparação de texto no HotXLS: word sort do Excel em Delphi

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 BExcel / HotXLSCompareStr (ordinal)CompareText
"a-b" vs "ab"maiormenormenor
"a'b" vs "ab"maiormenormenor
"a~b" vs "ab"menormaiormaior
"a_b" vs "ab"menormenormaior
"ab" vs "AB"igualmaiorigual
"é" vs "f"menormaiormaior
"Z" vs "f"maiormenormaior

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

Diagrama word sort do HotXLS classificando todas as 20 palavras de teste de a b, a.b, a_b e a~b passando por a0 e a1b, depois o grupo ab com AB e Ab, variantes com hífen e apóstrofo como a-b e a'b, até abc, b, e, é (e agudo), f e Z, mostrando pontuação antes de dígitos antes de letras com maiúsculas ignoradas
Pontuação e espaço ordenam antes dos dígitos, e os dígitos antes das letras, maiúsculas se dobram, e o hífen com o apóstrofo só desempatam; por isso a-b cai ao lado de ab e ainda assim compara como maior

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_IGNORECASE sozinha, com LOCALE_USER_DEFAULT, é o word sort puro e o que o Excel segue
  • Adicionar SORT_STRINGSORT muda para o string sort, em que o hífen e o apóstrofo viram símbolos comuns com peso total, então todo par estilo a-b / ab sai ao contrário
  • Adicionar NORM_IGNOREWIDTH faz 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

Diagrama de comparação do HotXLS contrastando a ordem por code point com o word sort do Excel: a comparação ordinal põe o apóstrofo, o hífen e o underscore em 0x27, 0x2D e 0x5F junto às letras, então a-b contra ab sai como menor, enquanto o word sort empurra a pontuação para antes de dígitos e letras e trata só o hífen e o apóstrofo como desempatadores
Os code points espalham a pontuação em volta das letras, então as comparações ordinal e com dobra ASCII invertem os vereditos; o word sort move a pontuação para a frente dos dígitos e rebaixa o hífen e o apóstrofo a desempatadores

As ferramentas Delphi de sempre caem dos dois lados da linha:

  • CompareStr, o operador < de string e TComparer<string>.Default são ordinais e diferenciam maiúsculas, então TArray.Sort<string> sem um comparer põe Z antes de f
  • CompareText e SameText são ordinais após a dobra ASCII de maiúsculas
  • AnsiCompareText e WideCompareText na RTL do Delphi no Windows chamam CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), a chamada que coincide com o Excel. Uma TStringList ordenada com os padrões dela (UseLocale True, CaseSensitive False) passa pela AnsiCompareText e também concorda com o Excel
  • Em alvos POSIX a RTL do Delphi roteia a AnsiCompareText por um collator ICU, um algoritmo diferente com regras de pontuação diferentes. A AnsiCompareText do Free Pascal no Windows converte para a code page ANSI e chama CompareStringA, 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

Diagrama de roteamento do HotXLS mostrando cada caminho de comparação de texto, dos seis operadores de comparação e critérios estilo COUNTIF passando por VLOOKUP, HLOOKUP, XLOOKUP e a ordenação de intervalos dos dois motores, convergindo na XlsCompareText, que chama CompareStringW com LOCALE_USER_DEFAULT e NORM_IGNORECASE e mapeia 1, 2, 3 para -1, 0, 1
Operadores, critérios, lookups e ordenação compartilham uma função, então a ordem que o Excel vê e a ordem com que o HotXLS ordena não podem divergir; a API devolve 1, 2 ou 3, e zero significa falha, não menor que

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öm e Öberg na 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