diff options
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()); + } +} |