Formatos de fracción de Excel con HotXLS en Delphi

Formatee 3.14159 con la máscara de fracción # ??/?? y Excel muestra 3 1/7, aunque 3 14/99 está más de siete veces más cerca, así que una exportación Delphi que busca la fracción más cercana imprime un valor que el usuario jamás ve. La razón es que Excel solo acepta convergentes de fracción continua; en HotXLS, TXLSXCell.FormattedText devuelve el mismo 3 1/7, espacios de relleno incluidos

Excel se detiene en los convergentes de la fracción continua

Toda máscara de fracción aproximante le asigna a Excel el mismo trabajo: encontrar un numerador y un denominador para la parte posterior al entero, con el denominador limitado por la cantidad de casillas de dígitos tras la barra. Excel lo hace expandiendo el valor como fracción continua y conservando solo los convergentes, las fracciones que se obtienen al cortar esa expansión después de cada término. Para 0.14159 la expansión arranca [0; 7, 15, 1, ...], que produce 1/7, luego 15/106, luego 16/113. El entero 3 no cambia nada. Con él, 3.14159 pasa por los mismos 22/7, 333/106 y 355/113 que aparecen en toda tabla de aproximaciones a pi. Como 106 ya desborda un denominador de dos dígitos, Excel se queda con 1/7. El 14/99 más cercano es un semiconvergente, una de las fracciones intermedias que se sientan entre dos convergentes vecinos. Excel nunca las visita. No hace falta código propio para toparse con esto: los tipos Up to one digit y Up to two digits del diálogo Format Cells son los IDs de formato predefinidos 12 y 13, # ?/? y # ??/??, en la lista de formatos numéricos predefinidos de ECMA-376 Part 1 §18.8.30

La brecha no se limita a entradas parecidas a pi. La mejor aproximación, típicamente un recorrido Stern-Brocot o un barrido sobre todos los denominadores permitidos, pregunta qué fracción es la más cercana, mientras que el bucle de convergentes pregunta qué fracción alcanza primero la expansión mientras el denominador todavía quepa. Con entradas ordinarias de dos decimales las dos reglas se separan lo suficiente como para importar, y el 0.27 del código de abajo es uno de esos valores. Si su código escribe fracciones en una factura PDF, un reporte HTML o un CSV que alguien va a cotejar contra el libro, copie ahí la regla de Excel, aunque sea la menos precisa. Quien ve 3 14/99 en papel y 3 1/7 en Excel no concluye que su exportación sea más precisa. Concluye que una de las dos está rota. No será Excel

El bucle en sí es corto. Cuente los marcadores de posición de dígito tras la barra, ya sean ?, # o 0, y el tope es 9, 99 o 999 con uno, dos o tres de ellos. Una máscara mixta como # ??/?? separa el entero y expande solo la parte fraccionaria; una máscara impropia como ?/? expande el valor completo, razón por la que 3.14159 bajo ?/? sale 22/7. Cada paso toma la parte entera A del valor en curso B y la pliega en el siguiente convergente. El bucle se detiene una vez que el resto B − A baja de 5e-8, así que una fracción simple más ruido de punto flotante sigue aterrizando en esa fracción. Cuando un paso empuja el denominador más allá del tope, Excel retrocede al convergente anterior, y esa caída puede aterrizar en 1/1, que una máscara mixta arrastra al entero, así que 12.9999 bajo # ?/? muestra 13. Abajo el bucle está escrito en Delphi plano junto a una búsqueda por fuerza bruta de la fracción más cercana, para que vea dónde divergen con sus propios números

uses
  SysUtils;

type
  TFraction = record
    Num, Den: Int64;
  end;

// La regla del convergente detrás de las máscaras # ?/?, # ??/?? y # ???/??? de Excel.
// X es no negativo: la parte fraccionaria para una máscara mixta, el valor completo
// para una impropia. MaxDen es 9, 99 o 999.
function ExcelFraction(X: Double; MaxDen: Integer): TFraction;
var
  B, R: Double;
  A, P, Q, P1, Q1, P2, Q2: Int64;
begin
  B := X;
  P2 := 0; Q2 := 1;      // convergente k-2
  P1 := 1; Q1 := 0;      // convergente k-1
  P := 0;  Q := 1;
  while Q1 < MaxDen do
  begin
    A := Trunc(B);
    P := A * P1 + P2;
    Q := A * Q1 + Q2;
    R := B - A;
    if R < 5E-8 then
      Break;               // el resto cuenta como cero
    B := 1 / R;
    P2 := P1; Q2 := Q1;
    P1 := P;  Q1 := Q;
  end;
  if Q > MaxDen then       // pasó el tope: retroceda un convergente
    if Q1 > MaxDen then
    begin
      P := P2; Q := Q2;
    end
    else
    begin
      P := P1; Q := Q1;
    end;
  Result.Num := P;
  Result.Den := Q;
end;

// Lo que devuelve una rutina de "mejor aproximación": la N/D más cercana con D <= MaxDen
function NearestFraction(X: Double; MaxDen: Integer): TFraction;
var
  D: Integer;
  N: Int64;
  Err, BestErr: Double;
begin
  Result.Num := Round(X);
  Result.Den := 1;
  BestErr := Abs(X - Result.Num);
  for D := 2 to MaxDen do
  begin
    N := Round(X * D);
    Err := Abs(X - N / D);
    if Err < BestErr then  // estricto: la forma reducida gana los empates
    begin
      BestErr := Err;
      Result.Num := N;
      Result.Den := D;
    end;
  end;
end;

procedure CompareFractionRules;
const
  Samples: array[0..3] of Double = (0.14159, 0.99, 0.27, 0.5);
var
  I: Integer;
  E, N: TFraction;
begin
  for I := Low(Samples) to High(Samples) do
  begin
    E := ExcelFraction(Samples[I], 99);
    N := NearestFraction(Samples[I], 99);
    Writeln(Format('%-8g Excel %d/%d   nearest %d/%d',
      [Samples[I], E.Num, E.Den, N.Num, N.Den]));
  end;
end;

// Esperado:
// 0.14159  Excel 1/7   nearest 14/99
// 0.99     Excel 98/99   nearest 98/99
// 0.27     Excel 10/37   nearest 17/63
// 0.5      Excel 1/2   nearest 1/2

La línea del 0.27 está en la lista porque una entrada ordinaria de dos decimales también diverge, 10/37 contra 17/63, sin nada exótico en el valor. La frecuencia con que las reglas se separan depende de sus datos: las fracciones simples verdaderas como 0.5 o 0.375 salen igual por ambos caminos, las cantidades medidas y los cocientes calculados con mucha menos confiabilidad. Trunc hace de Floor porque la entrada nunca es negativa. Excel maneja el signo fuera de la fracción. Su código debería hacer lo mismo. Y el argumento tiene que ser el Double que la celda realmente guarda, por una razón que la siguiente sección concreta

¿Qué pasa con 0.99 en una fracción de dos dígitos?

Excel muestra 0.99 bajo # ??/?? como ·98/99 (cada · en este artículo representa un carácter de espacio). La causa es el punto flotante binario. El literal 0.99 no tiene un Double exacto, y el valor guardado queda un pelín por debajo. Así que 1/(1/0.99 − 1) evalúa a aproximadamente 98.9999999999994 en lugar de 99, el segundo término de la expansión se vuelve 98, y el convergente 98/99 cabe bajo el tope. Haga la misma expansión con decimales exactos y obtiene [0; 1, 99]: ese 99 empuja el denominador a 100, el bucle retrocede a 1/1, y la celda leería 1······. Aquí, ser más cuidadoso que Excel es el bug. Un generador de reportes que guarda montos como Currency o TBcd, o que reparsea la cadena decimal desde la base de datos y la expande con aritmética exacta, imprime un 1 entero donde Excel imprime 98/99. Alimente al formateador el Double guardado. Deje cada intermedio en punto flotante. La fracción sigue siendo asunto de visualización, además: las fórmulas todavía ven 0.99, y HotXLS mantiene las secciones de fracción a precisión completa incluso en un libro con precisión tal como se muestra activada

Cómo rellena y alinea Excel la zona de la fracción

Excel acomoda las fracciones como se acomoda una columna de decimales: el numerador va alineado a la derecha en sus casillas, el denominador a la izquierda, de modo que la barra aterriza en la misma posición en todas las filas. Cada casilla sin usar se llena según su marcador de posición. Un ? deja un espacio, un 0 escribe un cero, un # no escribe nada. Por eso 3.14159 bajo # ??/?? se lee 3··1/7·: un espacio es el separador de la máscara, otro llena la casilla vacía del numerador delante del 1, y otro llena la casilla vacía del denominador tras el 7. La tabla lista el texto que Excel muestra para un conjunto de entradas que cubre las reglas de acomodo

Código de formato	Valor	Excel muestra
# ?/?	0.5	·1/2
# ?/?	4/3	1·1/3
# ??/??	3.14159	3··1/7·
# ??/??	0.99	·98/99
# ?/?	12	12····
# ?/?	0	0····
# ?/?	12.9999	13····
# #/#	12	12
0 ?/?	0.5	0·1/2
#,##0 ?/?	1234.75	1,234·3/4
?/?	3.14159	22/7
?/?	0	0/1
??/??	12	12/1·

Los números enteros son donde los formateadores escritos a mano más se desvían de Excel. Bajo una máscara mixta cuyo numerador usa ?, Excel conserva los dígitos del entero, cero incluido, y cambia el separador, el numerador, la barra y el denominador por un espacio cada uno, así que 12 bajo # ?/? se vuelve 12···· y bajo # ??/100 va seguido de siete espacios. Cambie el marcador del numerador a # y el relleno desaparece. Un entero cero funciona al revés: la casilla # no imprime nada para 0.5 y el separador sobrevive solo como espacio, mientras que 0 ?/? fuerza el dígito, y las máscaras impropias jamás sueltan la fracción

Qué hacer con ese relleno depende de adónde va el texto. Consérvelo para reportes de texto monoespaciado y para celdas de tabla PDF alineadas a la derecha. Alinear las barras es justo el punto ahí. Recórtelo para CSV y JSON, donde un espacio final vuelve 12 una cadena que ya no parsea como número. HTML también necesita una decisión, porque el navegador colapsa las corridas de espacios salvo que la celda use white-space: pre; fije eso o recorte, pero evite un relleno medio colapsado que desalinea unas filas y otras no

Denominadores fijos como # ?/8

Un denominador escrito como dígitos que empiezan con 1 a 9 es un valor fijo en lugar de un tope. La aritmética cambia por completo: Excel multiplica el valor por el denominador, redondea al numerador entero más cercano y deja la fracción sin reducir. Bajo 0/8, 0.375 muestra 3/8 y 0.64 muestra 5/8, porque 0.64 × 8 es 5.12. El tipo As sixteenths de fábrica muestra 0.5 como 8/16, que es lo que espera un mecanista que lee un plano y lo que un formateador que reduce cada resultado convertiría en 1/2. Un numerador redondeado puede alcanzar al denominador. Entonces una máscara mixta lo arrastra al entero: se espera que 2.97 bajo # ?/8 se lea 3····, ya que 0.97 × 8 = 7.76 redondea hacia arriba a un ocho completo. Elija entre las dos familias según lo que el denominador signifique para el lector. Los denominadores fijos sirven para todo lo que se cita en pasos fijos, como longitudes imperiales, medidas de brocas o precios viejos de acciones en octavos. Nadie que lee dimensiones de madera quiere 3/7 de pulgada. Las máscaras aproximantes sirven para cocientes y valores medidos donde ningún denominador en particular carga significado

Leer el texto de fracción de Excel en Delphi con FormattedText

En HotXLS rara vez necesita el bucle por su cuenta. TXLSXCell.FormattedText renderiza el valor guardado a través del NumberFormat de la celda usando TXLSFormater.GetDisplayValue de lxStandard.pas, así que la elección del convergente, el relleno y cualquier texto literal llegan juntos en una sola cadena. El procedimiento de abajo asigna pares de valor y máscara a celdas e imprime cada resultado entre corchetes para que los espacios finales sigan visibles. Un TXLSXWorkbook nuevo arranca sin hojas de cálculo, razón por la que el código llama a Sheets.Add antes de tocar una celda; Sheets.Items y Cells[Row, Col] son ambos de base 1

uses
  SysUtils, lxHandleX;

type
  TFractionCase = record
    Mask: string;
    Value: Double;
  end;

const
  Cases: array[0..8] of TFractionCase = (
    (Mask: '# ??/??';        Value: 3.14159),
    (Mask: '# ??/??';        Value: 0.99),
    (Mask: '# ?/?';          Value: 12),
    (Mask: '# ?/?';          Value: 12.9999),
    (Mask: '# #/#';          Value: 12),
    (Mask: '?/?';            Value: 3.14159),
    (Mask: '0/8';            Value: 0.64),
    (Mask: '"x"# ?/?" y"';   Value: -12),
    (Mask: '# ?/?;(# ?/?)';  Value: -1.5));

procedure ShowFractionText;
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Cell: TXLSXCell;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Fractions');   // un libro nuevo todavía no tiene hojas
    for I := Low(Cases) to High(Cases) do
    begin
      Cell := Sheet.Cells[I + 1, 1];          // filas y columnas empiezan en 1
      Cell.Value := Cases[I].Value;
      Cell.NumberFormat := Cases[I].Mask;
      Writeln(Format('%-16s %9g  [%s]',
        [Cases[I].Mask, Cases[I].Value, Cell.FormattedText]));
    end;
  finally
    Book.Free;
  end;
end;

// Esperado:
// # ??/??            3.14159  [3  1/7 ]
// # ??/??               0.99  [ 98/99]
// # ?/?                   12  [12    ]
// # ?/?              12.9999  [13    ]
// # #/#                   12  [12]
// ?/?                3.14159  [22/7]
// 0/8                   0.64  [5/8]
// "x"# ?/?" y"           -12  [-x12     y]
// # ?/?;(# ?/?)         -1.5  [(1 1/2)]

Los últimos dos casos muestran lo que rodea a la fracción. El texto entre comillas y los escapes con barra invertida se imprimen como caracteres planos, y el relleno de un valor entero aterriza antes del sufijo, así que "x"# ?/?" y" convierte -12 en -x12·····y. El signo menos va delante de todo, prefijo incluido, salvo que una sección negativa explícita como (# ?/?) escriba el signo por su cuenta; las fracciones siguen las mismas reglas de sección y de signo menos que cualquier otro formato numérico de Excel. Cuando el valor viene de un dataset en lugar de una celda, omita el libro: TXLSFormater tiene un constructor sin parámetros, y GetDisplayValue(Value, NumberFormat) es la llamada que FormattedText hace para valores numéricos

uses
  SysUtils, lxStandard;

function FractionText(const AValue: Double; const AMask: WideString): WideString;
var
  Fmt: TXLSFormater;
begin
  Fmt := TXLSFormater.Create;
  try
    Result := Fmt.GetDisplayValue(AValue, AMask);
  finally
    Fmt.Free;
  end;
end;

// FractionText(0.5, '# ?/?')                Esperado: ' 1/2'
// FractionText(1234.75, '#,##0 ?/?')        Esperado: '1,234 3/4'
// Trim(FractionText(12, '# ?/?'))           Esperado: '12'  (amigable a CSV)

Toda fracción que su código Delphi imprima fuera de Excel debe salir del bucle de convergentes o directamente de FormattedText, alimentado con el Double de la propia celda. Así el 3 1/7 del papel es el 3 1/7 de la cuadrícula, y con el componente de hojas de cálculo HotXLS para Delphi obtiene ese texto sin escribir el bucle para nada