Java POI FormulaEvaluator提供意外的浮点值 [英] Java POI FormulaEvaluator giving unexpected floating point value

问题描述

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

I am using Java POI library to read an Excel file and then display it in HTML table. The Excel file is very simple with 1 row and 3 columns:

A1单元格= 21.7B1单元格= 20.0C1单元格是具有公式=(A1-B1)/B1的公式单元格，并且其自定义格式为百分比"，小数位数为0.Excel将其值显示为9％.这是因为计算器上的1.7/20得出的结果为0.085；当将其转换为百分比"格式时，它将变为8.5％，并且由于格式说明包含0个小数位，因此将其向上舍入为9％，因此这就是Excel显示的内容.一切都很好.

A1 cell= 21.7 B1 cell= 20.0 C1 cell is a formula cell with the formula =(A1-B1)/B1 and it has a custom format of "Percentage" with 0 decimal places. Excel displays its value as 9%. This is because 1.7/20 on a calculator gives result as 0.085; when it is converted to "Percentage" format it becomes 8.5% and because format says include 0 decimal places, it gets rounded up to 9%, so that's what Excel displays. All good.

但是，POI会将值显示为8％.我观察到1.7/20计算为0.084999999.由于上面应用的百分比格式将其转换为8.4999999％，并且由于小数点后0位，因此四舍五入为8％.

However, POI displays the value as 8% instead. I observe that 1.7/20 is calculated to be 0.084999999. Because of the Percentage format as applied above it converts to 8.4999999% and because of 0 decimal places, it gets rounded down to 8%.

如何让POI回报我9％而不是8％?这是代码段:

How can I have POI return me 9% instead of 8%? Here is the code snippet:

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

此处评估者是org.apache.poi.ss.usermodel.FormulaEvaluator的实例，而dataFormatter是org.apache.poi.ss.usermodel.DataFormatter的实例

Here evaluator is an instance of org.apache.poi.ss.usermodel.FormulaEvaluator and dataFormatter is an instance of org.apache.poi.ss.usermodel.DataFormatter

当我打印变量"val"时，它返回的是8％，而不是Excel中显示的(9％).

When I print the variable "val" it is returning 8% instead of what is displayed in Excel (9%).

推荐答案

您的观察是正确的.出现此问题是由于一般的浮点问题.可以简单地显示出来:

Your observations are correct. The problem occurs because of the general floating point problems. It can simply be shown:

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

如您所见，双精度值1.7除以双精度值20.0得出0.08499999999999999.这很好，因为使用 DecimalFormat 将该值设为0.085.但是，更复杂的方程式(21.7-20.0)/20.0得出0.08499999999999996.而且显然低于0.085.

As you see, the division of double value 1.7 by double value 20.0 results in 0.08499999999999999. This would be fine since this value would be taken as 0.085 using DecimalFormat. But the more complex equation (21.7-20.0)/20.0 results in 0.08499999999999996. And this clearly is lower than 0.085 .

Excel 尝试通过附加的浮点值规则解决这些问题.它始终仅使用浮点值的15个有效十进制数字.因此 Excel 的作用类似于:

Excel tries solving those problems by an additional rule for floating point values. It always uses only 15 significant decimal digits of an floating point value. So Excel does something like :

...
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 .这就是为什么与众不同.

Neither apache poi's FormulaEvaluator nor it's DataFormatter behaves like Excel in this point. That's why the difference.

一个人可能拥有自己的 MyDataFormatter ，其中与

One could have an own MyDataFormatter where the only difference to /org/apache/poi/ss/usermodel/DataFormatter.java is:

...
    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 的行为更加兼容.

Then using that MyDataFormatter instead of DataFormatter would be more compatible to Excel's behavior.

示例:

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();

 }
}

这篇关于Java POI FormulaEvaluator提供意外的浮点值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！