Round-trip senza perdita dei double XLSX in Delphi: 17 cifre

HotXLS scrive i valori numerici ordinari delle celle e le cache delle formula in XLSX con 17 cifre significative, e in lettura corregge il parsing della RTL Delphi con un controllo esatto limitato da decimale a binary64, così un Double che memorizzate in una cella torna bit per bit dopo un salvataggio e una riapertura. Quindici cifre, il numero che Excel mostra sullo schermo, non bastano a identificare un valore IEEE 754 binary64, e il divario si manifesta esattamente dove vivono le colonne di finanza e di ID: gli interi oltre 10¹⁵

Il bug che ha forzato il cambiamento sembrava innocuo. Una formula di foglio INT(-1E15-0.1) valuta a -1000000000000001, un double perfettamente rappresentabile. HotXLS lo calcolava correttamente, lo metteva in cache, salvava il file, e la cache su disco recitava -1E15. Excel avrebbe mostrato il valore originale solo dopo un ricalcolo, e qualunque consumatore che si fida dei valori in cache, che è la maggior parte, riceveva silenziosamente un numero sbagliato di uno. Nulla sollevava, nulla avvisava, e il file validava bene

Perché 15 cifre significative non bastano per un double?

Quindici cifre decimali sono garantite a sopravvivere a un viaggio dentro binary64 e ritorno, ma la direzione inversa ne richiede 17. IEEE 754 binary64 ha un significando a 53 bit, che si traduce in tra 15 e 17 cifre decimali a seconda di dove sta il valore. Qualunque stringa decimale con al massimo 15 cifre significative si converte in un double e si ristampa invariata. Un double però ha bisogno di fino a 17 cifre prima che la sua notazione decimale lo identifichi univocamente, e nell'intervallo sopra 2⁵³ i double adiacenti distano 2, mentre tra 10¹⁵ e 2⁵³ distano da 0,125 a 1

Il FloatToStr di Delphi formatta con 15 cifre significative, che è la stessa precisione che Excel usa per la visualizzazione. Quella coincidenza è esattamente il motivo per cui il difetto si nasconde così bene: aprite il file in Excel e la cella appare esattamente come appariva nel vostro programma Delphi, perché Excel pure mostra solo 15 cifre. La differenza diventa visibile solo quando confrontate il double memorizzato, sottraete due valori vicini, o date la cache in pasto a qualcosa che fa aritmetica. Il valore -1000000000000001 ha 16 cifre significative, quindi arrotondare a 15 produce -1000000000000000 e l'unità finale è persa per sempre

ECMA-376 Part 1 §18.3.1.96 definisce l'elemento <v> che trasporta il valore di una cella come testo, e nulla nello schema detta quante cifre un writer debba emettere per una cella numerica. La precisione è interamente responsabilità del writer, ed è per questo che due produttori possono scrivere entrambi XLSX validi e discordare comunque su quale numero sia nella cella

Scrivere 17 cifre significative da Delphi

HotXLS ora usa un unico invariante per le celle numeriche: FloatToStrF con ffGeneral, precisione 17 e impostazioni di formato invarianti. Il ramo date del writer di celle usava già esattamente quella proiezione, quindi i numeri ordinari si sono semplicemente uniti. Dall'esterno nulla cambia nell'API; assegnate un Double o una formula, salvate, e il token che atterra in <v> è abbastanza preciso da riprodurre il 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');
    // I valori e la cache delle formula vengono scritti con 17 cifre significative
    if Book.SaveAs('ledger.xlsx') <> lxOk then
      raise Exception.Create('Save failed');
  finally
    Book.Free;
  end;
end;

Diciassette cifre sono un tetto, non una regola di riempimento. ffGeneral scarta gli zeri finali, quindi 0,5 viene comunque scritto come 0.5 e i piccoli interi restano corti; solo i valori che hanno davvero bisogno delle cifre extra le ottengono. Il costo è qualche byte sulle celle che prima venivano troncate, che è la correzione di correttezza più economica che farete mai

Perché scrivere 17 cifre perde comunque valori in lettura?

Scrivere abbastanza cifre aiuta solo se il reader le converte al double più vicino, e il Val della RTL non sempre lo fa. Durante la verifica la RTL sulla macchina di test leggeva sia 9007199254740990.7 sia l'intero semplice 9007199254740991 nel valore binary64 adiacente, sbagliato. Entrambe le stringhe dovrebbero atterrare su 2⁵³-1; una è una notazione a 17 cifre che HotXLS può produrre, l'altra è come Excel scrive lo stesso valore. Un writer corretto in isolamento avrebbe spedito un file che lo stesso HotXLS non sapeva rileggere correttamente

Così HotXLS mantiene Val come portinaio e corregge la sua risposta. Dopo che la grammatica numerica esistente accetta un token non data, un parser esatto costruisce il decimale come frazione a interi grandi e lo confronta con i punti medi binari ai due lati del candidato della RTL. Se il decimale cade sotto il punto medio inferiore, il candidato scende di un ulp; sopra il punto medio superiore, sale; su un pari esatto vince il significando pari, che è la regola IEEE 754 round-to-nearest-even. Al candidato sono concessi al massimo otto aggiustamenti di vicino, che sono più che sufficienti perché Val sbaglia di un'unità, non di un miglio

La correzione è deliberatamente limitata. I token più lunghi di 48 caratteri, con più di 17 cifre significative, o con un modulo dell'esponente sopra 324 la saltano e conservano il risultato del parser legacy, e qualunque fallimento interno ripiega sulla stessa cosa. Gli interi grandi usano 48 limb da 32 bit ciascuno, abbastanza per il numeratore e il denominatore più larghi dentro quei limiti. Un dettaglio di portabilità conta qui: i limb sono dichiarati esplicitamente come UInt32. LongWord sembra la scelta ovvia, ma è 8 byte sul target Linux64 di Delphi, e un tipo limb che raddoppia silenziosamente di larghezza rompe ogni carry e shift nell'aritmetica

Verificare il round-trip bit per bit

Confrontare i double con = va bene per questo test, perché l'obiettivo è l'uguaglianza esatta, ma un confronto sul pattern di bit rende il report del fallimento più utile: una deriva di un ulp si manifesta come differenza di 1 nel valore grezzo a 64 bit invece che come due numeri che si stampano identici

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;

La riga 3 è quella interessante. Legge il risultato in cache di INT(-1E15-0.1) senza ricalcolare, quindi dimostra che la cache stessa è senza perdite invece che il motore di formula essere capace di ricalcolare il valore. Se dipendete dai risultati in cache in una pipeline, la guida a parte sul leggere i valori delle formula in cache senza ricalcolo copre gli stati della cache e perché una cache mancante non viene mai inventata

Quali altre parti XLSX portano cache numeriche?

Correggere il writer di celle ha chiuso un percorso su parecchi, e ogni punto di scrittura numerico rimasto doveva essere controllato per conto suo. La prima release copriva i valori del foglio di lavoro e le cache delle formula, comprese le cache dei valori dell'output Pivot. Le cache sorgente Pivot e le cache Chart passavano ancora per l'imbuto a 15 cifre e sono state chiuse in due release successive invece che per assunzione

• Cache sorgente Pivot: i valori sharedItems e i writer cacheRecords sia inline sia in streaming usano ora lo stesso invariante a 17 cifre, e il percorso di lettura della pivot applica la stessa correzione esatta ai punti medi. Gli attributi descrittivi come refreshedDate conservano la propria notazione originale
• Cache Chart: l'unico punto di scrittura che costruisce i valori dei punti in cache è passato a 17 cifre, e i sei punti di lettura, che coprono valori e categorie delle trendline, validazione delle barre di errore e due percorsi di rendering, passano tutti per un unico helper di parse esatto
• Tutto il resto numerico, come larghezze di colonna e limiti degli assi, passa ancora per il formatter difensivo che scarica i valori non finiti e subnormali a 0, perché una parte serializzata non deve mai contenere un float non analizzabile

Il controllo di interoperabilità che contava di più era con Excel stesso. Excel scrive 2⁵³-1 come l'intero 9007199254740991; HotXLS può scrivere l'equivalente notazione a 17 cifre 9007199254740990.7. Entrambi i token si analizzano nello stesso double su entrambi i lati, quindi un file scritto in Excel, riaperto e risalvato da HotXLS, poi riaperto in Excel, conserva gli stessi valori nella propria cache Pivot. Il riconoscimento reciproco, non la notazione identica, è la proprietà da testare

Limiti e casi limite da conoscere

Senza perdite qui significa che il valore binary64 è preservato, non che ogni valore sia rappresentabile. I risultati non finiti si salvano ancora come errori #NUM! e i subnormali si salvano ancora come zero, esattamente come prima. Anche la visualizzazione è un capitolo a parte: Excel continuerà a mostrare 15 cifre significative sullo schermo, quindi un utente che digita 9007199254740993 in Excel riceve comunque un valore memorizzato diverso, e nessun writer può correggerlo. Se la vostra cartella usa Set precision as displayed, i valori vengono deliberatamente arrotondati al loro formato numerico, che è l'opposto di ciò di cui parla questo articolo; le regole di quella modalità sono trattate in come HotXLS applica l'arrotondamento precision as displayed di Excel

Il reader esatto inoltre non sostituisce Val; corregge solo i valori che Val ha già accettato. Un token che la RTL rifiuta resta rifiutato, e un token fuori dai limiti di 48 caratteri, 17 cifre ed esponente 324 conserva qualunque cosa abbia prodotto il parser legacy. Per i file scritti da HotXLS o Excel non conta mai, perché nessuno dei due produce token del genere per i valori delle celle, ma una parte XML fatta a mano con 40 cifre significative viene letta con l'accuratezza della RTL, non con la correzione esatta

FAQ

HotXLS cambia il numero che Excel mostra per una cella?

No. HotXLS cambia solo quante cifre finiscono nel token <v> memorizzato. Excel continua a mostrare 15 cifre significative, quindi le celle appaiono uguali; la differenza è che il double sottostante ora combacia con ciò che il vostro codice Delphi ha assegnato

Perché non scrivere semplicemente 16 cifre invece di 17?

Sedici cifre non bastano per ogni valore binary64. Alcuni double richiedono 17 cifre significative prima che la loro notazione decimale sia univoca, quindi un writer a 16 cifre mapperebbe comunque due double distinti sullo stesso testo in rari casi, che è esattamente la classe di deriva silenziosa che il cambiamento rimuove

I file scritti prima della correzione vanno rigenerati?

Solo se contengono valori che richiedevano più di 15 cifre. Quei token hanno già perso le loro ultime cifre su disco e leggerli con più accuratezza non può restaurare ciò che non è mai stato scritto. Risalvare dai dati originali, o ricalcolare le formula e salvare di nuovo, produce un file esatto

L'output numerico a 17 cifre, la correzione esatta in lettura binary64 e la gestione corrispondente delle cache Pivot e Chart fanno parte di HotXLS Delphi Spreadsheet Component per Delphi, C++Builder e Lazarus, che legge e scrive XLSX senza Excel o automazione OLE installati