Reading date value from excel cell as a String

Question

I am using apache poi library to read excel file. I am stuck while reading password cell. If user gives date as a password in password cell i.e. 16/05/2012. I am reading this value as "41045" while value should be "16/05/2012". This is my code :

cell = row.getCell(); // date in the cell password '16/05/2012'
switch (cell.getCellType()) {

case HSSFCell.CELL_TYPE_STRING:
    cellValue = cell.getRichStringCellValue().getString();
    break;
case HSSFCell.CELL_TYPE_NUMERIC:
    if(cellCount == TEMPLATE_PASSWORD) {// if cell is password
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cellValue = cell.getRichStringCellValue().getString(); // value read is 41045 and not "16/05/2012"
    }
    break;
default:
}

can anyone help on this?

Thanks.

Answer 1

Use the below code to get the exact date formate as u entered in excel sheet.

DataFormatter df = new DataFormatter();
stringCellValue = df.formatCellValue(cell);

Answer 2

The class you're looking for in POI is DataFormatter

When Excel writes the file, some cells are stored as literal Strings, while others are stored as numbers (including dates). For the latter, a floating point value representing the cell is stored in the file, so when you ask POI for the value of the cell that's what it actually has.

Sometimes though, especially when doing Text Extraction (but not always), you want to make the cell value look like it does in Excel. It isn't always possible to get that exactly in a String (non full space padding for example), but the DataFormatter class will get you close.

Also note that in Excel, dates are stored as floating point numbers since ~1900, which is why you're seeing a number not a date. Use DataFormatter (or similar) to have it rendered as a date

Answer 3

Prefixing the cell value with a ' while saving it into xls renders it into a string cell. So, whatever you read from that cell will be treated as a string and read as is.