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.6789012345toont als12345.6789, en12345678901.5rondt af op12345678902 - Een geheel deel van twaalf cijfers past niet, dus
123456789012wordt1.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.00012345678toont als0.000123457, maar0.0000123456789wordt1.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
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
| Waarde | General-weergave / TEXT | Formuletekst (&"") | 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 |
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
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
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