Reputation: 366
I am reading data from Excel using Apache POI. I want to convert some cells to number while reading in Java as following:
Input 01.0234500
Output 1.02345
Input 412206362371
Output 4.12206E+11
Input 1234.201400002345
Output 1234.2014
When I am using "Convert to Number" in Excel, it works fine. But I want the same output while reading Excel cells in Java.
It can also be achieved using =VALUE()
function in Excel. But how do I implement the same functionality in Java?
Upvotes: 1
Views: 8094
Reputation: 670
This worked for me
new BigDecimal(cell.getNumericCellValue()).toPlainString()
Upvotes: 0
Reputation: 118
I think there are a number of ways to accomplish what you stipulate, but the most direct method is just to use the VALUE()
function and then evaluate it. This certainly is not the most efficient method, but it works.
Essentially we just read in the value of the input cell, then create a new cell formula cell which contains the VALUE()
function with the original value as the parameter. After that we call evalutateInCell
to evaluate the VALUE()
function and replace the cell value with the results.
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("test");
Row row = sheet.createRow(0);
Cell inCell = row.createCell(0);
inCell.setCellValue("01.0234500");
Cell outCell = row.createCell(1);
FormulaEvaluator fev = wb.getCreationHelper().createFormulaEvaluator();
String value = inCell.getStringCellValue();
outCell.setCellFormula("VALUE(" + value + ")");
fev.evaluateInCell(outCell);
You can use the same method to replace the contents of the original cell if that is your preference.
Upvotes: 3