apache-poi - Apache POI 没有为来自 Excel 的大数返回正确的值

Question

我有一个值为 6228480018362050000 的 excel 文件，导出的 csv 看起来像这样......

Int,Bigint,String
1,6228480018362050000,Very big

当我尝试运行以下代码时...

InputStream inp = new FileInputStream("/.../test.xlsx");
DataFormatter df = new DataFormatter(true);
df.formatCellValue(WorkbookFactory.create(inp).getSheetAt(0).getRow(1).getCell(1));

我得到 6228480018362049500 这是错误的数字，因为精度受到限制。有没有办法获得实际值(value)？

Answer 1

如果我们将长数字放入 Excel 单元格中，那么这些数字将被截断为 15 位有效数字。这是因为 Excel 不知道诸如大整数之类的东西。它只有浮点数来存储数值。对于那些它遵循 IEEE 754 specification 。但是根据 IEEE 754 规范，有些数字不能存储为浮点数。在您的示例中，6228480018362050000，即 6.22848001836205E+018，不能这样存储。根据 IEEE 754 规范，它将是 6.2284800183620495E+018 或 6228480018362049500。

Microsoft's knowledge base 提到:“Excel 遵循关于如何存储和计算浮点数的 IEEE 754 规范。因此，Excel 仅在数字中存储 15 位有效数字，并将第 15 位之后的数字更改为零。”

这不是全部的真相。实际上，至少在 Office OpenXML ( *.xlsx ) 中，它根据 IEEE 754 规范存储值，而 而不是 仅 15 位有效数字。在您的示例中，它存储 <v>6.2284800183620495E+18</v> 。但那是次要的。因为即使它会存储 6.22848001836205E+018，也必须在某处将其重新转换为浮点数，然后再次变为 6.2284800183620495E+18。 Excel 在打开工作簿时执行相同的操作。它将 <v>6.2284800183620495E+18</v> 转换为浮点数和 然后 它只显示 15 位有效数字。

所以如果你真的需要将 6228480018362050000 作为数字存储在 Excel 中，那么获得与 Excel 中相同结果的唯一方法就是与 Excel 相同。为此，我们可以使用 BigDecimal 和它的 round 方法，它能够使用具有设置精度的 MathContext。

例子:

import org.apache.poi.ss.usermodel.*;

import java.io.*;

import java.math.BigDecimal;
import java.math.MathContext;

class ReadExcelBigNumbers {

 public static void main(String[] args) throws Exception{

  for (int i = 0; i < 10; i++) {
   String v = "6.2284800183620" + i + "E+018";
   double d = Double.parseDouble(v);
   System.out.print(v + "\t");
   System.out.print(d + "\t");
   BigDecimal bd = new BigDecimal(d);
   v = bd.round(new MathContext(15)).toPlainString();
   System.out.println(v);
  }

  InputStream inp = new FileInputStream("test.xlsx");
  Workbook wb = WorkbookFactory.create(inp);
  for (int i = 1; i < 9; i++) {
   double d = wb.getSheetAt(0).getRow(i).getCell(1).getNumericCellValue();
   BigDecimal bd = new BigDecimal(d);
   String v = bd.round(new MathContext(15)).toPlainString();
   System.out.println(v);
  }
 }
}

第一部分打印:

6.22848001836200E+018   6.2284800183620004E18   6228480018362000000
6.22848001836201E+018   6.2284800183620096E18   6228480018362010000
6.22848001836202E+018   6.2284800183620198E18   6228480018362020000
6.22848001836203E+018   6.2284800183620301E18   6228480018362030000
6.22848001836204E+018   6.2284800183620403E18   6228480018362040000
6.22848001836205E+018   6.2284800183620495E18   6228480018362050000
6.22848001836206E+018   6.2284800183620598E18   6228480018362060000
6.22848001836207E+018   6.22848001836207E18     6228480018362070000
6.22848001836208E+018   6.2284800183620803E18   6228480018362080000
6.22848001836209E+018   6.2284800183620905E18   6228480018362090000

在那里您可以看到想要的浮点值、符合 IEEE 754 规范的实际浮点值和重新格式化的 BigDecimal 之间的区别。如您所见，只能根据 IEEE 754 规范直接存储 6.22848001836207E+018。

第二部分使用以下 Excel 表执行相同的操作:



知识库文章中提到了另一种可能的解决方法:“要解决此问题，请将单元格格式化为文本，然后键入数字。然后该单元格最多可以显示 1,024 个字符。”。如果数字不是真正的数字，而是例如标识符或其他一些数字仅表示字符的字符串，这很好。使用这种“文本数字”进行计算当然是不可能的，除非将它们重新转换为浮点数，这将再次带来问题。