Round-trips sem perdas de doubles no Delphi: 17 dígitos

O HotXLS grava valores de células numéricas ordinárias e caches de fórmulas em XLSX com 17 dígitos significativos, e na leitura corrige a análise da RTL Delphi com uma verificação exata delimitada de decimal para binary64, pelo que um Double que guarde numa célula regressa bit a bit depois de gravar e reabrir. Quinze dígitos, o número que o Excel mostra no ecrã, não chegam para identificar um valor IEEE 754 binary64, e o fosso aparece exatamente onde vivem as colunas de finanças e de IDs: inteiros acima de 10¹⁵

O bug que forçou a mudança parecia inofensivo. Uma fórmula de folha INT(-1E15-0.1) avalia para -1000000000000001, um double perfeitamente representável. O HotXLS calculava-a corretamente, punha-a em cache, gravava o ficheiro, e a cache em disco lia-se -1E15. O Excel só mostraria o valor original depois de um recálculo, e qualquer consumidor que confie em valores em cache, que é a maioria, recebia silenciosamente um número com um de diferença. Nada rebentava, nada avisava, e o ficheiro validava bem

Por que é que 15 dígitos significativos não chegam para um double?

Quinze dígitos decimais têm garantia de sobreviver a uma viagem para binary64 e de volta, mas a direção inversa precisa de 17. O IEEE 754 binary64 tem um significando de 53 bits, que dá entre 15 e 17 dígitos decimais consoante onde o valor se situa. Qualquer string decimal com no máximo 15 dígitos significativos converte para um double e imprime-se de volta inalterada. Um double, porém, precisa de até 17 dígitos antes de a sua grafia decimal o identificar de forma única, e no intervalo acima de 2⁵³ doubles adjacentes distam 2, enquanto entre 10¹⁵ e 2⁵³ distam 0.125 a 1

O FloatToStr do Delphi formata com 15 dígitos significativos, que é a mesma precisão que o Excel usa para exibição. Essa correspondência é precisamente por isso que o defeito se esconde tão bem: abra o ficheiro no Excel e a célula parece exatamente como estava no seu programa Delphi, porque o Excel também só mostra 15 dígitos. A diferença só fica visível quando compara o double guardado, subtrai dois valores próximos, ou alimenta a cache a algo que faz aritmética. O valor -1000000000000001 tem 16 dígitos significativos, pelo que arredondar para 15 produz -1000000000000000 e a unidade final desaparece para sempre

A ECMA-376 Parte 1 §18.3.1.96 define o elemento <v> que transporta o valor de uma célula como texto, e nada no esquema dita quantos dígitos um escritor deve emitir para uma célula numérica. A precisão é inteiramente responsabilidade do escritor, que é por isso que dois produtores podem ambos escrever XLSX válido e ainda discordar sobre que número está na célula

Gravar 17 dígitos significativos a partir do Delphi

O HotXLS agora usa um invariante para células numéricas: FloatToStrF com ffGeneral, precisão 17, e configurações de formato invariantes. O ramo de datas do escritor de células já usava exatamente essa projeção, pelo que os números ordinários simplesmente se juntaram a ele. Do lado de fora nada muda na API; atribui um Double ou uma fórmula, grava, e o token que aterra em <v> é suficientemente preciso para reproduzir o double

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Ledger');
    Sheet.Cells[1, 1].Value := 9007199254740991.0;   // 2^53 - 1
    Sheet.Cells[2, 1].Value := -1000000000000001.0;
    Sheet.Cells[3, 1].Formula := 'INT(-1E15-0.1)';
    if Book.Recalculate <> lxOk then
      raise Exception.Create('Recalculation failed');
    // Os valores e a cache de fórmula são gravados com 17 dígitos significativos
    if Book.SaveAs('ledger.xlsx') <> lxOk then
      raise Exception.Create('Save failed');
  finally
    Book.Free;
  end;
end;

Dezassete dígitos é um teto, não uma regra de enchimento. O ffGeneral larga zeros finais, pelo que 0.5 continua a ser gravado como 0.5 e inteiros pequenos mantêm-se curtos; só valores que genuinamente precisam dos dígitos extra os recebem. O custo são uns bytes nas células que antes estavam a ser truncadas, que é a correção de exatidão mais barata que alguma vez vai publicar

Por que é que gravar 17 dígitos ainda perde valores na leitura?

Gravar dígitos suficientes só ajuda se o leitor os converter para o double mais próximo, e o Val da RTL nem sempre o faz. Durante a verificação a RTL na máquina de teste lia tanto 9007199254740990.7 como o inteiro simples 9007199254740991 para o valor binary64 adjacente e errado. Ambas as strings deviam aterrar em 2⁵³-1; uma delas é uma grafia de 17 dígitos que o HotXLS pode produzir, a outra é como o Excel escreve o mesmo valor. Um escritor corrigido isoladamente teria publicado um ficheiro que o próprio HotXLS não conseguia ler de volta corretamente

Por isso o HotXLS mantém o Val como porteiro e corrige a resposta dele. Depois de a gramática numérica existente aceitar um token não-data, um parser exato constrói o decimal como uma fração de inteiro grande e compara-o com os pontos médios binários de cada lado do candidato da RTL. Se o decimal cair abaixo do ponto médio inferior, o candidato desce um ulp; acima do ponto médio superior, sobe; num empate exato o significando par ganha, que é a regra IEEE 754 de arredondar-para-o-par-mais-próximo. Ao candidato é permitido no máximo oito ajustes de vizinho, o que chega porque o Val erra por uma unidade, não por um quilómetro

A correção é deliberadamente delimitada. Tokens com mais de 48 caracteres, com mais de 17 dígitos significativos, ou com magnitude de expoente acima de 324 saltam-na e mantêm o resultado do parser legado, e qualquer falha interna regride para o mesmo. Os inteiros grandes usam 48 membros de 32 bits cada, suficiente para o numerador e denominador mais largos dentro desses limites. Um detalhe de portabilidade importa aqui: os membros são declarados explicitamente como UInt32. O LongWord parece a escolha óbvia, mas tem 8 bytes no alvo Linux64 do Delphi, e um tipo de membro que dobre silenciosamente de largura parte todos os carries e deslocamentos da aritmética

Verificar o round-trip bit a bit

Comparar doubles com = serve para este teste, porque o objetivo é igualdade exata, mas uma comparação de padrões de bits torna o relatório de falha mais útil: uma deriva de um ulp aparece como uma diferença de 1 no valor bruto de 64 bits em vez de dois números que se imprimem iguais

function SameBits(A, B: Double): Boolean;
var
  BitsA, BitsB: UInt64;
begin
  Move(A, BitsA, SizeOf(BitsA));
  Move(B, BitsB, SizeOf(BitsB));
  Result := BitsA = BitsB;
end;

procedure CheckReopen(const FileName: string);
const
  Expected: array[1..3] of Double =
    (9007199254740991.0, -1000000000000001.0, -1000000000000001.0);
var
  Book: TXLSXWorkbook;
  Row: Integer;
  Stored: Double;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open(FileName) <> lxOk then
      raise Exception.Create('Open failed');
    for Row := 1 to 3 do
    begin
      Stored := Book.Sheets[1].Cells[Row, 1].Value;
      if not SameBits(Stored, Expected[Row]) then
        Writeln('row ', Row, ' drifted: ', FloatToStrF(Stored, ffGeneral, 17, 0));
    end;
  finally
    Book.Free;
  end;
end;

A linha 3 é a interessante. Lê o resultado em cache de INT(-1E15-0.1) sem recalcular, pelo que prova que a cache em si é sem perdas em vez de o motor de fórmulas conseguir recalcular o valor. Se depende de resultados em cache num pipeline, o guia separado sobre ler valores de fórmulas em cache sem recálculo cobre os estados da cache e por que uma cache em falta nunca é inventada

Que outras partes XLSX transportam caches numéricas?

Corrigir o escritor de células fechou um caminho de vários, e cada ponto de escrita numérico restante teve de ser verificado por si. A primeira versão cobriu valores de worksheet e caches de fórmulas, incluindo as caches de valor de saída de Pivot. As caches de origem de Pivot e as caches de gráfico ainda passavam pelo funil de 15 dígitos e foram fechadas em duas versões de seguimento em vez de por presumção

• Cache de origem de Pivot: os valores sharedItems e os escritores cacheRecords inline e em fluxo agora usam o mesmo invariante de 17 dígitos, e o caminho de leitura pivot aplica a mesma correção exata de pontos médios. Atributos descritivos como o refreshedDate mantêm a sua grafia original
• Caches de gráfico: o único ponto de escrita que constrói valores de pontos em cache mudou para 17 dígitos, e os seis pontos de leitura, cobrindo valores e categorias de linhas de tendência, validação de barras de erro e dois caminhos de renderização, passam todos por um único auxiliar de análise exata
• Todo o resto numérico, como larguras de coluna e limites de eixos, continua a passar pelo formatador defensivo que despeja valores não finitos e subnormais para 0, porque uma parte serializada nunca pode conter um float não analisável

A verificação de interoperabilidade que mais importava era com o próprio Excel. O Excel escreve 2⁵³-1 como o inteiro 9007199254740991; o HotXLS pode escrever a grafia equivalente de 17 dígitos 9007199254740990.7. Ambos os tokens analisam para o mesmo double de ambos os lados, pelo que um ficheiro autorado no Excel, reaberto e regravado pelo HotXLS, depois aberto outra vez no Excel, mantém os mesmos valores na sua cache de Pivot. Reconhecimento mútuo, não grafia idêntica, é a propriedade a testar

Limites e casos de fronteira que valem a pena conhecer

Sem perdas aqui significa que o valor binary64 é preservado, não que todo o valor é representável. Resultados não finitos continuam a gravar-se como erros #NUM! e subnormais continuam a gravar-se como zero, exatamente como antes. A exibição é também uma preocupação separada: o Excel vai continuar a mostrar 15 dígitos significativos no ecrã, pelo que um utilizador que digite 9007199254740993 no Excel ainda recebe um valor guardado diferente, e nenhum escritor consegue corrigir isso. Se o seu workbook usa Definir precisão como exibido, os valores são deliberadamente arredondados ao seu formato numérico, que é o oposto do que este artigo trata; as regras desse modo estão cobertas em como o HotXLS aplica o arredondamento de precisão como exibido do Excel

O leitor exato também não substitui o Val; só corrige valores que o Val já aceitou. Um token que a RTL rejeita continua a ser rejeitado, e um token fora dos limites de 48 caracteres, 17 dígitos e expoente 324 mantém o que o parser legado produziu. Para ficheiros gravados pelo HotXLS ou pelo Excel isso nunca importa, porque nenhum produz tais tokens para valores de células, mas uma parte XML artesanal com 40 dígitos significativos lê-se com a precisão da RTL, não com a correção exata

FAQ

O HotXLS muda o número que o Excel exibe para uma célula?

Não. O HotXLS só muda quantos dígitos entram no token <v> guardado. O Excel continua a exibir 15 dígitos significativos, pelo que as células parecem as mesmas; a diferença é que o double subjacente agora corresponde ao que o seu código Delphi atribuiu

Por que não gravar simplesmente 16 dígitos em vez de 17?

Dezasseis dígitos não chegam para todos os valores binary64. Alguns doubles precisam de 17 dígitos significativos antes de a sua grafia decimal ser única, pelo que um escritor de 16 dígitos ainda mapearia dois doubles distintos para o mesmo texto em casos raros, que é exatamente a classe de deriva silenciosa que a mudança remove

Os ficheiros gravados antes da correção precisam de ser regenerados?

Só se contiverem valores que precisavam de mais de 15 dígitos. Esses tokens já perderam os seus últimos dígitos em disco e lê-los com mais precisão não consegue restaurar o que nunca foi gravado. Regravar a partir dos dados originais, ou recalcular fórmulas e gravar de novo, produz um ficheiro exato

Saída numérica de dezassete dígitos, a correção exata de leitura binary64 e o tratamento correspondente de caches de Pivot e gráfico fazem parte do HotXLS Delphi Spreadsheet Component para Delphi, C++Builder e Lazarus, que lê e escreve XLSX sem Excel nem automação OLE instalados