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,十五个三;=1E+19&"" 返回 10000000000000000000,=1E-18&"" 返回 0.000000000000000001。超过二十字符,文本切到科学记法,用与网格相同的带符号两位指数:1E+20、1.23456789012345E-05。分隔符永远是句点。HotXLS 求值公式不参考运行你程序的机器的区域设置,用 & 拼的查找键在英文和德文服务器上结果一致
公式文本有一处 Excel 自己也依赖机器。CHAR 和 CODE 对 127 以上的数字按运行 Excel 的那台机器的 ANSI 代码页映射,=CHAR(128) 在西欧 Windows 上是欧元符号,别的代码页下是别的字符。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 本身,所以只取文本的导出别碰它
超过十五位的 ID 会怎么样?
变成文本的那一刻它就丢了尾巴。double 装得下直到 253(约 9.007 × 1015)的每个整数,所以十六位的客户号 1234567890123456 在单元格里、在文件里都完好,十七位有效数字足以支撑 XLSX double 的无损往返。文本是另一回事:General 规则显示 1.23457E+15,公式规则舍到十五位有效数字再补一个零,=A1&"" 返回 1234567890123460——另一位客户。用户手工重输时,Excel 也只保留十五位有效数字,落点是 1234567890123450。一个客户就此有了三个号码:单元格里 1234567890123456,公式键里 1234567890123460,重输之后 1234567890123450
从一开始就把标识符存成文本。账号、卡号和订单 ID 都是写在数字里的标签,没人对它们求和,前导零也是标签的一部分。以字符串赋值,它们以同样的十六个字符抵达网格和每条公式,对着它们的查找永远不会漂移。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 component 要 FormattedText;比较的是右边的,就用 EvaluateFormulaAt 构造文本