Technischer Artikel

Warum Excel für eine 12-stellige Zahl 1.23457E+11 zeigt

Ein Delphi-Export speichert die Kontonummer 123456789012 als Zahl; eine breite Excel-Spalte zeigt 1.23457E+11, während =A1&"" alle zwölf Ziffern zurückgibt. Excel verwandelt Zahlen nach zwei Regeln in Text, elf Zeichen für die General-Anzeige und fünfzehn signifikante Stellen in Formeln, also liest ein HotXLS-Export die erste aus FormattedText und baut die zweite mit der Formel-Engine

Wie viele Zeichen erlaubt das General-Format?

Elf, und das Minuszeichen zählt nicht mit. ECMA-376 Part 1 §18.8.30 sagt, die Anwendung "shall attempt to display the full number up to 11 digits (inc. decimal point)", und ihre Floating-Point-Regel präzisiert: "max overall length for cell display is 11, not including negative sign". Der Standard skizziert dann vier Bedingungen für den Wechsel in Exponentialform und stoppt dort. Das Runden bleibt offen. Ebenso der Umschaltpunkt für kleine Zahlen. Das Verhalten unten ist das, was Excel in einer breiten Spalte zeigt, und es ist das, was HotXLS im Anzeigetext einer Zelle und in TEXT(A1, "General") reproduziert:

  • Eine Zahl, deren Ganzzahlteil in elf Zeichen passt, bleibt in fester Notation und gibt Nachkommastellen auf: 12345.6789012345 zeigt als 12345.6789, und 12345678901.5 rundet zu 12345678902
  • Ein zwölfstelliger Ganzzahlteil passt nicht, also wird 123456789012 zu 1.23457E+11, die Mantisse gerundet, um Platz für den Exponenten zu lassen
  • Eine Zahl unter 1 bleibt in fester Notation, solange diese mindestens so viele signifikante Stellen zeigt wie die sechsstellige wissenschaftliche Form: 0.00012345678 zeigt als 0.000123457, aber 0.0000123456789 wird zu 1.23457E-05
  • Der Exponent trägt immer ein Vorzeichen und mindestens zwei Ziffern, also zeigt 1020 als 1E+20

Das Vorzeichen sitzt außerhalb des Budgets. -12345678901 zeigt mit allen elf Ziffern hinter seinem Minuszeichen, zwölf Zeichen insgesamt, während -123456789012 zu -1.23457E+11 wird. Der Variant-Typ, den Sie zuweisen, ändert das Ergebnis nicht. Excel hält jeden numerischen Zellwert als Double, und HotXLS schickt Integer-, Int64-, Currency- und Double-Werte durch dieselbe Elf-Zeichen-Regel, also zeigt auch Cells[1, 1].Value := Int64(123456789012) als 1.23457E+11. Die Regel trägt in benutzerdefinierte Formate hinein: Literaltext in einem Abschnitt wird um das General-Ergebnis herum ergänzt, also zeigt General" kg" dieselbe Zahl als 1.23457E+11 kg. Formate mit leeren oder rein literalen Abschnitten folgen ihren eigenen Regeln und können die Zahl ganz verstecken

Entscheidungsfluss der Elf-Zeichen-Anzeigeregel von Excels General-Format in HotXLS für Delphi: Positionsnotation, wissenschaftlicher Umschalt und das Minuszeichen außerhalb des Budgets
Eine breite Spalte gibt dem General-Format elf Zeichen für Ziffern und Dezimalpunkt, während das Minuszeichen außerhalb des Budgets wartet

Eine weitere Grenze sitzt außerhalb jeder Bibliothek. Der Standard ergänzt, dass "the optimal method of display is based on the available cell width", und in einer Spalte mit Standardbreite quetscht Excel weiter, bis hinunter zu 1.2E+11 oder einer Reihe von #-Zeichen, je nach den Font-Metriken der Maschine, die das Sheet zeigt. Ein Server, der Dateien erzeugt, hat keine Spalte auf dem Bildschirm, also überlässt HotXLS diesen letzten Schritt demjenigen, der die Zelle zeichnet. Breiten Sie die Spalte aus, bevor Sie vergleichen. Sonst messen Sie Excels Breiten-Fallback gegen die General-Regel, und die beiden werden bei jedem Wert länger als die Spalte auseinandergehen

Formeltext behält fünfzehn signifikante Stellen

Sobald eine Zahl in einer Formel zu Text wird — der &-Operator, CONCAT, TEXTJOIN, LEN, LEFT — übernimmt eine andere Regel. ECMA-376 §18.17 definiert die Formel-Grammatik, aber kein Zahl-zu-Text-Format für diesen Schritt, also ist Excels Ausgabe die einzige Referenz. Excel rundet auf fünfzehn signifikante Stellen und schreibt sie in schlichter Positionsnotation, solange das höchstens zwanzig Zeichen neben dem Vorzeichen braucht. Deshalb gibt =1/3&"" 0.333333333333333 zurück, fünfzehn Dreien. =1E+19&"" gibt 10000000000000000000 zurück, und =1E-18&"" gibt 0.000000000000000001. Jenseits von zwanzig Zeichen schaltet der Text auf wissenschaftliche Notation mit demselben vorzeichenbehafteten zweistelligen Exponenten um, den das Grid benutzt: 1E+20, 1.23456789012345E-05. Der Separator ist immer ein Punkt. HotXLS wertet Formeln aus, ohne die Regionaleinstellungen der Maschine zu konsultieren, auf der Ihr Programm läuft, also fällt ein mit & gebauter Lookup-Key auf einem englischen und einem deutschen Server identisch aus

Formeltext hat eine Ecke, in der Excel selbst an der Maschine hängt. CHAR und CODE mappen Zahlen über 127 durch die ANSI-Codepage des Rechners, auf dem Excel läuft, also ist =CHAR(128) das Eurozeichen auf einer westeuropäischen Windows-Installation und ein anderes Zeichen unter anderen Codepages. Ein Delphi-Dienst kann nicht wissen, welche Codepage der spätere Leser benutzt. HotXLS wertet diese Codes auf jeder Maschine als Windows-1252, die westeuropäische Codepage, aus, also gibt =CHAR(150) auf jedem Server einen Halbgeviertstrich. Sind Ihre Workbooks für eine andere Codepage gedacht, berechnen Sie diese Zeichen in Delphi und speichern sie als Text

FloatToStr folgt keiner der beiden Regeln

WertGeneral-Anzeige / TEXTFormeltext (&"")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

Die letzte Spalte erklärt die Drift. FloatToStr benutzt Delphis General-Format mit fünfzehn Stellen, schreibt Exponenten ohne Pluszeichen oder Null-Auffüllung und schaltet bei eigenen Schwellen auf Exponentialnotation um: unter 0.00001 oder sobald der Ganzzahlteil mehr als fünfzehn Stellen braucht. Außerdem nimmt es seinen Dezimalseparator aus den FormatSettings des Hosts. Es ist eine feine Funktion für Logdateien. Es reproduziert keine der beiden Excel-Regeln, und ein Helper, der beide, Anzeige- und Formeltext, bedienen soll, liegt in jeder Zeile falsch, in der diese Regeln auseinanderlaufen — vier der sechs Zeilen oben

Ein gespeicherter Double auf drei Strings gemappt in HotXLS für Delphi: Excel-Grid-Anzeige, Formeltext mit fünfzehn signifikanten Stellen und das Delphi-FloatToStr-Ergebnis
Grid-Anzeige, Formeltext und FloatToStr wenden jede ihre eigene Regel an, also wählen Sie danach, wo der String verglichen wird

Welchen Text sollen ein Export oder ein Lookup-Key benutzen?

Wählen Sie die Regel, indem Sie fragen, wo der String verglichen wird. Text für Menschen — ein PDF-Report, eine HTML-Tabelle, eine CSV, die jemand neben dem Workbook öffnet — sollte zum Grid passen, also lesen Sie den Anzeigetext der Zelle. Text, der als Daten dient, sollte der Formelregel folgen: ein konkatenierter Key, ein Wert, der mit dem Ergebnis einer Excel-Formel verglichen wird, ein Lookup-String. Eine CSV, die zurück nach Excel oder in eine Datenbank geladen wird, zählt ebenfalls als Daten. 1.23457E+11 in so eine Datei zu schreiben, wirft fünf Ziffern der Kontonummer für immer weg, denn der Importeur parst, was er liest, und kann das Original nicht wiederherstellen. Das erste Beispiel druckt alle drei Strings nebeneinander. Die Samples sind Doubles, aber ein Int64- oder Currency-Wert zeigt sich genauso:

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];       // standardmäßig das General-Format
      Writeln(Format('%-22s %-22s %s', [
        string(Sheet.Cells[1, 1].FormattedText),  // was das Grid zeigt
        VarToStr(Sheet.Calculate('A1&""')),         // was eine Formel sieht
        FloatToStr(Samples[I])]));                // die eigene Meinung der RTL
    end;
  finally
    Book.Free;
  end;
end;

Jede gedruckte Zeile sollte zur entsprechenden Tabellenzeile passen. Nur die Separatoren wandern. Auf einem deutschen Host schalten die Anzeigespalte und die FloatToStr-Spalte auf Dezimalkomma um, wie Excels Grid auf dieser Maschine, während die Formelspalte ihren Punkt behält. Das zweite Beispiel verlagert die Wahl in einen Exporter, der Reporttext so schreibt, wie Excel ihn anzeigt, und Key-Spalten so, wie Excel-Formeln sie bauen:

uses
  SysUtils, Classes, Variants, lxHandleX, lxCalc;

// Reporttext wie in Excel gezeigt, plus eine locale-unabhängige Key-Spalte
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;           // Betrag, wie angezeigt
    Key := VarToStr(Sheet.EvaluateFormulaAt(Row, 6,
      Format('A%d&"-"&C%d', [Row, Row])).Value);       // Rechnungsbetrag-Key
    Output.Add(Format('%s;%s', [Shown, Key]));
  end;
end;

EvaluateFormulaAt wertet eine Formel aus, als säße sie in der angegebenen Zelle, ohne sie zu speichern, also kommt der Key aus derselben Regel, die Excel auf eine Key-Spalte im Workbook anwendet. Führt der Betrag ein Currency- oder Tausenderformat, wendet FormattedText dieses Format an, und die General-Regel regiert nur Zellen ohne eines. Den gerundeten Anzeigewert zurück in die Berechnung zu füttern, ist ein anderer Schalter in Excel, precision as displayed, und er schreibt den gespeicherten Double selbst um, also halten Sie ihn aus einem Export heraus, der nur Text braucht

Was passiert mit einer ID länger als fünfzehn Ziffern?

Er verliert seinen Schwanz, sobald er zu Text wird. Ein Double hält jede Ganzzahl bis 253, etwa 9.007 × 1015, also überlebt die sechzehnstellige Kundennummer 1234567890123456 intakt in der Zelle und in der Datei, wo siebzehn signifikante Stellen für einen verlustfreien XLSX-Double-Roundtrip reichen. Der Text ist eine andere Geschichte. Die General-Regel zeigt 1.23457E+15, und die Formelregel rundet auf fünfzehn signifikante Stellen und füllt mit einer Null auf, also gibt =A1&"" 1234567890123460 zurück — ein anderer Kunde. Excel behält auch nur fünfzehn signifikante Stellen einer in eine Zelle getippten Zahl, also landet ein Nutzer, der den Wert neu eintippt, bei 1234567890123450. Ein Kunde endet mit drei Zahlen: 1234567890123456 in der Zelle, 1234567890123460 in einem Formel-Key und 1234567890123450 nach dem Neueintippen

Anatomie einer sechzehnstelligen ID in HotXLS für Delphi: Der gespeicherte Zellwert, der fünfzehnstellige Formeltext und der neu eingetippte Eintrag ergeben drei verschiedene Zahlen
Identifikatoren sind in Ziffern geschriebene Labels, also speichern Sie sie als Text, und keine Regel kann die letzte Ziffer wegrunden

Speichern Sie Identifikatoren von Anfang an als Text. Kontonummern, Kartennummern und Order-IDs sind in Ziffern geschriebene Labels; niemand summiert sie, und eine führende Null gehört zum Label. Als Strings zugewiesen, erreichen sie das Grid und jede Formel als dieselben sechzehn Zeichen, und ein Lookup dagegen kann nie driften. Excel markiert solche Zellen mit einem kleinen grünen Dreieck für als Text gespeicherte Zahlen, ein fairer Preis für einen Key, der seine letzte Ziffer behält:

// Identifikatoren kommen als Text hinein; Beträge bleiben numerisch
procedure WriteCustomerRow(Sheet: TXLSXWorksheet; Row: Integer;
  const CustomerNo: string; Balance: Currency);
begin
  Sheet.Cells[Row, 1].Value := WideString(CustomerNo); // '1234567890123456' bleibt ganz
  Sheet.Cells[Row, 2].Value := Balance;                // nach der General-Regel angezeigt
end;

Bevor Sie den Exporter anfassen, öffnen Sie ein von ihm erzeugtes Workbook in Excel, verbreitern Sie die Kontonummer-Spalte und setzen Sie =A1&"" daneben. Vergleicht Ihr Downstream-System gegen den linken String, fragen Sie die HotXLS Delphi Excel Component nach FormattedText; vergleicht es gegen den rechten, bauen Sie den Text mit EvaluateFormulaAt