Verliesvrije XLSX double round-trips in Delphi: 17 cijfers

HotXLS schrijft gewone numerieke celwaarden en formuliercaches met 17 significante cijfers in XLSX, en corrigeert bij het lezen de Delphi-RTL-parse met een begrensde exacte decimal-naar-binary64-controle, zodat een Double die u in een cel stopt na een opslag en heropen bit voor bit terugkomt. Vijftien cijfers, het aantal dat Excel op het scherm toont, is niet genoeg om een IEEE 754 binary64-waarde te identificeren, en de kloof verschijnt precies waar kolommen voor financiën en ID's wonen: gehele getallen voorbij 10¹⁵

De bug die de verandering afdwong zag er onschuldig uit. Een werkbladformule INT(-1E15-0.1) evalueert naar -1000000000000001, een volstrekt voorstelbare double. HotXLS berekende hem correct, cacheerde hem, sloeg het bestand op, en de cache op schijf las -1E15. Excel zou de originele waarde pas na een herberekening tonen, en elke consument die gecachte waarden vertrouwt, en dat zijn er de meeste, kreeg zwijgend een getal ernaast van één. Niets gooide iets, niets waarschuwde, en het bestand valideerde prima

Waarom zijn 15 significante cijfers niet genoeg voor een double?

Vijftien decimale cijfers zijn gegarandeerd een reis naar binary64 en terug te overleven, maar de omgekeerde richting heeft 17 nodig. IEEE 754 binary64 heeft een significand van 53 bits, wat uitkomt tussen 15 en 17 decimale cijfers afhankelijk van waar de waarde zit. Elke decimale string met ten hoogste 15 significante cijfers converteert naar een double en print onveranderd terug. Een double heeft echter tot 17 cijfers nodig voordat zijn decimale spelling hem uniek identificeert, en in het bereik boven 2⁵³ liggen aangrenzende doubles 2 uit elkaar, terwijl ze tussen 10¹⁵ en 2⁵³ 0.125 tot 1 uit elkaar liggen

De FloatToStr van Delphi formatteert met 15 significante cijfers, dezelfde precisie die Excel voor weergave gebruikt. Die match is precies waarom het defect zich zo goed verstopt: open het bestand in Excel en de cel ziet er exact uit als in uw Delphi-programma, want Excel toont ook maar 15 cijfers. Het verschil wordt pas zichtbaar wanneer u de opgeslagen double vergelijkt, twee nabije waarden aftrekt, of de cache in iets voedt dat rekent. De waarde -1000000000000001 heeft 16 significante cijfers, dus afronden op 15 levert -1000000000000000 op en de achterste eenheid is voorgoed weg

ECMA-376 Part 1 §18.3.1.96 definieert het <v>-element dat een celwaarde als tekst draagt, en niets in het schema voorschrijft hoeveel cijfers een writer voor een getalcel moet uitzenden. Precisie is geheel de verantwoordelijkheid van de writer, en daarom kunnen twee producenten allebei geldige XLSX schrijven en het toch oneens zijn over welk getal er in de cel zit

17 significante cijfers schrijven vanuit Delphi

HotXLS gebruikt nu één invariant voor numerieke cellen: FloatToStrF met ffGeneral, precisie 17 en invariante formatinstellingen. De datumtak van de celwriter gebruikte al precies die projectie, dus gewone getallen sloten er gewoon bij aan. Van buiten verandert er niets aan de API; u wijst een Double of een formule toe, slaat op, en de token die in <v> landt is precies genoeg om de double te reproduceren

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');
    // Waarden en de formuliercache worden met 17 significante cijfers geschreven
    if Book.SaveAs('ledger.xlsx') <> lxOk then
      raise Exception.Create('Save failed');
  finally
    Book.Free;
  end;
end;

Zeventien cijfers is een plafond, geen vulregel. ffGeneral laat volgnullen vallen, dus 0.5 wordt nog steeds als 0.5 geschreven en kleine gehele getallen blijven kort; alleen waarden die de extra cijfers werkelijk nodig hebben krijgen ze. De kost is een paar bytes op de cellen die eerder werden afgekapt, en dat is de goedkoopste correctheidsfix die u ooit zult uitleveren

Waarom verliest het schrijven van 17 cijfers toch waarden bij het lezen?

Genoeg cijfers schrijven helpt alleen als de reader ze naar de dichtstbijzijnde double converteert, en de Val van de RTL doet dat niet altijd. Tijdens de verificatie las de RTL op de testmachine zowel 9007199254740990.7 als het kale gehele getal 9007199254740991 in de aangrenzende, verkeerde binary64-waarde. Beide strings horen op 2⁵³-1 uit te komen; de ene is een spelling van 17 cijfers die HotXLS kan produceren, de andere is hoe Excel dezelfde waarde schrijft. Een writer die alleen zelf was gerepareerd zou een bestand hebben uitgeleverd dat HotXLS zelf niet correct kon teruglezen

HotXLS houdt Val dus als poortwachter en corrigeert diens antwoord. Nadat de bestaande numerieke grammatica een niet-datumtoken accepteert, bouwt een exacte parser de decimal als een big-integer-breuk en vergelijkt hem met de binaire middelpunten aan weerszijden van de kandidaat van de RTL. Valt de decimal onder het onderste middelpunt, dan stapt de kandidaat één ulp omlaag; boven het bovenste middelpunt stapt hij omhoog; bij een exacte gelijke stand wint de even significand, de IEEE 754 round-to-nearest-even-regel. De kandidaat krijgt ten hoogste acht buurcorrecties, en dat is ruim, want Val zit er een eenheid naast, geen mijl

De correctie is bewust begrensd. Tokens langer dan 48 tekens, met meer dan 17 significante cijfers, of met een exponentgrootte boven 324 slaan hem over en houden het resultaat van de legacy-parser, en elke interne misser valt terug op hetzelfde. De big integers gebruiken 48 limbs van elk 32 bits, genoeg voor de breedste teller en noemer binnen die grenzen. Eén portabiliteitsdetail telt hier: de limbs zijn expliciet als UInt32 gedeclareerd. LongWord lijkt de voor de hand liggende keuze, maar hij is 8 bytes op het Linux64-doel van Delphi, en een limbtype dat stilletjes in breedte verdubbelt breekt elke carry en shift in de rekenkunde

De round-trip bit voor bit verifiëren

Doubles met = vergelijken is voor deze test prima, want het doel is exacte gelijkheid, maar een bitpatroonvergelijking maakt het faalrapport bruikbaarder: een drift van één ulp verschijnt als een verschil van 1 in de rauwe 64-bit waarde in plaats van als twee getallen die identiek printen

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;

Rij 3 is de interessante. Hij leest het gecachte resultaat van INT(-1E15-0.1) zonder te herberekenen, dus hij bewijst dat de cache zelf verliesvrij is in plaats van dat de formule-engine de waarde opnieuw kan berekenen. Hangt u in een pipeline van gecachte resultaten af, dan behandelt de aparte gids over gecachte formulierwaarden lezen zonder herberekening de cachestatussen en waarom een ontbrekende cache nooit wordt verzonnen

Welke andere XLSX-onderdelen dragen numerieke caches?

De celwriter repareren sloot één pad van verschillende, en elk resterend numeriek schrijfpunt moest op eigen kracht worden gecontroleerd. De eerste release dekte werkbladwaarden en formuliercaches, inbegrepen de waardecaches van Pivot-output. Pivot-broncaches en Chart-caches gingen nog door de trechter van 15 cijfers en werden in twee opvolgende releases gesloten in plaats van bij aanname

• Pivot-broncache: de sharedItems-waarden en zowel de inline als de gestreamde cacheRecords-writers gebruiken nu dezelfde invariant van 17 cijfers, en het pivot-leespad past dezelfde exacte middelpuntcorrectie toe. Beschrijvende attributen zoals refreshedDate houden hun originele spelling
• Chart-caches: het enige schrijfpunt dat gecachte puntwaarden bouwt schakelde over op 17 cijfers, en de zes leespunten, die trendline-waarden en categorieën, error-bar-validatie en twee renderpaden dekken, gaan allemaal door één exact-parse-helper
• Al het andere numerieke, zoals kolombreedten en asgrenzen, gaat nog steeds door de defensieve formatter die niet-eindige en subnormale waarden op 0 zet, want een geserialiseerd onderdeel mag nooit een onparseerbare float bevatten

De interoperabiliteitscontrole die er het meest toe deed was met Excel zelf. Excel schrijft 2⁵³-1 als het gehele getal 9007199254740991; HotXLS schrijft mogelijk de equivalente spelling van 17 cijfers 9007199254740990.7. Beide tokens parsen aan beide kanten naar dezelfde double, dus een bestand dat in Excel is vervaardigd, door HotXLS heropend en opnieuw opgeslagen, en daarna weer in Excel geopend, houdt dezelfde waarden in zijn Pivot-cache. Wederzijdse herkenning, niet identieke spelling, is de eigenschap om op te testen

Grenzen en randgevallen die het kennen waard zijn

Verliesvrij betekent hier dat de binary64-waarde behouden blijft, niet dat elke waarde voorstelbaar is. Niet-eindige resultaten worden nog steeds als #NUM!-fouten opgeslagen en subnormalen nog steeds als nul, precies zoals voorheen. Weergave is ook een aparte zorg: Excel blijft op het scherm 15 significante cijfers tonen, dus een gebruiker die 9007199254740993 in Excel typt krijgt nog steeds een andere opgeslagen waarde, en geen enkele writer kan dat repareren. Gebruikt uw werkboek Set precision as displayed, dan worden waarden bewust op hun getalnotatie afgerond, het tegenovergestelde van waar dit artikel over gaat; de regels voor die modus worden behandeld in hoe HotXLS Excel precision as displayed-afronding toepast

De exacte lezer vervangt Val ook niet; hij corrigeert alleen waarden die Val al accepteerde. Een token dat de RTL weigert wordt nog geweigerd, en een token buiten de grenzen van 48 tekens, 17 cijfers en exponent 324 houdt wat de legacy-parser opleverde. Voor bestanden geschreven door HotXLS of Excel maakt dat nooit uit, want geen van beide produceert zulke tokens voor celwaarden, maar een handgemaakt XML-onderdeel met 40 significante cijfers wordt gelezen met de nauwkeurigheid van de RTL, niet met de exacte correctie

FAQ

Verandert HotXLS het getal dat Excel voor een cel toont?

Nee. HotXLS verandert alleen hoeveel cijfers er in de opgeslagen <v>-token gaan. Excel toont nog steeds 15 significante cijfers, dus cellen zien er hetzelfde uit; het verschil is dat de onderliggende double nu matcht met wat uw Delphi-code toekende

Waarom niet gewoon 16 cijfers schrijven in plaats van 17?

Zestien cijfers zijn niet genoeg voor elke binary64-waarde. Sommige doubles hebben 17 significante cijfers nodig voordat hun decimale spelling uniek is, dus een writer van 16 cijfers zou in zeldzame gevallen nog steeds twee onderscheiden doubles op dezelfde tekst afbeelden, en precies die klasse van stille drift haalt de verandering weg

Moeten bestanden die vóór de fix zijn geschreven opnieuw worden gegenereerd?

Alleen als ze waarden bevatten die meer dan 15 cijfers nodig hadden. Die tokens verloren hun laatste cijfers al op schijf en ze nauwkeuriger lezen kan herstellen wat nooit is geschreven. Opnieuw opslaan vanuit de originele data, of formules herberekenen en weer opslaan, levert een exact bestand op

Numerieke output van 17 cijfers, de exacte binary64-leescorrectie en de matchende Pivot- en Chart-cache-afhandeling maken deel uit van de HotXLS Delphi Spreadsheet Component voor Delphi, C++Builder en Lazarus, die XLSX leest en schrijft zonder Excel of OLE-automatisering geïnstalleerd