You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by fa...@apache.org on 2021/04/25 17:16:05 UTC
svn commit: r1889179 - in /poi/trunk:
poi-ooxml/src/test/java/org/apache/poi/ss/tests/format/TestCellFormatPart.java
poi/src/main/java/org/apache/poi/ss/format/CellDateFormatter.java
test-data/spreadsheet/DateFormatNumberTests.xlsx
Author: fanningpj
Date: Sun Apr 25 17:16:05 2021
New Revision: 1889179
URL: http://svn.apache.org/viewvc?rev=1889179&view=rev
Log:
[github-235] Fix date formatting for number cell values. Thanks to Anthony Schott. This close #235
Added:
poi/trunk/test-data/spreadsheet/DateFormatNumberTests.xlsx (with props)
Modified:
poi/trunk/poi-ooxml/src/test/java/org/apache/poi/ss/tests/format/TestCellFormatPart.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/format/CellDateFormatter.java
Modified: poi/trunk/poi-ooxml/src/test/java/org/apache/poi/ss/tests/format/TestCellFormatPart.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi-ooxml/src/test/java/org/apache/poi/ss/tests/format/TestCellFormatPart.java?rev=1889179&r1=1889178&r2=1889179&view=diff
==============================================================================
--- poi/trunk/poi-ooxml/src/test/java/org/apache/poi/ss/tests/format/TestCellFormatPart.java (original)
+++ poi/trunk/poi-ooxml/src/test/java/org/apache/poi/ss/tests/format/TestCellFormatPart.java Sun Apr 25 17:16:05 2021
@@ -136,6 +136,17 @@ class TestCellFormatPart {
}
@Test
+ void testDateFormatNumbers() throws IOException {
+ TimeZone tz = LocaleUtil.getUserTimeZone();
+ LocaleUtil.setUserTimeZone(TimeZone.getTimeZone("CET"));
+ try {
+ runFormatTests("DateFormatNumberTests.xlsx", Cell::getNumericCellValue);
+ } finally {
+ LocaleUtil.setUserTimeZone(tz);
+ }
+ }
+
+ @Test
void testElapsedFormat() throws IOException {
runFormatTests("ElapsedFormatTests.xlsx", Cell::getNumericCellValue);
}
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/format/CellDateFormatter.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/format/CellDateFormatter.java?rev=1889179&r1=1889178&r2=1889179&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/format/CellDateFormatter.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/format/CellDateFormatter.java Sun Apr 25 17:16:05 2021
@@ -39,9 +39,11 @@ public class CellDateFormatter extends C
private final DateFormat dateFmt;
private String sFmt;
- private final Calendar EXCEL_EPOCH_CAL =
+ private static final Calendar EXCEL_EPOCH_CAL =
LocaleUtil.getLocaleCalendar(1904, 0, 1);
+ private static final double NUM_MILLISECONDS_IN_DAY = 1000 * 60 * 60 * 24;
+
private static /* final */ CellDateFormatter SIMPLE_DATE;
class DatePartHandler implements CellFormatPart.PartHandler {
@@ -177,12 +179,15 @@ public class CellDateFormatter extends C
value = 0.0;
if (value instanceof Number) {
Number num = (Number) value;
- long v = num.longValue();
+ // Convert from fractional days to milliseconds. Excel always rounds up.
+ double v = Math.round(num.doubleValue() * NUM_MILLISECONDS_IN_DAY);
if (v == 0L) {
value = EXCEL_EPOCH_CAL.getTime();
} else {
Calendar c = (Calendar)EXCEL_EPOCH_CAL.clone();
- c.add(Calendar.SECOND, (int)(v / 1000));
+ // If milliseconds were not requested in the format string, round the seconds.
+ int seconds = (int) (sFmt == null ? Math.round(v / 1000) : v / 1000);
+ c.add(Calendar.SECOND, seconds);
c.add(Calendar.MILLISECOND, (int)(v % 1000));
value = c.getTime();
}
@@ -201,6 +206,9 @@ public class CellDateFormatter extends C
int pos = toAppendTo.length();
try (Formatter formatter = new Formatter(toAppendTo, Locale.ROOT)) {
long msecs = dateObj.getTime() % 1000;
+ if (msecs < 0) {
+ msecs += 1000;
+ }
formatter.format(locale, sFmt, msecs / 1000.0);
}
toAppendTo.delete(pos, pos + 2);
Added: poi/trunk/test-data/spreadsheet/DateFormatNumberTests.xlsx
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/DateFormatNumberTests.xlsx?rev=1889179&view=auto
==============================================================================
Binary file - no diff available.
Propchange: poi/trunk/test-data/spreadsheet/DateFormatNumberTests.xlsx
------------------------------------------------------------------------------
--- svn:mime-type (added)
+++ svn:mime-type Sun Apr 25 17:16:05 2021
@@ -0,0 +1 @@
+application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org