技術記事

Excel が 12 桁の数に 1.23457E+11 を表示する理由

Delphi のエクスポートは口座番号 123456789012 を数値として格納します。広い Excel の列は 1.23457E+11 を表示する一方、=A1&"" は 12 桁すべてを返します。Excel が数値をテキストに変える規則は 2 つ、General 表示では 11 文字、数式の中では 15 有効数字です。だから HotXLS のエクスポートは前者を FormattedText から読み、後者を数式エンジンで組み立てます

General 書式が許す文字数はいくつか

11 文字で、マイナス符号は数えません。ECMA-376 Part 1 の §18.8.30 は、アプリケーションは「完全な数を 11 桁まで(小数点を含めて)表示しようと試みなければならない」と述べ、浮動小数点の規則はより正確に「セル表示の最大全体長は 11、負符号を含まない」と言います。標準はそこで、指数形式へ切り替える 4 つの条件を概略示して終わりです。丸めは未定義のまま。小さい数の切り替え点もそうです。以下の挙動は広い列で Excel が見せるもので、HotXLS がセルの表示テキストと TEXT(A1, "General") で再現するものでもあります:

  • 整数部が 11 文字に収まる数は固定小数点表記に留まり、小数を諦めます。12345.6789012345 は 12345.6789 と表示され、12345678901.5 は 12345678902 に丸まります
  • 12 桁の整数部は収まらないので、123456789012 は 1.23457E+11 になり、仮数は指数の分の席を空けるよう丸められます
  • 1 未満の数は、6 桁の科学的形式と同じだけの有効数字が見える限り固定小数点表記を保ちます。0.00012345678 は 0.000123457 と表示されますが、0.0000123456789 は 1.23457E-05 になります
  • 指数は常に符号と 2 桁以上を運ぶので、1020 は 1E+20 と表示されます

符号は予算の外にいます。-12345678901 はマイナス符号の後ろに 11 桁すべてを並べ、合計 12 文字で表示されます。一方 -123456789012 は -1.23457E+11 になります。代入する Variant の型は結果を変えません。Excel はすべての数値セルの値を double で保持し、HotXLS は Integer、Int64、Currency、Double の値を同じ 11 文字規則に通すので、Cells[1, 1].Value := Int64(123456789012) も 1.23457E+11 と表示されます。この規則はカスタム書式にも続きます。セクション内のリテラルテキストは General の結果の周りに足されるので、General" kg" は同じ数を 1.23457E+11 kg と表示します。空またはリテラルのみのセクションを持つ書式は独自の規則に従い、数をまるごと隠すこともできます

Delphi 向け HotXLS における Excel の General 書式 11 文字表示規則の判断フロー。固定小数点表記、指数への切り替え、予算の外にいるマイナス符号
広い列は General 書式に数字と小数点の分の 11 文字を与え、マイナス符号は予算の外で待ちます

もう 1 つの限界はライブラリの外にあります。標準は「表示の最適な方法は利用可能なセル幅に基づく」とも加えていて、既定幅の列では Excel はさらに絞り、シートを表示するマシンのフォントメトリクス次第で 1.2E+11 まで縮めるか # の列にします。ファイルを生成するサーバーに画面上の列はないので、HotXLS はこの最後の一歩をセルを描く側に任せます。比較する前に列を広げましょう。そうしないと、Excel の幅フォールバックを General 規則と比較していることになり、列より長い値では必ず食い違います

数式テキストは 15 有効数字を保つ

数が数式の中でテキストになるとき——& 演算子、CONCAT、TEXTJOIN、LEN、LEFT——は別の規則が引き継ぎます。ECMA-376 の §18.17 は数式の文法を定義しますが、この段の数値からテキストへの書式は定義しないので、Excel の出力だけが参照実装です。Excel は 15 有効数字へ丸め、符号を除いて 20 文字以内に収まる限り、平坦な位取り表記で書きます。だから =1/3&"" は 0.333333333333333 を返します。3 が 15 個です。=1E+19&"" は 10000000000000000000 を返し、=1E-18&"" は 0.000000000000000001 を返します。20 文字を過ぎると、テキストはグリッドと同じ符号付き 2 桁指数の科学的表記へ切り替わります。1E+20、1.23456789012345E-05。区切り文字は常にピリオドです。HotXLS はプログラムを走らせるマシンの地域設定に触れずに数式を評価するので、& で作った検索キーは英語のサーバーでもドイツ語のサーバーでも同一になります

数式テキストには、Excel 自身がマシンに依存する一隅があります。CHAR と CODE は 127 を超える数を、Excel を走らせているコンピュータの ANSI コードページで写像するので、=CHAR(128) は西欧 Windows 環境ではユーロ記号に、他のコードページでは別の文字になります。Delphi サービスには、最終的な読み手がどのコードページを使うかは分かりません。HotXLS はこれらのコードを、どのマシンでも西欧のコードページ Windows-1252 として評価するので、=CHAR(150) はどのサーバーでも en ダッシュを返します。ワークブックが別のコードページ向けなら、その文字は Delphi 側で計算してテキストとして格納しましょう

FloatToStr はどちらの規則にも従わない

値General 表示 / TEXT数式テキスト(&"")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

最後の列がズレを説明します。FloatToStr は Delphi の一般形式を 15 桁で使うほか、指数をプラス符号なし、ゼロ埋めなしで書き、自分のしきい値で指数表記へ切り替えます。0.00001 未満か、整数部が 15 桁を超えたときです。さらに小数点記号はホストの FormatSettings から取ります。ログファイルには優れた関数です。しかし Excel のどちらの規則も再現しないので、表示と数式テキストの両方に仕えた 1 つのヘルパーは、両規則が食い違うすべての行で間違えます。上の 6 行のうち 4 行です

Delphi 向け HotXLS で 1 つの格納 double が 3 つの文字列に写る様子。Excel のグリッド表示、15 有効数字の数式テキスト、Delphi の FloatToStr の結果
グリッド表示、数式テキスト、FloatToStr はそれぞれ別の規則を適用するので、文字列がどこで比較されるかで選びます

エクスポートや検索キーはどのテキストを使うべきか

文字列がどこで比較されるかを問うて規則を選びます。人間向けのテキスト——PDF レポート、HTML テーブル、ワークブックの隣で開かれる CSV——はグリッドに合わせるべきなので、セルの表示テキストを読みます。データとして使うテキストは数式の規則に従います。連結したキー、Excel の数式が作った何かと比較される値、検索文字列です。Excel やデータベースに読み戻される CSV もデータです。そんなファイルに 1.23457E+11 と書き込むと、口座番号の 5 桁を取り返し不能のまま捨てます。インポータは読んだものをパースし、元の値を復元できないからです。最初の例は 3 つの文字列を並べて印字します。サンプルは Double ですが、Int64 や Currency の値も同じように表示されます:

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];       // 既定では General 書式
      Writeln(Format('%-22s %-22s %s', [
        string(Sheet.Cells[1, 1].FormattedText),  // グリッドに表示されるもの
        VarToStr(Sheet.Calculate('A1&""')),         // 数式が見るもの
        FloatToStr(Samples[I])]));                // RTL 独自の解釈
    end;
  finally
    Book.Free;
  end;
end;

印字される各行は表の対応する行と一致するはずです。動くのは区切り文字だけです。ドイツ語のホストでは表示列と FloatToStr 列が小数コンマに切り替わります。そのマシン上の Excel のグリッドと同じです。数式列はピリオドを保ちます。2 つ目の例は、この選択を、レポートテキストを Excel の表示どおりに、キー列を Excel の数式の組み立てどおりに書くエクスポータへ組み込みます:

uses
  SysUtils, Classes, Variants, lxHandleX, lxCalc;

// Excel の表示どおりのレポートテキストと、ロケール非依存のキー列
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;           // 金額、表示どおり
    Key := VarToStr(Sheet.EvaluateFormulaAt(Row, 6,
      Format('A%d&"-"&C%d', [Row, Row])).Value);       // 請求金額のキー
    Output.Add(Format('%s;%s', [Shown, Key]));
  end;
end;

EvaluateFormulaAt は数式を、格納せずに指定セルにあるものとして評価するので、キーはワークブック内のキー列に Excel が適用するのと同じ規則から出てきます。金額に通貨や桁区切りの書式が付いていれば、FormattedText はその書式を適用し、General 規則は書式のないセルだけを統べます。丸めた表示値を計算に戻すのは Excel の別のスイッチ、表示桁数で計算する precision as displayed の仕組みで、これは格納された double そのものを書き換えます。テキストだけが要るエクスポートには持ち込まないようにしましょう

15 桁を超える ID はどうなるのか

テキストになった瞬間に尻尾を失います。double は 253 までのすべての整数、約 9.007 × 1015 を保持できるので、16 桁の顧客番号 1234567890123456 はセルとファイルの中では無傷です。そこでは 17 有効数字が損失なしの XLSX double ラウンドトリップに足ります。テキストは別の話です。General 規則は 1.23457E+15 を表示し、数式規則は 15 有効数字へ丸めてゼロを埋めるので、=A1&"" は 1234567890123460 を返します。別の顧客です。Excel はさらに、セルに打ち込まれた数の 15 有効数字しか保持しないので、打ち直したユーザーは 1234567890123450 に着地します。1 人の顧客に 3 つの番号が生じます。セルの中では 1234567890123456、数式キーでは 1234567890123460、打ち直しの後は 1234567890123450

Delphi 向け HotXLS における 16 桁 ID の解剖図。格納されたセル値、15 桁の数式テキスト、打ち直された入力が 3 つの異なる数になる
識別子は数字で書かれたラベルです。テキストとして格納すれば、どんな規則も最後の 1 桁を丸め去れません

識別子は最初からテキストで格納しましょう。口座番号、カード番号、注文 ID は数字で書かれたラベルです。誰も合計せず、先行ゼロはラベルの一部です。文字列として代入すれば、グリッドにもすべての数式にも同じ 16 文字として届き、それらに対する検索がずれることはありません。Excel はそうしたセルに、テキストとして格納された数値の小さな緑の三角を付けます。最後の 1 桁を守るキーの、割に合う対価です:

// 識別子はテキストで入り、金額は数値のまま
procedure WriteCustomerRow(Sheet: TXLSXWorksheet; Row: Integer;
  const CustomerNo: string; Balance: Currency);
begin
  Sheet.Cells[Row, 1].Value := WideString(CustomerNo); // '1234567890123456' はそのまま保たれる
  Sheet.Cells[Row, 2].Value := Balance;                // General 規則で表示される
end;

エクスポータに手を入れる前に、作らせたワークブックを 1 つ Excel で開き、口座番号の列を広げて、その隣に =A1&"" を置きましょう。下流システムが左の文字列と比較するなら、HotXLS Delphi Excel component に FormattedText を求め、右の文字列と比較するなら、テキストは EvaluateFormulaAt で組み立てます