getting incorrect value from excel using apache poi

Question

in excel the value of column is 10101101010000000000 but when im reading it in java using POI the value is changed to 10101101009999999000, can anyone give me an idea on whats going on and how can i get the exact values from the excel. i've tried setting the celltype as string and use cell.getStringCellValue() and also this new BigDecimal(cell.getNumericCellValue()).toPlainString() but im still not getting the same value as with the excel

here's my code

List<BankClassVo> data = new ArrayList<BankClassVo>();
    FileInputStream fis = new FileInputStream(new File(Constant.VALIDATION_REFERENCE_FILE_PATH + Constant.BANK_CLASSIFICATION_REF_FILE + ".xlsx"));
    XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);
    XSSFSheet mySheet = myWorkBook.getSheetAt(1);
    Iterator<Row> rowIterator = mySheet.iterator();


    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();

        BankClassVo vo = new BankClassVo ();


            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                if (cell.getColumnIndex() == 0) {
                    vo.setsClass(new BigDecimal(cell.getNumericCellValue()).toPlainString());
                }
                else if (cell.getColumnIndex() == 1) {
                    vo.setClassification(cell.getStringCellValue());
                }
            }

        data.add(vo);
    }

    myWorkBook.close();
    return data;

You should probably add the code you're using to read the column — Dragondraikk
– Dragondraikk, Commented Jun 9, 2015 at 8:34
I suggest you to read it as a string and then check if it's reading correctly. — Uma Kanth
– Uma Kanth, Commented Jun 9, 2015 at 8:48
@ Uma Kanth, yes ive already done that and still not reading correctly — carlo del rosario
– carlo del rosario, Commented Jun 9, 2015 at 8:51
XLSX stores as XML, so text. Just to make sure the problem is in Java: unzip the contents of your XLSX file and open xl/worksheets/sheet1.xml to see what the value actually is. Other than that, it looks like an overflow issue, perhaps in your BankClassVo class. Maybe first send cell.getStringCellValue() to System.out. — Hummeling Engineering BV
– Hummeling Engineering BV, Commented Jun 9, 2015 at 8:55

Zaw Than oo · Accepted Answer · 2015-06-09 09:10:29Z

1

Use MathContext and RoundingMode. Ref

BigDecimal value = new BigDecimal(cell.getNumericCellValue(), new MathContext(10 , RoundingMode.CEILING));
System.out.println(value.toPlainString());

answered Jun 9, 2015 at 9:10

Zaw Than oo

9,94513 gold badges87 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

getting incorrect value from excel using apache poi

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related