add basic implementation of TIMEVALUE function

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1892044 13f79535-47bb-0310-9956-ffa450edef68

4 files changed, 183 insertions, 2 deletions

diff --git a/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java b/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java
index b5fdb8a8c8..7f3255b475 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/eval/FunctionEval.java
@@ -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
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/DateValue.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/DateValue.java
index 79c90cc23f..18caf0da0e 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/functions/DateValue.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/DateValue.java
@@ -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
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/TimeValue.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/TimeValue.java
new file mode 100644
index 0000000000..7afc3f0b0a
--- /dev/null
+++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/TimeValue.java
@@ -0,0 +1,84 @@
+/* ====================================================================
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to You under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+       http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.formula.functions;
+
+import org.apache.logging.log4j.LogManager;
+import org.apache.logging.log4j.Logger;
+import org.apache.poi.ss.formula.eval.*;
+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 TIMEVALUE() Excel function.<p>
+ *
+ * <b>Syntax:</b><br>
+ * <b>TIMEVALUE</b>(<b>date_text</b>)
+ * <p>
+ * 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>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 time, you must apply a times format to the cell.
+ */
+public class TimeValue extends Fixed1ArgFunction {
+
+    private static final Logger LOG = LogManager.getLogger(TimeValue.class);
+
+    @Override
+    public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval dateTimeTextArg) {
+        try {
+            String dateTimeText = OperandResolver.coerceValueToString(
+                    OperandResolver.getSingleValue(dateTimeTextArg, srcRowIndex, srcColumnIndex));
+
+            if (dateTimeText == null || dateTimeText.isEmpty()) {
+                return BlankEval.instance;
+            }
+
+            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/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)));
+    }
+}
diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestTimeValue.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestTimeValue.java
new file mode 100644
index 0000000000..480a58b8fe
--- /dev/null
+++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestTimeValue.java
@@ -0,0 +1,96 @@
+/* ====================================================================
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to You under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+       http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.formula.functions;
+
+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 TIMEVALUE()
+ */
+final class TestTimeValue {
+
+    @BeforeAll
+    public static void init() {
+        LocaleUtil.setUserLocale(Locale.US);
+    }
+
+    @AfterAll
+    public static void clear() {
+        LocaleUtil.setUserLocale(null);
+    }
+
+    @Test
+    void testTimeValue() {
+        LocaleUtil.setUserLocale(Locale.ENGLISH);
+        try {
+            confirmTimeValue(new StringEval(""));
+            confirmTimeValue(BlankEval.instance);
+
+            confirmTimeValueError(new StringEval("non-date text"));
+
+            // // EXCEL
+            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 testInvalidTimeValue() {
+        assertEquals(ErrorEval.VALUE_INVALID, invokeTimeValue(new StringEval("not-date")),
+                "not-date evals to invalid");
+        assertEquals(ErrorEval.VALUE_INVALID, invokeTimeValue(BoolEval.FALSE),
+                "false evals to invalid");
+        assertEquals(ErrorEval.VALUE_INVALID, invokeTimeValue(new NumberEval(Math.E)),
+                "Math.E evals to invalid");
+    }
+
+    private ValueEval invokeTimeValue(ValueEval text) {
+        return new TimeValue().evaluate(0, 0, 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 confirmTimeValue(ValueEval text) {
+        ValueEval result = invokeTimeValue(text);
+        assertEquals(BlankEval.class, result.getClass());
+    }
+
+    private void confirmTimeValueError(ValueEval text) {
+        ValueEval result = invokeTimeValue(text);
+        assertEquals(ErrorEval.class, result.getClass());
+        assertEquals(ErrorEval.VALUE_INVALID.getErrorCode(), ((ErrorEval) result).getErrorCode());
+    }
+}