我正在使用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 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();
}
}