Technisch artikel

Waarom Excel 1.23457E+11 toont voor een 12-cijferig getal

Een Delphi-export slaat rekeningnummer 123456789012 op als getal; een brede Excel-kolom toont 1.23457E+11, terwijl =A1&"" alle twaalf cijfers teruggeeft. Excel zet getallen met twee regels om in tekst: elf tekens voor de General-weergave en vijftien significante cijfers binnen formules, dus leest een HotXLS-export de eerste uit FormattedText en bouwt de tweede met de formula-engine

Hoeveel tekens staat het General-formaat toe?

Elf, met het minteken buiten de telling. ECMA-376 Part 1 §18.8.30 zegt dat de toepassing "shall attempt to display the full number up to 11 digits (inc. decimal point)", en de floating point-regel stelt het preciezer: de "max overall length for cell display is 11, not including negative sign". Daarna schetst de standaard vier voorwaarden voor de overschakeling naar exponentiële vorm en houdt het daar op. Rounding blijft open. Ook het omslagpunt voor kleine getallen. Het gedrag hieronder is wat Excel in een brede kolom toont, en het is wat HotXLS reproduceert in de displaytekst van een cel en in TEXT(A1, "General"):

  • Een getal waarvan het geheel deel in elf tekens past, blijft in vaste notatie en geeft decimalen op: 12345.6789012345 toont als 12345.6789, en 12345678901.5 rondt af op 12345678902
  • Een geheel deel van twaalf cijfers past niet, dus 123456789012 wordt 1.23457E+11, met de mantissa afgerond om de exponent ruimte te geven
  • Een getal onder 1 blijft in vaste notatie zolang die minstens evenveel significante cijfers toont als de wetenschappelijke vorm met zes tekens: 0.00012345678 toont als 0.000123457, maar 0.0000123456789 wordt 1.23457E-05
  • De exponent draagt altijd een teken en minstens twee cijfers, dus 1020 toont als 1E+20

Het teken zit buiten het budget. -12345678901 toont met alle elf cijfers achter het minteken, twaalf tekens in totaal, terwijl -123456789012 wordt -1.23457E+11. Het Variant-type dat u toekent verandert het resultaat niet. Excel bewaart elke numerieke celwaarde als double, en HotXLS stuurt Integer-, Int64-, Currency- en Double-waarden door dezelfde regel van elf tekens, dus Cells[1, 1].Value := Int64(123456789012) toont ook als 1.23457E+11. De regel loopt door in eigen formaten: literal tekst in een sectie wordt rond het General-resultaat gevoegd, dus General" kg" toont hetzelfde getal als 1.23457E+11 kg. Formaten met lege of alleen-literal-secties volgen hun eigen regels en kunnen het getal helemaal verbergen

Beslissingsstroom voor de displayregel van elf tekens in het General-formaat van Excel in HotXLS voor Delphi: positionele notatie, omschakeling naar wetenschappelijk en het minteken buiten het budget
een brede kolom geeft het General-formaat elf tekens voor cijfers en de decimale punt, terwijl het minteken buiten het budget wacht

Nog een limiet zit buiten elke bibliotheek. De standaard voegt eraan toe dat "the optimal method of display is based on the available cell width", en in een kolom met standaardbreedte perst Excel verder samen, tot 1.2E+11 of een rij #-tekens, afhankelijk van de fontmetrieken van de machine die het blad toont. Een server die bestanden genereert heeft geen kolom op het scherm, dus HotXLS laat die laatste stap over aan wie de cel tekent. Verbreed de kolom voordat u vergelijkt. Anders meet u de breedte-fallback van Excel tegen de General-regel, en de twee verschillen op elke waarde langer dan de kolom

De tekst in formules houdt vijftien significante cijfers over

Zodra een getal binnen een formule tekst wordt — de operator &, CONCAT, TEXTJOIN, LEN, LEFT — neemt een andere regel het over. ECMA-376 §18.17 definieert de formulegrammatica maar geen getal-naar-tekstformaat voor deze stap, dus de uitvoer van Excel is de enige referentie. Excel rondt af op vijftien significante cijfers en schrijft ze in gewone positionele notatie zolang dat hooguit twintig tekens naast het teken kost. Vandaar dat =1/3&"" 0.333333333333333 teruggeeft, vijftien drieën. =1E+19&"" geeft 10000000000000000000, en =1E-18&"" geeft 0.000000000000000001. Voorbij twintig tekens schakelt de tekst naar wetenschappelijke notatie met dezelfde getekende, tweecijferige exponent die het raster gebruikt: 1E+20, 1.23456789012345E-05. Het scheidingsteken is altijd een punt. HotXLS evalueert formules zonder de landinstellingen van de machine waarop uw programma draait te raadplegen, dus een lookup-sleutel gebouwd met & komt er op een Engelse en een Duitse server identiek uit

De tekst in formules heeft één hoekje waarin Excel zelf van de machine afhangt. CHAR en CODE mappen getallen boven 127 via de ANSI-codepagina van de computer waarop Excel draait, dus =CHAR(128) is het euroteken op een West-Europese Windows-installatie en een ander teken onder andere codepagina's. Een Delphi-service kan niet weten welke codepagina de uiteindelijke lezer gebruikt. HotXLS evalueert die codes op elke machine als Windows-1252, de West-Europese codepagina, dus =CHAR(150) geeft op elke server een en dash. Zijn uw werkmappen bedoeld voor een andere codepagina, bereken die tekens dan in Delphi en sla ze op als tekst

FloatToStr volgt geen van beide regels

WaardeGeneral-weergave / TEXTFormuletekst (&"")Delphi FloatToStr
1234567890121.23457E+11123456789012123456789012
12345678901234561.23457E+1512345678901234601.23456789012346E15
1/30.3333333330.3333333333333330.333333333333333
1E201E+201E+201E20
0.0000010.0000010.0000011E-6
0.000001234567891.23457E-060.000001234567891.23456789E-6

De laatste kolom verklaart de drift. FloatToStr gebruikt het algemene formaat van Delphi met vijftien cijfers, schrijft exponenten zonder plusteken of nulopvulling en schakelt op eigen drempels naar exponentiële notatie: onder 0.00001, of zodra het geheel deel meer dan vijftien cijfers nodig heeft. Ook haalt hij zijn decimale scheidingsteken uit de FormatSettings van de host. Het is een prima functie voor logbestanden. Hij reproduceert geen van beide Excel-regels, en één helper die zowel weergave- als formuletekst moet leveren, zit fout op elke rij waar die regels uiteenlopen — vier van de zes rijen hierboven

Eén opgeslagen double gemapt op drie strings in HotXLS voor Delphi: Excel-rasterweergave, formuletekst met vijftien significante cijfers en het Delphi FloatToStr-resultaat
rasterweergave, formuletekst en FloatToStr passen elk hun eigen regel toe, dus kies op basis van waar de string vergeleken wordt

Welke tekst moet een export of een lookup-sleutel gebruiken?

Kies de regel door te vragen waar de string vergeleken wordt. Tekst voor mensen — een PDF-rapport, een HTML-tabel, een CSV die iemand naast de werkmap opent — moet op het raster lijken, dus lees de displaytekst van de cel. Tekst die als data dient moet de formulieregel volgen: een samengevoegde sleutel, een waarde vergeleken met iets dat een Excel-formule produceerde, een lookup-string. Een CSV die terug wordt geladen in Excel of een database telt ook als data. 1.23457E+11 in zo'n bestand schrijven gooit vijf cijfers van het rekeningnummer definitief weg, want de importeur parseert wat hij leest en kan het origineel niet herstellen. Het eerste voorbeeld drukt alle drie de strings naast elkaar af. De samples zijn Doubles, maar een Int64- of Currency-waarde toont op dezelfde manier:

uses
  SysUtils, Variants, lxHandleX;

procedure CompareNumberTexts;
const
  Samples: array[0..5] of Double =
    (123456789012.0, 1234567890123456.0, 1 / 3, 1E20, 0.000001,
     0.00000123456789);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Numbers');
    for I := 0 to High(Samples) do
    begin
      Sheet.Cells[1, 1].Value := Samples[I];       // standaard het General-formaat
      Writeln(Format('%-22s %-22s %s', [
        string(Sheet.Cells[1, 1].FormattedText),  // wat het raster toont
        VarToStr(Sheet.Calculate('A1&""')),         // wat een formule ziet
        FloatToStr(Samples[I])]));                // de eigen opvatting van de RTL
    end;
  finally
    Book.Free;
  end;
end;

Elke afgedrukte regel moet overeenkomen met de bijbehorende rij van de tabel. Alleen de scheidingstekens schuiven. Op een Duitse host schakelen de weergavekolom en de FloatToStr-kolom naar een decimale komma, zoals het raster van Excel op die machine doet, terwijl de formulekolom zijn punt houdt. Het tweede voorbeeld stopt de keuze in een exporter die rapporttekst schrijft zoals Excel die toont en sleutelkolommen zoals Excel-formules die bouwen:

uses
  SysUtils, Classes, Variants, lxHandleX, lxCalc;

// Rapporttekst zoals Excel die toont, plus een locale-onafhankelijke sleutelkolom
procedure ExportInvoiceLines(Sheet: TXLSXWorksheet; FirstRow, LastRow: Integer;
  Output: TStrings);
var
  Row: Integer;
  Shown, Key: string;
begin
  for Row := FirstRow to LastRow do
  begin
    Shown := Sheet.Cells[Row, 3].FormattedText;           // bedrag, zoals getoond
    Key := VarToStr(Sheet.EvaluateFormulaAt(Row, 6,
      Format('A%d&"-"&C%d', [Row, Row])).Value);       // factuurbedrag-sleutel
    Output.Add(Format('%s;%s', [Shown, Key]));
  end;
end;

EvaluateFormulaAt evalueert een formule alsof hij in de gegeven cel zit zonder hem op te slaan, dus de sleutel komt uit dezelfde regel die Excel binnen het werkmap op een sleutelkolom toepast. Draagt het bedrag een valuta- of duizendtalformaat, dan past FormattedText dat formaat toe, en de General-regel regeert alleen cellen zonder formaat. De afgeronde weergavewaarde terug in de berekening voeren is een andere schakelaar in Excel, precision as displayed, en die herschrijft de opgeslagen double zelf, dus houd die uit een export die alleen tekst nodig heeft

Wat gebeurt er met een ID van meer dan vijftien cijfers?

Hij verliest zijn staart zodra hij tekst wordt. Een double bevat elk geheel getal tot 253, zo'n 9,007 × 1015, dus het klantnummer van zestien cijfers, 1234567890123456, overleeft intact in de cel en in het bestand, waar zeventien significante cijfers volstaan voor een verliesvrije XLSX double round trip. De tekst is een ander verhaal. De General-regel toont 1.23457E+15, en de formuletekstregel rondt af op vijftien significante cijfers en vult aan met een nul, dus =A1&"" geeft 1234567890123460 — een andere klant. Excel houdt ook van een getal dat in een cel wordt getypt maar vijftien significante cijfers over, dus een gebruiker die de waarde intypt belandt op 1234567890123450. Eén klant eindigt met drie getallen: 1234567890123456 in de cel, 1234567890123460 in een formulesleutel en 1234567890123450 na opnieuw intypen

Anatomie van een ID van zestien cijfers in HotXLS voor Delphi: de opgeslagen celwaarde, de formuletekst van vijftien cijfers en de opnieuw getypte invoer geven drie verschillende getallen
identifiers zijn labels in cijfers geschreven, dus sla ze als tekst op en geen enkele regel kan het laatste cijfer weg afronden

Sla identifiers vanaf het begin als tekst op. Rekeningnummers, kaartnummers en order-ID's zijn labels in cijfers geschreven; niemand telt ze op, en een voorloopnul hoort bij het label. Als strings toegekend bereiken ze het raster en elke formule als dezelfde zestien tekens, en een lookup ertegen kan nooit wegschuiven. Excel markeert zulke cellen met een klein groen driehoekje voor getallen opgeslagen als tekst, een eerlijke prijs voor een sleutel die zijn laatste cijfer houdt:

// Identifiers gaan als tekst naar binnen; bedragen blijven numeriek
procedure WriteCustomerRow(Sheet: TXLSXWorksheet; Row: Integer;
  const CustomerNo: string; Balance: Currency);
begin
  Sheet.Cells[Row, 1].Value := WideString(CustomerNo); // '1234567890123456' blijft heel
  Sheet.Cells[Row, 2].Value := Balance;                // getoond volgens de General-regel
end;

Voordat u de exporter aanraakt: open er één werkmap van in Excel, verbreed de kolom met het rekeningnummer en zet =A1&"" ernaast. Vergeleekt uw downstream-systeem met de linker string, vraag dan de HotXLS Delphi Excel-component om FormattedText; vergelijkt het met de rechter, bouw de tekst dan met EvaluateFormulaAt