Förlustfria XLSX-double-returer i Delphi: 17 siffror

HotXLS skriver vanliga numeriska cellvärden och formulär-cachar in i XLSX med 17 signifikanta siffror, och vid läsning korrigerar den Delphi-RTL:ens parsning med en avgränsad exakt decimal-till-binary64-kontroll, så en Double du lagrar i en cell kommer tillbaka bit för bit efter att ha sparats och återöppnats. Femton siffror, antalet Excel visar på skärmen, räcker inte för att identifiera ett IEEE 754 binary64-värde, och gapet syns exakt där finans- och ID-kolumner bor: heltal förbi 10¹⁵

Buggen som tvingade fram ändringen såg harmlös ut. En kalkylarksformel INT(-1E15-0.1) utvärderas till -1000000000000001, en fullt representerbar double. HotXLS beräknade den korrekt, cachade den, sparade filen, och cachen på disk läste -1E15. Excel skulle visa originalvärdet först efter en omkalkylering, och varje konsument som litar på cachade värden, vilket är de flesta, fick tyst ett tal fel med ett. Inget kastade, inget varnade, och filen validerade fint

Varför räcker inte 15 signifikanta siffror för en double?

Femton decimalsiffror är garanterade att överleva en tur in i binary64 och tillbaka, men motsatt riktning behöver 17. IEEE 754 binary64 har en 53-bitars significand, vilket ger mellan 15 och 17 decimalsiffror beroende på var värdet sitter. Varje decimalsträng med högst 15 signifikanta siffror konverteras till en double och skrivs tillbaka oförändrad. En double behöver däremot upp till 17 siffror innan dess decimalstavning unikt identifierar den, och i intervallet över 2⁵³ ligger intilliggande doubles 2 isär, medan de mellan 10¹⁵ och 2⁵³ ligger 0,125 till 1 isär

Delphis FloatToStr formaterar med 15 signifikanta siffror, vilket är samma precision Excel använder för visning. Den matchningen är precis varför defekten gömmer sig så bra: öppna filen i Excel och cellen ser exakt ut som den gjorde i ditt Delphi-program, eftersom Excel också bara visar 15 siffror. Skillnaden blir synlig först när du jämför den lagrade doublen, subtraherar två närliggande värden, eller matar cachen till något som gör aritmetik. Värdet -1000000000000001 har 16 signifikanta siffror, så avrundning till 15 ger -1000000000000000 och den avslutande enheten är borta för gott

ECMA-376 Part 1 §18.3.1.96 definierar elementet <v> som bär ett cellvärde som text, och ingenting i schemat föreskriver hur många siffror en skrivare måste sända ut för en talscell. Precisionen är helt skrivarens ansvar, vilket är varför två producenter kan båda skriva giltig XLSX och ändå tvista om vilket tal som finns i cellen

Att skriva 17 signifikanta siffror från Delphi

HotXLS använder nu en invariant för numeriska celler: FloatToStrF med ffGeneral, precision 17, och invarianta formatinställningar. Datumgrenen i cellskrivaren använde redan exakt den projektionen, så vanliga tal anslöt sig bara till den. Utvändigt ändras ingenting i API:et; du tilldelar en Double eller en formel, sparar, och tokenen som landar i <v> är tillräckligt precis för att återskapa doublen

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ärden och formelcachen skrivs med 17 signifikanta siffror
    if Book.SaveAs('ledger.xlsx') <> lxOk then
      raise Exception.Create('Save failed');
  finally
    Book.Free;
  end;
end;

Sjutton siffror är ett tak, inte en utfyllnadsregel. ffGeneral slänger avslutande nollor, så 0.5 skrivs fortfarande som 0.5 och små heltal förblir korta; bara värden som genuint behöver extra siffror får dem. Kostnaden är några byte på de celler som tidigare avkapades, vilket är den billigaste korrekthetsfix du någonsin levererar

Varför tappar skrivande av 17 siffror ändå värden vid läsning?

Att skriva tillräckligt många siffror hjälper bara om läsaren konverterar dem till närmaste double, och RTL:ens Val gör inte alltid det. Under verifieringen läste RTL:en på testmaskinen både 9007199254740990.7 och det blotta heltalet 9007199254740991 in i det intilliggande, felaktiga binary64-värdet. Båda strängarna borde landa på 2⁵³-1; den ena är en 17-siffrig stavning HotXLS kan producera, den andra är hur Excel skriver samma värde. En skrivare fixad isolerat skulle ha levererat en fil som HotXLS själv inte kunde läsa tillbaka korrekt

Så HotXLS behåller Val som portvakt och korrigerar dess svar. Efter att den befintliga numeriska grammatiken accepterat en icke-datum-token bygger en exakt parser decimalen som en big-integer-bråkdel och jämför den mot de binära mittpunkterna på vardera sidan om RTL:ens kandidat. Ligger decimalen under den undre mittpunkten stegar kandidaten ner en ulp; över den övre stegar den upp; vid exakt oavgjort vinner den jämna significanden, vilket är IEEE 754:s round-to-nearest-even-regel. Kandidaten tillåts högst åtta grannjusteringar, vilket är gott, eftersom Val har fel med en enhet, inte med en mil

Korrektionen är medvetet avgränsad. Token längre än 48 tecken, med mer än 17 signifikanta siffror, eller med en exponentstorlek över 324 hoppar över den och behåller legacy-parserns resultat, och varje internt misslyckande faller tillbaka på samma sak. De stora heltalen använder 48 limbs om 32 bitar vardera, nog för den bredaste täljaren och nämnaren inom de gränserna. En portabilitetsdetalj spelar roll här: limbs deklareras explicit som UInt32. LongWord ser ut som det självklara valet, men den är 8 byte på Delphis Linux64-mål, och en limb-typ som tyst fördubblas i bredd bryter varje carry och skift i aritmetiken

Att verifiera returen bit för bit

Att jämföra doubles med = är fint för det här testet, eftersom målet är exakt likhet, men en bitmönsterjämförelse gör felrapporten mer användbar: en ulp-drift syns som en skillnad på 1 i det råa 64-bitarsvärdet i stället för som två tal som skrivs ut identiskt

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;

Rad 3 är den intressanta. Den läser det cachade resultatet av INT(-1E15-0.1) utan omkalkylering, så den bevisar att cachen i sig är förlustfri i stället för att formelmotorn kan räkna om värdet. Beror du på cachade resultat i en pipeline täcker den separata guiden till att läsa cachade formelvärden utan omkalkylering cachetillstånden och varför en saknad cache aldrig hittas på

Vilka andra XLSX-delar bär numeriska cachar?

Att fixa cellskrivaren stängde en väg av flera, och varje kvarvarande numerisk skrivpunkt behövde kontrolleras för sig. Första releasen täckte kalkylarksvärden och formelcachar, inklusive värdecacharna hos Pivot-utdata. Pivot-källcachar och Diagram-cachar gick fortfarande genom 15-siffrors-tratten och stängdes i två uppföljningsreleaser i stället för av antagande

• Pivot-källcache: värdena i sharedItems och både den inline- och strömmande cacheRecords-skrivaren använder nu samma 17-siffriga invariant, och pivotläsvägen tillämpar samma exakta mittpunktskorrektion. Beskrivande attribut som refreshedDate behåller sin ursprungliga stavning
• Diagramcachar: den enda skrivpunkten som bygger cachade punktvärden växlade till 17 siffror, och de sex läspunkterna, som täcker trendlinjevärden och kategorier, felstapelvalidering och två renderingsvägar, går alla genom en enda exakt-pars-hjälpare
• Allt annat numeriskt, som kolumnbredder och axelgränser, går fortfarande genom den defensiva formateraren som spolar icke-ändliga och subnormala värden till 0, eftersom en serialiserad del aldrig får innehålla en oparsbar float

Den interop-kontroll som spelade störst roll var mot Excel själv. Excel skriver 2⁵³-1 som heltalet 9007199254740991; HotXLS kan skriva den ekvivalenta 17-siffriga stavningen 9007199254740990.7. Båda tokenen parsas till samma double på båda sidor, så en fil författad i Excel, återöppnad och sparad igen av HotXLS och sedan öppnad igen i Excel behåller samma värden i sin Pivot-cache. Ömsesidigt erkännande, inte identisk stavning, är egenskapen att testa för

Gränser och kantfall värda att känna till

Förlustfri här betyder att binary64-värdet bevaras, inte att varje värde är representerbart. Icke-ändliga resultat sparas fortfarande som #NUM!-fel och subnormala sparas fortfarande som noll, precis som tidigare. Visning är också en separat fråga: Excel fortsätter visa 15 signifikanta siffror på skärmen, så en användare som skriver 9007199254740993 i Excel får fortfarande ett annat lagrat värde, och ingen skrivare kan fixa det. Använder din arbetsboken Ställ in precision enligt visning avrundas värden med flit till sitt talformat, vilket är motsatsen till vad den här artikeln handlar om; reglerna för det läget täcks i hur HotXLS tillämpar Excel precision as displayed-avrundning

Den exakta läsaren ersätter inte heller Val; den korrigerar bara värden som Val redan accepterat. En token RTL:en avvisar avvisas fortfarande, och en token utanför gränserna 48 tecken, 17 siffror, exponent 324 behåller vad legacy-parsaren producerade. För filer skrivna av HotXLS eller Excel spelar det aldrig någon roll, eftersom ingen av dem producerar sådana token för cellvärden, men en handgjord XML-del med 40 signifikanta siffror läses med RTL:ens noggrannhet, inte med den exakta korrektionen

FAQ

Ändrar HotXLS talet Excel visar för en cell?

Nej. HotXLS ändrar bara hur många siffror som går in i den lagrade <v>-tokenen. Excel visar fortfarande 15 signifikanta siffror, så cellerna ser likadana ut; skillnaden är att doublen under nu matchar vad din Delphi-kod tilldelade

Varför inte bara skriva 16 siffror i stället för 17?

Sexton siffror räcker inte för varje binary64-värde. Vissa doubles behöver 17 signifikanta siffror innan deras decimalstavning är unik, så en 16-siffrig skrivare skulle fortfarande mappa två åtskilda doubles till samma text i sällsynta fall, vilket är precis den slags tysta drift ändringen tar bort

Måste filer skrivna före fixen återskapas?

Bara om de innehåller värden som behövde mer än 15 siffror. De tokenen tappade redan sina sista siffror på disken och att läsa dem mer exakt kan inte återställa det som aldrig skrevs. Att spara igen från originaldatan, eller räkna om formler och spara igen, ger en exakt fil

Sjuttonsiffrig numerisk utdata, den exakta binary64-läskorrektionen och den matchande Pivot- och Diagram-cachehanteringen är del av HotXLS Delphi Spreadsheet Component för Delphi, C++Builder och Lazarus, som läser och skriver XLSX utan att Excel eller OLE-automation är installerat