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 B | Excel / 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 |
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
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, conLOCALE_USER_DEFAULT, es el word sort puro y el que Excel sigue - Agregar
SORT_STRINGSORTcambia a string sort, donde el guion y el apóstrofo pasan a ser símbolos ordinarios con peso completo, así que cada par estiloa-b/absale al revés - Agregar
NORM_IGNOREWIDTHhace 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
Las herramientas usuales de Delphi caen a ambos lados de la línea:
CompareStr, el operador<de cadenas yTComparer<string>.Defaultson ordinales y distinguen mayúsculas, así queTArray.Sort<string>sin comparador poneZantes quefCompareTextySameTextson ordinales tras un plegado de mayúsculas solo ASCIIAnsiCompareTextyWideCompareTexten la RTL de Delphi en Windows llaman aCompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), la llamada que coincide con Excel. UnTStringListordenado con sus valores por defecto (UseLocaleTrue,CaseSensitiveFalse) pasa porAnsiCompareTexty también concuerda con Excel- En destinos POSIX la RTL de Delphi enruta
AnsiCompareTextpor un collator de ICU, un algoritmo distinto con reglas de puntuación diferentes. ElAnsiCompareTextde Free Pascal en Windows convierte a la página de códigos ANSI y llama aCompareStringA, 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
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ömyÖbergen 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