Bezztrátové round-tripy double v XLSX v Delphi: 17 číslic

HotXLS zapisuje obyčejné numerické hodnoty buněk i formula cache do XLSX s 17 významnými číslicemi a při čtení opravuje RTL parse Delphi ohraničenou přesnou dekadicko-binární kontrolou do binary64, takže Double, který do buňky uložíte, se vrátí bit po bitu po uložení a znovuotevření. Patnáct číslic, počet, který Excel ukazuje na obrazovce, nestačí k identifikaci hodnoty IEEE 754 binary64 a mezera se ukáže přesně tam, kde žijí finanční a ID sloupce: integery nad 10¹⁵

Bug, který změnu vynutil, vypadal neškodně. Formula INT(-1E15-0.1) na listu se vyhodnotí na -1000000000000001, naprosto reprezentovatelný double. HotXLS ho spočítal správně, cachoval, uložil soubor a cache na disku četla -1E15. Excel by původní hodnotu ukázal až po přepočtu a jakýkoli konzument, který důvěřuje cachovaným hodnotám — což je většina z nich — tiše dostal číslo vedle o jedničku. Nic nevyhodil, nic nevaroval a soubor prošel validací

Proč 15 významných číslic nestačí na double?

Patnáct dekadických číslic je garantované přežít cestu do binary64 a zpět, ale opačný směr potřebuje 17. IEEE 754 binary64 má 53bitovou mantisu, což vychází na 15 až 17 dekadických číslic podle toho, kde hodnota sedí. Jakýkoli dekadický řetězec s nejvýš 15 významnými číslicemi se převede na double a vytiskne se zpět nezměněný. Double ale potřebuje až 17 číslic, než jeho dekadický zápis jednoznačně identifikuje, a v rozsahu nad 2⁵³ jsou sousední doubles vzdálené 2, zatímco mezi 10¹⁵ a 2⁵³ jsou vzdálené 0,125 až 1

FloatToStr v Delphi formátuje s 15 významnými číslicemi, což je stejná přesnost, jakou Excel používá k zobrazení. Právě ta shoda je důvod, proč se defekt tak dobře schovává: otevřete soubor v Excelu a buňka vypadá přesně jako v programu v Delphi, protože Excel taky ukazuje jen 15 číslic. Rozdíl se stane viditelným, až srovnáte uložený double, odečtete dvě blízké hodnoty, nebo pustíte cache do něčeho, co dělá aritmetiku. Hodnota -1000000000000001 má 16 významných číslic, takže zaokrouhlení na 15 produkuje -1000000000000000 a závěrečná jednotka je pryč nadobro

ECMA-376 Part 1 §18.3.1.96 definuje element <v>, který nese hodnotu buňky jako text, a nic ve schématu nepředepisuje, kolik číslic musí writer pro numerickou buňku vyslat. Přesnost je zcela odpovědností writeru, proto mohou dva producenti psát validní XLSX a přesto se rozcházet v tom, jaké číslo je v buňce

Zápis 17 významných číslic z Delphi

HotXLS teď používá pro numerické buňky jedinou invariantu: FloatToStrF s ffGeneral, přesností 17 a invariantním nastavením formátu. Datová větev writeru buněk už přesně tu projekci používala, takže obyčejná čísla se prostě připojila. Zvenku se na API nic nemění; přiřadíte Double nebo formuli, uložíte a token, který dopadne do <v>, je dost přesný na reprodukci 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');
    // Hodnoty i formula cache se zapisují s 17 významnými číslicemi
    if Book.SaveAs('ledger.xlsx') <> lxOk then
      raise Exception.Create('Save failed');
  finally
    Book.Free;
  end;
end;

Sedmnáct číslic je strop, ne pravidlo vypchávání. ffGeneral zahazuje koncové nuly, takže 0,5 se pořád píše jako 0.5 a malé integery zůstávají krátké; navíc číslice dostanou jen hodnoty, které je doopravdy potřebují. Cena je pár bajtů na buňkách, které se dřív ořezávaly, což je nejlevnější oprava korektnosti, jakou kdy pošlete

Proč zápis 17 číslic pořád ztrácí hodnoty při čtení?

Zapsat dost číslic pomůže jen tehdy, když je čtečka převede na nejbližší double, a RTL Val to nedělá vždycky. Během ověřování četl RTL na testovacím stroji 9007199254740990.7 i prostý integer 9007199254740991 do sousední, špatné hodnoty binary64. Oba řetězce by měli dopadnout na 2⁵³-1; jeden z nich je 17číslicový zápis, který umí HotXLS, druhý je to, jak tu samou hodnotu píše Excel. Writer opravený izolovaně by odeslal soubor, který by sám HotXLS nedokázal správně přečíst

HotXLS proto nechává Val jako vrátného a opravuje jeho odpověď. Poté, co stávající numerická gramatika přijme token, který není datem, postaví přesný parser dekadické číslo jako zlomek big-integer a srovná ho s binárními středy po obou stranách kandidáta RTL. Padne-li dekadické číslo pod spodní střed, krokne kandidát o jeden ulp dolů; nad horní střed, krokne nahoru; při přesné remíze vyhraje sudá mantisa, což je IEEE 754 pravidlo round-to-nearest-even. Kandidát smí dostat nejvýš osm sousedních úprav, což bohatě stačí, protože Val se mýlí o jednotku, ne o míli

Oprava je záměrně ohraničená. Tokeny delší než 48 znaků, s víc než 17 významnými číslicemi nebo s velikostí exponentu nad 324 ji vynechají a ponechají výsledek legacy parseru a jakékoli interní selhání spadne k témuž. Big integery používají 48 limbů po 32 bitech, dost pro nejširšího čitatele i jmenovatele uvnitř těch hranic. Jeden přenositelný detail je tu důležitý: limbí jsou deklarované explicitně jako UInt32. LongWord vypadá jako zjevná volba, ale je to 8 bajtů na Linux64 cíli Delphi a typ limbu, který se tiše zdvojnásobí v šířce, rozbije každý carry a posun v aritmetice

Ověření round-tripu bit po bitu

Srovnávání doubles přes = je pro tenhle test v pořádku, protože cílem je přesná rovnost, ale srovnání bitových vzorů udělá hlášení selhání užitečnějším: drift o jeden ulp se projeví jako rozdíl 1 v syrové 64bitové hodnotě místo dvou čísel, která se tisknou stejně

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;

Řádek 3 je ten zajímavý. Čte cachovaný výsledek INT(-1E15-0.1) bez přepočtu, takže dokazuje, že bezztrátová je sama cache, ne to, že formula engine umí hodnotu přepočítat. Závisíte-li v pipeline na cachovaných výsledcích, samostatný průvodce čtením cachovaných hodnot formulí bez přepočtu pokrývá stavy cache a proč se chybějící cache nikdy nevymýšlí

Které další části XLSX nesou numerické cache?

Oprava writeru buněk zavřela jednu cestu z několika a každý zbývající numerický zápisový bod se musel zkontrolovat sám. První vydání pokrylo hodnoty listů a formula cache, včetně value cache výstupu Pivotu. Cache zdrojů Pivotu a grafů ještě šly 15číslicovým trychtýřem a zavřely se ve dvou navazujících vydáních, ne domněnkou

• Cache zdroje pivotu: hodnoty sharedItems i writeri cacheRecords inline i streamované teď používají tutéž 17číslicovou invariantu a čtecí cesta pivotu aplikuje tutéž přesnou opravu středů. Popisné atributy jako refreshedDate si drží původní pravopis
• Cache grafů: jediný zápisový bod, který staví cachované hodnoty bodů, se přepnul na 17 číslic a šest čtecích bodů, pokrývajících hodnoty a kategorie trendline, validaci error barů a dvě vykreslovací cesty, jde všemi přes jeden přesný-parse helper
• Všechno ostatní numerické, jako šířky sloupců a meze os, stále prochází defenzivním formátovačem, který spláchne ne-konečné a subnormální hodnoty na 0, protože serializovaná část nesmí nikdy obsahovat neparsovatelný float

Nejzajímavější interoperabilní kontrola byla s Excelem samotným. Excel zapisuje 2⁵³-1 jako integer 9007199254740991; HotXLS může zapsat ekvivalentní 17číslicový zápis 9007199254740990.7. Oba tokeny se na obou stranách parsují na tentýž double, takže soubor napsaný Excelem, znovu otevřený a znovu uložený HotXLSEM a pak znovu otevřený Excelem, drží ve své Pivot cache stejná hodnota. Vzájemné porozumění, ne identický pravopis, je vlastnost, kterou se má testovat

Limity a okrajové případy, které stojí za znalost

Bezztrátové tady znamená, že hodnota binary64 se zachová, ne že je každá hodnota reprezentovatelná. Ne-konečné výsledky se pořád ukládají jako chyby #NUM! a subnormály pořád jako nula, přesně jako dřív. I zobrazení je samostatná věc: Excel bude pořád ukazovat 15 významných číslic na obrazovce, takže uživatel, který do Excelu napíše 9007199254740993, pořád dostane jinou uloženou hodnotu a žádný writer to nespraví. Používá-li váš sešit Set precision as displayed, hodnoty se záměrně zaokrouhlují na jejich číselný formát, což je opak toho, o čem je tenhle článek; pravidla toho režimu popisuje jak HotXLS aplikuje zaokrouhlování Excel precision as displayed

Přesný čteč také nenahrazuje Val; opravuje jen hodnoty, které Val už přijal. Token, který RTL odmítne, se pořád odmítá a token mimo hranic 48 znaků, 17 číslic a exponentu 324 drží, co vyprodukoval legacy parser. U souborů psaných HotXLSEM nebo Excelem na tom nezáleží, protože žádný z nich takové tokeny pro hodnoty buněk neprodukuje, ale ručně vyrobená XML část se 40 významnými číslicemi se čte s přesností RTL, ne s přesnou opravou

FAQ

Mění HotXLS číslo, které Excel pro buňku zobrazuje?

Ne. HotXLS mění jen to, kolik číslic jde do uloženého tokenu <v>. Excel pořád zobrazuje 15 významných číslic, takže buňky vypadají stejně; rozdíl je v tom, že podkladový double teď odpovídá tomu, co váš kód v Delphi přiřadil

Proč rovnou nezápsat 16 číslic místo 17?

Šestnáct číslic nestačí na každou hodnotu binary64. Některé doubles potřebují 17 významných číslic, než je jejich dekadický zápis jednoznačný, takže 16číslicový writer by i pořád mapoval dva odlišné doubles na tentýž text ve vzácných případech, což je přesně ta třída tichého driftu, kterou ta změna odstraňuje

Je potřeba regenerovat soubory psané před opravou?

Jen pokud obsahují hodnoty, které potřebovaly víc než 15 číslic. Ty tokeny už ztratily své poslední číslice na disku a čtení přesněji nedokáže obnovit, co se nikdy nezapsalo. Znovuuložení z původních dat, nebo přepočet formulí a další uložení, vytvoří přesný soubor

Numerický výstup na 17 číslic, přesná oprava čtení binary64 a odpovídající obsluha cache Pivot a Chart jsou součástí HotXLS Delphi Spreadsheet Component pro Delphi, C++Builder a Lazarus, který čte a zapisuje XLSX bez instalovaného Excelu nebo OLE automatizace