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

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 el flag NORM_IGNORECASE. Los guiones y apóstrofes se saltan en la primera pasada y solo desempatan, así que ="a-b">"ab" es TRUE, mientras el resto de la puntuación se ordena antes que los dígitos y las letras, así que ="a~b"<"ab" también es TRUE. El mismo orden gobierna ahora los operadores de comparación, los criterios > / <, la ordenación de rangos y VLOOKUP

Nadie abre un bug titulado «desajuste de collation». Los reportes dicen que COUNTIF(A:A,">M") cuenta dos filas más en el servidor que en Excel, que una lista de precios ordenada por el servicio de informes coloca X-100 donde Excel no la pondría, o que VLOOKUP("ABC",...) devuelve #N/A aunque la columna contiene claramente abc. Los tres vienen 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é camino 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 en lugar de por sus code points, las letras acentuadas van 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 al lado del otro, y solo cuando el resto de las cadenas empatan su presencia decide el orden. Todo otro signo de puntuación es significativo y se ordena antes que los dígitos, y los dígitos antes que las letras

La tabla muestra lo que eso significa en la práctica, junto a 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 desempatador del guion significa que ="a-b"="ab" es FALSE: las cadenas son vecinas cercanas en la ordenación, 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 las 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ó midiendo, no por la documentación, porque la documentación de Excel no nombra la collation. La prueba generó 4.000 pares de cadenas aleatorios a partir de puntuación ASCII, dígitos, ambas cajas de letras, espacios, é, ß, ä, caracteres chinos, formas de ancho completo y el espacio de no separación, 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 conjuntos de flags

• NORM_IGNORECASE solo (word sort por defecto, locale de usuario): ningún desajuste genuino. Las únicas 7 diferencias eran celdas cuyo contenido entero era ', que Excel consume como carácter prefijo de texto, así que eran artefactos de muestreo más que 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 justo el comportamiento que Excel no tiene
• Añadiendo NORM_IGNOREWIDTH: mal de otra manera, porque hace que las formas de ancho completo y medio de la misma letra comparen iguales, y Excel las mantiene separadas

Una segunda comprobación, escogida a mano, comparó los 190 pares posibles extraídos de 20 palabras tramposas y el resultado del Range.Sort de Excel sobre la misma columna. Ambos coincidieron con el word sort NORM_IGNORECASE a secas, y esos 190 veredictos más el orden ordenado son hoy parte de la suite de regresión de HotXLS, ejecutada por ambos motores, el clásico TXLSWorkbook y el 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 de code points pone la puntuación en sitios arbitrarios respecto a 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 lugar 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 que añade una segunda distorsión: el subrayado U+005F está entre las letras mayúsculas y minúsculas, así que plegar a mayúsculas mueve "a_b" de debajo de "ab" a encima. Ninguna de las dos funciones sabe que é va entre e y f

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

• CompareStr, el operador < de strings y TComparer<string>.Default (que llama a CompareStr) son ordinales y sensibles a 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. Una TStringList ordenada con sus valores por defecto (UseLocale True, CaseSensitive False) pasa por AnsiCompareText y por tanto también coincide 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 code page ANSI, lo que pierde cualquier carácter que esa página no pueda representar

Así que las funciones de la RTL conscientes del locale están en lo cierto en Windows por implementación, no por contrato, y el código que necesita el orden de Excel sale ganando haciendo 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 de Variant de Delphi sensible a mayúsculas, y VLOOKUP / HLOOKUP emparejaban texto con esa comparación de Variant sensible a mayúsculas también, que es por lo que VLOOKUP("ABC",A1:A20,1,FALSE) no podía encontrar abc. La ordenación de rangos ya usaba WideCompareText. Tres caminos, tres órdenes

¿Qué cambió en HotXLS v2.384.67?

Desde v2.384.67 las comparaciones de texto contra texto en los caminos de cálculo y ordenación 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. Los llamadores son los seis operadores de comparación, las comparaciones elemento a 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 ordenación detrás de las funciones de matriz dinámica y XLOOKUP / XMATCH, y la ordenación de rangos de ambos motores. Enrutar la ordenación de rangos por la misma función garantiza que el orden de ordenación y el de comparación no puedan separarse de nuevo, lo cual importa porque el VLOOKUP aproximado sobre texto solo tiene sentido cuando la columna se ordenó en el orden en que la búsqueda 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: desempate roto, 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. La coincidencia con wildcards también es aparte: un criterio como "a*" o "=ab" es una prueba de patrón o de igualdad, tratada en la guía de wildcards de Excel en COUNTIF, MATCH y DSUM, y la collation de aquí decide solo los operadores de ordenación

El siguiente ejemplo carga las 20 palabras de prueba en una columna, la ordena con TXLSXWorksheet.SortRange, y comprueba un conteo de criterios y una búsqueda. 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: la búsqueda comparaba con mayúsculas sensibles
    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 de la ordenación. Las celdas vacías van al final en ambas direcciones, como en Excel

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

Para reproducir el orden de texto de Excel en su propio código Delphi, llame a CompareStringW con LOCALE_USER_DEFAULT y NORM_IGNORECASE, y no añada 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 de negativo / cero / positivo, y compruebe el 0 primero, porque un fallo confundido con un resultado se convierte en -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 de 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 convierten en -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 pieza donde X-100 y X100 son códigos distintos y deben ordenarse por code point. TXLSXWorksheet.SortRange tiene una sobrecarga que acepta un TXLSSortCompareEvent, un método con la firma function(const Left, Right: Variant): Integer of object, y lo usa en lugar 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 a medida 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, sensible a 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 suministra un comparer a medida, HotXLS se salta su propio manejo de vacíos y pasa los valores de clave en crudo, así que el comparer debe lidiar con Null. Para una clave descendente HotXLS niega lo que el comparer devuelva, lo que también mueve los vacíos arriba a menos que el comparer lo tenga en cuenta. 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 tratadas en la guía de los modos de búsqueda binaria de XLOOKUP y XMATCH

¿Por qué el mismo libro puede ordenarse distinto en otra máquina?

El mismo libro puede ordenarse distinto en otra máquina porque el orden de texto de Excel depende del locale de usuario de Windows, y HotXLS sigue deliberadamente esa dependencia. El word sort es específico del idioma: la collation sueca, por ejemplo, coloca ä después de z, donde inglés y alemán la mantienen junto a la a. Excel hereda eso del locale bajo el que corre, así que un libro recalculado por un colega de 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

Se siguen tres consecuencias prácticas 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 cacheados escritos en el archivo reflejan el locale de la máquina generadora. Excel recalcula con su propio locale, así que un valor puede cambiar cuando el archivo se abre en otro sitio 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 planas no revelarán el problema

La frontera de plataforma es simple. HotXLS es una biblioteca Windows, construida para Win32 y Win64 con Delphi y C++Builder y para objetivos win32 / win64 con Lazarus y Free Pascal, y todas esas compilaciones llaman al mismo CompareStringW. No hay camino 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 lugar de lanzar una excepción en medio de un recálculo, lo que mantiene el cálculo en marcha pero ya no garantiza el orden de Excel

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

• Regla: word sort del locale de 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 se ordena antes que los dígitos, los dígitos antes que las letras: ="a~b"<"ab" y ="a0"<"ab" son TRUE
• Las mayúsculas nunca importan: ="ABC"="abc" es TRUE y VLOOKUP("ABC",...) encuentra abc
• Caminos cubiertos: operadores de comparación, comparaciones de matriz, criterios > / <, VLOOKUP / HLOOKUP, ordenación de matriz dinámica, SortRange en ambos motores
• No cubierto por esta regla: tipos mezclados (número < texto < booleano) y criterios con wildcards, que tienen sus propias reglas
• En código Delphi: CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), compruebe el 0, reste CSTR_EQUAL; evite CompareText, CompareStr y TComparer<string>.Default cuando el resultado debe coincidir con Excel
• Los resultados dependen del locale de la cuenta que ejecuta el código, en Excel y en HotXLS por igual

Las palabras corrientes se 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 da ahora la respuesta de Excel sobre todos ellos en ambos motores, XLS y XLSX. Los detalles de licencias, versiones compatibles de Delphi y C++Builder y la descarga de prueba están en la página del componente Excel Delphi HotXLS