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

O HotXLS Delphi Component compara dois valores de texto do jeito que o Excel 16 faz desde a v2.384.67: sem diferenciar maiúsculas, na ordem "word sort" do locale do usuário do Windows, que é o que o CompareStringW devolve com a flag NORM_IGNORECASE. Hífen e apóstrofo são pulados na primeira passada e só desempatam, então ="a-b">"ab" é TRUE, enquanto a outra pontuação ordena antes de dígitos e letras, então ="a~b"<"ab" também é TRUE. A mesma ordem agora dirige os operadores de comparação, critérios > / <, ordenação de intervalos e VLOOKUP

Ninguém abre um bug intitulado "divergência de collation". Os relatos dizem que COUNTIF(A:A,">M") conta duas linhas a mais no servidor do que no Excel, que uma lista de preços ordenada pelo serviço de relatórios põe X-100 num lugar onde o Excel não poria, ou que VLOOKUP("ABC",...) devolve #N/A embora a coluna claramente contenha abc. Os três vêm da mesma pergunta: quando os dois operandos são texto, qual é o menor? O Excel tem uma resposta precisa, ela não é a que a maior parte do código Delphi dá, e antes da v2.384.67 o HotXLS dava três respostas diferentes dependendo de qual caminho de código perguntasse

Que regra o Excel usa para comparar duas strings de texto?

O Excel compara texto com o word sort do locale do usuário, ignorando maiúsculas. O word sort é a collation padrão das funções de comparação NLS do Windows: as letras comparam pela ordem linguística delas e não pelos code points, as letras acentuadas ficam ao lado da letra base, e dois caracteres recebem tratamento especial. O hífen - e o apóstrofo ' são ignorados na primeira passada, então co-op e coop caem um ao lado do outro, e só quando o resto das strings empata a presença deles decide a ordem. Todo outro sinal de pontuação é significativo e ordena antes dos dígitos, e os dígitos ordenam antes das letras

A tabela mostra o que isso significa na prática, ao lado das duas comparações que um desenvolvedor Delphi mais provavelmente vai buscar. A coluna do Excel guarda os vereditos que o Excel 16 devolveu para IF(A<B,...), que o HotXLS reproduz desde a v2.384.67

A vs B	Excel 16 / 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 são fáceis de perder. Primeira, o papel de desempate do hífen significa que ="a-b"="ab" é FALSE: as strings são vizinhas próximas na ordenação, mas não são iguais. Segunda, a igualdade ignora maiúsculas por completo, então ab, AB e Ab são a mesma chave no que qualquer comparação diz respeito. Ordenar as 20 palavras de teste com o Range.Sort do Excel dá 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 ignorado decide

Como a ordem de texto do Excel foi determinada?

A ordem de texto do Excel foi identificada por medição, não por documentação, porque a documentação do Excel não nomeia a collation. O teste gerou 4.000 pares aleatórios de strings com pontuação ASCII, dígitos, os dois casos de letra, espaços, é, ß, ä, caracteres chineses, formas full-width e o espaço não separável, com comprimentos de 0 a 4 e metade dos pares construídos como quase iguais entre si. O Excel 16 avaliou IF(A<B,-1,IF(A=B,0,1)) para cada par, e os vereditos foram confrontados com a API de comparação do Windows com diferentes conjuntos de flags

• NORM_IGNORECASE sozinha (word sort padrão, locale do usuário): nenhuma divergência genuína. As únicas 7 diferenças eram células cujo conteúdo inteiro era ', que o Excel consome como caractere de prefixo de texto, então eram artefatos de amostragem e não diferenças de collation
• NORM_IGNORECASE com SORT_STRINGSORT: 41 divergências. O string sort trata o hífen e o apóstrofo como símbolos comuns, que é exatamente o comportamento que o Excel não tem
• Adicionando NORM_IGNOREWIDTH: errado de outro jeito, porque faz as formas full-width e half-width da mesma letra compararem como iguais, e o Excel as mantém separadas

Uma segunda checagem, montada à mão, comparou todos os 190 pares tirados de 20 palavras traiçoeiras e o resultado do Range.Sort do Excel na mesma coluna. Ambos concordaram com o word sort simples com NORM_IGNORECASE, e esses 190 vereditos mais a ordem ordenada agora fazem parte da suíte de regressão do HotXLS, rodando tanto pelo motor clássico TXLSWorkbook quanto pelo motor nativo XLSX TXLSXWorkbook

Por que CompareText e a comparação ordinal erram?

O 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 põe a pontuação em lugares arbitrários em relação às letras. O hífen é U+002D e o apóstrofo U+0027, ambos abaixo de toda letra, então uma comparação ordinal diz que "a-b" é menor que "ab" em vez de tratar o hífen como desempate. O til U+007E fica acima de toda letra, então "a~b" sai maior, o oposto do Excel. O CompareText na RTL do Delphi dobra apenas a..z para maiúsculas e depois compara unidades de código, o que adiciona uma segunda distorção: o underscore U+005F fica entre as letras maiúsculas e minúsculas, então dobrar para maiúsculas move "a_b" de abaixo de "ab" para acima dela. Nenhuma das duas funções sabe que é pertence entre e e f

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

• O CompareStr, o operador < de string e o TComparer<string>.Default (que chama CompareStr) são ordinais e diferenciam maiúsculas, então TArray.Sort<string> sem um comparer põe Z antes de f
• O CompareText e o SameText são ordinais após a dobra de maiúsculas só ASCII
• O AnsiCompareText e o WideCompareText na RTL do Delphi no Windows chamam CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), a mesma chamada que casa com o Excel. Uma TStringList ordenada com os padrões dela (UseLocale True, CaseSensitive False) passa pelo AnsiCompareText e portanto também concorda com o Excel
• Em alvos POSIX a RTL do Delphi roteia o AnsiCompareText por um collator ICU, que é um algoritmo diferente com regras de pontuação diferentes, e o AnsiCompareText do Free Pascal no Windows chama CompareStringA depois de converter para a página de código ANSI, o que perde qualquer caractere que essa página não represente

Logo, as funções da RTL cientes de locale estão certas no Windows por implementação, não por contrato, e código que precisa da ordem do Excel sai ganhando fazendo a chamada de API explicitamente. O HotXLS tinha a mesma mistura internamente. Os operadores de comparação convertiam as duas strings para maiúsculas e comparavam code points, os ramos > / < das funções de critério usavam a comparação de Variant do Delphi que diferencia maiúsculas, e VLOOKUP / HLOOKUP casavam texto com essa comparação de Variant que diferencia maiúsculas também, e é por isso que VLOOKUP("ABC",A1:A20,1,FALSE) não encontrava abc. A ordenação de intervalos já usava WideCompareText. Três caminhos, três ordens

O que mudou no HotXLS v2.384.67?

Desde a v2.384.67 as comparações de texto contra texto nos caminhos de cálculo e ordenação do HotXLS passam por uma função, a XlsCompareText no lxStandard.pas, que chama CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) e subtrai CSTR_EQUAL. Os chamadores são os seis operadores de comparação, as comparações elemento a elemento em fórmulas de array, os ramos >, <, >= e <= de critérios estilo COUNTIF e das funções de banco de dados, VLOOKUP e HLOOKUP (exata e aproximada), os auxiliares de ordenação por trás das funções de matriz dinâmica e de XLOOKUP / XMATCH, e a ordenação de intervalos dos dois motores. Rotear a ordenação de intervalos pela mesma função garante que a ordem de classificação e a ordem de comparação não podem divergir de novo, o que importa porque a VLOOKUP aproximada sobre texto só faz sentido quando a coluna foi ordenada na ordem em que a lookup compara

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;

Comparações entre tipos são uma regra separada e não mudaram: 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. O word sort só se aplica quando os dois operandos são texto. Casamento de wildcard também é separado: um critério como "a*" ou "=ab" é um teste de padrão ou de igualdade, coberto em o guia de wildcards do Excel em COUNTIF, MATCH e DSUM, e a collation discutida aqui decide só os operadores de ordenação

O próximo exemplo carrega as 20 palavras de teste numa coluna, ordena com TXLSXWorksheet.SortRange, e confere uma contagem de critério e uma lookup. As contagens são as que o Excel 16 devolveu para a mesma coluna

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 na mesma coluna: 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 antes da v2.384.67: a lookup comparava diferenciando maiúsculas
    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;

O TXLSXWorksheet.SortRange usa um merge sort estável, então ab, AB e Ab, que comparam como iguais, mantêm a ordem relativa que tinham antes da ordenação. Células vazias vão para o fim nas duas direções, como no Excel

Como reproduzir a ordem de classificação do Excel no meu próprio código Delphi?

Para reproduzir a ordem de texto do Excel no seu próprio código Delphi, chame CompareStringW com LOCALE_USER_DEFAULT e NORM_IGNORECASE, e não adicione SORT_STRINGSORT nem NORM_IGNOREWIDTH. O valor de retorno não é um resultado de comparação com sinal: a API devolve CSTR_LESS_THAN (1), CSTR_EQUAL (2) ou CSTR_GREATER_THAN (3), e 0 quando a chamada falha. Subtraia 2 para obter a convenção usual de negativo / zero / positivo, e teste 0 primeiro, porque uma falha tomada por resultado vira -2, um silencioso "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;

O TArray.Sort não é estável, então chaves que comparam como iguais, como ab e AB, podem sair em qualquer ordem; se a ordem original de chaves iguais importa, ordene um array de índices com a posição original como chave secundária. O caso contrário também aparece: às vezes uma coluna não deve seguir a ordem do Excel, por exemplo números de peça em que X-100 e X100 são códigos distintos e devem ordenar por code point. O TXLSXWorksheet.SortRange tem uma sobrecarga que recebe um TXLSSortCompareEvent, um método com a assinatura function(const Left, Right: Variant): Integer of object, e o usa em vez 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;

Quando um comparer customizado é fornecido, o HotXLS pula o tratamento próprio de vazios e passa os valores de chave crus, então o comparer precisa lidar com Null. Para uma chave descendente o HotXLS nega o que o comparer devolver, o que também move os vazios para o topo a menos que o comparer leve isso em conta. Tenha em mente que uma coluna ordenada assim deixa de estar na ordem que a VLOOKUP aproximada do Excel ou uma XLOOKUP de busca binária esperam; as armadilhas desses modos sobre dados ordenados numa ordem diferente estão cobertas 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?

O mesmo workbook pode ordenar diferente em outra máquina porque a ordem de texto do Excel depende do locale do usuário do Windows, e o HotXLS segue essa dependência deliberadamente. O word sort é específico por idioma: a collation sueca, por exemplo, coloca ä depois de z, onde inglês e alemão a mantêm ao lado de a. O Excel herda isso do locale em que roda, então um workbook 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 os resultados dele se igualem aos do Excel na mesma máquina; qualquer locale fixo faria o HotXLS discordar do Excel em toda máquina com configuração diferente

Três consequências práticas seguem para geração no servidor:

• O locale que vale é o da conta sob a qual o processo roda. Um serviço do Windows ou um application pool do IIS pode usar um formato regional diferente do desktop do desenvolvedor, então resultados observados 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 próprio locale dele, então um valor pode mudar quando o arquivo é aberto em outro lugar e recalculado; esse é o comportamento do Excel, não um artefato do HotXLS
• Os locales divergem principalmente em letras acentuadas, em combinações de letras que alguns idiomas tratam como letra única, e em scripts não latinos, então dados de teste limitados a palavras inglesas simples não vão revelar o problema

A fronteira de plataforma é simples. O HotXLS é uma biblioteca Windows, construída para Win32 e Win64 com Delphi e C++Builder e para alvos win32 / win64 com Lazarus e Free Pascal, e todas essas builds chamam a mesma CompareStringW. Não há caminho de collation separado fora do Windows. O único fallback é para uma chamada de API que falhou: se a CompareStringW devolve 0, a XlsCompareText compara as strings em maiúsculas por unidade de código em vez de lançar uma exceção no meio de uma recalculação, o que mantém o cálculo rodando mas deixa de garantir a ordem do Excel

Referência rápida: comparação de texto do Excel no HotXLS

• Regra: word sort do locale do usuário com NORM_IGNORECASE, sem SORT_STRINGSORT, sem NORM_IGNOREWIDTH, no HotXLS desde a v2.384.67
• - e ' só desempatam: ="a-b">"ab" é TRUE e ="a-b"="ab" é FALSE
• A outra pontuação ordena antes dos dígitos, dígitos antes das letras: ="a~b"<"ab" e ="a0"<"ab" são TRUE
• Maiúsculas nunca importam: ="ABC"="abc" é TRUE e VLOOKUP("ABC",...) encontra abc
• Caminhos cobertos: operadores de comparação, comparações de array, critérios > / <, VLOOKUP / HLOOKUP, ordenação de matriz dinâmica, SortRange nos dois motores
• Não cobertos por esta regra: tipos mistos (número < texto < boolean) e critérios wildcard, que têm regras próprias
• Em código Delphi: CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), teste 0, subtraia CSTR_EQUAL; evite CompareText, CompareStr e TComparer<string>.Default quando o resultado precisa concordar com o Excel
• Os resultados dependem do locale da conta que roda o código, no Excel e no HotXLS igualmente

Palavras comuns ordenam igual sob qualquer regra, então só códigos com hífen, pontuação e nomes acentuados expõem uma collation errada. O HotXLS agora dá a resposta do Excel em todos eles nos motores XLS e XLSX. Detalhes de licenciamento, versões suportadas de Delphi e C++Builder e o download da versão trial estão na página do componente Excel HotXLS para Delphi