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

A1单元格= 21.7
B1单元格= 20.0
C1单元格是具有公式=(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%)。

最佳答案

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

...
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。

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 poiFormulaEvaluatorDataFormatter的行为都不像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代替DataFormatterExcel的行为更加兼容。

例:

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

 }
}

09-05 15:11
查看更多