returning decimal instead of string (POI jar)

returning decimal instead of string (POI jar)

apache poi
apache poi dataformatter example
apache poi number format example
apache poi dataformatter date format
apache poi convert cell value to string
apache poi number to string
how to set cell format in excel using java poi
apache poi string format

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

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

java - returning decimal instead of string (POI jar), 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� We can use DataFormatter to fetch the string value of an Excel cell. It can get a formatted string representation of the value stored in a cell. For example, if a cell's numeric value is 1.234, and the format rule of this cell is two decimal points, we'll get string representation “1.23”:

//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;

DataFormatter (POI API Documentation) - Apache POI, For Excel formats that are not compatible with DecimalFormat, you can provide your Create and return a Format based on the format string from a cell's style. Returns the raw, underlying ooxml value for the cell If the cell contains a string, then this value is an index into the shared string table, pointing to the actual string value. Otherwise, the value of the cell is expressed directly in this element. Cells containing formulas express the last calculated result of the formula in this element.

use switch and cases like this code :

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

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

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

                    default : 

NumberToTextConverter (POI API Documentation), Conversion to string (as handled here); Rendering numerical quantities in the cell grid. Conversion from text� HSSFCell returns numeric value instead of Strings (for numeric strings) When I read the spreadsheet (cell format as text), it has numbers with like: 100, 10000, 0.967, etc. When I read them using POI, I get 100.0, 10000.0, 0.97, etc. java code examples , Best Java code snippets using Enables excel style rounding mode (round half up) on the * Decimal Format given. formatCellValue(cell); //Returns the formatted value of a cell as a String regardless of the cell� The POI source release as well as the pre-built binary deployment packages are listed below. Pre-built versions of all POI components are available in the central Maven repository under Group ID "org.apache.poi" and Version "4.1.2". Binary Distribution

Customizing Formats (The Java™ Tutorials > Internationalization , You specify the formatting properties of DecimalFormat with a pattern String . a double value as an argument and returns the formatted number in a String : and trailing zeros, because the 0 character is used instead of the pound sign (#). Returns a string representation of the cell This method returns a simple representation, anything more complex should be in user code, with knowledge of the semantics of the sheet being processed. Formula cells return the formula string, rather than the formula result.

Cannot get a STRING value from a NUMERIC cell. How can I , This exception is occurred when code try to read Numeric value as string. Apache POI will not convert from Numeric to String. This returns the default string format for each cell type, and it won't execute Excel formulas (it returns the formula� DecimalFormat does not decide whether to return a Double or a Long based on the presence of a decimal separator in the source string. Doing so would prevent integers that overflow the mantissa of a double, such as "-9,223,372,036,854,775,808.00" , from being parsed accurately.