DO NOT REPLY [Bug 30302] cell.getDateCellValue() returns 10:59:59 instead of 11:00:00
https://issues.apache.org/bugzilla/show_bug.cgi?id=30302 Yegor Kozlov changed: What|Removed |Added Status|NEW |RESOLVED Resolution||WONTFIX --- Comment #6 from Yegor Kozlov 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/ 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/ 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/ 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/ 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/ 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: dev-unsubscr...@poi.apache.org For additional commands, e-mail: dev-h...@poi.apache.org
[g...@vmgump]: Project poi (in module poi) failed
To whom it may engage... This is an automated request, but not an unsolicited one. For more information please visit http://gump.apache.org/nagged.html, and/or contact the folk at gene...@gump.apache.org. Project poi has an issue affecting its community integration. This issue affects 1 projects, and has been outstanding for 5 runs. The current state of this project is 'Failed', with reason 'Build Failed'. For reference only, the following projects are affected by this: - poi : POI Full details are available at: http://vmgump.apache.org/gump/public/poi/poi/index.html That said, some information snippets are provided here. The following annotations (debug/informational/warning/error messages) were provided: -DEBUG- Output [poi-ooxml-gump-24122008.jar] identifier set to output basename: [poi-ooxml-gump] -DEBUG- Output [poi-contrib-gump-24122008.jar] identifier set to output basename: [poi-contrib-gump] -DEBUG- Output [poi-scratchpad-gump-24122008.jar] identifier set to output basename: [poi-scratchpad-gump] -DEBUG- Output [poi-gump-24122008.jar] identifier set to output basename: [poi-gump] -INFO- Failed with reason build failed -DEBUG- Extracted fallback artifacts from Gump Repository The following work was performed: http://vmgump.apache.org/gump/public/poi/poi/gump_work/build_poi_poi.html Work Name: build_poi_poi (Type: Build) Work ended in a state of : Failed Elapsed: 3 mins 12 secs Command Line: /usr/lib/jvm/java-1.5.0-sun/bin/java -Djava.awt.headless=true -Xbootclasspath/p:/srv/gump/public/workspace/xml-commons/java/external/build/xml-apis.jar:/srv/gump/public/workspace/xml-xerces2/build/xercesImpl.jar:/srv/gump/public/workspace/xml-xalan/build/serializer.jar:/srv/gump/public/workspace/xml-xalan/build/xalan-unbundled.jar org.apache.tools.ant.Main -Dgump.merge=/srv/gump/public/gump/work/merge.xml -Dbuild.sysclasspath=only -Dversion.id=gump -DDSTAMP=24122008 -f build.xml gump [Working Directory: /srv/gump/public/workspace/poi] CLASSPATH: /usr/lib/jvm/java-1.5.0-sun/lib/tools.jar:/srv/gump/public/workspace/poi/ooxml-lib/xmlbeans-2.3.0.jar:/srv/gump/public/workspace/poi/ooxml-lib/jsr173_1.0_api.jar:/srv/gump/public/workspace/poi/ooxml-lib/ooxml-schemas-1.0.jar:/srv/gump/public/workspace/poi/ooxml-lib/openxml4j-1.0-beta.jar:/srv/gump/public/workspace/poi/build/classes:/srv/gump/public/workspace/poi/build/contrib-classes:/srv/gump/public/workspace/poi/build/scratchpad-classes:/srv/gump/public/workspace/poi/build/ooxml-classes:/srv/gump/public/workspace/poi/build/test-classes:/srv/gump/public/workspace/poi/build/scratchpad-test-classes:/srv/gump/public/workspace/poi/build/ooxml-test-classes:/srv/gump/public/workspace/ant/dist/lib/ant-jmf.jar:/srv/gump/public/workspace/ant/dist/lib/ant-swing.jar:/srv/gump/public/workspace/ant/dist/lib/ant-apache-resolver.jar:/srv/gump/public/workspace/ant/dist/lib/ant-apache-xalan2.jar:/srv/gump/public/workspace/ant/dist/lib/ant-trax.jar:/srv/gump/public/workspace/ant/di st/lib/ant-junit.jar:/srv/gump/public/workspace/ant/dist/lib/ant-launcher.jar:/srv/gump/public/workspace/ant/dist/lib/ant-nodeps.jar:/srv/gump/public/workspace/ant/dist/lib/ant.jar:/srv/gump/public/workspace/logging-log4j-12/dist/lib/log4j-24122008.jar:/srv/gump/public/workspace/apache-commons/logging/target/commons-logging-24122008.jar:/srv/gump/public/workspace/apache-commons/logging/target/commons-logging-api-24122008.jar:/srv/gump/public/workspace/apache-commons/beanutils/dist/commons-beanutils-24122008.jar:/srv/gump/public/workspace/apache-commons/collections/build/commons-collections-24122008.jar:/srv/gump/public/workspace/apache-commons/lang/commons-lang-24122008.jar:/srv/gump/public/workspace/junit/dist/junit-24122008.jar:/srv/gump/public/workspace/xml-commons/java/external/build/xml-apis-ext.jar:/srv/gump/public/workspace/dom4j/build/dom4j.jar - Buildfile: /srv/gump/public/workspace/poi/build.xml check-jars: fetch-jars: testexist: downloadfile: [get] Getting: http://www.ibiblio.org/maven/commons-logging/jars/commons-logging-1.1.jar [get] To: /srv/gump/public/workspace/poi/lib/commons-logging-1.1.jar [get] Error getting http://www.ibiblio.org/maven/commons-logging/jars/commons-logging-1.1.jar to /srv/gump/public/workspace/poi/lib/commons-logging-1.1.jar BUILD FAILED /srv/gump/public/workspace/poi/build.xml:334: The following error occurred while executing this line: /srv/gump/public/workspace/poi/build.xml:316: java.net.ConnectException: Connection timed out Total time: 3 minutes 11 seconds - To subscribe to this information via syndicated feeds: - RSS: http://vmgump.apache.org/gump/public/poi/poi/rss.xml - Atom: http://vmgump.apache.org/gump/public/poi/poi/atom.xml == Gump Tracking Only === Produced by Gump version 2.3. Gump Run 1824122008, vmgump:vmgump-public:1824122008 Gump E-mail Identifier (unique