Tabsfri XLSX double-roundtrips i Delphi: 17 cifre

HotXLS skriver almindelige numeriske celleværdier og formelcacher ind i XLSX med 17 signifikante cifre, og ved læsning korrigerer den Delphi-RTL-parsen med en afgrænset eksakt decimal-til-binary64-tjek, så en Double, du gemmer i en celle, kommer tilbage bit for bit efter en gemning og genåbning. Femten cifre, tallet Excel viser på skærmen, er ikke nok til at identificere en IEEE 754 binary64-værdi, og gabet viser sig præcis dér, hvor finans- og ID-kolonner bor: heltal forbi 10¹⁵

Fejlen, der tvang ændringen frem, så harmløs ud. En regnearksformel INT(-1E15-0.1) evaluerer til -1000000000000001, en fuldt ud repræsentérbar double. HotXLS beregnede den korrekt, cachede den, gemte filen, og cachen på disk lød -1E15. Excel ville først vise den oprindelige værdi efter en genberegning, og enhver forbruger, der stoler på cachede værdier — det er de fleste — fik stille et tal forskelligt med én. Intet kastede, intet advarede, og filen validerede fint

Hvorfor er 15 signifikante cifre ikke nok til en double?

Femten decimalcifre er garanteret at overleve en tur ind i binary64 og tilbage, men den omvendte retning behøver 17. IEEE 754 binary64 har en 53-bit signifikand, hvilket giver mellem 15 og 17 decimalcifre afhængigt af, hvor værdien ligger. Enhver decimalstreng med højst 15 signifikante cifre konverterer til en double og printer tilbage uændret. En double behøver derimod op til 17 cifre, før dens decimalstavning entydigt identificerer den, og i intervallet over 2⁵³ ligger nabodoubles 2 fra hinanden, mens de mellem 10¹⁵ og 2⁵³ ligger 0,125 til 1 fra hinanden

Delphi's FloatToStr formatterer med 15 signifikante cifre, hvilket er samme præcision, Excel bruger til visning. Det match er præcis grunden til, at defekten skjuler sig så godt: åbn filen i Excel, og cellen ligner præcis det, den gjorde i dit Delphi-program, fordi Excel også kun viser 15 cifre. Forskellen bliver kun synlig, når du sammenligner den gemte double, subtraherer to nærliggende værdier eller fodrer cachen ind i noget, der laver aritmetik. Værdien -1000000000000001 har 16 signifikante cifre, så afrunding til 15 giver -1000000000000000, og den afsluttende enhed er væk for altid

ECMA-376 Part 1 §18.3.1.96 definerer <v>-elementet, der bærer en celleværdi som tekst, og intet i skemaet foreskriver, hvor mange cifre en writer skal udsende for en talcelle. Præcision er helt writerens ansvar, hvilket er grunden til, at to producenter begge kan skrive gyldig XLSX og stadig være uenige om, hvilket tal der er i cellen

Skrivning af 17 signifikante cifre fra Delphi

HotXLS bruger nu én invariant til numeriske celler: FloatToStrF med ffGeneral, præcision 17 og invariante formatindstillinger. Datogrenen af cellewriteren brugte allerede præcis den projektion, så almindelige tal sluttede sig simpelthen til. Undefra ændrer intet sig i API'en; du tildeler en Double eller en formel, gemmer, og tokenet, der lander i <v>, er præcist nok til at genskabe double'en

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');
    // Værdier og formelcachen skrives med 17 signifikante cifre
    if Book.SaveAs('ledger.xlsx') <> lxOk then
      raise Exception.Create('Save failed');
  finally
    Book.Free;
  end;
end;

Sytten cifre er et loft, ikke en padderegel. ffGeneral dropper afsluttende nuller, så 0,5 skrives stadig som 0.5, og små heltal forbliver korte; kun værdier, der reelt behøver ekstra cifre, får dem. Omkostningen er nogle få bytes på de celler, der tidligere blev afkortet, hvilket er den billigste korrekthedsfix, du nogensinde udsender

Hvorfor taber skrivning af 17 cifre stadig værdier ved læsning?

At skrive nok cifre hjælper kun, hvis readeren konverterer dem til den nærmeste double, og RTL'ens Val gør ikke altid det. Under verifikation læste RTL'en på testmaskinen både 9007199254740990.7 og det rene heltal 9007199254740991 ind i den tilstødende, forkerte binary64-værdi. Begge strenge bør lande på 2⁵³-1; den ene er en 17-cifret stavning, HotXLS kan producere, den anden er, hvordan Excel skriver samme værdi. En writer fikset isoleret ville have udsendt en fil, som HotXLS selv ikke kunne læse korrekt tilbage

Så HotXLS beholder Val som portvogten og korrigerer dens svar. Efter at den eksisterende numeriske grammatik accepterer et ikke-dato-token, bygger en eksakt parser decimalen som en big-integer-brøk og sammenligner den med de binære midtpunkter på hver side af RTL'ens kandidat. Ligger decimalen under det lavere midtpunkt, træder kandidaten én ulp ned; over det øvre midtpunkt træder den op; ved et eksakt ligepunkt vinder den lige signifikand, hvilket er IEEE 754 round-to-nearest-even-reglen. Kandidaten tillades højst otte nabotilpasninger, hvilket er rigeligt, fordi Val tager fejl med én enhed, ikke med en mil

Korrektionen er bevidst afgrænset. Tokens længere end 48 tegn, med mere end 17 signifikante cifre eller med en eksponentstørrelse over 324 springer den over og beholder legacy-parserens resultat, og enhver intern fiasko falder tilbage til samme ting. De store heltal bruger 48 limbs på 32 bits hver, nok til den bredeste tæller og nævner inden for de grænser. Én portabilitetsdetalje betyder noget her: limbs er erklæret eksplicit som UInt32. LongWord ligner det oplagte valg, men det er 8 bytes på Delphi's Linux64-mål, og en limb-type, der stille fordobler i bredde, knækker hver carry og shift i aritmetikken

Verificering af roundtrippet bit for bit

At sammenligne doubles med = er fint til denne test, fordi målet er eksakt lighed, men en bitmønstersammenligning gør fejlrapporten mere nyttig: et én-ulp-drift viser sig som en forskel på 1 i den rå 64-bit værdi frem for som to tal, der printer identisk

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;

Række 3 er den interessante. Den læser det cachede resultat af INT(-1E15-0.1) uden at genberegne, så den beviser, at cachen selv er tabsfri, frem for at formelmotoren kan genberegne værdien. Afhænger du af cachede resultater i en pipeline, dækker den separate guide til læsning af cachede formelværdier uden genberegning cachetilstandene og hvorfor en manglende cache aldrig opfindes

Hvilke andre XLSX-dele bærer numeriske cacher?

At fikse cellewriteren lukkede én sti ud af flere, og hvert resterende numeriske skrivepunkt måtte tjekkes for sig. Den første release dækkede regnearksværdier og formelcacher, inklusive værdicacherne af Pivot-output. Pivot-kildecacher og Chart-cacher gik stadig gennem den 15-cifrede tragt og blev lukket i to opfølgningsreleases frem for ved antagelse

• Pivot-kildecache: sharedItems-værdierne og både den inline og den streamede cacheRecords-writer bruger nu samme 17-cifrede invariant, og pivot-læsestien anvender samme eksakte midtpunktskorrektion. Deskriptive attributter som refreshedDate beholder deres oprindelige stavning
• Chart-cacher: det enkelte skrivepunkt, der bygger cachede punktværdier, skiftede til 17 cifre, og de seks læsepunkter, der dækker trendline-værdier og kategorier, error bar-validering og to renderingsstier, går alle gennem én eksakt-parse-hjælper
• Alt andet numerisk, såsom kolonnebredder og aksegrænser, går stadig gennem den defensive formatter, der skyller ikke-endelige og subnormale værdier til 0, fordi en serialiseret del aldrig må indeholde en uparsébar float

Interoperabilitetstjekket, der betød mest, var med Excel selv. Excel skriver 2⁵³-1 som heltallet 9007199254740991; HotXLS kan skrive den ækvivalente 17-cifrede stavning 9007199254740990.7. Begge tokens parser til samme double på begge sider, så en fil fabrikeret i Excel, genåbnet og gemt igen af HotXLS og derefter åbnet igen i Excel, beholder samme værdier i sin Pivot-cache. Gensidig genkendelse, ikke identisk stavning, er den egenskab, man skal teste for

Grænser og edge cases værd at kende

Tabsfri betyder her, at binary64-værdien bevares, ikke at enhver værdi er repræsentérbar. Ikke-endelige resultater gemmer stadig som #NUM!-fejl, og subnormaler gemmer stadig som nul, præcis som før. Visning er også en separat sag: Excel bliver ved med at vise 15 signifikante cifre på skærmen, så en bruger, der taster 9007199254740993 ind i Excel, får stadig en anden gemt værdi, og ingen writer kan fikse det. Bruger din projektmappe Set precision as displayed, afrundes værdier bevidst til deres talformat, hvilket er det modsatte af, hvad denne artikel handler om; reglerne for den tilstand er dækket i hvordan HotXLS anvender Excel precision as displayed-afrunding

Den eksakte reader erstatter heller ikke Val; den korrigerer kun værdier, som Val allerede har accepteret. Et token, RTL'en afviser, afvises stadig, og et token uden for 48-tegns-, 17-cifre- og eksponent-324-grænserne beholder, hvad legacy-parseren producerede. For filer skrevet af HotXLS eller Excel betyder det aldrig noget, fordi ingen af dem producerer sådanne tokens til celleværdier, men en håndlavet XML-del med 40 signifikante cifre læses med RTL'ens præcision, ikke med den eksakte korrektion

FAQ

Ændrer HotXLS det tal, Excel viser for en celle?

Nej. HotXLS ændrer kun, hvor mange cifre der går ind i det gemte <v>-token. Excel viser stadig 15 signifikante cifre, så celler ligner de samme; forskellen er, at den underliggende double nu matcher, hvad din Delphi-kode tildelte

Hvorfor ikke bare skrive 16 cifre i stedet for 17?

Seksten cifre er ikke nok til enhver binary64-værdi. Nogle doubles behøver 17 signifikante cifre, før deres decimalstavning er entydig, så en 16-cifret writer ville stadig afbilde to distinkte doubles til samme tekst i sjældne tilfælde, hvilket præcis er den klasse af stille drift, ændringen fjerner

Skal filer skrevet før fixet gengenereres?

Kun hvis de indeholder værdier, der behøvede mere end 15 cifre. De tokens mistede allerede deres sidste cifre på disk, og at læse dem mere præcist kan ikke genskabe det, der aldrig blev skrevet. At gemme igen fra originaldataene eller genberegne formler og gemme igen producerer en eksakt fil

Sytten-cifret numerisk output, den eksakte binary64-læseskorrektion og den matchende Pivot- og Chart-cachehåndtering er en del af HotXLS Delphi Spreadsheet Component til Delphi, C++Builder og Lazarus, som læser og skriver XLSX uden Excel eller OLE-automatisering installeret