Articolo tecnico

Perché Excel mostra 1.23457E+11 per un numero a 12 cifre

Un export Delphi memorizza il numero di conto 123456789012 come numero; una colonna Excel larga mostra 1.23457E+11, mentre =A1&"" restituisce tutte e dodici le cifre. Excel trasforma i numeri in testo con due regole, undici caratteri per la visualizzazione General e quindici cifre significative dentro le formule, così un export HotXLS legge la prima da FormattedText e costruisce la seconda con il motore di formule

Quanti caratteri ammette il formato General?

Undici, con il segno meno fuori dal conteggio. ECMA-376 Part 1 §18.8.30 dice che l'applicazione "deve tentare di mostrare il numero completo fino a 11 cifre (compreso il punto decimale)", e la sua regola sul floating point lo precisa meglio: "la lunghezza massima complessiva per la visualizzazione della cella è 11, senza contare il segno negativo". Lo standard poi abbozza quattro condizioni per passare alla forma esponenziale e si ferma lì. Il rounding resta aperto. Così come il punto di commutazione per i numeri piccoli. Il comportamento qui sotto è ciò che Excel mostra in una colonna larga, ed è ciò che HotXLS riproduce nel testo visualizzato di una cella e in TEXT(A1, "General"):

  • Un numero la cui parte intera sta in undici caratteri resta in notazione fissa e rinuncia ai decimali: 12345.6789012345 si mostra come 12345.6789, e 12345678901.5 si arrotonda a 12345678902
  • Una parte intera di dodici cifre non ci sta, così 123456789012 diventa 1.23457E+11, con la mantissa arrotondata per far posto all'esponente
  • Un numero sotto 1 resta in notazione fissa finché quella mostra almeno tante cifre significative quante ne mostra la forma scientifica a sei cifre: 0.00012345678 si visualizza come 0.000123457, ma 0.0000123456789 diventa 1.23457E-05
  • L'esponente porta sempre un segno e almeno due cifre, così 1020 si visualizza come 1E+20

Il segno sta fuori dal budget. -12345678901 si visualizza con tutte e undici le cifre dietro il suo segno meno, dodici caratteri in tutto, mentre -123456789012 diventa -1.23457E+11. Il tipo Variant che assegnate non cambia il risultato. Excel tiene ogni valore numerico di cella come double, e HotXLS manda i valori Integer, Int64, Currency e Double attraverso la stessa regola degli undici caratteri, così Cells[1, 1].Value := Int64(123456789012) si visualizza pure come 1.23457E+11. La regola entra anche nei formati personalizzati: il testo letterale di una sezione si aggiunge attorno al risultato General, così General" kg" mostra lo stesso numero come 1.23457E+11 kg. I formati con sezioni vuote o solo letterali seguono regole proprie e possono nascondere del tutto il numero

Flusso decisionale della regola degli undici caratteri del formato General di Excel in HotXLS per Delphi: notazione posizionale, passaggio alla notazione scientifica e segno meno fuori dal budget
Una colonna larga dà al formato General undici caratteri per le cifre e il punto decimale, mentre il segno meno aspetta fuori dal budget

Un limite in più sta fuori da qualunque libreria. Lo standard aggiunge che "il metodo ottimale di visualizzazione dipende dalla larghezza disponibile della cella", e in una colonna a larghezza predefinita Excel stringe ancora, fino a 1.2E+11 o a una fila di segni #, a seconda dei metrici del font della macchina che mostra il foglio. Un server che genera file non ha colonne sullo schermo, quindi HotXLS lascia quell'ultimo passo a chi disegna la cella. Allargate la colonna prima di confrontare. Altrimenti state misurando il fallback di larghezza di Excel contro la regola General, e le due saranno in disaccordo su qualunque valore più lungo della colonna

Il testo di formula tiene quindici cifre significative

Appena un numero diventa testo dentro una formula — l'operatore &, CONCAT, TEXTJOIN, LEN, LEFT — subentra una regola diversa. ECMA-376 §18.17 definisce la grammatica delle formule ma nessun formato numero-to-testo per questo passo, quindi l'output di Excel è l'unico riferimento. Excel arrotonda a quindici cifre significative e le scrive in notazione posizionale semplice finché ciò richiede al più venti caratteri oltre il segno. Per questo =1/3&"" restituisce 0.333333333333333, quindici tre. =1E+19&"" restituisce 10000000000000000000, e =1E-18&"" restituisce 0.000000000000000001. Oltre i venti caratteri il testo passa alla notazione scientifica con lo stesso esponente con segno a due cifre che usa la griglia: 1E+20, 1.23456789012345E-05. Il separatore è sempre un punto. HotXLS valuta le formule senza consultare le impostazioni regionali della macchina che esegue il vostro programma, così una chiave di lookup costruita con & esce identica su un server inglese e su uno tedesco

Il testo di formula ha un angolo in cui Excel stesso dipende dalla macchina. CHAR e CODE mappano i numeri sopra 127 attraverso la code page ANSI del computer che esegue Excel, così =CHAR(128) è il simbolo dell'euro su un'installazione Windows dell'Europa occidentale e un carattere diverso con altre code page. Un servizio Delphi non può sapere quale code page userà il lettore finale. HotXLS valuta quei codici come Windows-1252, la code page dell'Europa occidentale, su ogni macchina, così =CHAR(150) dà una en dash su qualunque server. Se i vostri workbook sono pensati per un'altra code page, calcolate quei caratteri in Delphi e memorizzateli come testo

FloatToStr non segue nessuna delle due regole

ValoreVisualizzazione General / TEXTTesto di formula (&"")FloatToStr Delphi
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

L'ultima colonna spiega la deriva. FloatToStr usa il formato generale di Delphi con quindici cifre, scrive gli esponenti senza segno più né zeri di riempimento, e passa alla notazione esponenziale alle sue soglie: sotto 0.00001, o appena la parte intera vuole più di quindici cifre. Prende inoltre il suo separatore decimale dalle FormatSettings dell'host. È una funzione ottima per i file di log. Non riproduce nessuna delle due regole di Excel, e un solo helper incaricato di servire sia la visualizzazione sia il testo di formula sarà sbagliato su ogni riga in cui le regole divergono — quattro delle sei righe viste sopra

Un double memorizzato mappato su tre stringhe in HotXLS per Delphi: visualizzazione nella griglia Excel, testo di formula con quindici cifre significative e risultato del FloatToStr Delphi
La visualizzazione nella griglia, il testo di formula e FloatToStr applicano ognuno la propria regola, così scegliete in base a dove la stringa verrà confrontata

Quale testo devono usare un export o una chiave di lookup?

Scegliete la regola chiedendovi dove la stringa verrà confrontata. Il testo per le persone — un report PDF, una tabella HTML, un CSV che qualcuno apre accanto al workbook — deve combaciare con la griglia, quindi leggete il testo visualizzato della cella. Il testo usato come dato deve seguire la regola delle formule: una chiave concatenata, un valore confrontato con qualcosa prodotto da una formula Excel, una stringa di lookup. Anche un CSV che verrà ricaricato in Excel o in un database conta come dato. Scrivere 1.23457E+11 in un file del genere butta via cinque cifre del numero di conto per sempre, perché l'importer parsa ciò che legge e non può recuperare l'originale. Il primo esempio stampa tutte e tre le stringhe fianco a fianco. I campioni sono Double, ma un valore Int64 o Currency si visualizza allo stesso modo:

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];       // formato General per default
      Writeln(Format('%-22s %-22s %s', [
        string(Sheet.Cells[1, 1].FormattedText),  // ciò che la griglia mostra
        VarToStr(Sheet.Calculate('A1&""')),         // ciò che vede una formula
        FloatToStr(Samples[I])]));                // l'idea della RTL
    end;
  finally
    Book.Free;
  end;
end;

Ogni riga stampata dovrebbe combaciare con la riga corrispondente della tabella. Si muovono solo i separatori. Su un host tedesco la colonna della visualizzazione e quella del FloatToStr passano alla virgola decimale, come fa la griglia di Excel su quella macchina, mentre la colonna delle formule tiene il suo punto. Il secondo esempio mette la scelta dentro un exporter che scrive il testo del report come lo mostra Excel e le colonne chiave come le costruiscono le formule Excel:

uses
  SysUtils, Classes, Variants, lxHandleX, lxCalc;

// Testo del report come lo mostra Excel, più una colonna chiave indipendente dal locale
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;           // importo, così come mostrato
    Key := VarToStr(Sheet.EvaluateFormulaAt(Row, 6,
      Format('A%d&"-"&C%d', [Row, Row])).Value);       // chiave dell'importo fattura
    Output.Add(Format('%s;%s', [Shown, Key]));
  end;
end;

EvaluateFormulaAt valuta una formula come se sedesse nella cella data senza memorizzarla, così la chiave esce dalla stessa regola che Excel applica a una colonna chiave dentro il workbook. Se l'importo porta un formato valuta o migliaia, FormattedText applica quel formato, e la regola General governa solo le celle senza uno. Rifornire di calcolo il valore visualizzato arrotondato è un interruttore diverso in Excel, la precision as displayed, e riscrive lui stesso il double memorizzato, quindi tenetelo fuori da un export che vuole solo testo

Cosa succede a un ID più lungo di quindici cifre?

Perde la coda nell'istante in cui diventa testo. Un double tiene ogni intero fino a 253, circa 9.007 × 1015, così il numero cliente di sedici cifre 1234567890123456 sopravvive intatto nella cella e nel file, dove diciassette cifre significative bastano per un round trip XLSX del double senza perdite. Il testo è un'altra storia. La regola General mostra 1.23457E+15, e la regola delle formule arrotonda a quindici cifre significative e riempie con uno zero, così =A1&"" restituisce 1234567890123460 — un cliente diverso. Excel inoltre tiene solo quindici cifre significative di un numero digitato in una cella, così un utente che ridigita il valore atterra su 1234567890123450. Un cliente finisce con tre numeri: 1234567890123456 nella cella, 1234567890123460 in una chiave di formula e 1234567890123450 dopo il redigitare

Anatomia di un ID di sedici cifre in HotXLS per Delphi: il valore di cella memorizzato, il testo di formula a quindici cifre e la voce redigitata danno tre numeri diversi
Gli identificatori sono etichette scritte in cifre, quindi memorizzateli come testo e nessuna regola potrà arrotondare via l'ultima cifra

Memorizzate gli identificatori come testo fin dall'inizio. Numeri di conto, numeri di carta e ID d'ordine sono etichette scritte in cifre; nessuno li somma, e uno zero iniziale appartiene all'etichetta. Assegnati come stringhe arrivano alla griglia e a ogni formula come gli stessi sedici caratteri, e un lookup contro di loro non può mai deragliare. Excel segnala quelle celle con un piccolo triangolo verde per i numeri memorizzati come testo, un prezzo giusto per una chiave che si tiene l'ultima cifra:

// Gli identificatori entrano come testo; gli importi restano numerici
procedure WriteCustomerRow(Sheet: TXLSXWorksheet; Row: Integer;
  const CustomerNo: string; Balance: Currency);
begin
  Sheet.Cells[Row, 1].Value := WideString(CustomerNo); // '1234567890123456' resta intero
  Sheet.Cells[Row, 2].Value := Balance;                // mostrato dalla regola General
end;

Prima di toccare l'exporter, aprite in Excel un workbook che ha prodotto, allargate la colonna del numero di conto e mettate =A1&"" accanto. Se il vostro sistema a valle confronta con la stringa di sinistra, chiedete al componente Excel HotXLS per Delphi FormattedText; se confronta con quella di destra, costruite il testo con EvaluateFormulaAt