How to read numerical cells without converting to scientific notation (reading excel using Apache POI)

Question

I am trying to read an excel file and validate the values.

While reading one of the columns which has decimal values, the digits after the decimal point are not correctly retrieved. Suppose, I am trying to read -117035791.877483 number but the result I am retrieving is -1.1703579187748297E8 (which is in exponential format and don't want that format) if I directly read using below method.

double d = cell.getNumericCellValue();

I also tried converting the same number to decimal first as below code, but it is adding more digits after decimal point like -117035791.87748297.

String s = BigDecimal.valueOf(cell.getNumericCellValue()).toPlainString();

I also tried to retrieve value directly as String value but still, the problem persists. Please suggest the way through which I will get Exact value with decimal points from excel.

Answer 1

Tested with all the data you gave:

      package runner;


import java.io.FileInputStream;

import java.io.IOException;

import java.util.Locale;

import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class test {

    public static void main(String[] args) throws IOException {
        // TODO Auto-generated method stub

        FileInputStream file = new FileInputStream("src\\files\\test2.xlsx");
        XSSFWorkbook wb = new XSSFWorkbook(file);
        XSSFSheet sheet = wb.getSheet("Sheet1");

        int row_count = sheet.getLastRowNum() + 1;
        System.out.println("no of rows in the data file is:" + (row_count));

        int column_count = sheet.getRow(0).getPhysicalNumberOfCells();
        System.out.println("no of columns in the data file is:" + (column_count));

         DataFormatter dataFormatter = new HSSFDataFormatter(Locale.ENGLISH);

        // Inside loop


        for (int row_index = 1; row_index < row_count; row_index++) {

            XSSFRow currentrow = sheet.getRow(row_index);

            //Create cell where there will be no data
            currentrow.createCell(6, CellType.STRING);

            //get value of the cell
            String val=currentrow.getCell(0).toString();

            //get address of the cell
            CellAddress add=currentrow.getCell(0).getAddress();

            //set formula on newly created cell to convert the value from the cell to text (=TEXT(A1,"0.00000000000000000000000") 
            currentrow.getCell(6).setCellFormula("TEXT("+add+",\"0.000000000000000000000000000000000000\")");  

            // set formula doesn't trigger the formula, we have to use evaluate cell to apply the formula
            CreationHelper creationHelper = wb.getCreationHelper();
            FormulaEvaluator evaluator = creationHelper.createFormulaEvaluator();
            Cell cell = evaluator.evaluateInCell(currentrow.getCell(6));

            //print the formula
            System.out.println(currentrow.getCell(6).getStringCellValue())  ;

            //close the workbook
            wb.close();
//                  String cellValueStr = dataFormatter.formatCellValue(currentrow.getCell(0) );
    }


    }

}

See if this helps

Note:

you will have more zeros in decimal as you mention in the formula . So if the formula is TEXT(A1,"0.0") the only one decimal place will be there, for TEXT(A1,"0.000") there will be 3 and so on

and

XSSFWorkbook wb = new XSSFWorkbook(file);
XSSFSheet sheet = wb.getSheet("Sheet1");

Answer 2

Not sure if this will work but it's worth a try. Add an apostrophe to the beggining of the field in excel and see if it picks up the number in the expected format.