1

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.

  • That's not exponentially formal that's the scientific notation for decimal point – PDHide Apr 26 at 7:38
  • E8 means valuebefore E * 10 ^ 8 – PDHide Apr 26 at 7:42
  • Which is the number in ur cell , – PDHide Apr 26 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. – Darshan Patel Apr 26 at 7:48
0

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");
| improve this answer | |
  • 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. – Darshan Patel Apr 26 at 11:54
  • @DarshanPatel i updated my answer, see if it works – PDHide Apr 26 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()); – Darshan Patel Apr 27 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 Apr 27 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 Apr 27 at 6:45
0

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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