add basic implementation of TIMEVALUE function

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

3 files changed, 87 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)));
+    }
+}