Java POI FormulaEvaluator 给出意外的浮点值 [英] Java POI FormulaEvaluator giving unexpected floating point value
问题描述
我正在使用 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 助手 = 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%).
您的观察是正确的.出现该问题是由于一般浮点问题.它可以简单地显示:
<预><代码>...System.out.println(1.7/20.0);//0.08499999999999999System.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.
Excel 尝试通过附加的浮点值规则来解决这些问题.它始终仅使用浮点值的 15 位有效十进制数字.所以 Excel 做了类似的事情:
apache poi 的 FormulaEvaluator 和 DataFormatter 在这一点上都不像 Excel.这就是差异的原因.
可以有一个自己的 MyDataFormatter，其中唯一的区别是 /org/apache/poi/ss/usermodel/DataFormatter.java 是:
然后使用 MyDataFormatter 而不是 DataFormatter 会更兼容 Excel 的行为.
示例:
import java.io.FileOutputStream;导入 org.apache.poi.ss.usermodel.*;导入 org.apache.poi.ss.util.*;导入 org.apache.poi.xssf.usermodel.XSSFWorkbook；类 CreateExcelEvaluateFormula {public static void main(String[] args) 抛出异常 {工作簿工作簿 = new XSSFWorkbook();CreationHelper creationHelper = workbook.getCreationHelper();FormulaEvaluator formulaEvaluator = creationHelper.createFormulaEvaluator();Sheet sheet = workbook.createSheet();行行 = sheet.createRow(0);单元格单元格 = row.createCell(0);cell.setCellValue(21.7);单元格 = row.createCell(1);cell.setCellValue(20.0);单元格 = row.createCell(2);cell.setCellFormula("(A1-B1)/B1");formulaEvaluator.evaluateFormulaCell(cell);double d = cell.getNumericCellValue();System.out.println(d);//0.08499999999999996MyDataFormatter dataFormatter = new MyDataFormatter();字符串 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);关闭()；workbook.close();}}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 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.
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%.
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);
Here evaluator is an instance of org.apache.poi.ss.usermodel.FormulaEvaluator and dataFormatter is an instance of org.apache.poi.ss.usermodel.DataFormatter
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
...
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 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
...
Neither apache poi's FormulaEvaluator nor it's DataFormatter behaves like Excel in this point. That's why the difference.
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
}
...
Then using that MyDataFormatter instead of DataFormatter would be more compatible to Excel's behavior.
Example:
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屋！
