Comparación de texto en HotXLS: word sort de Excel en Delphi

HotXLS Delphi Component compara dos valores de texto como lo hace Excel 16 desde v2.384.67: sin distinguir mayúsculas, en el orden "word sort" del locale de usuario de Windows, que es lo que devuelve CompareStringW con la bandera NORM_IGNORECASE. Los guiones y apóstrofes se saltan en la primera pasada y solo desempatan, así que ="a-b">"ab" es TRUE, mientras que el resto de la puntuación ordena antes que los dígitos y las letras, así que ="a~b"<"ab" también es TRUE. Ese mismo orden ahora mueve a los operadores de comparación, los criterios > / <, el ordenamiento de rangos y VLOOKUP

Nadie reporta un bug titulado "collation mismatch". Los reportes dicen que COUNTIF(A:A,">M") cuenta dos filas de más en el servidor que en Excel, que una lista de precios ordenada por el servicio de reportes pone X-100 donde Excel no la pondría, o que VLOOKUP("ABC",...) devuelve #N/A aunque la columna contiene claramente abc. Los tres salen de la misma pregunta: cuando ambos operandos son texto, ¿cuál es el menor? Excel tiene una respuesta precisa, no es la que da la mayoría del código Delphi, y antes de v2.384.67 HotXLS daba tres respuestas distintas según qué ruta de código preguntara

¿Qué regla usa Excel para comparar dos cadenas de texto?

Excel compara texto con el word sort del locale del usuario, ignorando mayúsculas. El word sort es la collation por defecto de las funciones de comparación NLS de Windows: las letras se comparan por su orden lingüístico y no por sus code points, las letras acentuadas quedan junto a su letra base, y dos caracteres reciben tratamiento especial. El guion - y el apóstrofe ' se ignoran en la primera pasada, así que co-op y coop quedan uno junto al otro, y solo cuando el resto de las cadenas empata su presencia decide el orden. Cualquier otro signo de puntuación es significativo y ordena antes que los dígitos, y los dígitos ordenan antes que las letras

La tabla muestra lo que esto significa en la práctica, al lado de las dos comparaciones a las que un desarrollador Delphi más probablemente recurrirá. La columna Excel guarda los veredictos que Excel 16 devolvió para IF(A<B,...), que HotXLS reproduce desde v2.384.67

A vs B	Excel 16 / HotXLS	CompareStr (ordinal)	CompareText
"a-b" vs "ab"	mayor	menor	menor
"a'b" vs "ab"	mayor	menor	menor
"a~b" vs "ab"	menor	mayor	mayor
"a_b" vs "ab"	menor	menor	mayor
"ab" vs "AB"	igual	mayor	igual
"é" vs "f"	menor	mayor	mayor
"Z" vs "f"	mayor	menor	mayor

Dos consecuencias son fáciles de pasar por alto. Primera: el papel de desempate del guion implica que ="a-b"="ab" es FALSE: las cadenas son vecinas cercanas en el orden, pero no iguales. Segunda: la igualdad ignora las mayúsculas por completo, así que ab, AB y Ab son la misma clave para cualquier comparación. Ordenar 20 palabras de prueba con el Range.Sort de Excel da 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 del grupo ab, la posición del carácter ignorado decide

¿Cómo se fijó el orden de texto de Excel?

El orden de texto de Excel se identificó por medición, no por documentación, porque la documentación de Excel no nombra la collation. La prueba generó 4,000 pares de cadenas aleatorias a partir de puntuación ASCII, dígitos, ambas cajas de letras, espacios, é, ß, ä, caracteres chinos, formas full-width y el espacio sin corte, con longitudes de 0 a 4 y la mitad de los pares construidos como casi idénticos entre sí. Excel 16 evaluó IF(A<B,-1,IF(A=B,0,1)) para cada par, y los veredictos se cotejaron contra la API de comparación de Windows con distintos juegos de banderas

• NORM_IGNORECASE solo (word sort por defecto, locale del usuario): ningún desajuste genuino. Las únicas 7 diferencias eran celdas cuyo contenido completo era ', que Excel consume como carácter de prefijo de texto, así que eran artefactos de muestreo y no diferencias de collation
• NORM_IGNORECASE con SORT_STRINGSORT: 41 desajustes. El string sort trata el guion y el apóstrofe como símbolos ordinarios, que es exactamente el comportamiento que Excel no tiene
• Agregar NORM_IGNOREWIDTH: mal de otra manera, porque hace que las formas full-width y half-width de la misma letra comparen iguales, y Excel las mantiene separadas

Un segundo chequeo, elegido a mano, comparó los 190 pares sacados de 20 palabras tramposas y el resultado del Range.Sort de Excel sobre la misma columna. Ambos concordaron con el word sort simple de NORM_IGNORECASE, y esos 190 veredictos más el orden ordenado son ahora parte de la suite de regresión de HotXLS, corrida tanto por el engine clásico TXLSWorkbook como por el engine nativo XLSX TXLSXWorkbook

¿Por qué CompareText y la comparación ordinal se equivocan?

CompareText y la comparación ordinal se equivocan con el orden de Excel porque comparan unidades de código UTF-16, y el orden por code point pone la puntuación en lugares arbitrarios respecto de las letras. El guion es U+002D y el apóstrofe U+0027, ambos por debajo de cualquier letra, así que una comparación ordinal dice que "a-b" es menor que "ab" en vez de tratar el guion como desempate. La tilde U+007E está por encima de cualquier letra, así que "a~b" sale mayor, lo contrario de Excel. CompareText en la RTL de Delphi solo pliega a..z a mayúsculas y luego compara unidades de código, lo cual agrega una segunda distorsión: el underscore U+005F queda entre las letras mayúsculas y minúsculas, así que plegar a mayúsculas mueve "a_b" de debajo de "ab" a por encima de ella. Ninguna de las dos funciones sabe que é va entre e y f

Las herramientas usuales de Delphi caen a ambos lados de la línea:

• CompareStr, el operador < de strings y TComparer<string>.Default (que llama a CompareStr) son ordinales y distinguen mayúsculas, así que TArray.Sort<string> sin comparer pone Z antes que f
• CompareText y SameText son ordinales tras un plegado de mayúsculas solo ASCII
• AnsiCompareText y WideCompareText en la RTL de Delphi sobre Windows llaman a CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), la misma llamada que coincide con Excel. Un TStringList ordenado con sus valores por defecto (UseLocale True, CaseSensitive False) pasa por AnsiCompareText y por lo tanto también concuerda con Excel
• En objetivos POSIX la RTL de Delphi enruta AnsiCompareText por un collator de ICU, que es un algoritmo distinto con reglas de puntuación diferentes, y el AnsiCompareText de Free Pascal sobre Windows llama a CompareStringA tras convertir a la página de códigos ANSI, lo cual pierde cualquier carácter que esa página no pueda representar

Así que las funciones de la RTL conscientes del locale aciertan en Windows por implementación, no por contrato, y el código que necesita el orden de Excel hace bien en hacer la llamada a la API explícitamente. HotXLS tenía la misma mezcla internamente. Los operadores de comparación pasaban ambas cadenas a mayúsculas y comparaban code points, las ramas > / < de las funciones de criterios usaban la comparación Variant de Delphi con distinción de mayúsculas, y VLOOKUP / HLOOKUP emparejaban texto con esa comparación Variant sensible a mayúsculas también, razón por la cual VLOOKUP("ABC",A1:A20,1,FALSE) no podía encontrar abc. El ordenamiento de rangos ya usaba WideCompareText. Tres rutas, tres órdenes

¿Qué cambió en HotXLS v2.384.67?

Desde v2.384.67 las comparaciones texto contra texto en las rutas de cálculo y ordenamiento de HotXLS pasan por una sola función, XlsCompareText en lxStandard.pas, que llama a CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) y resta CSTR_EQUAL. Quienes la llaman son los seis operadores de comparación, las comparaciones elemento por elemento en fórmulas de matriz, las ramas >, <, >= y <= de los criterios estilo COUNTIF y las funciones de base de datos, VLOOKUP y HLOOKUP (exacta y aproximada), los helpers de ordenamiento detrás de las funciones de matriz dinámica y XLOOKUP / XMATCH, y el ordenamiento de rangos de ambos engines. Enrutar el ordenamiento de rangos por la misma función garantiza que el orden de ordenamiento y el orden de comparación no puedan volver a separarse, lo cual importa porque el VLOOKUP aproximado sobre texto solo tiene sentido cuando la columna se ordenó en el orden en que el lookup compara

uses
  System.Variants, lxHandleX;

var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Sheets.Add('Data');  // Calculate evalúa contra la hoja activa
    Writeln(VarToStr(Book.Calculate('="a-b">"ab"')));   // True: el guion solo desempata
    Writeln(VarToStr(Book.Calculate('="a-b"="ab"')));   // False: desempatadas, no iguales
    Writeln(VarToStr(Book.Calculate('="a~b"<"ab"')));   // True: puntuación primero
    Writeln(VarToStr(Book.Calculate('="ABC"="abc"')));  // True: mayúsculas ignoradas
  finally
    Book.Free;
  end;
end;

Las comparaciones entre tipos son una regla aparte y no cambiaron: todo número está por debajo de todo valor de texto y todo valor de texto por debajo de todo booleano, como se describe en el artículo sobre cadenas de comparación, operandos vacíos y SUMIF. El word sort solo aplica cuando ambos operandos son texto. El matching con wildcards también es aparte: un criterio como "a*" o "=ab" es una prueba de patrón o igualdad, cubierta en la guía de wildcards de Excel en COUNTIF, MATCH y DSUM, y la collation de la que aquí se habla decide solo los operadores de orden

El siguiente ejemplo carga las 20 palabras de prueba en una columna, la ordena con TXLSXWorksheet.SortRange, y verifica un conteo de criterios y un lookup. Los conteos son los que Excel 16 devolvió para la misma columna

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 sobre la misma columna: 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 de v2.384.67: el lookup comparaba con distinción de mayúsculas
    Sheet.Cells[1, 3].Formula := '=VLOOKUP("ABC",A1:A20,1,FALSE)';
    Book.Recalculate;
    Writeln(VarToStr(Sheet.Cells[1, 3].Value));                // abc

    // Una columna clave, 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;

TXLSXWorksheet.SortRange usa un merge sort estable, así que ab, AB y Ab, que comparan iguales, conservan el orden relativo que tenían antes del ordenamiento. Las celdas vacías van al final en ambas direcciones, como en Excel

¿Cómo igualo el orden de Excel en mi propio código Delphi?

Para igualar el orden de texto de Excel en su propio código Delphi, llame a CompareStringW con LOCALE_USER_DEFAULT y NORM_IGNORECASE, y no agregue SORT_STRINGSORT ni NORM_IGNOREWIDTH. El valor de retorno no es un resultado de comparación con signo: la API devuelve CSTR_LESS_THAN (1), CSTR_EQUAL (2) o CSTR_GREATER_THAN (3), y 0 cuando la llamada falla. Reste 2 para obtener la convención habitual negativo / cero / positivo, y pruebe por 0 primero, porque un fallo confundido con un resultado se vuelve -2, un "menor que" silencioso

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

// El orden de texto de Excel: word sort del locale del usuario, sin mayú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 es un fallo, no un resultado de comparación
  Result := R - CSTR_EQUAL;    // 1/2/3 se vuelven -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 (iguales, en cualquier orden), a-b, -ab, abc
end;

TArray.Sort no es estable, así que claves que comparan iguales, como ab y AB, pueden salir en cualquier orden; si el orden original de claves iguales importa, ordene un array de índices con la posición original como clave secundaria. El caso contrario también aparece: a veces una columna no debe seguir el orden de Excel, por ejemplo números de parte donde X-100 y X100 son códigos distintos y deben ordenarse por code point. TXLSXWorksheet.SortRange tiene una sobrecarga que toma un TXLSSortCompareEvent, un método con la firma function(const Left, Right: Variant): Integer of object, y lo usa en vez de la comparación integrada

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 custom también recibe celdas vacías (como Null): colóquelas usted
  if VarIsNull(Left) or VarIsNull(Right) then
    Exit(Ord(VarIsNull(Left)) - Ord(VarIsNull(Right)));
  Result := CompareStr(VarToStr(Left), VarToStr(Right));   // ordinal, con mayúsculas
end;

var
  Sheet: TXLSXWorksheet;   // una hoja llena, filas 2..501, columnas A..D
  Order: TPartNumberOrder;
begin
  // ...
  Order := TPartNumberOrder.Create;
  try
    // con clave en la columna A, ascendente
    Sheet.SortRange(2, 1, 501, 4, [1], [False], xlsSortByRows,
      xlsSortExcelLike, Order.Compare);
  finally
    Order.Free;
  end;
end;

Cuando se entrega un comparer custom, HotXLS se salta su manejo propio de celdas vacías y pasa los valores de clave crudos, así que el comparer debe lidiar con Null. Para una clave descendente HotXLS niega lo que el comparer devuelva, lo cual además manda las celdas vacías al tope salvo que el comparer lo contemple. Tenga presente que una columna ordenada así ya no está en el orden que el VLOOKUP aproximado de Excel o un XLOOKUP de búsqueda binaria esperan; las trampas de esos modos sobre datos ordenados en otro orden están cubiertas en la guía de modos de búsqueda binaria de XLOOKUP y XMATCH

¿Por qué el mismo workbook puede ordenar distinto en otra máquina?

El mismo workbook puede ordenar distinto en otra máquina porque el orden de texto de Excel depende del locale de usuario de Windows, y HotXLS deliberadamente sigue esa dependencia. El word sort es específico del idioma: la collation sueca, por ejemplo, coloca ä después de z, donde el inglés y el alemán la dejan junto a la a. Excel lo hereda del locale bajo el que corre, así que un workbook recalculado por un colega en Estocolmo puede devolver un COUNTIF(...,">y") distinto al del mismo archivo en un escritorio de Chicago. HotXLS pasa LOCALE_USER_DEFAULT para que sus resultados igualen a los de Excel en la misma máquina; cualquier locale fijo haría que HotXLS discrepara de Excel en cada máquina con una configuración distinta

Tres consecuencias prácticas se siguen para la generación del lado servidor:

• El locale que cuenta es el de la cuenta bajo la que corre el proceso. Un servicio de Windows o un application pool de IIS puede usar un formato regional distinto al del escritorio del desarrollador, así que los resultados observados en el IDE no son automáticamente lo que producción calcula
• Los resultados de fórmulas en caché escritos en el archivo reflejan el locale de la máquina que genera. Excel recalcula con su propio locale, así que un valor puede cambiar cuando el archivo se abre en otro lado y se recalcula; ese es el comportamiento de Excel, no un artefacto de HotXLS
• Los locales discrepan sobre todo en letras acentuadas, en combinaciones de letras que algunos idiomas tratan como una sola letra, y en escrituras no latinas, así que datos de prueba limitados a palabras inglesas simples no revelarán el problema

La frontera de plataforma es simple. HotXLS es una librería Windows, construida para Win32 y Win64 con Delphi y C++Builder y para objetivos win32 / win64 con Lazarus y Free Pascal, y todos estos builds llaman al mismo CompareStringW. No hay ruta de collation aparte para no Windows. El único fallback es para una llamada a la API fallida: si CompareStringW devuelve 0, XlsCompareText compara las cadenas en mayúsculas por unidad de código en vez de lanzar una excepción en medio de un recálculo, lo cual mantiene el cálculo corriendo pero ya no garantiza el orden de Excel

Referencia rápida: comparación de texto de Excel en HotXLS

• Regla: word sort del locale del usuario con NORM_IGNORECASE, sin SORT_STRINGSORT, sin NORM_IGNOREWIDTH, en HotXLS desde v2.384.67
• - y ' solo desempatan: ="a-b">"ab" es TRUE y ="a-b"="ab" es FALSE
• El resto de la puntuación ordena antes que los dígitos, y los dígitos antes que las letras: ="a~b"<"ab" y ="a0"<"ab" son TRUE
• Las mayúsculas jamás importan: ="ABC"="abc" es TRUE y VLOOKUP("ABC",...) encuentra abc
• Rutas cubiertas: operadores de comparación, comparaciones de matriz, criterios > / <, VLOOKUP / HLOOKUP, ordenamiento de matriz dinámica, SortRange en ambos engines
• No cubierto por esta regla: tipos mezclados (number < text < boolean) y criterios con wildcard, que tienen sus propias reglas
• En código Delphi: CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), verificar por 0, restar CSTR_EQUAL; evite CompareText, CompareStr y TComparer<string>.Default cuando el resultado debe concordar con Excel
• Los resultados dependen del locale de la cuenta que corre el código, tanto en Excel como en HotXLS

Las palabras corrientes ordenan igual bajo cualquier regla, así que solo los códigos con guion, la puntuación y los nombres acentuados exponen una collation equivocada. HotXLS ahora da la respuesta de Excel en todos ellos en ambos engines, XLS y XLSX. Los detalles de licenciamiento, versiones compatibles de Delphi y C++Builder y la descarga de prueba están en la página del componente Excel HotXLS para Delphi