Bug 30302 - cell.getDateCellValue() returns 10:59:59 instead of 11:00:00
Summary: cell.getDateCellValue() returns 10:59:59 instead of 11:00:00
Status: RESOLVED WONTFIX
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 2.5-FINAL
Hardware: PC Windows XP
: P3 critical with 13 votes (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2004-07-24 06:31 UTC by Heinz Ernst
Modified: 2008-12-24 09:23 UTC (History)
1 user (show)



Attachments
XLS where POI reads dates incorrectly (190.12 KB, application/zip)
2008-12-17 06:05 UTC, Toni Helenius
Details
XLS where POI reads dates incorrectly (repack) (302.16 KB, application/zip)
2008-12-17 13:21 UTC, Toni Helenius
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Heinz Ernst 2004-07-24 06:31:05 UTC
I am loading cells on a hour base. The cells are formatted as TT.MM.JJJJ hh:mm, 
Language is German.
The Sheet looks like this
01.01.2005 00:00
01.01.2005 01:00
01.01.2005 02:00
etc.

The Cells are calculated like this: =(A1+$D$1), where d1 is a helper cell that 
holds an hour 01:00 formatted hh:mm

The Sheet has rows for one year (365*24=8760 Rows).

I loop through all the rows an fetch the date and on the row of Sun Apr 10 I 
get the Date as 10:59:59 CEST 2005 Numeric Val= 38452.458333327544, instead of 
11:00:00!
After this Date all the times are wrong, it means also, I get on that day 25 
values back and everything screws up!

Kind reagrds 
Heinz
Comment 1 Jinjo 2005-05-03 19:38:50 UTC
I am having this problem too. I use the auto generate cell function in excel. I
basic copy time of one cell and paste to other cells. The original cell import
ok, however the other cells read 1 sec less. 
Comment 2 Toni Helenius 2008-12-17 06:05:10 UTC
Created attachment 23034 [details]
XLS where POI reads dates incorrectly

cell.getDateCellValue() returns xx:59:59 instead of xx:00:00. Not on every row, rather randomly...
Comment 3 Toni Helenius 2008-12-17 06:06:03 UTC
I can confirm this bug. I attached a test XLS for you guys so you can hurry up the fixing. No need for extra 4 years anymore! :)
Comment 4 Yegor Kozlov 2008-12-17 11:37:04 UTC
I can't unzip the attached file. Please re-attach.

Yegor
Comment 5 Toni Helenius 2008-12-17 13:21:51 UTC
Created attachment 23035 [details]
XLS where POI reads dates incorrectly (repack)
Comment 6 Yegor Kozlov 2008-12-24 09:23:48 UTC
It's not a POI bug, rather a difference in Excel vs Java date format. When formatting seconds, Excel rounds the milliseconds part while Java does not.

Change format of the date cells in Excel to show milliseconds: m/d/yyyy h:mm.000

Excel outputs the top ten cells as follows:

1/1/2008 0:00.000
1/1/2008 1:00.000
1/1/2008 2:00.000
1/1/2008 2:59.985
1/1/2008 3:59.980
1/1/2008 4:59.975
1/1/2008 5:59.970
1/1/2008 6:59.965
1/1/2008 7:59.960
1/1/2008 8:59.955

Java prints the same:


        SimpleDateFormat fmt = new SimpleDateFormat("M/d/yyyy h:mm.SSS");

        HSSFWorkbook book = new HSSFWorkbook(new FileInputStream("poi_bug.xls"));
        HSSFSheet sheet = book.getSheetAt(0);

        for (int i = 1; i <= 10; i++) {
            HSSFCell cell = sheet.getRow(i).getCell(0);
            System.out.println(fmt.format(cell.getDateCellValue()) );
        }


1/1/2008 12:00.000
1/1/2008 1:00.000
1/1/2008 2:00.000
1/1/2008 2:59.985
1/1/2008 3:59.980
1/1/2008 4:59.975
1/1/2008 5:59.970
1/1/2008 6:59.965
1/1/2008 7:59.960
1/1/2008 8:59.955


Then remove milliseconds from the format:

Excel (m/d/yyyy h:mm)

1/1/2008 0:00
1/1/2008 1:00
1/1/2008 2:00
1/1/2008 3:00
1/1/2008 4:00
1/1/2008 5:00
1/1/2008 6:00
1/1/2008 7:00
1/1/2008 8:00
1/1/2008 9:00

Java (M/d/yyyy h:mm)
1/1/2008 12:00
1/1/2008 1:00
1/1/2008 2:00
1/1/2008 2:59
1/1/2008 3:59
1/1/2008 4:59
1/1/2008 5:59
1/1/2008 6:59
1/1/2008 7:59
1/1/2008 8:59

As you see, Excel date formatter rounds the milliseconds part while Java just strips it off. 

A simple workaround is to use a wrapper that rounds milliseconds:


   public static Date getRoundedDate(double date) {
       int SECONDS_PER_DAY = 24*3600;

       int wholeDays = (int)Math.floor(date);
       double ms = date - wholeDays;

       /**
        * round the time part to seconds
        */
       int millisecondsInDay = (int)Math.round(SECONDS_PER_DAY*ms)*1000;

       Calendar calendar = new GregorianCalendar(); // using default time-zone
       org.apache.poi.ss.usermodel.DateUtil.setCalendar(calendar, wholeDays, millisecondsInDay, false);
       return calendar.getTime();
   }

        HSSFWorkbook book = new HSSFWorkbook(new FileInputStream("poi_bug/poi_bug.xls"));
        HSSFSheet sheet = book.getSheetAt(0);

        SimpleDateFormat fmt = new SimpleDateFormat("M/d/yyyy h:mm");

        for (int i = 1; i <= 10; i++) {
            HSSFCell cell = sheet.getRow(i).getCell(0);
            System.out.println(fmt.format(cell.getDateCellValue()) + "\t" + fmt.format(getRoundedDate(cell.getNumericCellValue())));
        }


1/1/2008 12:00	1/1/2008 12:00
1/1/2008 1:00	1/1/2008 1:00
1/1/2008 2:00	1/1/2008 2:00
1/1/2008 2:59	1/1/2008 3:00
1/1/2008 3:59	1/1/2008 4:00
1/1/2008 4:59	1/1/2008 5:00
1/1/2008 5:59	1/1/2008 6:00
1/1/2008 6:59	1/1/2008 7:00
1/1/2008 7:59	1/1/2008 8:00
1/1/2008 8:59	1/1/2008 9:00


Regards,
Yegor