Artículo técnico

Excel muestra 1.23457E+11 para un número de 12 dígitos

Una exportación Delphi guarda el número de cuenta 123456789012 como número; una columna ancha de Excel muestra 1.23457E+11, mientras que =A1&"" devuelve los doce dígitos completos. Excel convierte números en texto con dos reglas, once caracteres para el display de General y quince dígitos significativos dentro de fórmulas, así que una exportación HotXLS lee la primera desde FormattedText y construye la segunda con el motor de fórmulas

¿Cuántos caracteres permite el formato General?

Once, dejando el signo menos fuera del conteo. ECMA-376 Part 1 §18.8.30 dice que la aplicación "shall attempt to display the full number up to 11 digits (inc. decimal point)", y su regla de punto flotante lo pone más preciso: la longitud "max overall length for cell display is 11, not including negative sign". El estándar luego dibuja cuatro condiciones para pasarse a forma exponencial y ahí se detiene. El redondeo queda abierto. También el punto de corte para números chicos. El comportamiento de abajo es lo que Excel muestra en una columna ancha, y es lo que HotXLS reproduce en el texto de display 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 sacrifica decimales: 12345.6789012345 se muestra como 12345.6789, y 12345678901.5 se redondea a 12345678902
  • Una parte entera de doce dígitos no cabe, así que 123456789012 se convierte en 1.23457E+11, con la mantisa redondeada para dejarle lugar al exponente
  • Un número abajo de 1 se queda en notación fija mientras esta muestre al menos tantos dígitos significativos como la forma científica de seis dígitos: 0.00012345678 se muestra como 0.000123457, pero 0.0000123456789 se convierte en 1.23457E-05
  • El exponente siempre lleva signo y al menos dos dígitos, así que 1020 se muestra como 1E+20

El signo vive fuera del presupuesto. -12345678901 se muestra con los once dígitos completos detrás de su signo menos, doce caracteres en total, mientras -123456789012 se convierte en -1.23457E+11. El tipo Variant que asigne no cambia el resultado. Excel guarda todo valor de celda numérico 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 arrastra a los formatos personalizados: el texto literal de una sección se agrega 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

Flujo de decisión de la regla de display de once caracteres del formato General de Excel en HotXLS para Delphi: notación posicional, cambio a científica y el signo menos fuera del presupuesto
una columna ancha le da al formato General once caracteres para dígitos y el punto decimal, mientras el signo menos espera fuera del presupuesto

Un límite más vive fuera de cualquier librería. El estándar agrega que "the optimal method of display is based on the available cell width", y en una columna de ancho por default Excel aprieta más, hasta 1.2E+11 o una fila de signos #, según las métricas de fuente de la computadora 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. Ensanche la columna antes de comparar. De lo contrario estará midiendo el fallback de ancho de Excel contra la regla General, y los dos van a discrepar 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 vuelve texto dentro de una fórmula, el operador &, CONCAT, TEXTJOIN, LEN, LEFT, toma el mando otra regla. ECMA-376 §18.17 define la gramática de fórmulas pero ningún formato 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 cuando mucho veinte caracteres además del signo. Por eso =1/3&"" devuelve 0.333333333333333, quince treses. =1E+19&"" devuelve 10000000000000000000, y =1E-18&"" devuelve 0.000000000000000001. Pasados veinte caracteres el texto se cambia a notación científica con el mismo exponente con signo de dos dígitos que usa la retícula: 1E+20, 1.23456789012345E-05. El separador siempre es un punto. HotXLS evalúa fórmulas sin consultar la configuración regional de la computadora que corre su 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 computadora. CHAR y CODE mapean números arriba de 127 por la página de códigos ANSI de la computadora donde corre Excel, así que =CHAR(128) es el signo de euro en una instalación de Windows de Europa occidental y otro carácter bajo otras páginas de códigos. Un servicio Delphi no puede saber qué página de códigos usará el lector final. HotXLS evalúa esos códigos como Windows-1252, la página de códigos de Europa occidental, en todas las computadoras, así que =CHAR(150) da un en dash en cualquier servidor. Si sus libros van destinados a otra página de códigos, calcule esos caracteres en Delphi y guárdelos como texto

FloatToStr no sigue ninguna de las dos reglas

ValorDisplay General / TEXTTexto de fórmula (&"")FloatToStr de Delphi
1234567890121.23457E+11123456789012123456789012
12345678901234561.23457E+1512345678901234601.23456789012346E15
1/30.3333333330.3333333333333330.333333333333333
1E201E+201E+201E20
0.0000010.0000010.0000011E-6
0.000001234567891.23457E-060.000001234567891.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 se cambia a notación exponencial en sus propios umbrales: abajo de 0.00001, o en cuanto la parte entera necesita más de quince dígitos. Además toma su separador decimal de los FormatSettings del host. Es una función excelente para archivos de log. No reproduce ninguna de las dos reglas de Excel, y un solo helper al que se le pida servir al display y al texto de fórmula estará mal en cada fila donde esas reglas discrepan, cuatro de las seis filas de arriba

Un double almacenado mapeado a tres strings en HotXLS para Delphi: el display de la retícula de Excel, el texto de fórmula con quince dígitos significativos y el resultado de FloatToStr de Delphi
el display de la retícula, el texto de fórmula y FloatToStr aplican cada uno su propia regla, así que elija según dónde se va a comparar el string

¿Qué texto deben usar una exportación o una clave de búsqueda?

Elija la regla preguntándose dónde se va a comparar el string. El texto para personas, un reporte PDF, una tabla HTML, un CSV que alguien abre junto al libro, debe coincidir con la retícula, así que lea el texto de display 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, un string de búsqueda. Un CSV que se va a cargar de vuelta a Excel o a una base de datos también cuenta como datos. Escribir 1.23457E+11 en un archivo así tira para siempre cinco dígitos del número de cuenta, porque el importador parsea lo que lee y no puede recuperar el original. El primer ejemplo imprime los tres strings lado a lado. Las muestras son Doubles, pero un valor Int64 o Currency se muestra de la misma forma:

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 default
      Writeln(Format('%-22s %-22s %s', [
        string(Sheet.Cells[1, 1].FormattedText),  // lo que muestra la retícula
        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 debe coincidir con la fila correspondiente de la tabla. Solo se mueven los separadores. En un host alemán la columna de display y la columna FloatToStr se cambian a coma decimal, como lo hace la retícula 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 reporte 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 reporte 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;           // el monto, tal como se muestra
    Key := VarToStr(Sheet.EvaluateFormulaAt(Row, 6,
      Format('A%d&"-"&C%d', [Row, Row])).Value);       // clave de monto 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 guardarla, así que la clave sale de la misma regla que Excel aplica a una columna de clave dentro del libro. Si el monto lleva formato de moneda o de miles, FormattedText aplica ese formato, y la regla General gobierna solo las celdas sin formato. Alimentar el cálculo con el valor de display redondeado es otro interruptor de Excel, precisión según lo mostrado, y reescribe el double almacenado mismo, así que manténgalo lejos de una exportación que solo necesita texto

¿Qué pasa con un ID de más de quince dígitos?

Pierde la cola en el momento en que se convierte en texto. Un double sostiene todo entero 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 round trip de double XLSX sin pérdidas. 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, un cliente distinto. 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 termina con tres números: 1234567890123456 en la celda, 1234567890123460 en una clave de fórmula y 1234567890123450 tras reteclearlo

Anatomía de un ID de dieciséis dígitos en HotXLS para Delphi: el valor de celda almacenado, el texto de fórmula de quince dígitos y la entrada retecleada dan tres números distintos
los identificadores son etiquetas escritas en dígitos, así que guárdelos como texto y ninguna regla podrá redondear el último dígito

Guarde los identificadores como texto desde el inicio. Los números de cuenta, números de tarjeta e IDs de orden son etiquetas escritas en dígitos; nadie los suma, y un cero a la izquierda pertenece a la etiqueta. Asignados como strings, llegan a la retícula y a cada fórmula como los mismos dieciséis caracteres, y una búsqueda contra ellos jamás puede derivar. Excel marca esas celdas con un triangulito verde para números almacenados como texto, un precio justo por una clave que conserva su último dígito:

// Los identificadores entran como texto; los montos se quedan 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, abra en Excel un libro que haya producido, ensanche la columna del número de cuenta y ponga =A1&"" al lado. Si su sistema downstream compara contra el string de la izquierda, pídale FormattedText al componente Excel HotXLS para Delphi; si compara contra el de la derecha, construya el texto con EvaluateFormulaAt