10

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.

Nandkumar Tekale
  • 16,024
  • 8
  • 58
  • 85
  • take a look at this thread, it is almost same with your problem. http://stackoverflow.com/questions/3148535/how-to-read-excel-cell-having-date-with-apache-poi –  May 16 '12 at 10:49
  • @mashhur On your link, there is no solution according to my requirement. – Nandkumar Tekale May 16 '12 at 10:58
  • I think that you receive number instead of string because the type of cell is numeric. Are you sure that numeric type is correct for password cell? – ImLearning May 16 '12 at 11:03

3 Answers3

19

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

Gagravarr
  • 47,320
  • 10
  • 111
  • 156
  • Well, I checked DataFormatter, I will format date string i.e. "41045" to "16/05/2012". But what if user enters string as "05/16/2012"? here I will have same date string "41045" but according to logic it will convert to "16/05/2012" and not to "05/16/2012". OR how do I know what format does user enter? – Nandkumar Tekale May 23 '12 at 06:11
  • When the user enters a value into the cell, a format is applied to it (either automatically or explicitly), which is what is used to render it. DataFormatter will simply render the cell based on whatever that format is. – Gagravarr May 23 '12 at 09:17
  • Hello @NandkumarTekale please, how you did, to convert "41045" to "05/16/2012".?? – ederrafo May 08 '21 at 02:18
  • @ederrafo It's days since 1st Jan 1900 or 1904, depending on Workbook settings. Just ask Apache POI for the cell's date value to have the conversion done for you – Gagravarr May 08 '21 at 15:23
  • This does not answer the question, in fact the DataFormatter doesn't do anything usefull. It is a completely obsolete class. You might as well just take the dateformat direct. To reason why you would want to take the date as a string is to take in the date correct dispite the workbook setttings – Yannick Mussche Jun 22 '23 at 10:41
14

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

DataFormatter df = new DataFormatter();
stringCellValue = df.formatCellValue(cell);
Qantas 94 Heavy
  • 15,750
  • 31
  • 68
  • 83
user3044855
  • 141
  • 1
  • 2
5

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.

nunchuckNinja
  • 89
  • 1
  • 7
  • 1
    This solution requires the user, who enters the data, to know and remember this for the sake of the program (which they might not even know about) – Attila May 18 '12 at 13:00