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 2020/05/15 20:15:00 UTC
svn commit: r1877793 - in /poi/trunk:
src/java/org/apache/poi/ss/formula/eval/
src/java/org/apache/poi/ss/formula/functions/
src/testcases/org/apache/poi/hssf/usermodel/
src/testcases/org/apache/poi/ss/formula/functions/ test-data/spreadsheet/
Author: fanningpj
Date: Fri May 15 20:15:00 2020
New Revision: 1877793
URL: http://svn.apache.org/viewvc?rev=1877793&view=rev
Log:
[github-180] Add DateValue function. Thanks to Milosz Rembisz. This closes #180
Added:
poi/trunk/src/java/org/apache/poi/ss/formula/functions/DateValue.java (with props)
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestDateValue.java (with props)
poi/trunk/test-data/spreadsheet/63819.xls (with props)
Modified:
poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java?rev=1877793&r1=1877792&r2=1877793&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java Fri May 15 20:15:00 2020
@@ -180,7 +180,7 @@ public final class FunctionEval {
retval[129] = LogicalFunction.ISBLANK;
retval[130] = new T();
// 131: N
- // 140: DATEVALUE
+ retval[140] = new DateValue();
// 141: TIMEVALUE
// 142: SLN
// 143: SYD
Added: poi/trunk/src/java/org/apache/poi/ss/formula/functions/DateValue.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/DateValue.java?rev=1877793&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/DateValue.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/DateValue.java Fri May 15 20:15:00 2020
@@ -0,0 +1,135 @@
+/* ====================================================================
+ 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 java.text.DateFormatSymbols;
+import java.time.DateTimeException;
+import java.time.LocalDate;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.regex.MatchResult;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
+
+import org.apache.poi.ss.formula.eval.BlankEval;
+import org.apache.poi.ss.formula.eval.ErrorEval;
+import org.apache.poi.ss.formula.eval.EvaluationException;
+import org.apache.poi.ss.formula.eval.NumberEval;
+import org.apache.poi.ss.formula.eval.OperandResolver;
+import org.apache.poi.ss.formula.eval.ValueEval;
+import org.apache.poi.ss.usermodel.DateUtil;
+
+/**
+ * Implementation for the DATEVALUE() Excel function.<p>
+ *
+ * <b>Syntax:</b><br>
+ * <b>DATEVALUE</b>(<b>date_text</b>)<p>
+ * <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
+ * <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.
+ * <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.
+ *
+ * @author Milosz Rembisz
+ */
+public class DateValue extends Fixed1ArgFunction {
+
+ private enum Format {
+ YMD_DASHES("^(\\d{4})-(\\w+)-(\\d{1,2})$", "ymd"),
+ DMY_DASHES("^(\\d{1,2})-(\\w+)-(\\d{4})$", "dmy"),
+ MD_DASHES("^(\\w+)-(\\d{1,2})$", "md"),
+ MDY_SLASHES("^(\\w+)/(\\d{1,2})/(\\d{4})$", "mdy"),
+ YMD_SLASHES("^(\\d{4})/(\\w+)/(\\d{1,2})$", "ymd"),
+ MD_SLASHES("^(\\w+)/(\\d{1,2})$", "md");
+
+ private Pattern pattern;
+ private boolean hasYear;
+ private int yearIndex;
+ private int monthIndex;
+ private int dayIndex;
+
+ Format(String patternString, String groupOrder) {
+ this.pattern = Pattern.compile(patternString);
+ this.hasYear = groupOrder.contains("y");
+ if (hasYear) {
+ yearIndex = groupOrder.indexOf("y");
+ }
+ monthIndex = groupOrder.indexOf("m");
+ dayIndex = groupOrder.indexOf("d");
+ }
+
+ }
+
+ @Override
+ public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval dateTextArg) {
+ try {
+ String dateText = OperandResolver.coerceValueToString(
+ OperandResolver.getSingleValue(dateTextArg, srcRowIndex, srcColumnIndex));
+
+ if (dateText == null || dateText.isEmpty()) {
+ return BlankEval.instance;
+ }
+
+ for (Format format : Format.values()) {
+ Matcher matcher = format.pattern.matcher(dateText);
+ if (matcher.find()) {
+ MatchResult matchResult = matcher.toMatchResult();
+ List<String> groups = new ArrayList<>();
+ for (int i = 1; i <= matchResult.groupCount(); ++i) {
+ groups.add(matchResult.group(i));
+ }
+ int year = format.hasYear
+ ? Integer.valueOf(groups.get(format.yearIndex))
+ : LocalDate.now().getYear();
+ int month = parseMonth(groups.get(format.monthIndex));
+ int day = Integer.valueOf(groups.get(format.dayIndex));
+ return new NumberEval(DateUtil.getExcelDate(LocalDate.of(year, month, day)));
+
+ }
+ }
+ } catch (DateTimeException e) {
+ return ErrorEval.VALUE_INVALID;
+ } catch (EvaluationException e) {
+ return e.getErrorEval();
+ }
+
+ return ErrorEval.VALUE_INVALID;
+ }
+
+ private int parseMonth(String monthPart) {
+ try {
+ return Integer.valueOf(monthPart);
+ } catch (NumberFormatException ignored) {
+ }
+
+
+ String[] months = new DateFormatSymbols().getMonths();
+ for (int month = 0; month < months.length; ++month) {
+ if (months[month].toLowerCase().startsWith(monthPart.toLowerCase())) {
+ return month + 1;
+ }
+ }
+ return -1;
+ }
+}
Propchange: poi/trunk/src/java/org/apache/poi/ss/formula/functions/DateValue.java
------------------------------------------------------------------------------
svn:eol-style = native
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java?rev=1877793&r1=1877792&r2=1877793&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java Fri May 15 20:15:00 2020
@@ -2899,6 +2899,10 @@ public final class TestBugs extends Base
public void test64261() throws IOException {
simpleTest("64261.xls");
}
+ @Test
+ public void test63819() throws IOException {
+ simpleTest("63819.xls");
+ }
// a simple test which rewrites the file once and evaluates its formulas
private void simpleTest(String fileName) throws IOException {
simpleTest(fileName, null);
Added: poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestDateValue.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestDateValue.java?rev=1877793&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestDateValue.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestDateValue.java Fri May 15 20:15:00 2020
@@ -0,0 +1,78 @@
+/* ====================================================================
+ 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.BlankEval;
+import org.apache.poi.ss.formula.eval.BoolEval;
+import org.apache.poi.ss.formula.eval.ErrorEval;
+import org.apache.poi.ss.formula.eval.NumberEval;
+import org.apache.poi.ss.formula.eval.StringEval;
+import org.apache.poi.ss.formula.eval.ValueEval;
+import org.junit.Test;
+
+import static org.junit.Assert.assertEquals;
+
+/**
+ * Tests for Excel function DATEVALUE()
+ *
+ * @author Milosz Rembisz
+ */
+public final class TestDateValue {
+
+ @Test
+ public void testDateValue() {
+ 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"), 43862);
+ confirmDateValue(new StringEval("2/1/2020"), 43862);
+ confirmDateValue(new StringEval("2/1"), 43862);
+ 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);
+
+ confirmDateValue(new StringEval(""), BlankEval.instance);
+ confirmDateValue(BlankEval.instance, BlankEval.instance);
+
+ confirmDateValue(new StringEval("non-date text"), ErrorEval.VALUE_INVALID);
+ }
+
+ private ValueEval invokeDateValue(ValueEval text) {
+ return new DateValue().evaluate(0, 0, text);
+ }
+
+ private void confirmDateValue(ValueEval text, double expected) {
+ ValueEval result = invokeDateValue(text);
+ assertEquals(NumberEval.class, result.getClass());
+ assertEquals(expected, ((NumberEval) result).getNumberValue(), 0.0001);
+ }
+
+ private void confirmDateValue(ValueEval text, BlankEval expected) {
+ ValueEval result = invokeDateValue(text);
+ assertEquals(BlankEval.class, result.getClass());
+ }
+
+ private void confirmDateValue(ValueEval text, ErrorEval expected) {
+ ValueEval result = invokeDateValue(text);
+ assertEquals(ErrorEval.class, result.getClass());
+ assertEquals(expected.getErrorCode(), ((ErrorEval) result).getErrorCode());
+ }
+}
\ No newline at end of file
Propchange: poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestDateValue.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: poi/trunk/test-data/spreadsheet/63819.xls
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/63819.xls?rev=1877793&view=auto
==============================================================================
Binary file - no diff available.
Propchange: poi/trunk/test-data/spreadsheet/63819.xls
------------------------------------------------------------------------------
svn:mime-type = application/vnd.ms-excel
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org