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
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.
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...
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! :)
I can't unzip the attached file. Please re-attach. Yegor
Created attachment 23035 [details] XLS where POI reads dates incorrectly (repack)
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