Fração do Excel Mostra 3 1/7 para 3.14159, Não 3 14/99

Formate 3.14159 com a máscara de fração # ??/?? e o Excel mostra 3 1/7, embora 3 14/99 seja mais de sete vezes mais próximo, então uma exportação Delphi que busca a fração mais próxima imprime um valor que o usuário nunca vê. O motivo é que o Excel só aceita convergentes de fração continuada; no HotXLS, o TXLSXCell.FormattedText retorna o mesmo 3 1/7, espaços de preenchimento incluídos

O Excel para nos convergentes de fração continuada

Toda máscara de fração aproximada dá ao Excel o mesmo trabalho: achar um numerador e um denominador para a parte depois do inteiro, com o denominador limitado pelo número de casas de dígitos depois da barra. O Excel faz isso expandindo o valor como fração continuada e guardando apenas os convergentes, as frações que você obtém ao cortar essa expansão depois de cada termo. Para 0.14159 a expansão começa [0; 7, 15, 1, ...], que rende 1/7, depois 15/106, depois 16/113. O inteiro 3 não muda nada. Com ele, 3.14159 passa pelos mesmos 22/7, 333/106 e 355/113 encontrados em toda tabela de aproximações de pi. Como 106 já estoura um denominador de dois dígitos, o Excel fica com 1/7. O 14/99, mais próximo, é um semiconvergente, uma das frações intermediárias que ficam entre dois convergentes vizinhos. O Excel nunca visita essas. Você não precisa de código custom para esbarrar nisso: os tipos Up to one digit e Up to two digits na caixa de diálogo Format Cells são os IDs de formato embutidos 12 e 13, # ?/? e # ??/??, na lista de formatos numéricos predefinidos da ECMA-376 Part 1 §18.8.30

A diferença não se limita a entradas tipo pi. Melhor aproximação, tipicamente uma caminhada Stern-Brocot ou uma varredura por todo denominador permitido, pergunta qual fração é a mais próxima, enquanto o laço de convergentes pergunta qual fração a expansão alcança primeiro enquanto o denominador ainda cabe. Em entradas comuns de duas casas decimais as duas regras divergem com frequência suficiente para importar, e o 0.27 no código abaixo é um desses valores. Se o seu código escreve frações em uma fatura PDF, um relatório HTML ou um CSV que alguém vai confrontar com a pasta de trabalho, copie lá a regra do Excel, mesmo sendo a menos precisa. Quem vê 3 14/99 no papel e 3 1/7 no Excel não conclui que a sua exportação é mais precisa. A conclusão é que uma das duas está quebrada. Não vai ser o Excel

O laço em si é curto. Conte os placeholders de dígitos depois da barra, sejam ?, # ou 0, e o limite é 9, 99 ou 999 para um, dois ou três deles. Uma máscara mista como # ??/?? separa o inteiro e expande só a parte fracionária; uma máscara imprópria como ?/? expande o valor inteiro, e é por isso que 3.14159 sob ?/? sai como 22/7. Cada passo pega a parte inteira A do valor corrente B e a dobra no próximo convergente. O laço para quando o restante B − A cai abaixo de 5e-8, então uma fração simples mais ruído de ponto flutuante ainda cai nessa fração. Quando um passo empurra o denominador para além do limite, o Excel recua para o convergente anterior, e esse recuo pode cair em 1/1, que uma máscara mista carrega para o inteiro, então 12.9999 sob # ?/? mostra 13. Abaixo, o laço está escrito em Delphi puro ao lado de uma busca de força bruta pela fração mais próxima, para você ver onde os dois divergem nos seus próprios números

uses
  SysUtils;

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

// A regra do convergente por trás das máscaras # ?/?, # ??/?? e # ???/??? do Excel.
// X não é negativo: a parte fracionária para uma máscara mista, o valor inteiro
// para uma imprópria. MaxDen é 9, 99 ou 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;               // o restante conta como zero
    B := 1 / R;
    P2 := P1; Q2 := Q1;
    P1 := P;  Q1 := Q;
  end;
  if Q > MaxDen then       // estourou o limite: recua um 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;

// O que uma rotina de "melhor aproximação" retorna: o N/D mais próximo com 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  // estrito: a forma reduzida vence os 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

A linha do 0.27 está na lista porque uma entrada comum de duas casas decimais também diverge, 10/37 contra 17/63, sem nada de exótico no valor. Com que frequência as regras divergem depende dos seus dados: frações simples de verdade como 0.5 ou 0.375 saem iguais dos dois jeitos, quantidades medidas e razões computadas bem menos confiavelmente. O Trunc faz as vezes do Floor porque a entrada nunca é negativa. O Excel trata o sinal fora da fração. O seu código também deveria. E o argumento precisa ser o Double que a célula de fato armazena, por um motivo que a próxima seção torna concreto

O que acontece com 0.99 em uma fração de dois dígitos?

O Excel mostra 0.99 sob # ??/?? como ·98/99 (cada · neste artigo representa um caractere de espaço). A causa é o ponto flutuante binário. O literal 0.99 não tem um Double exato, e o valor armazenado fica um fio abaixo dele. Então 1/(1/0.99 − 1) avalia para cerca de 98.9999999999994 em vez de 99, o segundo termo da expansão vira 98, e o convergente 98/99 cabe sob o limite. Faça a mesma expansão com decimais exatos e você obtém [0; 1, 99]: aquele 99 empurra o denominador para 100, o laço recua para 1/1, e a célula leria 1······. Aqui, ser mais cuidadoso que o Excel é o bug. Um gerador de relatórios que guarda valores como Currency ou TBcd, ou re-parseia a string decimal do banco de dados e a expande com aritmética exata, imprime um 1 inteiro onde o Excel imprime 98/99. Alimente o formatador com o Double armazenado. Mantenha cada intermediário em ponto flutuante. A fração continua sendo assunto de exibição, também: as fórmulas ainda veem 0.99, e o HotXLS mantém as seções de fração em precisão total mesmo em uma pasta de trabalho com o precision as displayed ligado

Como o Excel preenche e alinha a área da fração

O Excel dispõe frações do jeito que uma coluna de decimais é disposta: o numerador é alinhado à direita nas suas casas, o denominador à esquerda, então a barra cai na mesma posição em todas as linhas. Cada casa não usada é preenchida de acordo com o seu placeholder. Um ? deixa um espaço, um 0 escreve um zero, um # não escreve nada. É por isso que 3.14159 sob # ??/?? lê 3··1/7·: um espaço é o separador da máscara, um preenche a casa vazia do numerador na frente do 1, e um preenche a casa vazia do denominador depois do 7. A tabela lista o texto que o Excel exibe para um conjunto de entradas que cobre as regras de layout

Código de formato	Valor	O Excel mostra
# ?/?	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·

Números inteiros são onde formatadores escritos à mão mais se afastam do Excel. Sob uma máscara mista cujo numerador usa ?, o Excel mantém os dígitos do inteiro, zero incluído, e troca separador, numerador, barra e denominador por um espaço cada, então 12 sob # ?/? vira 12···· e sob # ??/100 é seguido por sete espaços. Troque o placeholder do numerador para # e o preenchimento vai embora. Um inteiro zero funciona ao contrário: a casa # não imprime nada para 0.5 e só o separador sobrevive como um espaço, enquanto 0 ?/? força o dígito, e máscaras impróprias nunca descartam a fração

O que fazer com esse preenchimento depende de para onde o texto vai. Mantenha-o para relatórios de texto monoespaçado e para células de tabela PDF alinhadas à direita. Alinhar as barras é o objetivo todo ali. Corte-o para CSV e JSON, onde um espaço no fim torna 12 uma string que não parseia mais como número. HTML também precisa de uma decisão, porque o navegador colapsa sequências de espaços a menos que a célula use white-space: pre; defina isso ou corte, mas evite preenchimento meio colapsado que desalinha algumas linhas e outras não

Denominadores fixos como # ?/8

Um denominador escrito como dígitos começando de 1 a 9 é um valor fixo em vez de um limite. A aritmética muda completamente: o Excel multiplica o valor pelo denominador, arredonda para o numerador inteiro mais próximo e deixa a fração sem reduzir. Sob 0/8, 0.375 mostra 3/8 e 0.64 mostra 5/8, porque 0.64 × 8 é 5.12. O tipo de fábrica As sixteenths mostra 0.5 como 8/16, que é o que um mecânico lendo um desenho técnico espera e o que um formatador que reduz todo resultado transformaria em 1/2. Um numerador arredondado pode chegar ao denominador. Aí uma máscara mista o carrega para o inteiro: 2.97 sob # ?/8 deve ler 3····, já que 0.97 × 8 = 7.76 arredonda para cima até um oito inteiro. Escolha entre as duas famílias pelo que o denominador significa para o leitor. Denominadores fixos servem para qualquer coisa cotada em passos fixos, como medidas imperiais, tamanhos de broca ou preços antigos de ações em oitavos. Ninguém lendo medidas de madeira quer 3/7 de polegada. As máscaras aproximativas servem para razões e valores medidos onde nenhum denominador específico carrega significado

Lendo o texto de fração do Excel em Delphi com FormattedText

No HotXLS raramente você precisa do laço por conta própria. O TXLSXCell.FormattedText renderiza o valor armazenado por meio do NumberFormat da célula usando o TXLSFormater.GetDisplayValue do lxStandard.pas, então a escolha do convergente, o preenchimento e qualquer texto literal chegam juntos em uma única string. O procedimento abaixo atribui pares de valor e máscara a células e imprime cada resultado entre colchetes para os espaços do fim ficarem visíveis. Um TXLSXWorkbook novo começa sem planilhas, e é por isso que o código chama Sheets.Add antes de tocar em uma célula; Sheets.Items e Cells[Row, Col] são ambos 1-based

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');   // uma workbook nova ainda não tem sheets
    for I := Low(Cases) to High(Cases) do
    begin
      Cell := Sheet.Cells[I + 1, 1];          // linhas e colunas começam em 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)]

Os dois últimos casos mostram o que cerca a fração. Texto entre aspas e escapes com barra invertida imprimem como caracteres comuns, e o preenchimento de um valor inteiro cai antes do sufixo, então "x"# ?/?" y" transforma -12 em -x12·····y. O sinal de menos vai na frente de tudo, prefixo incluído, a menos que uma seção negativa explícita como (# ?/?) escreva o sinal ela mesma; frações seguem as mesmas regras de seção e de sinal de menos que todo outro formato numérico do Excel. Quando o valor vem de um dataset em vez de uma célula, dispense a workbook: o TXLSFormater tem um construtor sem parâmetros, e o GetDisplayValue(Value, NumberFormat) é a chamada que o FormattedText faz 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'  (amigável a CSV)

Toda fração que o seu código Delphi imprime fora do Excel deve vir do laço de convergentes ou direto do FormattedText, alimentado com o Double da própria célula. Assim o 3 1/7 no papel é o 3 1/7 na grade, e com o componente de planilhas HotXLS para Delphi você obtém esse texto sem escrever o laço nenhum