Un export Delphi stocke le numéro de compte 123456789012 en nombre ; une colonne Excel large affiche 1.23457E+11, tandis que =A1&"" renvoie les douze chiffres entiers. Excel transforme les nombres en texte par deux règles, onze caractères pour l'affichage General et quinze chiffres significatifs dans les formules, si bien qu'un export HotXLS lit la première depuis FormattedText et construit la seconde avec le moteur de formules
Combien de caractères le format General autorise-t-il ?
Onze, sans compter le signe moins. ECMA-376 Partie 1 §18.8.30 dit que l'application "doit tenter d'afficher le nombre entier jusqu'à 11 chiffres (virgule décimale comprise)", et sa règle sur la virgule flottante le formule plus précisément : la "longueur maximale globale pour l'affichage d'une cellule est 11, signe négatif non compris". La norme esquisse ensuite quatre conditions de passage à la forme exponentielle et s'arrête là. L'arrondi reste ouvert. Le point de bascule des petits nombres aussi. Le comportement ci-dessous est ce qu'Excel affiche dans une colonne large, et c'est ce que HotXLS reproduit dans le texte d'affichage d'une cellule et dans TEXT(A1, "General") :
- Un nombre dont la partie entière tient en onze caractères reste en notation fixe et abandonne des décimales :
12345.6789012345s'affiche12345.6789, et12345678901.5s'arrondit en12345678902 - Une partie entière de douze chiffres ne tient pas, si bien que
123456789012devient1.23457E+11, la mantisse arrondie pour laisser la place à l'exposant - Un nombre sous 1 garde la notation fixe tant que celle-ci montre au moins autant de chiffres significatifs que la forme scientifique à six caractères :
0.00012345678s'affiche0.000123457, mais0.0000123456789devient1.23457E-05 - L'exposant porte toujours un signe et au moins deux chiffres, si bien que 1020 s'affiche
1E+20
Le signe siège hors du budget. -12345678901 s'affiche avec les onze chiffres entiers derrière son signe moins, douze caractères en tout, tandis que -123456789012 devient -1.23457E+11. Le type Variant que vous affectez ne change pas le résultat. Excel détient chaque valeur de cellule numérique en double, et HotXLS fait passer les valeurs Integer, Int64, Currency et Double par la même règle des onze caractères, si bien que Cells[1, 1].Value := Int64(123456789012) s'affiche aussi 1.23457E+11. La règle se prolonge dans les formats personnalisés : le texte littéral d'une section s'ajoute autour du résultat General, si bien que General" kg" montre le même nombre que 1.23457E+11 kg. Les formats à sections vides ou uniquement littérales suivent leurs propres règles et peuvent carrément cacher le nombre
Une limite de plus siège hors de toute bibliothèque. La norme ajoute que "la méthode d'affichage optimale repose sur la largeur de cellule disponible", et dans une colonne de largeur par défaut Excel resserre davantage, jusqu'à 1.2E+11 ou une rangée de #, selon les métriques de police de la machine qui montre la feuille. Un serveur qui génère des fichiers n'a aucune colonne à l'écran, si bien que HotXLS laisse ce dernier pas à celui qui dessine la cellule. Élargissez la colonne avant de comparer. Sinon vous mesurez le repli de largeur d'Excel contre la règle General, et les deux divergeront sur toute valeur plus longue que la colonne
Le texte de formule garde quinze chiffres significatifs
Dès qu'un nombre devient texte dans une formule — l'opérateur &, CONCAT, TEXTJOIN, LEN, LEFT — une autre règle prend le relais. ECMA-376 §18.17 définit la grammaire des formules mais aucun format nombre-vers-texte pour cette étape, si bien que la sortie d'Excel est la seule référence. Excel arrondit à quinze chiffres significatifs et les écrit en notation positionnelle simple tant que cela tient en vingt caractères au plus, signe non compris. D'où =1/3&"" qui renvoie 0.333333333333333, quinze fois le chiffre 3. =1E+19&"" renvoie 10000000000000000000, et =1E-18&"" renvoie 0.000000000000000001. Passé vingt caractères, le texte bascule en notation scientifique avec le même exposant signé à deux chiffres qu'emploie la grille : 1E+20, 1.23456789012345E-05. Le séparateur est toujours un point. HotXLS évalue les formules sans consulter les paramètres régionaux de la machine qui exécute votre programme, si bien qu'une clé de recherche construite avec & sort identique sur un serveur anglais comme sur un serveur allemand
Le texte de formule a un coin où Excel lui-même dépend de la machine. CHAR et CODE font passer les nombres au-dessus de 127 par la page de codes ANSI de l'ordinateur qui exécute Excel, si bien que =CHAR(128) est le signe euro sur une installation Windows d'Europe de l'Ouest et un autre caractère sous d'autres pages de codes. Un service Delphi ne peut pas savoir quelle page de codes emploiera le lecteur final. HotXLS évalue ces codes comme du Windows-1252, la page de codes d'Europe de l'Ouest, sur chaque machine, si bien que =CHAR(150) donne un tiret demi-cadratin sur n'importe quel serveur. Si vos classeurs visent une autre page de codes, calculez ces caractères en Delphi et stockez-les en texte
FloatToStr ne suit aucune des deux règles
| Valeur | Affichage General / TEXT | Texte de formule (&"") | FloatToStr Delphi |
|---|---|---|---|
| 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 |
La dernière colonne explique la dérive. FloatToStr emploie le format général de Delphi à quinze chiffres, écrit les exposants sans signe plus ni zéro de calage, et bascule en notation exponentielle selon ses propres seuils : sous 0.00001, ou dès que la partie entière réclame plus de quinze chiffres. Il prend aussi son séparateur décimal dans les FormatSettings de l'hôte. C'est une très bonne fonction pour des fichiers de log. Il ne reproduit aucune des deux règles d'Excel, et un utilitaire unique chargé de servir à la fois l'affichage et le texte de formule se trompera sur chaque ligne où ces règles divergent — quatre des six lignes ci-dessus
Quel texte un export ou une clé de recherche doit-il employer ?
Choisissez la règle en vous demandant où la chaîne sera comparée. Le texte pour les humains — un rapport PDF, une table HTML, un CSV que quelqu'un ouvre à côté du classeur — doit coller à la grille, donc lisez le texte d'affichage de la cellule. Le texte employé comme donnée doit suivre la règle des formules : une clé concaténée, une valeur comparée à ce qu'une formule Excel a produite, une chaîne de recherche. Un CSV destiné à être rechargé dans Excel ou dans une base compte comme donnée, lui aussi. Écrire 1.23457E+11 dans un tel fichier jette cinq chiffres du numéro de compte définitivement, car l'importeur analyse ce qu'il lit et ne peut pas retrouver l'original. Le premier exemple imprime les trois chaînes côte à côte. Les échantillons sont des Doubles, mais une valeur Int64 ou Currency s'affiche de la même façon :
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]; // format General par défaut
Writeln(Format('%-22s %-22s %s', [
string(Sheet.Cells[1, 1].FormattedText), // ce que montre la grille
VarToStr(Sheet.Calculate('A1&""')), // ce que voit une formule
FloatToStr(Samples[I])])); // l'idée propre de la RTL
end;
finally
Book.Free;
end;
end;
Chaque ligne imprimée doit correspondre à la ligne correspondante de la table. Seuls les séparateurs bougent. Sur un hôte allemand, la colonne d'affichage et la colonne FloatToStr passent à la virgule décimale, comme la grille d'Excel sur cette machine, tandis que la colonne des formules garde son point. Le second exemple met le choix dans un exportateur qui écrit le texte de rapport comme Excel l'affiche et les colonnes de clés comme les formules Excel les construisent :
uses
SysUtils, Classes, Variants, lxHandleX, lxCalc;
// Texte de rapport tel qu'affiché dans Excel, plus une colonne de clé indépendante des paramètres régionaux
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; // montant, tel qu'affiché
Key := VarToStr(Sheet.EvaluateFormulaAt(Row, 6,
Format('A%d&"-"&C%d', [Row, Row])).Value); // clé montant de facture
Output.Add(Format('%s;%s', [Shown, Key]));
end;
end;
EvaluateFormulaAt évalue une formule comme si elle siégeait dans la cellule donnée sans la stocker, si bien que la clé sort de la même règle qu'Excel applique à une colonne de clés dans le classeur. Si le montant porte un format monétaire ou de milliers, FormattedText applique ce format, et la règle General ne gouverne que les cellules sans format. Réinjecter la valeur d'affichage arrondie dans le calcul est un autre interrupteur d'Excel, la précision telle qu'affichée, et il réécrit le double stocké lui-même, donc tenez-le hors d'un export qui ne réclame que du texte
Que devient un identifiant de plus de quinze chiffres ?
Il perd sa queue au moment où il devient texte. Un double porte tout entier jusqu'à 253, environ 9.007 × 1015, si bien que le numéro client à seize chiffres 1234567890123456 survit intact dans la cellule et dans le fichier, où dix-sept chiffres significatifs suffisent pour un aller-retour XLSX du double sans perte. Le texte est une autre histoire. La règle General montre 1.23457E+15, et la règle des formules arrondit à quinze chiffres significatifs et complète par un zéro, si bien que =A1&"" renvoie 1234567890123460 — un autre client. Excel ne garde aussi que quinze chiffres significatifs d'un nombre tapé dans une cellule, si bien qu'un utilisateur qui retape la valeur atterrit sur 1234567890123450. Un client se retrouve avec trois nombres : 1234567890123456 dans la cellule, 1234567890123460 dans une clé de formule et 1234567890123450 après la retape
Stockez les identifiants en texte dès le départ. Numéros de compte, numéros de carte et identifiants de commande sont des étiquettes écrites en chiffres ; personne ne les somme, et un zéro de tête appartient à l'étiquette. Affectés en chaînes, ils atteignent la grille et chaque formule comme les mêmes seize caractères, et une recherche dessus ne peut jamais dériver. Excel marque ces cellules d'un petit triangle vert pour les nombres stockés en texte, un juste prix pour une clé qui garde son dernier chiffre :
// Les identifiants entrent en texte ; les montants restent numériques
procedure WriteCustomerRow(Sheet: TXLSXWorksheet; Row: Integer;
const CustomerNo: string; Balance: Currency);
begin
Sheet.Cells[Row, 1].Value := WideString(CustomerNo); // '1234567890123456' reste entier
Sheet.Cells[Row, 2].Value := Balance; // montré par la règle General
end;
Avant de toucher à l'exportateur, ouvrez dans Excel un classeur qu'il a produit, élargissez la colonne du numéro de compte et posez =A1&"" à côté. Si votre système aval compare à la chaîne de gauche, demandez le FormattedText au composant Excel HotXLS pour Delphi ; s'il compare à celle de droite, construisez le texte avec EvaluateFormulaAt