https://issues.apache.org/bugzilla/show_bug.cgi?id=30302
Yegor Kozlov <[email protected]> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |RESOLVED Resolution| |WONTFIX --- Comment #6 from Yegor Kozlov <[email protected]> 2008-12-24 09:23:48 PST --- 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 -- Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the assignee for the bug. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
