CODEWITHSUNDEEP
javaexcelapache-poi
Anup Ganatra
Anup Ganatra

Reputation: 366

How to do Excel's "Convert to Number" in Java

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.

enter image description here

It can also be achieved using =VALUE() function in Excel. But how do I implement the same functionality in Java?

Upvotes: 1

Views: 8094

Answers (2)

DaviesTobi alex
DaviesTobi alex

Reputation: 670

This worked for me

new BigDecimal(cell.getNumericCellValue()).toPlainString()

Upvotes: 0

Dave Ross
Dave Ross

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

Related Questions