Unable to read date which is of numerical type from excel using poi

Question

I am trying to read an excel file using POI. The first excel column is date(String type) and second is string.

So, my code is working fine if i do cell.getStringCellValue()

However, there is one particular row in the excel where both the columns are numeric type.

Hence I get :

java.lang.IllegalStateException: Cannot get a text value from a numeric cell

System.out.println(dateCell.getNumericCellValue());
System.out.println(ctrCell.getNumericCellValue());

It prints:

42360.0
0.3227586206896551

But in excel, I can see it as : 12/22/2015 and 32.2758620689655%

Kindly suggest how can I read the date as 12/22/2015 only instead of 42360.0 from the numerical cell.

Any help will be highly appreciated!!

Sonal · Answer 1 · 2016-01-22T04:49:35.610

Try this:

          Cell cell = row.getCell(colPos,Row.RETURN_BLANK_AS_NULL);
                            if (cell == null) {

                            value = "";

                            } 
                            else {

                            value = cell.toString().trim();
                            switch (cell.getCellType()) {
                            case  XSSFCell.CELL_TYPE_NUMERIC:

                            value = BigDecimal.valueOf(cell.getNumericCellValue()).toPlainString().trim();

                            if (DateUtil.isCellDateFormatted(cell)) {
                            double val = cell.getNumericCellValue();
                            Date date = DateUtil.getJavaDate(val);

                            String dateFmt = null;


                            if(cell.getCellStyle().getDataFormat()==14){
                                dateFmt = "dd/mm/yyyy";

                                value = new CellDateFormatter(dateFmt).format(date);

                            }
                            else{
                                DataFormatter fmt = new DataFormatter();
                                String valueAsInExcel = fmt.formatCellValue(cell);



                                    value = valueAsInExcel;


                            }

                    }

                            break;

                            case XSSFCell.CELL_TYPE_STRING:
                            value = cell.getStringCellValue().trim();
                            break;

                            case XSSFCell.CELL_TYPE_BLANK:
                            value = "";
                            break;

                            default:
                            break;
                            }

Unable to read date which is of numerical type from excel using poi

1 Answers1