CODEWITHSUNDEEP
javaapache-poi
Dil Se...
Dil Se...

Reputation: 877

Reading time using Apache POI HSSFCell getting error

Hi i am trying to read an excel sheet value with the HSSFCell. My excel shhet consistes of 4 columns. Date, Name, In_time and Out_time.

in my loop i have given..

            HSSFRow row = (HSSFRow) itr.next();
            Iterator cell = row.cellIterator();
            List data = new ArrayList();
            while(cell.hasNext())
            {
                HSSFCell value = (HSSFCell) cell.next();

                if (value.getCellType() == Cell.CELL_TYPE_NUMERIC) 
                {
                    if(HSSFDateUtil.isCellDateFormatted(value))
                    {
                        if(HSSFDateUtil.isADateFormat((int)value.getNumericCellValue(), "dd:mmm:yyyy"))
                        {
                            DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
                            String date = df.format(value.getDateCellValue());
                            data.add(date);
                        }
                                             }
                        else

                    {
                        data.add(value.getNumericCellValue());
                    }
                }
                else if (value.getCellType() == Cell.CELL_TYPE_STRING)
                {
                    data.add(value.getStringCellValue());
                }
                else
                {
                    data.add("");
                }
            }
            sheetData.add(data);

the fields date ans name are retriveing correctly. But when ever i tries to get the time, its giving so date value.. For example in my excel i have the time 4:05:12 AM.. But its taking the value as some date like 1899-12-30.. How can i read a date value from an excel sheet using Apache POI..

Upvotes: 1

Views: 2451

Answers (1)

Gagravarr
Gagravarr

Reputation: 48366

You seem to be explicitly formatting the cell as a "yyyy-MM-dd" date, via a call to a SimpleDateFormat. So, it's shouldn't be a surprise that you're getting exactly what you asked for, and ending up with a yyyy-MM-dd date string!

Currently, your explicit yyyy-MM-dd code, taken from your question, is the slightly odd:

 if(HSSFDateUtil.isCellDateFormatted(value))
     {
     if(HSSFDateUtil.isADateFormat((int)value.getNumericCellValue(), "dd:mmm:yyyy"))
                    {
                        DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
                        String date = df.format(value.getDateCellValue());
                        data.add(date);
                    }

If you want to format the cell so it looks the same as it does in Excel, then you need to take account of the formatting rules applied to the cell. If you do that, your code would instead look something much simpler like:

 DataFormatter formatter = new DataFormatter();

 .....

 if(HSSFDateUtil.isCellDateFormatted(value)) {
     String date = formatter.formatCell(value);
 }

If the cell has a yyyy-MM-dd format applied to it, then you'll still get something like 2012-07-20. If it's a time format instead, then you'll get that.

Upvotes: 2

Related Questions