Una exportación Delphi guarda el número de cuenta 123456789012 como número; una columna de Excel ancha muestra 1.23457E+11, mientras que =A1&"" devuelve los doce dígitos enteros. Excel convierte números en texto por dos reglas, once caracteres para la vista General y quince dígitos significativos dentro de las fórmulas, así que una exportación HotXLS lee la primera de FormattedText y construye la segunda con el motor de fórmulas
¿Cuántos caracteres admite el formato General?
Once, dejando fuera el signo menos del recuento. El ECMA-376 Parte 1 §18.8.30 dice que la aplicación «intentará mostrar el número completo hasta 11 dígitos (incl. punto decimal)», y su regla de coma flotante lo precisa más: la «longitud total máxima para la vista de celda es 11, sin incluir el signo negativo». La norma luego esboza cuatro condiciones para pasar a forma exponencial y ahí se queda. El redondeo queda abierto. También el punto de conmutación para números pequeños. El comportamiento de abajo es lo que Excel muestra en una columna ancha, y es lo que HotXLS reproduce en el texto de vista de una celda y en TEXT(A1, "General"):
- Un número cuya parte entera cabe en once caracteres se queda en notación fija y renuncia a decimales:
12345.6789012345se muestra como12345.6789, y12345678901.5redondea a12345678902 - Una parte entera de doce dígitos no cabe, así que
123456789012se convierte en1.23457E+11, con la mantisa redondeada para dejar sitio al exponente - Un número por debajo de 1 mantiene la notación fija mientras esa muestre al menos tantos dígitos significativos como la forma científica de seis dígitos:
0.00012345678se muestra como0.000123457, pero0.0000123456789se convierte en1.23457E-05 - El exponente siempre lleva signo y al menos dos dígitos, así que 1020 se muestra como
1E+20
El signo queda fuera del presupuesto. -12345678901 se muestra con los once dígitos enteros detrás de su signo menos, doce caracteres en total, mientras que -123456789012 se convierte en -1.23457E+11. El tipo Variant que asignes no cambia el resultado. Excel guarda todo valor numérico de celda como double, y HotXLS pasa los valores Integer, Int64, Currency y Double por la misma regla de once caracteres, así que Cells[1, 1].Value := Int64(123456789012) también se muestra como 1.23457E+11. La regla se extiende a los formatos personalizados: el texto literal de una sección se añade alrededor del resultado General, así que General" kg" muestra el mismo número como 1.23457E+11 kg. Los formatos con secciones vacías o solo literales siguen sus propias reglas y pueden esconder el número por completo
Un límite más se queda fuera de cualquier biblioteca. La norma añade que «el método óptimo de visualización se basa en el ancho de celda disponible», y en una columna de ancho por defecto Excel comprime más todavía, hasta 1.2E+11 o una fila de signos #, según las métricas de fuente de la máquina que muestra la hoja. Un servidor que genera archivos no tiene columna en pantalla, así que HotXLS deja ese último paso a quien dibuje la celda. Ensancha la columna antes de comparar. Si no, estarás midiendo el fallback de ancho de Excel contra la regla General, y los dos discreparán en cualquier valor más largo que la columna
El texto de fórmula conserva quince dígitos significativos
En cuanto un número se convierte en texto dentro de una fórmula —el operador &, CONCAT, TEXTJOIN, LEN, LEFT— se hace cargo otra regla. El ECMA-376 §18.17 define la gramática de fórmulas pero ningún formato de número a texto para este paso, así que la salida de Excel es la única referencia. Excel redondea a quince dígitos significativos y los escribe en notación posicional plana mientras eso ocupe como mucho veinte caracteres además del signo. De ahí que =1/3&"" devuelva 0.333333333333333, quince treses. =1E+19&"" devuelve 10000000000000000000, y =1E-18&"" devuelve 0.000000000000000001. Pasados los veinte caracteres el texto pasa a notación científica con el mismo exponente con signo de dos dígitos que usa la rejilla: 1E+20, 1.23456789012345E-05. El separador es siempre un punto. HotXLS evalúa fórmulas sin consultar la configuración regional de la máquina que ejecuta tu programa, así que una clave de búsqueda construida con & sale idéntica en un servidor inglés y en uno alemán
El texto de fórmula tiene una esquina donde el propio Excel depende de la máquina. CHAR y CODE mapean números por encima de 127 a través de la página de códigos ANSI del ordenador que ejecuta Excel, así que =CHAR(128) es el signo del euro en una instalación Windows de Europa occidental y un carácter distinto bajo otras páginas de códigos. Un servicio Delphi no puede saber qué página de códigos usa el lector final. HotXLS evalúa esos códigos como Windows-1252, la página de códigos de Europa occidental, en todas las máquinas, así que =CHAR(150) da un en dash en cualquier servidor. Si tus libros van destinados a otra página de códigos, calcula esos caracteres en Delphi y guárdalos como texto
FloatToStr no sigue ninguna de las dos reglas
| Valor | Vista General / TEXT | Texto de fórmula (&"") | FloatToStr de Delphi |
|---|---|---|---|
| 123456789012 | 1.23457E+11 | 123456789012 | 123456789012 |
| 1234567890123456 | 1.23457E+15 | 1234567890123460 | 1.23456789012346E15 |
| 1/3 | 0.333333333 | 0.333333333333333 | 0.333333333333333 |
| 1E20 | 1E+20 | 1E+20 | 1E20 |
| 0.000001 | 0.000001 | 0.000001 | 1E-6 |
| 0.00000123456789 | 1.23457E-06 | 0.00000123456789 | 1.23456789E-6 |
La última columna explica la deriva. FloatToStr usa el formato general de Delphi con quince dígitos, escribe exponentes sin signo más ni relleno de ceros, y pasa a notación exponencial con sus propios umbrales: por debajo de 0.00001, o en cuanto la parte entera necesita más de quince dígitos. Además toma su separador decimal del FormatSettings del host. Es una función estupenda para logs. No reproduce ninguna de las dos reglas de Excel, y un único ayudante al que se le pida servir vista y texto de fórmula se equivocará en cada fila donde esas reglas discrepen —cuatro de las seis filas de arriba
¿Qué texto deben usar una exportación o una clave de búsqueda?
Elige la regla preguntándote dónde se va a comparar la cadena. Texto para personas —un informe PDF, una tabla HTML, un CSV que alguien abre junto al libro— debe coincidir con la rejilla, así que lee el texto de vista de la celda. El texto usado como datos debe seguir la regla de fórmula: una clave concatenada, un valor comparado con algo que produjo una fórmula de Excel, una cadena de búsqueda. Un CSV que se va a volver a cargar en Excel o en una base de datos también cuenta como datos. Escribir 1.23457E+11 en tal archivo tira cinco dígitos del número de cuenta para siempre, porque el importador parsea lo que lee y no puede recuperar el original. El primer ejemplo imprime las tres cadenas lado a lado. Las muestras son Doubles, pero un valor Int64 o Currency se muestra igual:
uses
SysUtils, Variants, lxHandleX;
procedure CompareNumberTexts;
const
Samples: array[0..5] of Double =
(123456789012.0, 1234567890123456.0, 1 / 3, 1E20, 0.000001,
0.00000123456789);
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
I: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Numbers');
for I := 0 to High(Samples) do
begin
Sheet.Cells[1, 1].Value := Samples[I]; // formato General por defecto
Writeln(Format('%-22s %-22s %s', [
string(Sheet.Cells[1, 1].FormattedText), // lo que muestra la rejilla
VarToStr(Sheet.Calculate('A1&""')), // lo que ve una fórmula
FloatToStr(Samples[I])])); // la idea de la propia RTL
end;
finally
Book.Free;
end;
end;
Cada línea impresa debería coincidir con la fila correspondiente de la tabla. Solo se mueven los separadores. En un host alemán la columna de vista y la columna de FloatToStr cambian a coma decimal, como hace la rejilla de Excel en esa máquina, mientras la columna de fórmula conserva su punto. El segundo ejemplo mete la elección en un exportador que escribe el texto de informe como lo muestra Excel y las columnas de clave como las construyen las fórmulas de Excel:
uses
SysUtils, Classes, Variants, lxHandleX, lxCalc;
// Texto de informe como se muestra en Excel, más una columna de clave independiente del locale
procedure ExportInvoiceLines(Sheet: TXLSXWorksheet; FirstRow, LastRow: Integer;
Output: TStrings);
var
Row: Integer;
Shown, Key: string;
begin
for Row := FirstRow to LastRow do
begin
Shown := Sheet.Cells[Row, 3].FormattedText; // importe, tal como se muestra
Key := VarToStr(Sheet.EvaluateFormulaAt(Row, 6,
Format('A%d&"-"&C%d', [Row, Row])).Value); // clave de importe de factura
Output.Add(Format('%s;%s', [Shown, Key]));
end;
end;
EvaluateFormulaAt evalúa una fórmula como si estuviera en la celda dada sin almacenarla, así que la clave sale de la misma regla que Excel aplica a una columna de clave dentro del libro. Si el importe lleva un formato de moneda o de miles, FormattedText aplica ese formato, y la regla General solo gobierna las celdas sin formato. Realimentar el valor de vista redondeado al cálculo es otro interruptor de Excel, la precisión como se muestra, y reescribe el double almacenado en sí, así que mantenlo fuera de una exportación que solo necesita texto
¿Qué pasa con un ID de más de quince dígitos?
Pierde su cola en el momento en que se convierte en texto. Un double sostiene todos los enteros hasta 253, unos 9.007 × 1015, así que el número de cliente de dieciséis dígitos 1234567890123456 sobrevive intacto en la celda y en el archivo, donde diecisiete dígitos significativos bastan para un viaje de ida y vuelta sin pérdidas del double XLSX. El texto es otra historia. La regla General muestra 1.23457E+15, y la regla de fórmula redondea a quince dígitos significativos y rellena con un cero, así que =A1&"" devuelve 1234567890123460 — otro cliente. Excel también conserva solo quince dígitos significativos de un número tecleado en una celda, así que un usuario que reteclee el valor cae en 1234567890123450. Un cliente acaba con tres números: 1234567890123456 en la celda, 1234567890123460 en una clave de fórmula y 1234567890123450 tras reteclear
Guarda los identificadores como texto desde el principio. Números de cuenta, números de tarjeta e IDs de pedido son etiquetas escritas en dígitos; nadie los suma, y un cero a la izquierda pertenece a la etiqueta. Asignados como cadenas llegan a la rejilla y a todas las fórmulas como los mismos dieciséis caracteres, y una búsqueda contra ellos nunca puede derivar. Excel marca esas celdas con un triangulillo verde de números almacenados como texto, un precio justo por una clave que conserva su último dígito:
// Los identificadores entran como texto; los importes siguen numéricos
procedure WriteCustomerRow(Sheet: TXLSXWorksheet; Row: Integer;
const CustomerNo: string; Balance: Currency);
begin
Sheet.Cells[Row, 1].Value := WideString(CustomerNo); // '1234567890123456' se queda entero
Sheet.Cells[Row, 2].Value := Balance; // mostrado por la regla General
end;
Antes de tocar el exportador, abre en Excel un libro que haya producido, ensancha la columna del número de cuenta y pon =A1&"" al lado. Si tu sistema aguas abajo compara contra la cadena de la izquierda, pide FormattedText al componente Excel de HotXLS para Delphi; si compara contra la de la derecha, construye el texto con EvaluateFormulaAt