技術文章

為什麼 Excel 把 12 位數字顯示成 1.23457E+11

Delphi 匯出把帳號 123456789012 存成數字;拉寬的 Excel 欄顯示 1.23457E+11,=A1&"" 卻回傳完整十二位。Excel 按兩條規則把數字變文字:General 顯示用十一個字元,公式內部用十五個有效位數。所以 HotXLS 匯出從 FormattedText 讀前者,用公式引擎建後者

General 格式允許幾個字元?

十一個,負號不計入。ECMA-376 Part 1 §18.8.30 說應用程式「應嘗試以最多 11 位數(含小數點)顯示完整數字」,其浮點數規則講得更精確:「儲存格顯示的總長度上限為 11,不含負號」。標準接著畫了四個切到指數形式的條件就止步了:捨入方式留白,小數的切換點也留白。以下行為就是 Excel 在拉寬欄位裡的表現,也是 HotXLS 在儲存格顯示文字與 TEXT(A1, "General") 中重現的行為:

  • 整數部分放得進十一個字元的數字保持固定記法、犧牲小數:12345.6789012345 顯示為 12345.6789,12345678901.5 捨入成 12345678902
  • 十二位整數部分塞不下,123456789012 就變 1.23457E+11,尾數捨入以騰出指數的空間
  • 小於 1 的數字保持固定記法,條件是顯示出的有效位數至少與六位數字的科學記法一樣多:0.00012345678 顯示為 0.000123457,0.0000123456789 則變 1.23457E-05
  • 指數必帶正負號且至少兩位數,所以 1020 顯示為 1E+20

正負號在預算之外。-12345678901 連同負號後的全部十一位一起顯示,總共十二個字元;-123456789012 則變 -1.23457E+11。指派什麼 Variant 型別不影響結果:Excel 把每個數值儲存格的值都當 double 保存,HotXLS 讓 Integer、Int64、Currency 與 Double 值走同一條十一字元規則,所以 Cells[1, 1].Value := Int64(123456789012) 同樣顯示為 1.23457E+11。這條規則也延伸到自訂格式:區段裡的字面文字會加在 General 結果周圍,General" kg" 顯示的同一個數字就成了 1.23457E+11 kg。帶空區段或純字面區段的格式另有自己的規則,甚至能把數字整個藏起來

HotXLS for Delphi 中 Excel General 格式十一字元顯示規則的判定流程:固定記法、切換科學記法,以及預算之外的負號
拉寬的欄位給 General 格式十一個字元裝數字與小數點,負號則在預算外等著

還有一道限制在任何函式庫之外。標準補了一句「最佳顯示方式取決於可用的儲存格寬度」,在預設寬度的欄位裡 Excel 會壓得更緊,壓到 1.2E+11 或一排 # 號,取決於顯示這張工作表那台機器的字型度量。產生檔案的伺服器螢幕上沒有欄,HotXLS 就把最後一步留給畫儲存格的人。比較之前先把欄拉寬,否則您量到的是 Excel 的寬度退讓與 General 規則之間的落差,兩者在任何長過欄寬的值上都會對不上

公式文字保留十五個有效位數

數字在公式內部變成文字的那一刻——經 & 運算子、CONCAT、TEXTJOIN、LEN、LEFT——接手的就是另一條規則。ECMA-376 §18.17 定義了公式文法,卻沒定義這一步的數字轉文字格式,Excel 的輸出是唯一參考。Excel 捨入到十五個有效位數,只要不含正負號不超過二十個字元,就以純固定記法寫出。所以 =1/3&"" 回傳 0.333333333333333,十五個 3;=1E+19&"" 回傳 10000000000000000000;=1E-18&"" 回傳 0.000000000000000001。超過二十個字元就切到科學記法,指數帶正負號、兩位數,與儲存格相同:1E+20、1.23456789012345E-05。分隔符永遠是句點。HotXLS 求值公式時不參考執行程式那台機器的區域設定,所以用 & 建的查找鍵在英文與德文伺服器上完全一致

公式文字有一個角落連 Excel 自己都依機器而定。CHAR 與 CODE 把 127 以上的數字經執行 Excel 那台電腦的 ANSI 字碼頁映射,所以在西歐 Windows 安裝上 =CHAR(128) 是歐元符號,其他字碼頁下是別的字元。Delphi 服務無從知道最終讀者用哪個字碼頁。HotXLS 在每台機器上都按 Windows-1252(西歐字碼頁)求這些碼,所以 =CHAR(150) 在任何伺服器上都給 en dash。若您的工作簿要給別的字碼頁用,就在 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 的 general 格式取十五位數,指數不寫加號也不補零,並按自己的門檻切指數記法:低於 0.00001,或整數部分超過十五位時。它還從主機的 FormatSettings 拿小數分隔符。寫日誌檔它是把好手;但兩條 Excel 規則它一條都不重現,一個被要求同時供應顯示文字與公式文字的輔助函式,在這兩條規則不一致的每一行都會錯——上面六行裡有四行

HotXLS for Delphi 中一個儲存的 double 映射到三種字串:Excel 儲存格顯示、十五個有效位數的公式文字,以及 Delphi FloatToStr 的結果
儲存格顯示、公式文字與 FloatToStr 各走各的規則,選哪個要看字串將在哪裡被比對

匯出與查找鍵該用哪種文字?

問字串會在哪裡被比對,規則就跟著定了。給人看的文字——PDF 報表、HTML 表格、有人會開來對照工作簿的 CSV——應與儲存格一致,讀儲存格的顯示文字;當資料用的文字應遵循公式規則:串接的鍵、要與 Excel 公式產物比對的值、查找字串。會被重新載入 Excel 或資料庫的 CSV 也算資料。把 1.23457E+11 寫進這種檔案,帳號的五個位數就永久丟了,因為匯入器解析它讀到的東西,救不回原值。第一個範例把三種字串並排印出;樣本是 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 的儲存格在那台機器上也這樣——公式欄則保持句點。第二個範例把這個選擇放進匯出器:報表文字照 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 裡另一個開關——以顯示值精確計算——它會直接改寫儲存的 double,只要文字的匯出請離它遠一點

超過十五位的識別碼會怎樣?

變成文字的那一刻就丟了尾巴。double 裝得下 253 以內的每個整數,約 9.007 × 1015,所以十六位客戶編號 1234567890123456 在儲存格與檔案裡完好無缺——檔案裡十七個有效位數足以無損往返 XLSX 的 double。文字則是另一回事:General 規則顯示 1.23457E+15,公式規則捨入到十五個有效位數再補零,=A1&"" 回傳 1234567890123460——另一位客戶。Excel 對手動輸入儲存格的數字也只保留十五個有效位數,使用者重打一遍就落在 1234567890123450。一位客戶於是有了三個號碼:儲存格裡 1234567890123456,公式鍵裡 1234567890123460,重打之後 1234567890123450

HotXLS for Delphi 中十六位識別碼的剖析:儲存的儲存格值、十五位的公式文字與重打的輸入得出三個不同號碼
識別碼是用數字寫成的標籤,存成文字就沒有任何規則能把最後一位捨掉

識別碼從一開始就存成文字。帳號、卡號與訂單編號都是用數字寫成的標籤:沒有人會把它們加總,前導零也是標籤的一部分。以字串指派,它們進到儲存格與每個公式都是同樣十六個字元,拿它們做查找永不漂移。Excel 會給這種儲存格插上「數字儲存為文字」的小綠三角,為一個保得住最後一位的鍵,這代價公道:

// 識別碼以文字進場;金額保持數值
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;

動匯出器之前,先在 Excel 裡打開它產生的一份工作簿,把帳號欄拉寬,旁邊放個 =A1&""。下游系統若比對的是左邊那個字串,向 HotXLS Delphi Excel 元件要 FormattedText;比對的是右邊那個,就用 EvaluateFormulaAt 建文字