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
