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。帶空區段或純字面區段的格式另有自己的規則,甚至能把數字整個藏起來
還有一道限制在任何函式庫之外。標準補了一句「最佳顯示方式取決於可用的儲存格寬度」,在預設寬度的欄位裡 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 |
|---|---|---|---|
| 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 |
最後一欄解釋了漂移。FloatToStr 用 Delphi 的 general 格式取十五位數,指數不寫加號也不補零,並按自己的門檻切指數記法:低於 0.00001,或整數部分超過十五位時。它還從主機的 FormatSettings 拿小數分隔符。寫日誌檔它是把好手;但兩條 Excel 規則它一條都不重現,一個被要求同時供應顯示文字與公式文字的輔助函式,在這兩條規則不一致的每一行都會錯——上面六行裡有四行
匯出與查找鍵該用哪種文字?
問字串會在哪裡被比對,規則就跟著定了。給人看的文字——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
識別碼從一開始就存成文字。帳號、卡號與訂單編號都是用數字寫成的標籤:沒有人會把它們加總,前導零也是標籤的一部分。以字串指派,它們進到儲存格與每個公式都是同樣十六個字元,拿它們做查找永不漂移。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 建文字