I have one Column in numeric and the value is 0.142 but convert to 0.14200000000000002
This is my code , I use cell.getNumericCellValue()
case Cell.CELL_TYPE_NUMERIC :
vaTmpVal = String.valueOf(voCel.getNumericCellValue());
-
Take a look at this post on ApachePOIavinandan012– avinandan0122019-11-15 09:56:56 +00:00Commented Nov 15, 2019 at 9:56
-
Please edit your question to contain the code rather than a link to an image. Use the code formatting, so that everything is easy to read within the question.Olaf Kock– Olaf Kock2019-11-16 12:30:06 +00:00Commented Nov 16, 2019 at 12:30
Add a comment
|
2 Answers
The Cell.getNumericCellValue() gets numeric cell values exact as they are stored. But Excel always uses only 15 significant digits for numeric values. So the exact value 0.14200000000000002 (having 17 significant digits) will be 0.142 for Excel.
If the need is to get all cell values as string, then you should not get the cell values by CellType. Instead you should using apache poi's DataFormatter. The DataFormatter.formatCellValue method gets all cell values as strings by formatting them the same kind as Excel will do. So if Excel shows 0.142 as the cell value then also DataFormatter will get 0.142 even if the exact numeric cell value is 0.14200000000000002.
Example:
import org.apache.poi.ss.usermodel.*;
import java.io.FileInputStream;
import java.util.Locale;
class GetDataFromExcelUsingDataFormatter {
public static void main(String[] args) throws Exception {
Workbook workbook = WorkbookFactory.create(new FileInputStream("ExcelExample.xlsx"));
DataFormatter dataFormatter = new DataFormatter();
FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
Sheet sheet = workbook.getSheetAt(0);
for (Row row : sheet) {
for (Cell cell : row) {
String cellValue = dataFormatter.formatCellValue(cell, formulaEvaluator);
System.out.println(cellValue);
}
}
workbook.close();
}
}
Comments
Floating point numbers, as you understand them in a base-10 numbering system (i.e. 0 - 9), cannot be accurately represented in binary (i.e. 0 - 1). Thus, most of the time what you'll see (from a programming language construct) is an approximation of the value.
In your case, if you don't care about the precision of your values past the thousandths position, you can use DecimalFormat:
import java.text.DecimalFormat;
public class Scratch {
public static void main(String[] args) {
System.out.println(new DecimalFormat("0.000").format(0.142));
}
}
Just for your reference as well, this exact question has been asked on poi's bug tracker and has been closed as "RESOLVED INVALID".
Additionally, as a fun little experiment to prove out the above, execute the following in a jshell session:
% jshell
| Welcome to JShell -- Version 11
| For an introduction type: /help intro
jshell> 0.1*0.1
$1 ==> 0.010000000000000002
You'd expect the above answer to be only 0.01; however, that's not the case.
