Java POI FormulaEvaluator 给出意外的浮点值

Question

我正在使用 Java POI 库读取 Excel 文件，然后将其显示在 HTML 表格中。 Excel 文件非常简单，只有 1 行 3 列:

A1 细胞= 21.7B1 细胞= 20.0C1 单元格是一个公式单元格，其公式为 =(A1-B1)/B1，它具有自定义格式“百分比”，小数点后 0 位。 Excel 将其值显示为 9%。这是因为 1.7/20 在计算器上给出的结果是 0.085；当它转换为“百分比”格式时，它会变成 8.5%，并且因为格式显示包含 0 位小数，所以它会四舍五入到 9%，这就是 Excel 显示的内容。一切都好。

但是，POI 将值显示为 8%。我观察到 1.7/20 的计算结果是 0.084999999。由于上面应用的百分比格式，它会转换为 8.4999999%，并且由于小数点后 0 位，它会向下舍入为 8%。

如何让 POI 返回 9% 而不是 8%？这是代码片段:

String myFormat="0%";
CreationHelper helper = wbWrapper.getWb().getCreationHelper();
CellUtil.setCellStyleProperty(cell, CellUtil.DATA_FORMAT,helper.createDataFormat().getFormat(myFormat));
String val = dataFormatter.formatCellValue(cell, evaluator);

这里 evaluator 是 org.apache.poi.ss.usermodel.FormulaEvaluator 的实例，dataFormatter 是 org.apache.poi.ss.usermodel.DataFormatter 的实例

当我打印变量“val”时，它返回 8%，而不是 Excel 中显示的值 (9%)。

Answer 1

您的观察是正确的。该问题的出现是因为一般的浮点问题。它可以简单地显示:

...
System.out.println(1.7/20.0); //0.08499999999999999
System.out.println((21.7-20.0)/20.0); //0.08499999999999996
...

如您所见， double 值 1.7 除以 double 值 20.0 得到的结果是 0.08499999999999999。这很好，因为使用 DecimalFormat 该值将被视为 0.085。但更复杂的方程 (21.7-20.0)/20.0 结果为 0.08499999999999996。这显然低于 0.085 。

Excel 尝试通过浮点值的附加规则来解决这些问题。它始终仅使用浮点值的 15 位有效十进制数字。所以 Excel 会执行以下操作:

...
BigDecimal bd = new BigDecimal((21.7-20.0)/20.0);
System.out.println(bd.round(new MathContext(15)).doubleValue()); //0.085
...

在这一点上，apache poi 的 FormulaEvaluator 和 DataFormatter 的行为都不像 Excel。这就是差异的原因。

一个人可以有一个自己的MyDataFormatter，唯一的区别是 /org/apache/poi/ss/usermodel/DataFormatter.java是:

...
    private String getFormattedNumberString(Cell cell, ConditionalFormattingEvaluator cfEvaluator) {
        if (cell == null) {
            return null;
        }
        Format numberFormat = getFormat(cell, cfEvaluator);

        double d = cell.getNumericCellValue();
        java.math.BigDecimal bd = new java.math.BigDecimal(d);
        d = bd.round(new java.math.MathContext(15)).doubleValue();

        if (numberFormat == null) {
            return String.valueOf(d);
        }
        String formatted = numberFormat.format(Double.valueOf(d));
        return formatted.replaceFirst("E(\\d)", "E+$1"); // to match Excel's E-notation
    }
...

然后使用 MyDataFormatter 而不是 DataFormatter 将更兼容 Excel 的行为。

示例:

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

class CreateExcelEvaluateFormula {

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

  Workbook workbook  = new XSSFWorkbook();
  CreationHelper creationHelper = workbook.getCreationHelper();
  FormulaEvaluator formulaEvaluator = creationHelper.createFormulaEvaluator();

  Sheet sheet = workbook.createSheet();
  Row row = sheet.createRow(0);
  Cell cell = row.createCell(0); cell.setCellValue(21.7);
  cell = row.createCell(1); cell.setCellValue(20.0);

  cell = row.createCell(2); cell.setCellFormula("(A1-B1)/B1");
  formulaEvaluator.evaluateFormulaCell(cell); 
  double d = cell.getNumericCellValue();
System.out.println(d); //0.08499999999999996

  MyDataFormatter dataFormatter = new MyDataFormatter();

  String myFormat="0%";
  CellUtil.setCellStyleProperty(cell, CellUtil.DATA_FORMAT, creationHelper.createDataFormat().getFormat(myFormat));
  String val = dataFormatter.formatCellValue(cell, formulaEvaluator);
  System.out.println(val); //9%

  FileOutputStream out = new FileOutputStream("Excel.xlsx");
  workbook.write(out);
  out.close();
  workbook.close();

 }
}