技术文章

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。带 空节或纯字面量节的格式按自己的规则来,甚至能把数字整个藏掉

Delphi 中 HotXLS 复刻 Excel General 格式十一字符显示规则的判定流程:定点记法、科学记法切换,负号留在预算之外
宽列给 General 格式十一字符的数字与小数点预算,负号在预算之外等着

还有一道限制在所有库之外。标准补充说“最佳显示方式取决于可用单元格宽度”,默认宽度的列里 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
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 的两条规则它一条都不复刻,一个既管显示又管公式文本的辅助函数,在两条规则不一致的每一行上都会错——上表六行里错了四行

Delphi 中 HotXLS 把同一个存储的 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 本身,所以只取文本的导出别碰它

超过十五位的 ID 会怎么样?

变成文本的那一刻它就丢了尾巴。double 装得下直到 253(约 9.007 × 1015)的每个整数,所以十六位的客户号 1234567890123456 在单元格里、在文件里都完好,十七位有效数字足以支撑 XLSX double 的无损往返。文本是另一回事:General 规则显示 1.23457E+15,公式规则舍到十五位有效数字再补一个零,=A1&"" 返回 1234567890123460——另一位客户。用户手工重输时,Excel 也只保留十五位有效数字,落点是 1234567890123450。一个客户就此有了三个号码:单元格里 1234567890123456,公式键里 1234567890123460,重输之后 1234567890123450

Delphi 中 HotXLS 十六位 ID 剖析:存储的单元格值、十五位公式文本与手工重输给出三个不同的号码
标识符是写在数字里的标签,存成文本,任何规则都舍不掉最后一位

从一开始就把标识符存成文本。账号、卡号和订单 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 构造文本