You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ce...@apache.org on 2019/10/09 19:13:00 UTC
svn commit: r1868198 [1/2] - in /poi/trunk/src:
java/org/apache/poi/hssf/usermodel/ java/org/apache/poi/ss/usermodel/
ooxml/java/org/apache/poi/xssf/streaming/
ooxml/java/org/apache/poi/xssf/usermodel/
testcases/org/apache/poi/hssf/usermodel/ testcases...
Author: centic
Date: Wed Oct 9 19:12:59 2019
New Revision: 1868198
URL: http://svn.apache.org/viewvc?rev=1868198&view=rev
Log:
63779 Add support for the new Java date/time API added in Java 8
Deprecate HSSFDateUtil
Closes #160 on Github
Modified:
poi/trunk/src/java/org/apache/poi/hssf/usermodel/DVConstraint.java
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java
poi/trunk/src/java/org/apache/poi/ss/usermodel/Cell.java
poi/trunk/src/java/org/apache/poi/ss/usermodel/CellBase.java
poi/trunk/src/java/org/apache/poi/ss/usermodel/DateUtil.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFCell.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestCellStyle.java
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestReadWriteChart.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestYearFracCalculatorFromSpreadsheet.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestDays360.java
poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestCell.java
poi/trunk/src/testcases/org/apache/poi/ss/usermodel/TestDateUtil.java
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/DVConstraint.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/DVConstraint.java?rev=1868198&r1=1868197&r2=1868198&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/DVConstraint.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/DVConstraint.java Wed Oct 9 19:12:59 2019
@@ -31,6 +31,7 @@ import org.apache.poi.ss.formula.ptg.Num
import org.apache.poi.ss.formula.ptg.Ptg;
import org.apache.poi.ss.formula.ptg.StringPtg;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
+import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.util.LocaleUtil;
/**
@@ -242,7 +243,7 @@ public class DVConstraint implements Dat
if (timeStr == null) {
return null;
}
- return Double.valueOf(HSSFDateUtil.convertTime(timeStr));
+ return Double.valueOf(DateUtil.convertTime(timeStr));
}
/**
* @param dateFormat pass <code>null</code> for default YYYYMMDD
@@ -254,7 +255,7 @@ public class DVConstraint implements Dat
}
Date dateVal;
if (dateFormat == null) {
- dateVal = HSSFDateUtil.parseYYYYMMDDDate(dateStr);
+ dateVal = DateUtil.parseYYYYMMDDDate(dateStr);
} else {
try {
dateVal = dateFormat.parse(dateStr);
@@ -263,7 +264,7 @@ public class DVConstraint implements Dat
+ "' using specified format '" + dateFormat + "'", e);
}
}
- return Double.valueOf(HSSFDateUtil.getExcelDate(dateVal));
+ return Double.valueOf(DateUtil.getExcelDate(dateVal));
}
public static DVConstraint createCustomFormulaConstraint(String formula) {
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java?rev=1868198&r1=1868197&r2=1868198&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java Wed Oct 9 19:12:59 2019
@@ -18,6 +18,7 @@
package org.apache.poi.hssf.usermodel;
import java.text.SimpleDateFormat;
+import java.time.LocalDateTime;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
@@ -47,6 +48,7 @@ import org.apache.poi.ss.usermodel.CellS
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.Comment;
+import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaError;
import org.apache.poi.ss.usermodel.Hyperlink;
import org.apache.poi.ss.usermodel.RichTextString;
@@ -453,11 +455,22 @@ public class HSSFCell extends CellBase {
* {@inheritDoc}
*
* <p>In HSSF, only the number of days is stored. The fractional part is ignored.</p>
- * @see HSSFDateUtil
+ * @see DateUtil
* @see org.apache.poi.ss.usermodel.DateUtil
*/
protected void setCellValueImpl(Date value) {
- setCellValue(HSSFDateUtil.getExcelDate(value, _book.getWorkbook().isUsing1904DateWindowing()));
+ setCellValue(DateUtil.getExcelDate(value, _book.getWorkbook().isUsing1904DateWindowing()));
+ }
+
+ /**
+ * {@inheritDoc}
+ *
+ * <p>In HSSF, only the number of days is stored. The fractional part is ignored.</p>
+ * @see DateUtil
+ * @see org.apache.poi.ss.usermodel.DateUtil
+ */
+ protected void setCellValueImpl(LocalDateTime value) {
+ setCellValue(DateUtil.getExcelDate(value, _book.getWorkbook().isUsing1904DateWindowing()));
}
/**
@@ -465,7 +478,7 @@ public class HSSFCell extends CellBase {
*/
@Override
protected void setCellValueImpl(Calendar value) {
- setCellValue( HSSFDateUtil.getExcelDate(value, _book.getWorkbook().isUsing1904DateWindowing()) );
+ setCellValue( DateUtil.getExcelDate(value, _book.getWorkbook().isUsing1904DateWindowing()) );
}
/**
@@ -679,9 +692,28 @@ public class HSSFCell extends CellBase {
}
double value = getNumericCellValue();
if (_book.getWorkbook().isUsing1904DateWindowing()) {
- return HSSFDateUtil.getJavaDate(value, true);
+ return DateUtil.getJavaDate(value, true);
}
- return HSSFDateUtil.getJavaDate(value, false);
+ return DateUtil.getJavaDate(value, false);
+ }
+
+ /**
+ * Get the value of the cell as a LocalDateTime.
+ * For strings we throw an exception.
+ * For blank cells we return a null.
+ * See {@link HSSFDataFormatter} for formatting
+ * this date into a string similar to how excel does.
+ */
+ public LocalDateTime getLocalDateTimeCellValue() {
+
+ if (_cellType == CellType.BLANK) {
+ return null;
+ }
+ double value = getNumericCellValue();
+ if (_book.getWorkbook().isUsing1904DateWindowing()) {
+ return DateUtil.getLocalDateTime(value, true);
+ }
+ return DateUtil.getLocalDateTime(value, false);
}
/**
@@ -853,7 +885,7 @@ public class HSSFCell extends CellBase {
default:
throw new IllegalStateException("Unexpected formula result type (" + _cellType + ")");
}
-
+
}
/**
@@ -899,7 +931,7 @@ public class HSSFCell extends CellBase {
/**
* <p>Set the style for the cell. The style should be an HSSFCellStyle created/retreived from
* the HSSFWorkbook.</p>
- *
+ *
* <p>To change the style of a cell without affecting other cells that use the same style,
* use {@link org.apache.poi.ss.util.CellUtil#setCellStyleProperties(org.apache.poi.ss.usermodel.Cell, java.util.Map)}</p>
*
@@ -1001,7 +1033,7 @@ public class HSSFCell extends CellBase {
return getCellFormula();
case NUMERIC:
//TODO apply the dataformat for this cell
- if (HSSFDateUtil.isCellDateFormatted(this)) {
+ if (DateUtil.isCellDateFormatted(this)) {
SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy", LocaleUtil.getUserLocale());
sdf.setTimeZone(LocaleUtil.getUserTimeZone());
return sdf.format(getDateCellValue());
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java?rev=1868198&r1=1868197&r2=1868198&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFDateUtil.java Wed Oct 9 19:12:59 2019
@@ -30,8 +30,10 @@ import org.apache.poi.ss.usermodel.DateU
/**
* Contains methods for dealing with Excel dates.
+ * @deprecated Use {@link DateUtil} instead
*/
-public class HSSFDateUtil extends DateUtil {
+@Deprecated
+public final class HSSFDateUtil extends DateUtil {
protected static int absoluteDay(Calendar cal, boolean use1904windowing) {
return DateUtil.absoluteDay(cal, use1904windowing);
}
Modified: poi/trunk/src/java/org/apache/poi/ss/usermodel/Cell.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/Cell.java?rev=1868198&r1=1868197&r2=1868198&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/Cell.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/Cell.java Wed Oct 9 19:12:59 2019
@@ -17,6 +17,8 @@
package org.apache.poi.ss.usermodel;
+import java.time.LocalDate;
+import java.time.LocalDateTime;
import java.util.Calendar;
import java.util.Date;
import java.util.Map;
@@ -179,6 +181,42 @@ public interface Cell {
void setCellValue(Date value);
/**
+ * <p>Converts the supplied date to its equivalent Excel numeric value and sets
+ * that into the cell.</p>
+ *
+ * <p><b>Note</b> - There is actually no 'DATE' cell type in Excel. In many
+ * cases (when entering date values), Excel automatically adjusts the
+ * <i>cell style</i> to some date format, creating the illusion that the cell
+ * data type is now something besides {@link CellType#NUMERIC}. POI
+ * does not attempt to replicate this behaviour. To make a numeric cell
+ * display as a date, use {@link #setCellStyle(CellStyle)} etc.</p>
+ *
+ * @param value the numeric value to set this cell to. For formulas we'll set the
+ * precalculated value, for numerics we'll set its value. For other types we
+ * will change the cell to a numerics cell and set its value.
+ */
+ void setCellValue(LocalDateTime value);
+
+ /**
+ * <p>Converts the supplied date to its equivalent Excel numeric value and sets
+ * that into the cell.</p>
+ *
+ * <p><b>Note</b> - There is actually no 'DATE' cell type in Excel. In many
+ * cases (when entering date values), Excel automatically adjusts the
+ * <i>cell style</i> to some date format, creating the illusion that the cell
+ * data type is now something besides {@link CellType#NUMERIC}. POI
+ * does not attempt to replicate this behaviour. To make a numeric cell
+ * display as a date, use {@link #setCellStyle(CellStyle)} etc.</p>
+ *
+ * @param value the numeric value to set this cell to. For formulas we'll set the
+ * precalculated value, for numerics we'll set its value. For other types we
+ * will change the cell to a numerics cell and set its value.
+ */
+ default void setCellValue(LocalDate value) {
+ setCellValue(value.atStartOfDay());
+ }
+
+ /**
* <p>Set a date value for the cell. Excel treats dates as numeric so you will need to format the cell as
* a date.</p>
* <p>
@@ -280,6 +318,18 @@ public interface Cell {
Date getDateCellValue();
/**
+ * Get the value of the cell as a LocalDateTime.
+ * <p>
+ * For strings we throw an exception. For blank cells we return a null.
+ * </p>
+ * @return the value of the cell as a LocalDateTime
+ * @throws IllegalStateException if the cell type returned by {@link #getCellType()} is {@link CellType#STRING}
+ * @exception NumberFormatException if the cell value isn't a parsable <code>double</code>.
+ * @see DataFormatter for formatting this date into a string similar to how excel does.
+ */
+ LocalDateTime getLocalDateTimeCellValue();
+
+ /**
* Get the value of the cell as a XSSFRichTextString
* <p>
* For numeric cells we throw an exception. For blank cells we return an empty string.
Modified: poi/trunk/src/java/org/apache/poi/ss/usermodel/CellBase.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/CellBase.java?rev=1868198&r1=1868197&r2=1868198&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/CellBase.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/CellBase.java Wed Oct 9 19:12:59 2019
@@ -24,6 +24,7 @@ import org.apache.poi.ss.util.CellRangeA
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.util.Removal;
+import java.time.LocalDateTime;
import java.util.Calendar;
import java.util.Date;
import java.util.Locale;
@@ -228,6 +229,15 @@ public abstract class CellBase implement
setCellValueImpl(value);
}
+ @Override
+ public void setCellValue(LocalDateTime value) {
+ if(value == null) {
+ setBlank();
+ return;
+ }
+ setCellValueImpl(value);
+ }
+
/**
* Implementation-specific way to set a date value.
* <code>value</code> is guaranteed to be non-null.
@@ -238,6 +248,15 @@ public abstract class CellBase implement
protected abstract void setCellValueImpl(Date value);
/**
+ * Implementation-specific way to set a date value.
+ * <code>value</code> is guaranteed to be non-null.
+ * The implementation is expected to adjust the cell type accordingly, so that after this call
+ * getCellType() or getCachedFormulaResultType() would return {@link CellType#NUMERIC}.
+ * @param value the new date to set
+ */
+ protected abstract void setCellValueImpl(LocalDateTime value);
+
+ /**
* {@inheritDoc}
*/
@Override
Modified: poi/trunk/src/java/org/apache/poi/ss/usermodel/DateUtil.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/DateUtil.java?rev=1868198&r1=1868197&r2=1868198&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/DateUtil.java [UTF-8] (original)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/DateUtil.java [UTF-8] Wed Oct 9 19:12:59 2019
@@ -18,7 +18,11 @@
package org.apache.poi.ss.usermodel;
+import org.apache.poi.ss.formula.ConditionalFormattingEvaluator;
+import org.apache.poi.util.LocaleUtil;
+
import java.time.LocalDate;
+import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
@@ -31,13 +35,11 @@ import java.util.Date;
import java.util.TimeZone;
import java.util.regex.Pattern;
-import org.apache.poi.ss.formula.ConditionalFormattingEvaluator;
-import org.apache.poi.util.LocaleUtil;
-
/**
* Contains methods for dealing with Excel dates.
*/
public class DateUtil {
+ // FIXME this should be changed to private and the class marked final once HSSFDateUtil can be removed
protected DateUtil() {
// no instances of this class
}
@@ -75,6 +77,88 @@ public class DateUtil {
.toFormatter();
/**
+ * Convert a Java Date (at UTC) to LocalDateTime.
+ * @param date the date
+ * @return LocalDateTime instance
+ */
+ public static LocalDateTime toLocalDateTime(Date date) {
+ return date.toInstant()
+ .atZone(TimeZone.getTimeZone("UTC").toZoneId()) // java.util.Date uses UTC
+ .toLocalDateTime();
+ }
+
+ /**
+ * Convert a Java Calendar (at UTC) to LocalDateTime.
+ * @param date the date
+ * @return LocalDateTime instance
+ */
+ public static LocalDateTime toLocalDateTime(Calendar date) {
+ return date.toInstant()
+ .atZone(TimeZone.getTimeZone("UTC").toZoneId()) // java.util.Date uses UTC
+ .toLocalDateTime();
+ }
+
+ /**
+ * Given a LocalDate, converts it into a double representing its internal Excel representation,
+ * which is the number of days since 1/1/1900. Fractional days represent hours, minutes, and seconds.
+ *
+ * @return Excel representation of Date (-1 if error - test for error by checking for less than 0.1)
+ * @param date the Date
+ */
+ public static double getExcelDate(LocalDate date) {
+ return getExcelDate(date, false);
+ }
+
+ /**
+ * Given a LocalDate, converts it into a double representing its internal Excel representation,
+ * which is the number of days since 1/1/1900. Fractional days represent hours, minutes, and seconds.
+ *
+ * @return Excel representation of Date (-1 if error - test for error by checking for less than 0.1)
+ * @param date the Date
+ * @param use1904windowing Should 1900 or 1904 date windowing be used?
+ */
+ public static double getExcelDate(LocalDate date, boolean use1904windowing) {
+ int year = date.getYear();
+ int dayOfYear = date.getDayOfYear();
+ int hour = 0;
+ int minute = 0;
+ int second = 0;
+ int milliSecond = 0;
+
+ return internalGetExcelDate(year, dayOfYear, hour, minute, second, milliSecond, use1904windowing);
+ }
+
+ /**
+ * Given a LocalDateTime, converts it into a double representing its internal Excel representation,
+ * which is the number of days since 1/1/1900. Fractional days represent hours, minutes, and seconds.
+ *
+ * @return Excel representation of Date (-1 if error - test for error by checking for less than 0.1)
+ * @param date the Date
+ */
+ public static double getExcelDate(LocalDateTime date) {
+ return getExcelDate(date, false);
+ }
+
+ /**
+ * Given a LocalDateTime, converts it into a double representing its internal Excel representation,
+ * which is the number of days since 1/1/1900. Fractional days represent hours, minutes, and seconds.
+ *
+ * @return Excel representation of Date (-1 if error - test for error by checking for less than 0.1)
+ * @param date the Date
+ * @param use1904windowing Should 1900 or 1904 date windowing be used?
+ */
+ public static double getExcelDate(LocalDateTime date, boolean use1904windowing) {
+ int year = date.getYear();
+ int dayOfYear = date.getDayOfYear();
+ int hour = date.getHour();
+ int minute = date.getMinute();
+ int second = date.getSecond();
+ int milliSecond = date.getNano()/1_000_000;
+
+ return internalGetExcelDate(year, dayOfYear, hour, minute, second, milliSecond, use1904windowing);
+ }
+
+ /**
* Given a Date, converts it into a double representing its internal Excel representation,
* which is the number of days since 1/1/1900. Fractional days represent hours, minutes, and seconds.
*
@@ -84,6 +168,7 @@ public class DateUtil {
public static double getExcelDate(Date date) {
return getExcelDate(date, false);
}
+
/**
* Given a Date, converts it into a double representing its internal Excel representation,
* which is the number of days since 1/1/1900. Fractional days represent hours, minutes, and seconds.
@@ -94,9 +179,17 @@ public class DateUtil {
*/
public static double getExcelDate(Date date, boolean use1904windowing) {
Calendar calStart = LocaleUtil.getLocaleCalendar();
- calStart.setTime(date); // If date includes hours, minutes, and seconds, set them to 0
- return internalGetExcelDate(calStart, use1904windowing);
+ calStart.setTime(date);
+ int year = calStart.get(Calendar.YEAR);
+ int dayOfYear = calStart.get(Calendar.DAY_OF_YEAR);
+ int hour = calStart.get(Calendar.HOUR_OF_DAY);
+ int minute = calStart.get(Calendar.MINUTE);
+ int second = calStart.get(Calendar.SECOND);
+ int milliSecond = calStart.get(Calendar.MILLISECOND);
+
+ return internalGetExcelDate(year, dayOfYear, hour, minute, second, milliSecond, use1904windowing);
}
+
/**
* Given a Date in the form of a Calendar, converts it into a double
* representing its internal Excel representation, which is the
@@ -108,15 +201,23 @@ public class DateUtil {
* @param use1904windowing Should 1900 or 1904 date windowing be used?
*/
public static double getExcelDate(Calendar date, boolean use1904windowing) {
- // Don't alter the supplied Calendar as we do our work
- return internalGetExcelDate( (Calendar)date.clone(), use1904windowing );
- }
- private static double internalGetExcelDate(Calendar date, boolean use1904windowing) {
- if ((!use1904windowing && date.get(Calendar.YEAR) < 1900) ||
- (use1904windowing && date.get(Calendar.YEAR) < 1904))
+ int year = date.get(Calendar.YEAR);
+ int dayOfYear = date.get(Calendar.DAY_OF_YEAR);
+ int hour = date.get(Calendar.HOUR_OF_DAY);
+ int minute = date.get(Calendar.MINUTE);
+ int second = date.get(Calendar.SECOND);
+ int milliSecond = date.get(Calendar.MILLISECOND);
+
+ return internalGetExcelDate(year, dayOfYear, hour, minute, second, milliSecond, use1904windowing);
+ }
+
+ private static double internalGetExcelDate(int year, int dayOfYear, int hour, int minute, int second, int milliSecond, boolean use1904windowing) {
+ if ((!use1904windowing && year < 1900) ||
+ (use1904windowing && year < 1904))
{
return BAD_DATE;
}
+
// Because of daylight time saving we cannot use
// date.getTime() - calStart.getTimeInMillis()
// as the difference in milliseconds between 00:00 and 04:00
@@ -124,14 +225,13 @@ public class DateUtil {
// be 4 hours.
// E.g. 2004-03-28 04:00 CEST - 2004-03-28 00:00 CET is 3 hours
// and 2004-10-31 04:00 CET - 2004-10-31 00:00 CEST is 5 hours
- double fraction = (((date.get(Calendar.HOUR_OF_DAY) * 60.0
- + date.get(Calendar.MINUTE)
- ) * 60.0 + date.get(Calendar.SECOND)
- ) * 1000.0 + date.get(Calendar.MILLISECOND)
+ double fraction = (((hour * 60.0
+ + minute
+ ) * 60.0 + second
+ ) * 1000.0 + milliSecond
) / DAY_MILLISECONDS;
- Calendar calStart = dayStart(date);
- double value = fraction + absoluteDay(calStart, use1904windowing);
+ double value = fraction + absoluteDay(year, dayOfYear, use1904windowing);
if (!use1904windowing && value >= 60) {
value++;
@@ -241,6 +341,86 @@ public class DateUtil {
public static Date getJavaDate(double date, boolean use1904windowing) {
return getJavaDate(date, use1904windowing, null, false);
}
+
+ /**
+ * Given an Excel date with using 1900 date windowing, and
+ * converts it to a java.time.LocalDateTime.
+ *
+ * NOTE: If the default <code>TimeZone</code> in Java uses Daylight
+ * Saving Time then the conversion back to an Excel date may not give
+ * the same value, that is the comparison
+ * <CODE>excelDate == getExcelDate(getLocalDateTime(excelDate,false))</CODE>
+ * is not always true. For example if default timezone is
+ * <code>Europe/Copenhagen</code>, on 2004-03-28 the minute after
+ * 01:59 CET is 03:00 CEST, if the excel date represents a time between
+ * 02:00 and 03:00 then it is converted to past 03:00 summer time
+ *
+ * @param date The Excel date.
+ * @return Java representation of the date, or null if date is not a valid Excel date
+ * @see java.util.TimeZone
+ */
+ public static LocalDateTime getLocalDateTime(double date) {
+ return getLocalDateTime(date, false, false);
+ }
+
+ /**
+ * Given an Excel date with either 1900 or 1904 date windowing,
+ * converts it to a java.time.LocalDateTime.
+ *
+ * Excel Dates and Times are stored without any timezone
+ * information. If you know (through other means) that your file
+ * uses a different TimeZone to the system default, you can use
+ * this version of the getJavaDate() method to handle it.
+ *
+ * @param date The Excel date.
+ * @param use1904windowing true if date uses 1904 windowing,
+ * or false if using 1900 date windowing.
+ * @return Java representation of the date, or null if date is not a valid Excel date
+ */
+ public static LocalDateTime getLocalDateTime(double date, boolean use1904windowing) {
+ return getLocalDateTime(date, use1904windowing, false);
+ }
+
+ /**
+ * Given an Excel date with either 1900 or 1904 date windowing,
+ * converts it to a java.time.LocalDateTime.
+ *
+ * Excel Dates and Times are stored without any timezone
+ * information. If you know (through other means) that your file
+ * uses a different TimeZone to the system default, you can use
+ * this version of the getJavaDate() method to handle it.
+ *
+ * @param date The Excel date.
+ * @param use1904windowing true if date uses 1904 windowing,
+ * or false if using 1900 date windowing.
+ * @param roundSeconds round to closest second
+ * @return Java representation of the date, or null if date is not a valid Excel date
+ */
+ public static LocalDateTime getLocalDateTime(double date, boolean use1904windowing, boolean roundSeconds) {
+ if (!isValidExcelDate(date)) {
+ return null;
+ }
+ int wholeDays = (int)Math.floor(date);
+ int millisecondsInDay = (int)((date - wholeDays) * DAY_MILLISECONDS + 0.5);
+
+ int startYear = 1900;
+ int dayAdjust = -1; // Excel thinks 2/29/1900 is a valid date, which it isn't
+ if (use1904windowing) {
+ startYear = 1904;
+ dayAdjust = 1; // 1904 date windowing uses 1/2/1904 as the first day
+ }
+ else if (wholeDays < 61) {
+ // Date is prior to 3/1/1900, so adjust because Excel thinks 2/29/1900 exists
+ // If Excel date == 2/29/1900, will become 3/1/1900 in Java representation
+ dayAdjust = 0;
+ }
+
+ LocalDateTime ldt = LocalDateTime.of(startYear, 1, 1, 0, 0);
+ ldt = ldt.plusDays(wholeDays+dayAdjust-1);
+ ldt = ldt.plusNanos(millisecondsInDay*1_000_000L);
+
+ return ldt;
+ }
public static void setCalendar(Calendar calendar, int wholeDays,
int millisecondsInDay, boolean use1904windowing, boolean roundSeconds) {
@@ -616,11 +796,34 @@ public class DateUtil {
*/
protected static int absoluteDay(Calendar cal, boolean use1904windowing)
{
- return cal.get(Calendar.DAY_OF_YEAR)
- + daysInPriorYears(cal.get(Calendar.YEAR), use1904windowing);
+ return absoluteDay(cal.get(Calendar.YEAR), cal.get(Calendar.DAY_OF_YEAR), use1904windowing);
+ }
+
+ /**
+ * Given a LocalDateTime, return the number of days since 1900/12/31.
+ *
+ * @return days number of days since 1900/12/31
+ * @param date the Date
+ * @exception IllegalArgumentException if date is invalid
+ */
+ protected static int absoluteDay(LocalDateTime date, boolean use1904windowing)
+ {
+ return absoluteDay(date.getYear(), date.getDayOfYear(), use1904windowing);
}
/**
+ * Given a year and day of year, return the number of days since 1900/12/31.
+ *
+ * @return days number of days since 1900/12/31
+ * @param dayOfYear the day of the year
+ * @param year the year
+ * @exception IllegalArgumentException if date is invalid
+ */
+ private static int absoluteDay(int year, int dayOfYear, boolean use1904windowing) {
+ return dayOfYear + daysInPriorYears(year, use1904windowing);
+ }
+
+ /**
* Return the number of days in prior years since 1900
*
* @return days number of days in years prior to yr.
@@ -629,7 +832,7 @@ public class DateUtil {
* @exception IllegalArgumentException if year is outside of range.
*/
- private static int daysInPriorYears(int yr, boolean use1904windowing)
+ static int daysInPriorYears(int yr, boolean use1904windowing)
{
if ((!use1904windowing && yr < 1900) || (use1904windowing && yr < 1904)) {
throw new IllegalArgumentException("'year' must be 1900 or greater");
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFCell.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFCell.java?rev=1868198&r1=1868197&r2=1868198&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFCell.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFCell.java Wed Oct 9 19:12:59 2019
@@ -19,6 +19,7 @@ package org.apache.poi.xssf.streaming;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
+import java.time.LocalDateTime;
import java.util.Calendar;
import java.util.Date;
import java.util.Map;
@@ -185,7 +186,15 @@ public class SXSSFCell extends CellBase
setCellValue(DateUtil.getExcelDate(value, date1904));
}
-
+ /**
+ * {@inheritDoc}
+ */
+ @Override
+ protected void setCellValueImpl(LocalDateTime value) {
+ boolean date1904 = getSheet().getWorkbook().isDate1904();
+ setCellValue(DateUtil.getExcelDate(value, date1904));
+ }
+
/**
* {@inheritDoc}
*/
@@ -368,6 +377,27 @@ public class SXSSFCell extends CellBase
}
/**
+ * Get the value of the cell as a LocalDateTime.
+ * <p>
+ * For strings we throw an exception. For blank cells we return a null.
+ * </p>
+ * @return the value of the cell as a date
+ * @throws IllegalStateException if the cell type returned by {@link #getCellType()} is CellType.STRING
+ * @exception NumberFormatException if the cell value isn't a parsable <code>double</code>.
+ * @see org.apache.poi.ss.usermodel.DataFormatter for formatting this date into a string similar to how excel does.
+ */
+ @Override
+ public LocalDateTime getLocalDateTimeCellValue() {
+ if (getCellType() == CellType.BLANK) {
+ return null;
+ }
+
+ double value = getNumericCellValue();
+ boolean date1904 = getSheet().getWorkbook().isDate1904();
+ return DateUtil.getLocalDateTime(value, date1904);
+ }
+
+ /**
* Get the value of the cell as a XSSFRichTextString
* <p>
* For numeric cells we throw an exception. For blank cells we return an empty string.
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java?rev=1868198&r1=1868197&r2=1868198&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java Wed Oct 9 19:12:59 2019
@@ -19,6 +19,7 @@ package org.apache.poi.xssf.usermodel;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
+import java.time.LocalDateTime;
import java.util.Calendar;
import java.util.Date;
@@ -763,6 +764,27 @@ public final class XSSFCell extends Cell
}
/**
+ * Get the value of the cell as a LocalDateTime.
+ * <p>
+ * For strings we throw an exception. For blank cells we return a null.
+ * </p>
+ * @return the value of the cell as a LocalDateTime
+ * @throws IllegalStateException if the cell type returned by {@link #getCellType()} is {@link CellType#STRING}
+ * @exception NumberFormatException if the cell value isn't a parsable <code>double</code>.
+ * @see DataFormatter for formatting this date into a string similar to how excel does.
+ */
+ @Override
+ public LocalDateTime getLocalDateTimeCellValue() {
+ if (getCellType() == CellType.BLANK) {
+ return null;
+ }
+
+ double value = getNumericCellValue();
+ boolean date1904 = getSheet().getWorkbook().isDate1904();
+ return DateUtil.getLocalDateTime(value, date1904);
+ }
+
+ /**
* {@inheritDoc}
*/
@Override
@@ -770,6 +792,15 @@ public final class XSSFCell extends Cell
boolean date1904 = getSheet().getWorkbook().isDate1904();
setCellValue(DateUtil.getExcelDate(value, date1904));
}
+
+ /**
+ * {@inheritDoc}
+ */
+ @Override
+ protected void setCellValueImpl(LocalDateTime value) {
+ boolean date1904 = getSheet().getWorkbook().isDate1904();
+ setCellValue(DateUtil.getExcelDate(value, date1904));
+ }
/**
* {@inheritDoc}
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestCellStyle.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestCellStyle.java?rev=1868198&r1=1868197&r2=1868198&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestCellStyle.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestCellStyle.java Wed Oct 9 19:12:59 2019
@@ -28,6 +28,7 @@ import org.apache.poi.ss.usermodel.Borde
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
+import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
@@ -439,7 +440,7 @@ public final class TestCellStyle extends
Cell cell = row.getCell(idxCell);
cell.getCellStyle().getDataFormatString();
if (cell.getCellType() == CellType.NUMERIC) {
- boolean isDate = HSSFDateUtil.isCellDateFormatted(cell);
+ boolean isDate = DateUtil.isCellDateFormatted(cell);
if (idxCell > 0 && isDate) {
fail("cell " + idxCell + " is not a date: " + idxCell);
}
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java?rev=1868198&r1=1868197&r2=1868198&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java Wed Oct 9 19:12:59 2019
@@ -729,7 +729,7 @@ public final class TestFormulas {
c.setCellStyle(cellStyle);
// assertEquals("Checking hour = " + hour, date.getTime().getTime(),
- // HSSFDateUtil.getJavaDate(excelDate).getTime());
+ // DateUtil.getJavaDate(excelDate).getTime());
for (int k=1; k < 100; k++) {
r=s.createRow(k);
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java?rev=1868198&r1=1868197&r2=1868198&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java Wed Oct 9 19:12:59 2019
@@ -17,25 +17,12 @@
package org.apache.poi.hssf.usermodel;
-import static java.util.Calendar.AUGUST;
-import static java.util.Calendar.FEBRUARY;
-import static java.util.Calendar.JANUARY;
-import static java.util.Calendar.JULY;
-import static java.util.Calendar.MARCH;
-import static java.util.Calendar.MAY;
-import static java.util.Calendar.OCTOBER;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertTrue;
-import static org.junit.Assert.fail;
import java.io.IOException;
-import java.text.ParseException;
-import java.text.SimpleDateFormat;
-import java.util.Calendar;
-import java.util.Date;
-import java.util.Locale;
import java.util.TimeZone;
import org.apache.poi.hssf.HSSFTestDataSamples;
@@ -63,289 +50,6 @@ public class TestHSSFDateUtil {
public static void resetTimeZone() {
LocaleUtil.setUserTimeZone(userTimeZone);
}
-
- /**
- * Checks the date conversion functions in the HSSFDateUtil class.
- */
- @Test
- public void dateConversion() {
-
- // Iteratating over the hours exposes any rounding issues.
- Calendar cal = LocaleUtil.getLocaleCalendar(2002,JANUARY,1,0,1,1);
- for (int hour = 0; hour < 23; hour++) {
- double excelDate = HSSFDateUtil.getExcelDate(cal.getTime(), false);
-
- assertEquals("Checking hour = " + hour, cal.getTime().getTime(),
- HSSFDateUtil.getJavaDate(excelDate, false).getTime());
-
- cal.add(Calendar.HOUR_OF_DAY, 1);
- }
-
- // check 1900 and 1904 date windowing conversions
- double excelDate = 36526.0;
- // with 1900 windowing, excelDate is Jan. 1, 2000
- // with 1904 windowing, excelDate is Jan. 2, 2004
- cal.set(2000,JANUARY,1,0,0,0); // Jan. 1, 2000
- Date dateIf1900 = cal.getTime();
- cal.add(Calendar.YEAR,4); // now Jan. 1, 2004
- cal.add(Calendar.DATE,1); // now Jan. 2, 2004
- Date dateIf1904 = cal.getTime();
- // 1900 windowing
- assertEquals("Checking 1900 Date Windowing",
- dateIf1900.getTime(),
- HSSFDateUtil.getJavaDate(excelDate,false).getTime());
- // 1904 windowing
- assertEquals("Checking 1904 Date Windowing",
- dateIf1904.getTime(),
- HSSFDateUtil.getJavaDate(excelDate,true).getTime());
- }
-
- /**
- * Checks the conversion of a java.util.date to Excel on a day when
- * Daylight Saving Time starts.
- */
- @Test
- public void excelConversionOnDSTStart() {
- Calendar cal = LocaleUtil.getLocaleCalendar(2004,MARCH,28,0,0,0);
- for (int hour = 0; hour < 24; hour++) {
-
- // Skip 02:00 CET as that is the Daylight change time
- // and Java converts it automatically to 03:00 CEST
- if (hour == 2) {
- continue;
- }
-
- cal.set(Calendar.HOUR_OF_DAY, hour);
- Date javaDate = cal.getTime();
-
-
- double excelDate = HSSFDateUtil.getExcelDate(javaDate, false);
- double difference = excelDate - Math.floor(excelDate);
- int differenceInHours = (int) (difference * 24 * 60 + 0.5) / 60;
- assertEquals("Checking " + hour + " hour on Daylight Saving Time start date",
- hour,
- differenceInHours);
- assertEquals("Checking " + hour + " hour on Daylight Saving Time start date",
- javaDate.getTime(),
- HSSFDateUtil.getJavaDate(excelDate, false).getTime());
- }
- }
-
- /**
- * Checks the conversion of an Excel date to a java.util.date on a day when
- * Daylight Saving Time starts.
- */
- @Test
- public void javaConversionOnDSTStart() {
- Calendar cal = LocaleUtil.getLocaleCalendar(2004,MARCH,28,0,0,0);
- double excelDate = HSSFDateUtil.getExcelDate(cal.getTime(), false);
- double oneHour = 1.0 / 24;
- double oneMinute = oneHour / 60;
- for (int hour = 0; hour < 24; hour++, excelDate += oneHour) {
-
- // Skip 02:00 CET as that is the Daylight change time
- // and Java converts it automatically to 03:00 CEST
- if (hour == 2) {
- continue;
- }
-
- cal.set(Calendar.HOUR_OF_DAY, hour);
- Date javaDate = HSSFDateUtil.getJavaDate(excelDate, false);
- double actDate = HSSFDateUtil.getExcelDate(javaDate, false);
- assertEquals("Checking " + hour + " hours on Daylight Saving Time start date",
- excelDate, actDate, oneMinute);
- }
- }
-
- /**
- * Checks the conversion of a java.util.Date to Excel on a day when
- * Daylight Saving Time ends.
- */
- @Test
- public void excelConversionOnDSTEnd() {
- Calendar cal = LocaleUtil.getLocaleCalendar(2004,OCTOBER,31,0,0,0);
- for (int hour = 0; hour < 24; hour++) {
- cal.set(Calendar.HOUR_OF_DAY, hour);
- Date javaDate = cal.getTime();
- double excelDate = HSSFDateUtil.getExcelDate(javaDate, false);
- double difference = excelDate - Math.floor(excelDate);
- int differenceInHours = (int) (difference * 24 * 60 + 0.5) / 60;
- assertEquals("Checking " + hour + " hour on Daylight Saving Time end date",
- hour,
- differenceInHours);
- assertEquals("Checking " + hour + " hour on Daylight Saving Time start date",
- javaDate.getTime(),
- HSSFDateUtil.getJavaDate(excelDate, false).getTime());
- }
- }
-
- /**
- * Checks the conversion of an Excel date to java.util.Date on a day when
- * Daylight Saving Time ends.
- */
- @Test
- public void javaConversionOnDSTEnd() {
- Calendar cal = LocaleUtil.getLocaleCalendar(2004,OCTOBER,31,0,0,0);
- double excelDate = HSSFDateUtil.getExcelDate(cal.getTime(), false);
- double oneHour = 1.0 / 24;
- double oneMinute = oneHour / 60;
- for (int hour = 0; hour < 24; hour++, excelDate += oneHour) {
- cal.set(Calendar.HOUR_OF_DAY, hour);
- Date javaDate = HSSFDateUtil.getJavaDate(excelDate, false);
- assertEquals("Checking " + hour + " hours on Daylight Saving Time start date",
- excelDate,
- HSSFDateUtil.getExcelDate(javaDate, false), oneMinute);
- }
- }
-
- /**
- * Tests that we deal with time-zones properly
- */
- @Test
- public void calendarConversion() {
- TimeZone userTZ = LocaleUtil.getUserTimeZone();
- LocaleUtil.setUserTimeZone(TimeZone.getTimeZone("CET"));
- try {
- Calendar cal = LocaleUtil.getLocaleCalendar(2002,JANUARY,1,12,1,1);
- Date expected = cal.getTime();
-
- // Iterating over the hours exposes any rounding issues.
- for (int hour = -12; hour <= 12; hour++)
- {
- String id = "GMT" + (hour < 0 ? "" : "+") + hour + ":00";
- cal.setTimeZone(TimeZone.getTimeZone(id));
- cal.set(Calendar.HOUR_OF_DAY, 12);
- double excelDate = HSSFDateUtil.getExcelDate(cal, false);
- Date javaDate = HSSFDateUtil.getJavaDate(excelDate);
-
- // Should match despite time-zone
- assertEquals("Checking timezone " + id, expected.getTime(), javaDate.getTime());
- }
-
- // Check that the timezone aware getter works correctly
- TimeZone cet = TimeZone.getTimeZone("Europe/Copenhagen");
- TimeZone ldn = TimeZone.getTimeZone("Europe/London");
-
- // 12:45 on 27th April 2012
- double excelDate = 41026.53125;
-
- // Same, no change
- assertEquals(
- HSSFDateUtil.getJavaDate(excelDate, false).getTime(),
- HSSFDateUtil.getJavaDate(excelDate, false, cet).getTime()
- );
-
- // London vs Copenhagen, should differ by an hour
- Date cetDate = HSSFDateUtil.getJavaDate(excelDate, false);
- Date ldnDate = HSSFDateUtil.getJavaDate(excelDate, false, ldn);
- assertEquals(ldnDate.getTime() - cetDate.getTime(), 60*60*1000);
- } finally {
- LocaleUtil.setUserTimeZone(userTZ);
- }
- }
-
- /**
- * Tests that we correctly detect date formats as such
- */
- @Test
- public void identifyDateFormats() {
- // First up, try with a few built in date formats
- short[] builtins = new short[] { 0x0e, 0x0f, 0x10, 0x16, 0x2d, 0x2e };
- for (short builtin : builtins) {
- String formatStr = HSSFDataFormat.getBuiltinFormat(builtin);
- assertTrue( HSSFDateUtil.isInternalDateFormat(builtin) );
- assertTrue( HSSFDateUtil.isADateFormat(builtin,formatStr) );
- }
-
- // Now try a few built-in non date formats
- builtins = new short[] { 0x01, 0x02, 0x17, 0x1f, 0x30 };
- for (short builtin : builtins) {
- String formatStr = HSSFDataFormat.getBuiltinFormat(builtin);
- assertFalse( HSSFDateUtil.isInternalDateFormat(builtin) );
- assertFalse( HSSFDateUtil.isADateFormat(builtin,formatStr) );
- }
-
- // Now for some non-internal ones
- // These come after the real ones
- int numBuiltins = HSSFDataFormat.getNumberOfBuiltinBuiltinFormats();
- assertTrue(numBuiltins < 60);
- short formatId = 60;
- assertFalse( HSSFDateUtil.isInternalDateFormat(formatId) );
-
- // Valid ones first
- String[] formats = new String[] {
- "yyyy-mm-dd", "yyyy/mm/dd", "yy/mm/dd", "yy/mmm/dd",
- "dd/mm/yy", "dd/mm/yyyy", "dd/mmm/yy",
- "dd-mm-yy", "dd-mm-yyyy",
- "DD-MM-YY", "DD-mm-YYYY",
- "dd\\-mm\\-yy", // Sometimes escaped
- "dd.mm.yyyy", "dd\\.mm\\.yyyy",
- "dd\\ mm\\.yyyy AM", "dd\\ mm\\.yyyy pm",
- "dd\\ mm\\.yyyy\\-dd", "[h]:mm:ss",
- "mm/dd/yy", "\"mm\"/\"dd\"/\"yy\"",
- "m\\/d\\/yyyy",
-
- // These crazy ones are valid
- "yyyy-mm-dd;@", "yyyy/mm/dd;@",
- "dd-mm-yy;@", "dd-mm-yyyy;@",
- // These even crazier ones are also valid
- // (who knows what they mean though...)
- "[$-F800]dddd\\,\\ mmm\\ dd\\,\\ yyyy",
- "[$-F900]ddd/mm/yyy",
- // These ones specify colours, who knew that was allowed?
- "[BLACK]dddd/mm/yy",
- "[yeLLow]yyyy-mm-dd"
- };
- for (String format : formats) {
- assertTrue(
- format + " is a date format",
- HSSFDateUtil.isADateFormat(formatId, format)
- );
- }
-
- // Then time based ones too
- formats = new String[] {
- "yyyy-mm-dd hh:mm:ss", "yyyy/mm/dd HH:MM:SS",
- "mm/dd HH:MM", "yy/mmm/dd SS",
- "mm/dd HH:MM AM", "mm/dd HH:MM am",
- "mm/dd HH:MM PM", "mm/dd HH:MM pm",
- "m/d/yy h:mm AM/PM",
- "hh:mm:ss", "hh:mm:ss.0", "mm:ss.0",
- //support elapsed time [h],[m],[s]
- "[hh]", "[mm]", "[ss]", "[SS]", "[red][hh]"
- };
- for (String format : formats) {
- assertTrue(
- format + " is a datetime format",
- HSSFDateUtil.isADateFormat(formatId, format)
- );
- }
-
- // Then invalid ones
- formats = new String[] {
- "yyyy*mm*dd",
- "0.0", "0.000",
- "0%", "0.0%",
- "[]Foo", "[BLACK]0.00%",
- "[ms]", "[Mh]",
- "", null
- };
- for (String format : formats) {
- assertFalse(
- format + " is not a date or datetime format",
- HSSFDateUtil.isADateFormat(formatId, format)
- );
- }
-
- // And these are ones we probably shouldn't allow,
- // but would need a better regexp
- formats = new String[] {
- "yyyy:mm:dd",
- };
- for (String format : formats) {
- // assertFalse( HSSFDateUtil.isADateFormat(formatId, formats[i]) );
- }
- }
/**
* Test that against a real, test file, we still do everything
@@ -374,217 +78,42 @@ public class TestHSSFDateUtil {
style = cell.getCellStyle();
assertEquals(aug_10_2007, cell.getNumericCellValue(), 0.0001);
assertEquals("d-mmm-yy", style.getDataFormatString());
- assertTrue(HSSFDateUtil.isInternalDateFormat(style.getDataFormat()));
- assertTrue(HSSFDateUtil.isADateFormat(style.getDataFormat(), style.getDataFormatString()));
- assertTrue(HSSFDateUtil.isCellDateFormatted(cell));
+ assertTrue(DateUtil.isInternalDateFormat(style.getDataFormat()));
+ assertTrue(DateUtil.isADateFormat(style.getDataFormat(), style.getDataFormatString()));
+ assertTrue(DateUtil.isCellDateFormatted(cell));
row = sheet.getRow(1);
cell = row.getCell(1);
style = cell.getCellStyle();
assertEquals(aug_10_2007, cell.getNumericCellValue(), 0.0001);
- assertFalse(HSSFDateUtil.isInternalDateFormat(cell.getCellStyle().getDataFormat()));
- assertTrue(HSSFDateUtil.isADateFormat(style.getDataFormat(), style.getDataFormatString()));
- assertTrue(HSSFDateUtil.isCellDateFormatted(cell));
+ assertFalse(DateUtil.isInternalDateFormat(cell.getCellStyle().getDataFormat()));
+ assertTrue(DateUtil.isADateFormat(style.getDataFormat(), style.getDataFormatString()));
+ assertTrue(DateUtil.isCellDateFormatted(cell));
row = sheet.getRow(2);
cell = row.getCell(1);
style = cell.getCellStyle();
assertEquals(aug_10_2007, cell.getNumericCellValue(), 0.0001);
- assertTrue(HSSFDateUtil.isInternalDateFormat(cell.getCellStyle().getDataFormat()));
- assertTrue(HSSFDateUtil.isADateFormat(style.getDataFormat(), style.getDataFormatString()));
- assertTrue(HSSFDateUtil.isCellDateFormatted(cell));
+ assertTrue(DateUtil.isInternalDateFormat(cell.getCellStyle().getDataFormat()));
+ assertTrue(DateUtil.isADateFormat(style.getDataFormat(), style.getDataFormatString()));
+ assertTrue(DateUtil.isCellDateFormatted(cell));
row = sheet.getRow(3);
cell = row.getCell(1);
style = cell.getCellStyle();
assertEquals(aug_10_2007, cell.getNumericCellValue(), 0.0001);
- assertFalse(HSSFDateUtil.isInternalDateFormat(cell.getCellStyle().getDataFormat()));
- assertTrue(HSSFDateUtil.isADateFormat(style.getDataFormat(), style.getDataFormatString()));
- assertTrue(HSSFDateUtil.isCellDateFormatted(cell));
+ assertFalse(DateUtil.isInternalDateFormat(cell.getCellStyle().getDataFormat()));
+ assertTrue(DateUtil.isADateFormat(style.getDataFormat(), style.getDataFormatString()));
+ assertTrue(DateUtil.isCellDateFormatted(cell));
row = sheet.getRow(4);
cell = row.getCell(1);
style = cell.getCellStyle();
assertEquals(aug_10_2007, cell.getNumericCellValue(), 0.0001);
- assertFalse(HSSFDateUtil.isInternalDateFormat(cell.getCellStyle().getDataFormat()));
- assertTrue(HSSFDateUtil.isADateFormat(style.getDataFormat(), style.getDataFormatString()));
- assertTrue(HSSFDateUtil.isCellDateFormatted(cell));
-
- workbook.close();
- }
-
- @Test
- public void excelDateBorderCases() throws ParseException {
- SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd", Locale.ROOT);
- df.setTimeZone(LocaleUtil.getUserTimeZone());
-
- assertEquals(1.0, DateUtil.getExcelDate(df.parse("1900-01-01")), 0.00001);
- assertEquals(31.0, DateUtil.getExcelDate(df.parse("1900-01-31")), 0.00001);
- assertEquals(32.0, DateUtil.getExcelDate(df.parse("1900-02-01")), 0.00001);
- assertEquals(/* BAD_DATE! */ -1.0, DateUtil.getExcelDate(df.parse("1899-12-31")), 0.00001);
- }
-
- @Test
- public void dateBug_2Excel() {
- assertEquals(59.0, HSSFDateUtil.getExcelDate(createDate(1900, FEBRUARY, 28), false), 0.00001);
- assertEquals(61.0, HSSFDateUtil.getExcelDate(createDate(1900, MARCH, 1), false), 0.00001);
-
- assertEquals(37315.00, HSSFDateUtil.getExcelDate(createDate(2002, FEBRUARY, 28), false), 0.00001);
- assertEquals(37316.00, HSSFDateUtil.getExcelDate(createDate(2002, MARCH, 1), false), 0.00001);
- assertEquals(37257.00, HSSFDateUtil.getExcelDate(createDate(2002, JANUARY, 1), false), 0.00001);
- assertEquals(38074.00, HSSFDateUtil.getExcelDate(createDate(2004, MARCH, 28), false), 0.00001);
- }
-
- @Test
- public void dateBug_2Java() {
- assertEquals(createDate(1900, FEBRUARY, 28), HSSFDateUtil.getJavaDate(59.0, false));
- assertEquals(createDate(1900, MARCH, 1), HSSFDateUtil.getJavaDate(61.0, false));
-
- assertEquals(createDate(2002, FEBRUARY, 28), HSSFDateUtil.getJavaDate(37315.00, false));
- assertEquals(createDate(2002, MARCH, 1), HSSFDateUtil.getJavaDate(37316.00, false));
- assertEquals(createDate(2002, JANUARY, 1), HSSFDateUtil.getJavaDate(37257.00, false));
- assertEquals(createDate(2004, MARCH, 28), HSSFDateUtil.getJavaDate(38074.00, false));
- }
+ assertFalse(DateUtil.isInternalDateFormat(cell.getCellStyle().getDataFormat()));
+ assertTrue(DateUtil.isADateFormat(style.getDataFormat(), style.getDataFormatString()));
+ assertTrue(DateUtil.isCellDateFormatted(cell));
- @Test
- public void date1904() {
- assertEquals(createDate(1904, JANUARY, 2), HSSFDateUtil.getJavaDate(1.0, true));
- assertEquals(createDate(1904, JANUARY, 1), HSSFDateUtil.getJavaDate(0.0, true));
- assertEquals(0.0, HSSFDateUtil.getExcelDate(createDate(1904, JANUARY, 1), true), 0.00001);
- assertEquals(1.0, HSSFDateUtil.getExcelDate(createDate(1904, JANUARY, 2), true), 0.00001);
-
- assertEquals(createDate(1998, JULY, 5), HSSFDateUtil.getJavaDate(35981, false));
- assertEquals(createDate(1998, JULY, 5), HSSFDateUtil.getJavaDate(34519, true));
-
- assertEquals(35981.0, HSSFDateUtil.getExcelDate(createDate(1998, JULY, 5), false), 0.00001);
- assertEquals(34519.0, HSSFDateUtil.getExcelDate(createDate(1998, JULY, 5), true), 0.00001);
- }
-
- /**
- * @param month zero based
- * @param day one based
- */
- private static Date createDate(int year, int month, int day) {
- return createDate(year, month, day, 0, 0, 0);
- }
-
- /**
- * @param month zero based
- * @param day one based
- */
- private static Date createDate(int year, int month, int day, int hour, int minute, int second) {
- Calendar c = LocaleUtil.getLocaleCalendar(year, month, day, hour, minute, second);
- return c.getTime();
- }
-
- /**
- * Check if HSSFDateUtil.getAbsoluteDay works as advertised.
- */
- @Test
- public void absoluteDay() {
- // 1 Jan 1900 is 1 day after 31 Dec 1899
- Calendar cal = LocaleUtil.getLocaleCalendar(1900,JANUARY,1,0,0,0);
- assertEquals("Checking absolute day (1 Jan 1900)", 1, HSSFDateUtil.absoluteDay(cal, false));
- // 1 Jan 1901 is 366 days after 31 Dec 1899
- cal.set(1901,JANUARY,1,0,0,0);
- assertEquals("Checking absolute day (1 Jan 1901)", 366, HSSFDateUtil.absoluteDay(cal, false));
- }
-
- @Test
- public void absoluteDayYearTooLow() {
- Calendar cal = LocaleUtil.getLocaleCalendar(1899,JANUARY,1,0,0,0);
- try {
- HSSFDateUtil.absoluteDay(cal, false);
- fail("Should fail here");
- } catch (IllegalArgumentException e) {
- // expected here
- }
-
- try {
- cal.set(1903,JANUARY,1,0,0,0);
- HSSFDateUtil.absoluteDay(cal, true);
- fail("Should fail here");
- } catch (IllegalArgumentException e) {
- // expected here
- }
- }
-
- @Test
- public void convertTime() {
-
- final double delta = 1E-7; // a couple of digits more accuracy than strictly required
- assertEquals(0.5, HSSFDateUtil.convertTime("12:00"), delta);
- assertEquals(2.0/3, HSSFDateUtil.convertTime("16:00"), delta);
- assertEquals(0.0000116, HSSFDateUtil.convertTime("0:00:01"), delta);
- assertEquals(0.7330440, HSSFDateUtil.convertTime("17:35:35"), delta);
- }
-
- @Test
- public void parseDate() {
- assertEquals(createDate(2008, AUGUST, 3), HSSFDateUtil.parseYYYYMMDDDate("2008/08/03"));
- assertEquals(createDate(1994, MAY, 1), HSSFDateUtil.parseYYYYMMDDDate("1994/05/01"));
- }
-
- /**
- * Ensure that date values *with* a fractional portion get the right time of day
- */
- @Test
- public void convertDateTime() {
- // Excel day 30000 is date 18-Feb-1982
- // 0.7 corresponds to time 16:48:00
- Date actual = HSSFDateUtil.getJavaDate(30000.7);
- Date expected = createDate(1982, 1, 18, 16, 48, 0);
- assertEquals(expected, actual);
- }
-
- /**
- * User reported a datetime issue in POI-2.5:
- * Setting Cell's value to Jan 1, 1900 without a time doesn't return the same value set to
- * @throws IOException
- */
- @Test
- public void bug19172() throws IOException
- {
- HSSFWorkbook workbook = new HSSFWorkbook();
- HSSFSheet sheet = workbook.createSheet();
- HSSFCell cell = sheet.createRow(0).createCell(0);
-
- // A pseudo special Excel dates
- Calendar cal = LocaleUtil.getLocaleCalendar(1900, JANUARY, 1);
-
- Date valueToTest = cal.getTime();
-
- cell.setCellValue(valueToTest);
-
- Date returnedValue = cell.getDateCellValue();
-
- assertEquals(valueToTest.getTime(), returnedValue.getTime());
-
workbook.close();
}
-
- /**
- * DateUtil.isCellFormatted(Cell) should not true for a numeric cell
- * that's formatted as ".0000"
- */
- @Test
- public void bug54557() throws Exception {
- final String format = ".0000";
- boolean isDateFormat = HSSFDateUtil.isADateFormat(165, format);
-
- assertEquals(false, isDateFormat);
- }
-
- @Test
- public void bug56269() throws Exception {
- double excelFraction = 41642.45833321759d;
- Calendar calNoRound = HSSFDateUtil.getJavaCalendar(excelFraction, false);
- assertEquals(10, calNoRound.get(Calendar.HOUR));
- assertEquals(59, calNoRound.get(Calendar.MINUTE));
- assertEquals(59, calNoRound.get(Calendar.SECOND));
- Calendar calRound = HSSFDateUtil.getJavaCalendar(excelFraction, false, null, true);
- assertEquals(11, calRound.get(Calendar.HOUR));
- assertEquals(0, calRound.get(Calendar.MINUTE));
- assertEquals(0, calRound.get(Calendar.SECOND));
- }
}
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestReadWriteChart.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestReadWriteChart.java?rev=1868198&r1=1868197&r2=1868198&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestReadWriteChart.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestReadWriteChart.java Wed Oct 9 19:12:59 2019
@@ -29,6 +29,7 @@ import org.apache.poi.hssf.model.Interna
import org.apache.poi.hssf.record.BOFRecord;
import org.apache.poi.hssf.record.EOFRecord;
import org.apache.poi.hssf.record.RecordBase;
+import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.util.LocaleUtil;
import org.junit.Test;
@@ -49,7 +50,7 @@ public final class TestReadWriteChart {
//System.out.println("first assertion for date");
Calendar calExp = LocaleUtil.getLocaleCalendar(2000, 0, 1, 10, 51, 2);
- Date dateAct = HSSFDateUtil.getJavaDate(firstCell.getNumericCellValue(), false);
+ Date dateAct = DateUtil.getJavaDate(firstCell.getNumericCellValue(), false);
assertEquals(calExp.getTime(), dateAct);
HSSFRow row = sheet.createRow(15);
HSSFCell cell = row.createCell(1);
Modified: poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestYearFracCalculatorFromSpreadsheet.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestYearFracCalculatorFromSpreadsheet.java?rev=1868198&r1=1868197&r2=1868198&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestYearFracCalculatorFromSpreadsheet.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestYearFracCalculatorFromSpreadsheet.java Wed Oct 9 19:12:59 2019
@@ -26,13 +26,13 @@ import java.util.Iterator;
import org.apache.poi.hssf.HSSFTestDataSamples;
import org.apache.poi.hssf.usermodel.HSSFCell;
-import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.eval.EvaluationException;
import org.apache.poi.ss.usermodel.CellType;
+import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.util.LocaleUtil;
import org.junit.Test;
@@ -97,7 +97,7 @@ public final class TestYearFracCalculato
int month = getIntCell(row, yearColumn + 1);
int day = getIntCell(row, yearColumn + 2);
Calendar c = LocaleUtil.getLocaleCalendar(year, month-1, day);
- return HSSFDateUtil.getExcelDate(c.getTime());
+ return DateUtil.getExcelDate(c.getTime());
}
private static int getIntCell(HSSFRow row, int colIx) {
Modified: poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestDays360.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestDays360.java?rev=1868198&r1=1868197&r2=1868198&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestDays360.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestDays360.java Wed Oct 9 19:12:59 2019
@@ -24,10 +24,10 @@ import java.util.Calendar;
import java.util.Date;
import java.util.Locale;
-import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.formula.eval.BoolEval;
import org.apache.poi.ss.formula.eval.NumberEval;
import org.apache.poi.ss.formula.eval.ValueEval;
+import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.util.LocaleUtil;
import org.junit.Test;
@@ -160,6 +160,6 @@ public final class TestDays360 {
}
private static NumberEval convert(Date d) {
- return new NumberEval(HSSFDateUtil.getExcelDate(d));
+ return new NumberEval(DateUtil.getExcelDate(d));
}
}
Modified: poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestCell.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestCell.java?rev=1868198&r1=1868197&r2=1868198&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestCell.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestCell.java Wed Oct 9 19:12:59 2019
@@ -30,6 +30,8 @@ import static org.mockito.Mockito.verify
import java.io.IOException;
import java.nio.charset.StandardCharsets;
+import java.time.LocalDate;
+import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
@@ -112,6 +114,26 @@ public abstract class BaseTestCell {
assertEquals(CellType.NUMERIC, cell.getCellType());
assertProhibitedValueAccess(cell, CellType.BOOLEAN, CellType.STRING,
CellType.FORMULA, CellType.ERROR);
+
+ cell.setCellErrorValue(FormulaError.NA.getCode());
+ assertEquals(FormulaError.NA.getCode(), cell.getErrorCellValue());
+ assertEquals(CellType.ERROR, cell.getCellType());
+ assertProhibitedValueAccess(cell, CellType.NUMERIC, CellType.BOOLEAN,
+ CellType.FORMULA, CellType.STRING);
+
+ LocalDateTime ldt = DateUtil.toLocalDateTime(c);
+ cell.setCellValue(ldt);
+ assertEquals(ldt, cell.getLocalDateTimeCellValue());
+ assertEquals(CellType.NUMERIC, cell.getCellType());
+ assertProhibitedValueAccess(cell, CellType.BOOLEAN, CellType.STRING,
+ CellType.FORMULA, CellType.ERROR);
+
+ LocalDate ld = ldt.toLocalDate();
+ cell.setCellValue(ld);
+ assertEquals(ld, cell.getLocalDateTimeCellValue().toLocalDate());
+ assertEquals(CellType.NUMERIC, cell.getCellType());
+ assertProhibitedValueAccess(cell, CellType.BOOLEAN, CellType.STRING,
+ CellType.FORMULA, CellType.ERROR);
cell.setCellErrorValue(FormulaError.NA.getCode());
assertEquals(FormulaError.NA.getCode(), cell.getErrorCellValue());
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org