Artículo técnico

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

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

Los síntomas rara vez mencionan la 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 informes coloca X-100 donde Excel no la 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 de Windows actual, 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 orden lingüístico en lugar de por code point, así 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óstrofe ' se saltan en la primera pasada, lo que deja co-op justo al lado de coop; solo cuando el resto de los caracteres empatan deciden su presencia y su posición. Todo otro signo de puntuación cuenta con peso completo y se ordena antes que los dígitos, y los dígitos antes que las letras

La tabla enfrenta el veredicto de Excel para IF(A<B,...) con las dos comparaciones que un desarrollador Delphi escribiría primero

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

Dos consecuencias suelen sorprender. Como el guion solo desempata, ="a-b"="ab" es FALSE: las dos cadenas son vecinas inmediatas en una ordenación 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. Con un locale de usuario inglés, una columna de 20 palabras complicadas se ordena como 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: el guion final primero, el guion inicial al final

Diagrama word sort de HotXLS clasificando 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 que dígitos antes que letras con las mayúsculas ignoradas
La puntuación y el espacio se ordenan antes que los dígitos y los dígitos antes que las letras, las mayúsculas se pliegan fuera, y el guion con el apóstrofe solo desempatan; por eso a-b aterriza al lado de ab y aun así compara como mayor

Cómo comprobar la collation de Excel por su cuenta

La documentación de Excel nunca nombra la collation que usa, así que la forma fiable 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 conjuntos 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 merece la pena generar mezclan puntuación ASCII, dígitos, ambas cajas, espacios, letras acentuadas como é, ß y ä, caracteres CJK, formas de ancho completo y el espacio de no separación, con muchos casos casi iguales que difieren en un solo carácter. Palabras aleatorias de un diccionario son una mala muestra, porque las letras planas se ordenan igual bajo todas las reglas de este artículo

Cada conjunto de flags conduce a resultados claramente distintos:

  • NORM_IGNORECASE solo, con LOCALE_USER_DEFAULT, es el word sort llano y el que sigue Excel
  • Añadir SORT_STRINGSORT cambia al string sort, donde el guion y el apóstrofe pasan a ser símbolos ordinarios con peso completo, así que cada par al estilo a-b / ab sale al revés
  • Añadir NORM_IGNOREWIDTH hace que una letra de ancho completo iguale a su forma de medio ancho, y Excel mantiene ambas separadas

Una trampa está en el propio banco de pruebas. Excel trata un apóstrofe inicial tecleado en una celda como el prefijo «guardar como texto», así que una celda cuya entrada completa es ' acaba vacía, y cualquier par construido así informa de un desajuste que no tiene nada que ver con la collation. Construya esos pares con literales de cadena en la fórmula (="'") u omítalos

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

CompareText y la comparación ordinal fallan frente al orden de Excel porque comparan unidades de código UTF-16, y el orden de code points dispersa la puntuación alrededor del alfabeto. 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", donde Excel trata el guion como desempate. 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: solo pliega a..z a mayúsculas y luego compara unidades de código, y como el subrayado (U+005F) está 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 contrastando el orden de code points con el word sort de Excel: la comparación ordinal pone el apóstrofe, el guion y el subrayado en 0x27, 0x2D y 0x5F alrededor de las letras así que a-b frente a ab sale menor, mientras el word sort empuja la puntuación antes de dígitos y letras y trata solo el guion y el apóstrofe como desempatadores
Los code points dispersan la puntuación alrededor de las letras, así que las comparaciones ordinal y con plegado ASCII invierten los veredictos; el word sort mueve la puntuación delante de los dígitos y degrada el guion y el apóstrofe a desempatadores

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

  • CompareStr, el operador < de strings y TComparer<string>.Default 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 llamada que coincide con Excel. Una TStringList ordenada con sus valores por defecto (UseLocale True, CaseSensitive False) pasa por AnsiCompareText y también coincide con Excel
  • En objetivos 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 sobre Windows convierte a la code page ANSI y llama a CompareStringA, lo que pierde todo 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 cómo están implementadas hoy. El código que debe coincidir con Excel debería hacer la llamada a la API de forma explícita y decirlo. El problema más difícil aparece en cualquier motor de fórmulas donde cada camino de código toma la comparación más cercana: mayúsculas y comparar code points en los operadores = y <, una comparación de Variant sensible a mayúsculas en la rama > / < de los criterios de COUNTIF, otra dentro de VLOOKUP (que es exactamente por lo que VLOOKUP("ABC",A1:A20,1,FALSE) no encuentra abc), y una llamada al locale en la ordenación de rangos. Cada camino es defendible por separado. Juntos producen tres órdenes distintos sobre la misma columna

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

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

Diagrama de enrutado de HotXLS mostrando cada camino de comparación de texto, desde los seis operadores de comparación y los criterios estilo COUNTIF pasando por VLOOKUP, HLOOKUP, XLOOKUP y la ordenación de rangos de ambos motores, 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, búsquedas y ordenación comparten una función, así que el orden que ve Excel y el orden con 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: 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;

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 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. Los criterios con wildcards como "a*" o "=ab" son pruebas de patrón y de igualdad con reglas propias, tratadas en la guía de wildcards de Excel en COUNTIF, MATCH y DSUM; la collation de la que hablamos 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 según 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 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 tanto si la clave es ascendente como descendente, que es donde las pone Excel

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

Para reproducir el orden de texto de Excel fuera de un libro, 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 tratar 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 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;

Si HotXLS ya está en la cláusula uses, lxStandard.XlsCompareText es pública y devuelve el mismo orden. Se diferencian 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 unidad de código para que un recálculo no quede interrumpido a medias. Para una ordenación 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 las claves iguales importa, ordene un array 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 pieza son el caso clásico: X-100 y X100 son códigos distintos y deben ordenarse por code point. SortRange tiene una sobrecarga que acepta 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 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;

Con un comparer a medida, SortRange entrega los valores de clave en crudo y deja la colocación de vacíos en sus manos, por eso el comparer de arriba trata Null primero. Para una clave descendente la ordenación niega lo que el comparer devuelva, así que en esa dirección los vacíos suben arriba salvo que el comparer lo tenga en cuenta. Una columna ordenada así ya no está en el orden que el VLOOKUP aproximado o un XLOOKUP de búsqueda binaria esperan; lo que falla en esos modos con datos ordenados de otra forma está tratado 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?

Un 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 esa dependencia a propósito. El word sort es específico del idioma. La collation sueca, por ejemplo, coloca ä después de z, mientras que inglés y alemán la mantienen junto a a, así 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 igualen a las de 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 otra forma

Se siguen tres consecuencias prácticas para la generación del lado servidor:

  • El locale que cuenta pertenece a la cuenta bajo la que corre el proceso. Un servicio de Windows o un application pool de IIS puede tener una configuración regional distinta a la del escritorio del desarrollador, así que los resultados vistos 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 y se recalcula en otro sitio; 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 planas nunca revelarán la diferencia, así que meta algunos nombres como Ångström y Öberg en la muestra

El camino que coincide con Excel es el de Windows: cada compilación Win32 y Win64, ya se compile con Delphi, C++Builder, o Lazarus y Free Pascal, llama al mismo CompareStringW. Cuando una compilación apunta a una plataforma no Windows, XlsCompareText usa el WideCompareText de la RTL, cuyo orden procede de la biblioteca de collation de esa plataforma

Las palabras corrientes se ordenan igual bajo todas las reglas, y por eso una collation equivocada suele sobrevivir a las pruebas hasta que llegan códigos de pieza 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 HotXLS para Delphi