enter image description hereI 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.

  • 1
    That's not exponentially formal that's the scientific notation for decimal point
    – PDHide
    Commented Apr 26, 2020 at 7:38
  • stackoverflow.com/a/19984096/6793637
    – PDHide
    Commented Apr 26, 2020 at 7:40
  • E8 means valuebefore E * 10 ^ 8
    – PDHide
    Commented Apr 26, 2020 at 7:42
  • Which is the number in ur cell ,
    – PDHide
    Commented Apr 26, 2020 at 7:42
  • -117035791.877483 is the number which I want to get but not able to retrieve with exact decimal points without scientific notation. Commented Apr 26, 2020 at 7:48

2 Answers 2


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") 

            // 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
//                  String cellValueStr = dataFormatter.formatCellValue(currentrow.getCell(0) );



See if this helps


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


XSSFWorkbook wb = new XSSFWorkbook(file);
XSSFSheet sheet = wb.getSheet("Sheet1");
  • Thank you for help.Above code works fine for cell value -117035791.877483 but for other values it is adding more decimal points , I guess. For example, cell value -44227642340.6819 , it is reading as -44227642340.68186 so instead of 0.6819 , it is taking 0..68186. Same for -3666272860.55857, it is reading it as -3666272860.5585732 and for -11029273623.5079, reading it as -11029273623.507858. Commented Apr 26, 2020 at 11:54
  • @DarshanPatel i updated my answer, see if it works
    – PDHide
    Commented Apr 26, 2020 at 18:18
  • Hi, I tried with below code but still some values are not retrieving as expected like in excel value is -65533.333 but when I am reading it is coming as -65533.332999999999.For value -3666272860.55857, it is coming as -3666272860.5585732. cell.setCellType(Cell.CELL_TYPE_STRING); System.out.println(cell.toString()); Commented Apr 27, 2020 at 5:11
  • @DarshanPatel i updated the answer , see if this helps , i used all the test data you mentioned adn is working fine
    – PDHide
    Commented Apr 27, 2020 at 6:42
  • There will be extra 0 but I think there is no way to avoid it. Unless all values have same decimal digits
    – PDHide
    Commented Apr 27, 2020 at 6:45

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.

  • That will work because that makes the Excel field as text . But he want to read numerical cell
    – PDHide
    Commented May 1, 2020 at 21:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.