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/08/06 16:53:28 UTC
svn commit: r1892044 - in /poi/trunk/poi/src:
main/java/org/apache/poi/ss/formula/eval/
main/java/org/apache/poi/ss/formula/functions/
test/java/org/apache/poi/ss/formula/functions/
Author: fanningpj
Date: Fri Aug 6 16:53:27 2021
New Revision: 1892044
URL: http://svn.apache.org/viewvc?rev=1892044&view=rev
Log:
add basic implementation of TIMEVALUE function
Added:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/TimeValue.java
- copied, changed from r1892018, poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DateValue.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestTimeValue.java
- copied, changed from r1892018, poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDateValue.java
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DateValue.java
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java?rev=1892044&r1=1892043&r2=1892044&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java Fri Aug 6 16:53:27 2021
@@ -181,7 +181,7 @@ public final class FunctionEval {
retval[130] = new T();
// 131: N
retval[140] = new DateValue();
- // 141: TIMEVALUE
+ retval[141] = new TimeValue();
// 142: SLN
// 143: SYD
// 144: DDB
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DateValue.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DateValue.java?rev=1892044&r1=1892043&r2=1892044&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DateValue.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DateValue.java Fri Aug 6 16:53:27 2021
@@ -33,7 +33,8 @@ import java.time.DateTimeException;
* <p>
* The <b>DATEVALUE</b> function converts a date that is stored as text to a serial number that Excel
* recognizes as a date. For example, the formula <b>=DATEVALUE("1/1/2008")</b> returns 39448, the
- * serial number of the date 1/1/2008. Remember, though, that your computer's system date setting may
+ * serial number of the date 1/1/2008. Any time element is ignored (see {@link TimeValue}).
+ * Remember, though, that your computer's system date setting may
* cause the results of a <b>DATEVALUE</b> function to vary from this example
* <p>
* The <b>DATEVALUE</b> function is helpful in cases where a worksheet contains dates in a text format
Copied: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/TimeValue.java (from r1892018, poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DateValue.java)
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/TimeValue.java?p2=poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/TimeValue.java&p1=poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DateValue.java&r1=1892018&r2=1892044&rev=1892044&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/DateValue.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/TimeValue.java Fri Aug 6 16:53:27 2021
@@ -20,48 +20,65 @@ package org.apache.poi.ss.formula.functi
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.ss.formula.eval.*;
-import org.apache.poi.ss.util.DateParser;
import org.apache.poi.ss.usermodel.DateUtil;
+import org.apache.poi.ss.util.DateParser;
import java.time.DateTimeException;
+import java.time.LocalDate;
+import java.util.Date;
/**
- * Implementation for the DATEVALUE() Excel function.<p>
+ * Implementation for the TIMEVALUE() Excel function.<p>
*
* <b>Syntax:</b><br>
- * <b>DATEVALUE</b>(<b>date_text</b>)
+ * <b>TIMEVALUE</b>(<b>date_text</b>)
* <p>
- * The <b>DATEVALUE</b> function converts a date that is stored as text to a serial number that Excel
- * recognizes as a date. For example, the formula <b>=DATEVALUE("1/1/2008")</b> returns 39448, the
- * serial number of the date 1/1/2008. Remember, though, that your computer's system date setting may
- * cause the results of a <b>DATEVALUE</b> function to vary from this example
+ * The <b>TIMEVALUE</b> function converts a time that is stored as text to a serial number that Excel
+ * recognizes as a date/time. For example, the formula <b>=TIMEVALUE("1/1/2008 12:00")</b> returns 0.5, the
+ * serial number of the time 12:00. The date element is ignored (see {@link DateValue}).
+ * Remember, though, that your computer's system date setting may
+ * cause the results of a <b>TIMEVALUE</b> function to vary from this example.
* <p>
- * The <b>DATEVALUE</b> function is helpful in cases where a worksheet contains dates in a text format
- * that you want to filter, sort, or format as dates, or use in date calculations.
+ * The <b>TIMEVALUE</b> function is helpful in cases where a worksheet contains dates/times in a text format
+ * that you want to filter, sort, or format as times, or use in time calculations.
* <p>
- * To view a date serial number as a date, you must apply a date format to the cell. Find links to more
- * information about displaying numbers as dates in the See Also section.
+ * To view a date serial number as a time, you must apply a times format to the cell.
*/
-public class DateValue extends Fixed1ArgFunction {
+public class TimeValue extends Fixed1ArgFunction {
- private static final Logger LOG = LogManager.getLogger(DateValue.class);
+ private static final Logger LOG = LogManager.getLogger(TimeValue.class);
@Override
- public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval dateTextArg) {
+ public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval dateTimeTextArg) {
try {
- String dateText = OperandResolver.coerceValueToString(
- OperandResolver.getSingleValue(dateTextArg, srcRowIndex, srcColumnIndex));
+ String dateTimeText = OperandResolver.coerceValueToString(
+ OperandResolver.getSingleValue(dateTimeTextArg, srcRowIndex, srcColumnIndex));
- if (dateText == null || dateText.isEmpty()) {
+ if (dateTimeText == null || dateTimeText.isEmpty()) {
return BlankEval.instance;
}
- return new NumberEval(DateUtil.getExcelDate(DateParser.parseLocalDate(dateText)));
+ try {
+ return parseTime(dateTimeText);
+ } catch (Exception e) {
+ try {
+ return parseTime("1/01/2000 " + dateTimeText);
+ } catch (Exception e2) {
+ LocalDate ld = DateParser.parseLocalDate(dateTimeText);
+ //return 0 as this is a pure date with no time element
+ return new NumberEval(0);
+ }
+ }
} catch (DateTimeException dte) {
- LOG.atInfo().log("Failed to parse date", dte);
+ LOG.atInfo().log("Failed to parse date/time", dte);
return ErrorEval.VALUE_INVALID;
} catch (EvaluationException e) {
return e.getErrorEval();
}
}
+
+ private NumberEval parseTime(String dateTimeText) throws EvaluationException {
+ double dateTimeValue = DateUtil.parseDateTime(dateTimeText);
+ return new NumberEval(dateTimeValue - DateUtil.getExcelDate(DateParser.parseLocalDate(dateTimeText)));
+ }
}
Copied: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestTimeValue.java (from r1892018, poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDateValue.java)
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestTimeValue.java?p2=poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestTimeValue.java&p1=poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDateValue.java&r1=1892018&r2=1892044&rev=1892044&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestDateValue.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestTimeValue.java Fri Aug 6 16:53:27 2021
@@ -17,24 +17,20 @@
package org.apache.poi.ss.formula.functions;
-import static org.junit.jupiter.api.Assertions.assertEquals;
-
-import java.time.LocalDate;
-import java.time.Month;
-import java.time.Year;
-import java.time.temporal.ChronoUnit;
-import java.util.Locale;
-
import org.apache.poi.ss.formula.eval.*;
import org.apache.poi.util.LocaleUtil;
import org.junit.jupiter.api.AfterAll;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Test;
+import java.util.Locale;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
/**
- * Tests for Excel function DATEVALUE()
+ * Tests for Excel function TIMEVALUE()
*/
-final class TestDateValue {
+final class TestTimeValue {
@BeforeAll
public static void init() {
@@ -47,71 +43,53 @@ final class TestDateValue {
}
@Test
- void testDateValue() {
+ void testTimeValue() {
LocaleUtil.setUserLocale(Locale.ENGLISH);
try {
- int days1900 = (int)ChronoUnit.DAYS.between(
- LocalDate.of(1899, Month.DECEMBER, 31),
- LocalDate.of(Year.now(LocaleUtil.getUserTimeZone().toZoneId()).getValue(), Month.FEBRUARY, 1)
- )+1;
- confirmDateValue(new StringEval("2020-02-01"), 43862);
- confirmDateValue(new StringEval("01-02-2020"), 43862);
- confirmDateValue(new StringEval("2020-FEB-01"), 43862);
- confirmDateValue(new StringEval("2020-Feb-01"), 43862);
- confirmDateValue(new StringEval("2020-FEBRUARY-01"), 43862);
- confirmDateValue(new StringEval("FEB-01"), days1900);
- confirmDateValue(new StringEval("2/1/2020"), 43862);
- confirmDateValue(new StringEval("2/1"), days1900);
- confirmDateValue(new StringEval("2020/2/1"), 43862);
- confirmDateValue(new StringEval("2020/FEB/1"), 43862);
- confirmDateValue(new StringEval("FEB/1/2020"), 43862);
- confirmDateValue(new StringEval("2020/02/01"), 43862);
+ confirmTimeValue(new StringEval(""));
+ confirmTimeValue(BlankEval.instance);
- confirmDateValue(new StringEval(""));
- confirmDateValue(BlankEval.instance);
-
- confirmDateValueError(new StringEval("non-date text"));
+ confirmTimeValueError(new StringEval("non-date text"));
// // EXCEL
- confirmDateValue(new StringEval("8/22/2011"), 40777); // Serial number of a date entered as text.
- confirmDateValue(new StringEval("8/22/2011 12:00"), 40777); // Serial number of a date entered as text.
- confirmDateValue(new StringEval("22-MAY-2011"), 40685); // Serial number of a date entered as text.
- confirmDateValue(new StringEval("2011/02/23"), 40597); // Serial number of a date entered as text.
-
- // LibreOffice compatibility
- confirmDateValue(new StringEval("1954-07-20"), 19925);
+ confirmTimeValue(new StringEval("8/22/2011"), 0); // Serial number of a time entered as text.
+ confirmTimeValue(new StringEval("8/22/2011 12:00"), 0.5); // Serial number of a time entered as text.
+ confirmTimeValue(new StringEval("1/01/2000 06:00"), 0.25); // Serial number of a time entered as text.
+ confirmTimeValue(new StringEval("1/01/2000 6:00 PM"), 0.75); // Serial number of a time entered as text.
+ confirmTimeValue(new StringEval("12:00"), 0.5); // Serial number of a time entered as text.
+ confirmTimeValue(new StringEval("6:00 PM"), 0.75); // Serial number of a time entered as text.
} finally {
LocaleUtil.setUserLocale(null);
}
}
@Test
- void testInvalidDateValue() {
- assertEquals(ErrorEval.VALUE_INVALID, invokeDateValue(new StringEval("not-date")),
+ void testInvalidTimeValue() {
+ assertEquals(ErrorEval.VALUE_INVALID, invokeTimeValue(new StringEval("not-date")),
"not-date evals to invalid");
- assertEquals(ErrorEval.VALUE_INVALID, invokeDateValue(BoolEval.FALSE),
+ assertEquals(ErrorEval.VALUE_INVALID, invokeTimeValue(BoolEval.FALSE),
"false evals to invalid");
- assertEquals(ErrorEval.VALUE_INVALID, invokeDateValue(new NumberEval(Math.E)),
+ assertEquals(ErrorEval.VALUE_INVALID, invokeTimeValue(new NumberEval(Math.E)),
"Math.E evals to invalid");
}
- private ValueEval invokeDateValue(ValueEval text) {
- return new DateValue().evaluate(0, 0, text);
+ private ValueEval invokeTimeValue(ValueEval text) {
+ return new TimeValue().evaluate(0, 0, text);
}
- private void confirmDateValue(ValueEval text, double expected) {
- ValueEval result = invokeDateValue(text);
+ private void confirmTimeValue(ValueEval text, double expected) {
+ ValueEval result = invokeTimeValue(text);
assertEquals(NumberEval.class, result.getClass());
assertEquals(expected, ((NumberEval) result).getNumberValue(), 0.0001);
}
- private void confirmDateValue(ValueEval text) {
- ValueEval result = invokeDateValue(text);
+ private void confirmTimeValue(ValueEval text) {
+ ValueEval result = invokeTimeValue(text);
assertEquals(BlankEval.class, result.getClass());
}
- private void confirmDateValueError(ValueEval text) {
- ValueEval result = invokeDateValue(text);
+ private void confirmTimeValueError(ValueEval text) {
+ ValueEval result = invokeTimeValue(text);
assertEquals(ErrorEval.class, result.getClass());
assertEquals(ErrorEval.VALUE_INVALID.getErrorCode(), ((ErrorEval) result).getErrorCode());
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org