CODEWITHSUNDEEP
javaapache-poi
Subhash N
Subhash N

Reputation: 1

Excel Cell value with Number format and Category as Currency is not retrieved through Apache POI 3.6

Hi Iam parsing a cell in excel with a particluar format for which the Data format string is coming as "$"#,##0 I want to fetch value within that cell but not able to do so, can someone help?

Currently with this code i am getting value as blank , but in excel the value is for e.g. $40.

 for (Row row : sheet) 
        {     
                Cell dataCell = row.getCell(colIndex); 

                if(dataCell!=null )
                {
                   if( row.getRowNum()==5)
                   {

                       System.out.println("Cell Value is::"+dataCell.toString());
                   }


                }

            }

Upvotes: 0

Views: 6225

Answers (3)

swamy
swamy

Reputation: 1210

Use this method for getting values in cell.It helps you 

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

Upvotes: 0

Gagravarr
Gagravarr

Reputation: 48376

You seem to want the string to be magically formatted to look the same as in Excel, without any coding on your part. You can do that in POI, but that isn't the normal usecase. People normally want to get the values as numbers, booleans etc, and process them themselves.

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

Upvotes: 1

Alfabravo
Alfabravo

Reputation: 7589

You must get the value according to the data type, not just doing it "stringly typed".

Example with HSSF:

HSSFCell cell = poiFilaActual.getCell(intColActual);
if (cell != null) {
    if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
        return cell.getRichStringCellValue().toString();
    }  else if (HSSFCell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
        return new String( (cell.getBooleanCellValue() == true ? "true" : "false") );
    } else if (HSSFCell.CELL_TYPE_BLANK == cell.getCellType()) {
        return "";
    } else if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
        if(HSSFDateUtil.isCellDateFormatted(cell)){
            return ( new SimpleDateFormat("dd/MM/yyyy").format(cell.getDateCellValue()) );
        }else{
            return new BigDecimal(cell.getNumericCellValue()).toString();
        }
    }
}

Upvotes: 0

Related Questions