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.6789012345zeigt als12345.6789, und12345678901.5rundet zu12345678902 - Ein zwölfstelliger Ganzzahlteil passt nicht, also wird
123456789012zu1.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.00012345678zeigt als0.000123457, aber0.0000123456789wird zu1.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
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
| Wert | General-Anzeige / TEXT | Formeltext (&"") | Delphi FloatToStr |
|---|---|---|---|
| 123456789012 | 1.23457E+11 | 123456789012 | 123456789012 |
| 1234567890123456 | 1.23457E+15 | 1234567890123460 | 1.23456789012346E15 |
| 1/3 | 0.333333333 | 0.333333333333333 | 0.333333333333333 |
| 1E20 | 1E+20 | 1E+20 | 1E20 |
| 0.000001 | 0.000001 | 0.000001 | 1E-6 |
| 0.00000123456789 | 1.23457E-06 | 0.00000123456789 | 1.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
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
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