returning decimal instead of string (POI jar)

Question

I need to read xls or xlsx sheet. successfully I read the sheet, but it returning decimal value instead of string (eg: for 3 -- it is returning 3.0). I need to read the cell values as it is. so I need to return as string

score 2 · Answer 1 · answered Sep 21 '12 at 09:09

2

POI is giving you the exact value that Excel has stored in the File. Generally, if you write a number in an Excel cell, Excel will store that as a number with formatting. POI provides support to do that formatting for you if you want it (most people don't - they want the numbers as numbers so they can use them)

The class you're looking for is DataFormatter. Your code would be something like

 DataFormatter fmt = new DataFormatter();
 for (Row r : sheet) {
    for (Cell c : r) {
       CellReference cr = new CellRefence(c);
       System.out.println("Cell " + cr.formatAsString() + " is " + 
                          fmt.formatCellValue(c) );
    }
 }

answered Sep 21 '12 at 09:09

Gagravarr

47,320
10
111
156

Thanks 4 ur reply........ It is useful 4 me....... Now i have a doubt if a cell is empty in intermediate the POI jar is skip that cell..... But I need to return as null – softmage99 Sep 21 '12 at 09:27
There have been lots of questions asked here about blank cells and POI, you'd probably be best off reading the answers given on those – Gagravarr Sep 21 '12 at 12:34

score 0 · Answer 2 · answered Sep 21 '12 at 11:42

//use this method for getting values from excel.It might help u.
private String getCellValue(Cell cell) {
    if (cell == null) {
        return null;
    }
    if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
        return cell.getStringCellValue();
    } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        return cell.getNumericCellValue() + "";
    } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
        return cell.getBooleanCellValue() + "";
    }else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
        return cell.getStringCellValue();
    }else if(cell.getCellType() == Cell.CELL_TYPE_ERROR){
        return cell.getErrorCellValue() + "";
    } 
    else {
        return null;
    }
}

thanks 4 ur reply..... if u try with ur sample it will return 3.0 instead of 3, if ur cell value is 3. try with Gagravarr solution, It will return 3 as it is — softmage99, Sep 26 '12 at 14:39

score 0 · Answer 3 · answered Mar 30 '19 at 14:57

use switch and cases like this code :

switch (cell.getCellType()) { 
                    case STRING: 
                        String c = cell.getStringCellValue();

                        break; 
                    case NUMERIC: 
                        int n = (int) cell.getNumericCellValue();

                        break; 
                    case BOOLEAN:
                        boolean b = cell.getBooleanCellValue();

                    break; 
                    default : 
                        }

returning decimal instead of string (POI jar)

3 Answers3

Linked

Related