Artículo técnico

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

En Excel, ="a-b">"ab" devuelve TRUE, y ="a~b"<"ab" también devuelve TRUE. Pase esos mismos pares al CompareText de Delphi y ambas respuestas se invierten, porque el guion queda por debajo de las letras en orden de code points y la tilde queda por encima. Excel compara texto con el "word sort" del locale de usuario de Windows, sin distinción de mayúsculas, donde guiones y apóstrofos solo desempatan y cualquier otro signo de puntuación se ordena antes que dígitos y letras

Los síntomas casi nunca mencionan collation. Un COUNTIF(A:A,">M") calculado en el servidor cuenta dos filas más que la misma fórmula en Excel. Una lista de precios ordenada por un servicio de reportes coloca X-100 donde Excel no lo pondría. VLOOKUP("ABC",...) devuelve #N/A aunque la columna contiene claramente abc. Los tres casos se reducen a una pregunta: cuando ambos operandos son texto, ¿cuál es el menor? Excel tiene una respuesta precisa, y casi nunca es la que el código Delphi da por defecto

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

Excel compara dos valores de texto con el word sort del locale de usuario actual de Windows, sin distinguir mayúsculas. Word sort es la collation por defecto de las funciones de comparación NLS de Windows: las letras se comparan en orden lingüístico y no por code point, de modo que é cae entre e y f, y mayúsculas y minúsculas se pliegan juntas. Dos caracteres reciben tratamiento especial. El guion - y el apóstrofo ' se saltan en la primera pasada, lo que coloca co-op justo al lado de coop; solo cuando los caracteres restantes empatan su presencia, y su posición, deciden el orden. Cualquier otro signo de puntuación cuenta con peso completo y se ordena antes que los dígitos, y los dígitos se ordenan antes que las letras

La tabla enfrenta el veredicto de Excel para IF(A<B,...) con las dos comparaciones que un desarrollador Delphi escribe primero con más probabilidad

A vs BExcel / HotXLSCompareStr (ordinal)CompareText
"a-b" vs "ab"mayormenormenor
"a'b" vs "ab"mayormenormenor
"a~b" vs "ab"menormayormayor
"a_b" vs "ab"menormenormayor
"ab" vs "AB"igualmayorigual
"é" vs "f"menormayormayor
"Z" vs "f"mayormenormayor

Hay dos consecuencias que despistan. Como el guion solo desempata, ="a-b"="ab" es FALSE: las dos cadenas son vecinas inmediatas en un ordenamiento y aun así no son iguales. Y como las mayúsculas se pliegan por completo, ab, AB y Ab son una sola clave para todos los operadores de comparación. Bajo un locale de usuario inglés, una columna de 20 palabras incómodas se ordena así: 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 decide la posición del carácter saltado: guion final primero, guion inicial al final

Diagrama de word sort de HotXLS que ordena las 20 palabras de prueba desde a b, a.b, a_b y a~b pasando por a0 y a1b, luego el grupo ab con AB y Ab, variantes con guion y apóstrofe como a-b y a'b, hasta abc, b, e, e-acute, f y Z, mostrando puntuación antes de dígitos antes de letras con las mayúsculas ignoradas
La puntuación y el espacio ordenan antes que los dígitos y los dígitos antes que las letras, las mayúsculas se pliegan, y el guion con el apóstrofe solo desempatan; por eso a-b queda al lado de ab y aun así compara como mayor

Comprobar la collation contra Excel por su cuenta

La documentación de Excel nunca nombra la collation que usa, así que la forma confiable de confirmarla en su propia máquina es dejar que Excel juzgue un lote de pares de cadenas y comparar sus veredictos con CompareStringW bajo distintos juegos de flags. Ponga los pares en dos columnas, haga que Excel evalúe =IF(A1<B1,-1,IF(A1=B1,0,1)) para cada fila y pase los mismos pares por la API. Los pares que valen la pena generar mezclan puntuación ASCII, dígitos, mayúsculas y minúsculas, espacios, letras acentuadas como é, ß y ä, caracteres CJK, formas full-width y el espacio de no separación, con muchos casos al borde que difieren en un solo carácter. Palabras al azar de un diccionario son una mala muestra, porque las letras simples se ordenan igual bajo todas las reglas de este artículo

Los juegos de flags llevan a resultados claramente distintos:

  • Solo NORM_IGNORECASE, con LOCALE_USER_DEFAULT, es el word sort puro y el que Excel sigue
  • Agregar SORT_STRINGSORT cambia a string sort, donde el guion y el apóstrofo pasan a ser símbolos ordinarios con peso completo, así que cada par estilo a-b / ab sale al revés
  • Agregar NORM_IGNOREWIDTH hace que una letra full-width sea igual a su forma half-width, y Excel mantiene esas dos separadas

Una trampa vive en el propio banco de pruebas. Excel trata un apóstrofo inicial escrito en una celda como el prefijo de "guardar como texto", así que una celda cuya entrada completa es ' termina vacía, y cualquier par construido así reporta un desajuste que no tiene nada que ver con la collation. Construya esos pares con literales de cadena en la fórmula (="'") u omita los

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

CompareText y la comparación ordinal fallan en reproducir el orden de Excel porque comparan unidades de código UTF-16, y el orden por code points dispersa la puntuación alrededor del alfabeto. El guion es U+002D y el apóstrofo U+0027, ambos por debajo de cualquier letra, así que una comparación ordinal dice que "a-b" es menor que "ab", mientras Excel trata el guion como desempatador. La tilde es U+007E, por encima de cualquier letra, lo que hace "a~b" mayor, lo contrario de Excel. CompareText añade una segunda distorsión propia: pliega solo a..z a mayúsculas y luego compara unidades de código, y como el subrayado (U+005F) queda entre el rango de mayúsculas y el de minúsculas, el plegado mueve "a_b" de por debajo de "ab" a por encima. Ninguna de las dos funciones tiene idea de que é va entre e y f

Diagrama de comparación de HotXLS que contrasta el orden por code point con el word sort de Excel: la comparación ordinal pone el apóstrofe, el guion y el underscore en 0x27, 0x2D y 0x5F alrededor de las letras, así que a-b versus ab sale menor, mientras que el word sort empuja la puntuación antes de dígitos y letras y solo trata el guion y el apóstrofe como desempates
Los code points dispersan la puntuación alrededor de las letras, así que las comparaciones ordinal y con plegado ASCII voltean los veredictos; el word sort mueve la puntuación delante de los dígitos y degrada el guion y el apóstrofe a desempates

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

  • CompareStr, el operador < de cadenas y TComparer<string>.Default son ordinales y distinguen mayúsculas, así que TArray.Sort<string> sin comparador 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 en Windows llaman a CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), la llamada que coincide con Excel. Un TStringList ordenado con sus valores por defecto (UseLocale True, CaseSensitive False) pasa por AnsiCompareText y también concuerda con Excel
  • En destinos POSIX la RTL de Delphi enruta AnsiCompareText por un collator de ICU, un algoritmo distinto con reglas de puntuación diferentes. El AnsiCompareText de Free Pascal en Windows convierte a la página de códigos ANSI y llama a CompareStringA, que pierde todo carácter que esa página de códigos no pueda representar

Así que las funciones de la RTL conscientes del locale aciertan en Windows por cómo están implementadas hoy, no por contrato. El código que debe concordar con Excel debería hacer la llamada a la API explícitamente y dejarlo dicho. El problema más duro aparece en cualquier motor de fórmulas donde cada ruta de código toma la comparación que tenga más a mano: pasar a mayúsculas y comparar code points en los operadores = y <, una comparación Variant sensible a mayúsculas en la rama > / < de los criterios de COUNTIF, otra dentro de VLOOKUP (que es exactamente cómo VLOOKUP("ABC",A1:A20,1,FALSE) no encuentra abc) y una llamada al locale en el ordenamiento de rangos. Cada ruta se defiende por separado. Juntas producen tres órdenes distintos sobre la misma columna

Una sola comparación detrás de fórmulas, búsquedas y ordenamiento en HotXLS

HotXLS pasa toda comparación de texto contra texto en su código de cálculo y ordenamiento por una sola función, XlsCompareText en lxStandard.pas, que llama a CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) y le resta CSTR_EQUAL. La usan 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 de las funciones de base de datos, VLOOKUP y HLOOKUP en modo exacto y aproximado, el ordenamiento detrás de las funciones de matriz dinámica y de XLOOKUP / XMATCH, y SortRange tanto en TXLSWorksheet como en TXLSXWorksheet. Poner el ordenamiento de rangos sobre la misma función fue el punto de diseño decisivo. Un VLOOKUP aproximado sobre texto solo da respuestas sensatas cuando la columna se ordenó en el orden en que la búsqueda compara, y compartir una sola función hace imposible que esos dos órdenes se separen

Diagrama de enrutado de HotXLS que muestra cada ruta de comparación de texto, desde los seis operadores de comparación y los criterios estilo COUNTIF pasando por VLOOKUP, HLOOKUP, XLOOKUP y el ordenamiento de rangos de ambos engines, convergiendo en XlsCompareText, que llama a CompareStringW con LOCALE_USER_DEFAULT y NORM_IGNORECASE y mapea 1, 2, 3 a -1, 0, 1
Operadores, criterios, lookups y ordenamiento comparten una función, así que el orden que ve Excel y el orden con el que ordena HotXLS no pueden separarse; la API devuelve 1, 2 o 3, y cero significa fallo, no menor que

La forma más rápida de ver la regla desde Delphi es TXLSXWorkbook.Calculate, que evalúa una cadena de fórmula contra la hoja activa y devuelve el resultado como Variant

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;

El word sort solo aplica cuando ambos operandos son texto. Los tipos mezclados siguen una jerarquía fija donde todo número está por debajo de todo texto y todo texto por debajo de todo boolean, como se describe en el artículo sobre cadenas de comparación, operandos en blanco y SUMIF. Los criterios con comodines como "a*" o "=ab" son pruebas de patrón y de igualdad con reglas propias, cubiertas en la guía de comodines de Excel en COUNTIF, MATCH y DSUM; la collation que se trata aquí decide los operadores de orden

El siguiente ejemplo carga las 20 palabras en la columna A de una hoja, comprueba tres conteos de criterios y una búsqueda, y luego ordena la columna con TXLSXWorksheet.SortRange. Los conteos esperados se deducen directamente del orden mostrado antes: once palabras se ordenan después del grupo ab, once antes de a-b, y los tres miembros del grupo ab se suman a los once 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 por el orden 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")')));

    // Encuentra abc: la búsqueda compara sin distinguir mayúsculas, como Excel
    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;

SortRange ejecuta 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 en blanco van al fondo tanto si la clave es ascendente como descendente, que es donde Excel las pone

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

Para reproducir el orden de texto de Excel fuera de un workbook, llame a CompareStringW con LOCALE_USER_DEFAULT y NORM_IGNORECASE, y deje fuera SORT_STRINGSORT y NORM_IGNOREWIDTH. Vigile el valor de retorno. La API no devuelve un resultado con signo; devuelve CSTR_LESS_THAN (1), CSTR_EQUAL (2) o CSTR_GREATER_THAN (3), y 0 cuando la llamada falla. Restar 2 da la convención habitual de negativo / cero / positivo, pero solo después de manejar el caso 0; de lo contrario una llamada fallida se convierte en -2 y se lee como "menor que"

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;

Si HotXLS ya está en la cláusula uses, lxStandard.XlsCompareText es público y devuelve el mismo orden. Ambos difieren solo en una llamada a la API fallida: la función de arriba lanza una excepción, mientras que XlsCompareText recurre a comparar las cadenas en mayúsculas por unidades de código para que un recálculo nunca se interrumpa a mitad. Para un ordenamiento independiente, lanzar la excepción es la mejor opción, porque un orden silenciosamente equivocado es más difícil de rastrear que una excepción

TArray.Sort no es estable, así que claves iguales como ab y AB pueden salir en cualquier orden. Cuando el orden original de claves iguales importa, ordene un arreglo de índices y use la posición original como clave secundaria

A veces una columna debe ignorar deliberadamente el orden de Excel. Los números de parte son el caso clásico: X-100 y X100 son códigos distintos y deberían ordenarse por code point. SortRange tiene una sobrecarga que recibe un TXLSSortCompareEvent, declarado como 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 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;

Con un comparador personalizado, SortRange entrega los valores de clave crudos y le deja a usted la ubicación de los blancos, razón por la cual el comparador de arriba trata Null primero. Para una clave descendente el ordenamiento niega lo que el comparador devuelva, así que en esa dirección los blancos suben arriba salvo que el comparador lo contemple. Una columna ordenada así ya no está en el orden que un VLOOKUP aproximado o un XLOOKUP por búsqueda binaria espera; lo que sale mal en esos modos con datos ordenados de otra forma está cubierto en la guía de los modos de búsqueda binaria de XLOOKUP y XMATCH

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

Un workbook puede ordenar distinto en otra máquina porque el orden de texto de Excel depende del locale de usuario de Windows, y HotXLS sigue esa dependencia a propósito. El word sort es específico por idioma. La collation sueca, por ejemplo, coloca ä después de z, mientras el inglés y el alemán la dejan junto a la a, de modo que un archivo 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 respuestas coincidan con Excel en la misma máquina. Fijar un locale arbitrario parecería más ordenado y haría que HotXLS discrepara de Excel en cada máquina configurada de forma distinta

Para la generación del lado del servidor eso tiene tres consecuencias prácticas:

  • 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 tener configuraciones regionales distintas a las del escritorio del desarrollador, así que lo que se ve en el IDE no es automáticamente lo que producción calcula
  • Los resultados de fórmula cacheados escritos en el archivo reflejan el locale de la máquina que lo generó. Excel recalcula con su propio locale, así que un valor puede cambiar al abrir el archivo y recalcularlo en otro lado; Excel se comporta igual con sus propios archivos
  • Los locales discrepan sobre todo en letras acentuadas, en pares de letras que algunos idiomas tratan como una sola letra y en escrituras no latinas. Datos de prueba hechos de palabras inglesas simples nunca expondrán la diferencia, así que meta unos nombres como Ångström y Öberg en la muestra

La ruta que iguala a Excel es la de Windows: cada build Win32 y Win64, ya sea compilada con Delphi, C++Builder, o Lazarus y Free Pascal, llama al mismo CompareStringW. Cuando un build apunta a una plataforma que no es Windows, XlsCompareText usa el WideCompareText de la RTL, cuyo orden viene de la biblioteca de collation de esa plataforma

Las palabras ordinarias se ordenan igual bajo cualquier regla, y por eso una collation equivocada suele sobrevivir a las pruebas hasta que llegan códigos de parte con guion, puntuación o nombres de cliente acentuados. HotXLS da la respuesta de Excel con esas entradas en ambos motores, XLS y XLSX, desde los operadores de comparación hasta SortRange. Las versiones de Delphi y C++Builder soportadas, las licencias y la descarga de prueba están en la página del componente Excel en Delphi HotXLS