I have recently met this problem. I have to read data from excel.xlsx and put into my table. The problem is when I read the value from cell, it changes. E.G. Data from excel is: 4.1 and was stored 4.09999999999999964472863211994990706443 . Cell Type is Numeric. Column is NUMBER. I have printed the value after I read it and the value is 4.09999999999999964472863211994990706443.
Get value class:
public static BigDecimal getValueAsBigDecimal(Workbook workbook, Sheet sheet, String columnIndex, int rowIndex) throws ParseException {
String reference = decodeChacartersToDoubles(columnIndex) + rowIndex;
CellReference cellReference = new CellReference(reference);
Row row = sheet.getRow(cellReference.getRow());
Cell cell = row.getCell(cellReference.getCol());
if (cell.getCellType().equals(CellType.NUMERIC)) {
return new BigDecimal(cell.getNumericCellValue());
}
if (cell.getCellType().equals(CellType.STRING)) {
DataFormatter dataFormatter = new DataFormatter();
return new BigDecimal(dataFormatter.formatCellValue(cell));
}
if (cell.getCellType().equals(CellType.FORMULA)) {
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
CellValue cellValue = evaluator.evaluate(cell);
return new BigDecimal(cellValue.getNumberValue());
}
return null;
}