Texto no HotXLS: ordenação de palavras do Excel em Delphi

O HotXLS Delphi Component compara dois valores de texto da maneira como o Excel 16 o faz desde a v2.384.67: sem distinguir maiúsculas, na ordem "word sort" do locale de utilizador do Windows, que é o que o CompareStringW devolve com a flag NORM_IGNORECASE. Hífenes e apóstrofos são saltados na primeira passagem e só desempatam, por isso ="a-b">"ab" é TRUE, enquanto outra pontuação ordena antes dos dígitos e das letras, por isso ="a~b"<"ab" também é TRUE. A mesma ordem agora comanda os operadores de comparação, os critérios > / <, a ordenação de intervalos e o VLOOKUP

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

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

O Excel compara texto com o word sort do locale do utilizador, ignorando maiúsculas. O word sort é a ordenação por omissão das funções de comparação NLS do Windows: as letras comparam-se pela sua ordem linguística em vez dos seus code points, as letras acentuadas sentam-se ao lado da sua letra de base, e dois caracteres recebem tratamento especial. O hífen - e o apóstrofo ' são ignorados na primeira passagem, por isso co-op e coop caem lado a lado, e só quando o resto das strings empata é que a presença deles decide a ordem. Qualquer 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 a que um programador Delphi mais recorre. A coluna do Excel guarda os veredictos 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. Primeiro, 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 iguais. Segundo, a igualdade ignora maiúsculas por completo, por isso ab, AB e Ab são a mesma chave no que toca a qualquer comparação. 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 carácter ignorado decide

Como se chegou à ordem de texto do Excel?

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 ordenação. O teste gerou 4.000 pares de strings aleatórios a partir de pontuação ASCII, dígitos, ambas as caixas das letras, 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-colisões um do outro. O Excel 16 avaliou IF(A<B,-1,IF(A=B,0,1)) para cada par, e os veredictos foram confrontados com a API de comparação do Windows com diferentes conjuntos de flags

• NORM_IGNORECASE sozinho (word sort por omissão, locale do utilizador): nenhuma divergência genuína. As únicas 7 diferenças eram células cujo conteúdo inteiro era ', que o Excel consome como carácter prefixo de texto, por isso eram artefactos de amostragem e não diferenças de ordenação
• 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
• Acrescentar NORM_IGNOREWIDTH: errado de outra maneira, porque faz as formas full-width e half-width da mesma letra compararem como iguais, e o Excel mantém-nas separadas

Uma segunda verificação, escolhida à mão, comparou todos os 190 pares retirados de 20 palavras traiçoeiras e o resultado do Range.Sort do Excel na mesma coluna. Ambos coincidiram com o word sort simples de NORM_IGNORECASE, e esses 190 veredictos mais a ordem ordenada fazem agora parte da suite de regressão do HotXLS, corrida tanto no motor clássico TXLSWorkbook como no motor nativo XLSX TXLSXWorkbook

Porque é que o CompareText e a comparação ordinal a 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 sítios arbitrários relativamente às letras. O hífen é U+002D e o apóstrofo U+0027, ambos abaixo de qualquer letra, por isso uma comparação ordinal considera "a-b" menor do que "ab" em vez de tratar o hífen como desempate. O til U+007E senta-se acima de qualquer letra, por isso "a~b" sai maior, o oposto do Excel. O CompareText no RTL do Delphi converte só a..z para maiúsculas e depois compara unidades de código, o que acrescenta uma segunda distorção: o underscore U+005F está entre as letras maiúsculas e minúsculas, por isso converter para maiúsculas move "a_b" de abaixo de "ab" para acima dele. Nenhuma das funções sabe que é pertence entre e e f

As ferramentas Delphi habituais caem dos dois lados da linha:

• O CompareStr, o operador < para strings e o TComparer<string>.Default (que chama CompareStr) são ordinais e distinguem maiúsculas, por isso um TArray.Sort<string> sem comparer põe Z antes de f
• O CompareText e o SameText são ordinais após conversão de caixa só ASCII
• O AnsiCompareText e o WideCompareText no RTL Delphi em Windows chamam CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), a mesma chamada que coincide com o Excel. Uma TStringList ordenada com as suas predefinições (UseLocale True, CaseSensitive False) passa por AnsiCompareText e por isso também coincide com o Excel
• Em alvos POSIX o RTL Delphi encaminha o AnsiCompareText por um collator ICU, que é um algoritmo diferente com regras de pontuação diferentes, e o AnsiCompareText do Free Pascal em Windows chama CompareStringA depois de converter para a página de código ANSI, o que perde qualquer carácter que essa página não represente

Por isso as funções do RTL conscientes do locale estão certas em Windows por implementação, não por contrato, e código que precise da ordem do Excel sai a ganhar fazendo a chamada à API explicitamente. O HotXLS tinha a mesma mistura internamente. Os operadores de comparação punham ambas as strings em maiúsculas e comparavam code points, os ramos > / < das funções de critérios usavam a comparação de Variant do Delphi que distingue maiúsculas, e o VLOOKUP / HLOOKUP casavam texto com essa comparação de Variant que distingue maiúsculas também, razão pela qual o VLOOKUP("ABC",A1:A20,1,FALSE) não conseguia encontrar 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 texto-contra-texto nos caminhos de cálculo e ordenação do HotXLS passam por uma função, XlsCompareText em 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 matriz, os ramos >, <, >= e <= dos critérios ao estilo COUNTIF e das funções de base de dados, o VLOOKUP e o HLOOKUP (exato e aproximado), os ajudantes de ordenação por trás das funções de matriz dinâmica e do XLOOKUP / XMATCH, e a ordenação de intervalos de ambos os motores. Encaminhar a ordenação de intervalos pela mesma função garante que a ordem de ordenação e a ordem de comparação não podem voltar a divergir, o que importa porque o VLOOKUP aproximado sobre texto só faz sentido quando a coluna foi ordenada na ordem em que o lookup compara

uses
  System.Variants, lxHandleX;

var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Sheets.Add('Data');  // O Calculate avalia contra a folha ativa
    Writeln(VarToStr(Book.Calculate('="a-b">"ab"')));   // True: o hífen só desempata
    Writeln(VarToStr(Book.Calculate('="a-b"="ab"')));   // False: desempate feito, 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;

As comparações entre tipos são uma regra separada e não mudaram: qualquer número está abaixo de qualquer valor de texto e qualquer valor de texto está abaixo de qualquer booleano, como descrito em o artigo sobre cadeias de comparação, operandos em branco e SUMIF. O word sort só se aplica quando ambos os operandos são texto. A correspondência com wildcards também é separada: um critério como "a*" ou "=ab" é um teste de padrão ou de igualdade, coberto em o guia dos wildcards do Excel em COUNTIF, MATCH e DSUM, e a ordenação aqui discutida decide apenas os operadores de ordem

O exemplo seguinte carrega as 20 palavras de teste para uma coluna, ordena-a com TXLSXWorksheet.SortRange, e verifica uma contagem de critérios e um 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: o lookup comparava distinguindo 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-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, por isso ab, AB e Ab, que comparam como iguais, mantêm a ordem relativa que tinham antes da ordenação. Células em branco vão para o fim nas duas direções, como no Excel

Como faço coincidir a ordem do Excel no meu próprio código Delphi?

Para fazer coincidir 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 acrescente SORT_STRINGSORT nem NORM_IGNOREWIDTH. O valor devolvido 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 habitual de negativo / zero / positivo, e teste primeiro o 0, porque uma falha tomada por um resultado torna-se -2, um "menor que" silencioso

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

// A ordem de texto do Excel: word sort do locale do utilizador, sem 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 é uma falha, não um resultado de comparação
  Result := R - CSTR_EQUAL;    // 1/2/3 tornam-se -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 (equal, either order), a-b, -ab, abc
end;

O TArray.Sort não é estável, por isso chaves que comparam como iguais, como ab e AB, podem sair em qualquer ordem; se a ordem original de chaves iguais interessa, ordene um array de índices com a posição original como chave secundária. O caso oposto também aparece: por 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 overload que recebe um TXLSSortCompareEvent, um método com a assinatura function(const Left, Right: Variant): Integer of object, e usa-o em vez da comparação incorporada

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 personalizado também recebe células vazias (como Null): coloque-as você
  if VarIsNull(Left) or VarIsNull(Right) then
    Exit(Ord(VarIsNull(Left)) - Ord(VarIsNull(Right)));
  Result := CompareStr(VarToStr(Left), VarToStr(Right));   // ordinal, distinguindo maiúsculas
end;

var
  Sheet: TXLSXWorksheet;   // uma folha preenchida, linhas 2..501, colunas A..D
  Order: TPartNumberOrder;
begin
  // ...
  Order := TPartNumberOrder.Create;
  try
    // chaveado pela coluna A, ascendente
    Sheet.SortRange(2, 1, 501, 4, [1], [False], xlsSortByRows,
      xlsSortExcelLike, Order.Compare);
  finally
    Order.Free;
  end;
end;

Quando um comparer personalizado é fornecido, o HotXLS salta o seu próprio tratamento de brancos e passa os valores brutos das chaves, por isso o comparer tem de lidar com Null. Para uma chave descendente o HotXLS nega o que o comparer devolver, o que também move os brancos para o topo a menos que o comparer o tenha em conta. Tenha presente que uma coluna ordenada desta forma já não está na ordem que o VLOOKUP aproximado do Excel ou um XLOOKUP por pesquisa binária esperam; as armadilhas desses modos sobre dados ordenados noutra ordem estão cobertas em o guia dos modos de pesquisa binária XLOOKUP e XMATCH

Porque é que o mesmo livro pode ordenar de forma diferente noutra máquina?

O mesmo livro pode ordenar de forma diferente noutra máquina porque a ordem de texto do Excel depende do locale de utilizador do Windows, e o HotXLS segue deliberadamente essa dependência. O word sort é específico da língua: a ordenação sueca, por exemplo, põe o ä depois do z, onde o inglês e o alemão o mantêm junto do a. O Excel herda isso do locale sob o qual corre, por isso um livro recalculado por um colega em Estocolmo pode devolver um COUNTIF(...,">y") diferente do mesmo ficheiro num desktop em Chicago. O HotXLS passa LOCALE_USER_DEFAULT para que os seus resultados sejam iguais aos do Excel na mesma máquina; qualquer locale fixo faria o HotXLS divergir do Excel em todas as máquinas com uma definição diferente

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

• O locale que conta é o da conta sob a qual o processo corre. Um serviço Windows ou um application pool do IIS pode usar um formato regional diferente do desktop do programador, por isso os resultados observados no IDE não são automaticamente o que a produção calcula
• Os resultados de fórmulas em cache escritos no ficheiro refletem o locale da máquina que gerou. O Excel recalcula com o seu próprio locale, por isso um valor pode mudar quando o ficheiro é aberto noutro sítio e recalculado; isso é comportamento do Excel, não um artefacto do HotXLS
• Os locales divergem sobretudo nas letras acentuadas, nas combinações de letras que algumas línguas tratam como uma única letra, e nos scripts não latinos, por isso dados de teste limitados a palavras inglesas simples não revelarão o problema

A fronteira de plataforma é simples. O HotXLS é uma biblioteca Windows, compilada para Win32 e Win64 com Delphi e C++Builder e para alvos win32 / win64 com Lazarus e Free Pascal, e todas estas compilações chamam o mesmo CompareStringW. Não há caminho de ordenação não Windows separado. O único fallback é para uma chamada à API que falhe: se o CompareStringW devolver 0, o 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 a correr 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 utilizador 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
• Outra pontuação ordena antes dos dígitos, dígitos antes das letras: ="a~b"<"ab" e ="a0"<"ab" são TRUE
• As maiúsculas nunca contam: ="ABC"="abc" é TRUE e o VLOOKUP("ABC",...) encontra abc
• Caminhos cobertos: operadores de comparação, comparações de matrizes, critérios > / <, VLOOKUP / HLOOKUP, ordenação de matrizes dinâmicas, SortRange em ambos os motores
• Não cobertos por esta regra: tipos mistos (número < texto < booleano) e critérios wildcard, que têm as suas próprias regras
• Em código Delphi: CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), teste o 0, subtraia CSTR_EQUAL; evite CompareText, CompareStr e TComparer<string>.Default quando o resultado tem de coincidir com o Excel
• Os resultados dependem do locale da conta que corre o código, no Excel e no HotXLS igualmente

Palavras comuns ordenam da mesma maneira sob qualquer regra, por isso só códigos com hífenes, pontuação e nomes acentuados expõem uma ordenação errada. O HotXLS agora dá a resposta do Excel em todos eles nos motores XLS e XLSX. Detalhes sobre licenciamento, versões suportadas de Delphi e C++Builder e a transferência de avaliação estão na página do componente Excel Delphi HotXLS