aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorYegor Kozlov <yegor@apache.org>2019-03-16 15:41:46 +0000
committerYegor Kozlov <yegor@apache.org>2019-03-16 15:41:46 +0000
commit6ef332b48eab1fe1e133e7617b587ad2048c1a03 (patch)
tree0fe8385f681819436cc6e1e6057128e0d9b57e8f
parentee83ce5fcd19ae18713bfbcf9d6f508aff466eeb (diff)
downloadpoi-6ef332b48eab1fe1e133e7617b587ad2048c1a03.tar.gz
poi-6ef332b48eab1fe1e133e7617b587ad2048c1a03.zip
Bug 61472: Convert date/time strings to numbers when evaluating formulas
git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1855662 13f79535-47bb-0310-9956-ffa450edef68
-rw-r--r--src/java/org/apache/poi/ss/formula/eval/OperandResolver.java16
-rw-r--r--src/java/org/apache/poi/ss/formula/functions/Value.java16
-rw-r--r--src/java/org/apache/poi/ss/usermodel/DateUtil.java32
-rw-r--r--src/testcases/org/apache/poi/ss/formula/eval/TestOperandResolver.java28
-rw-r--r--src/testcases/org/apache/poi/ss/formula/functions/BaseTestFunctionsFromSpreadsheet.java27
-rwxr-xr-xsrc/testcases/org/apache/poi/ss/formula/functions/TestDateTimeToNumberFromSpreadsheet.java29
-rwxr-xr-xtest-data/spreadsheet/DateTimeToNumberTestCases.xlsbin0 -> 34304 bytes
7 files changed, 142 insertions, 6 deletions
diff --git a/src/java/org/apache/poi/ss/formula/eval/OperandResolver.java b/src/java/org/apache/poi/ss/formula/eval/OperandResolver.java
index 1c6e604a13..162410f159 100644
--- a/src/java/org/apache/poi/ss/formula/eval/OperandResolver.java
+++ b/src/java/org/apache/poi/ss/formula/eval/OperandResolver.java
@@ -18,8 +18,10 @@
package org.apache.poi.ss.formula.eval;
import org.apache.poi.ss.formula.EvaluationCell;
+import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellRangeAddress;
+import java.time.DateTimeException;
import java.util.regex.Pattern;
/**
@@ -258,7 +260,9 @@ public final class OperandResolver {
return ((NumericValueEval)ev).getNumberValue();
}
if (ev instanceof StringEval) {
- Double dd = parseDouble(((StringEval) ev).getStringValue());
+ String sval = ((StringEval) ev).getStringValue();
+ Double dd = parseDouble(sval);
+ if(dd == null) dd = parseDateTime(sval);
if (dd == null) {
throw EvaluationException.invalidValue();
}
@@ -300,6 +304,16 @@ public final class OperandResolver {
}
+ public static Double parseDateTime(String pText) {
+
+ try {
+ return DateUtil.parseDateTime(pText);
+ } catch (DateTimeException e) {
+ return null;
+ }
+
+ }
+
/**
* @param ve must be a <tt>NumberEval</tt>, <tt>StringEval</tt>, <tt>BoolEval</tt>, or <tt>BlankEval</tt>
* @return the converted string value. never <code>null</code>
diff --git a/src/java/org/apache/poi/ss/formula/functions/Value.java b/src/java/org/apache/poi/ss/formula/functions/Value.java
index 4280111b99..176a776906 100644
--- a/src/java/org/apache/poi/ss/formula/functions/Value.java
+++ b/src/java/org/apache/poi/ss/formula/functions/Value.java
@@ -22,6 +22,9 @@ 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;
+
+import java.time.DateTimeException;
/**
* Implementation for Excel VALUE() function.<p>
@@ -48,6 +51,7 @@ public final class Value extends Fixed1ArgFunction {
}
String strText = OperandResolver.coerceValueToString(veText);
Double result = convertTextToNumber(strText);
+ if(result == null) result = parseDateTime(strText);
if (result == null) {
return ErrorEval.VALUE_INVALID;
}
@@ -59,7 +63,7 @@ public final class Value extends Fixed1ArgFunction {
*
* @return <code>null</code> if there is any problem converting the text
*/
- private static Double convertTextToNumber(String strText) {
+ public static Double convertTextToNumber(String strText) {
boolean foundCurrency = false;
boolean foundUnaryPlus = false;
boolean foundUnaryMinus = false;
@@ -189,4 +193,14 @@ public final class Value extends Fixed1ArgFunction {
double result = foundUnaryMinus ? -d : d;
return foundPercentage ? result/100. : result;
}
+
+ public static Double parseDateTime(String pText) {
+
+ try {
+ return DateUtil.parseDateTime(pText);
+ } catch (DateTimeException e) {
+ return null;
+ }
+
+ }
}
diff --git a/src/java/org/apache/poi/ss/usermodel/DateUtil.java b/src/java/org/apache/poi/ss/usermodel/DateUtil.java
index 60cc74500a..82cf7f6e5c 100644
--- a/src/java/org/apache/poi/ss/usermodel/DateUtil.java
+++ b/src/java/org/apache/poi/ss/usermodel/DateUtil.java
@@ -18,6 +18,14 @@
package org.apache.poi.ss.usermodel;
+import java.time.LocalDate;
+import java.time.LocalTime;
+import java.time.ZoneId;
+import java.time.format.DateTimeFormatter;
+import java.time.format.DateTimeFormatterBuilder;
+import java.time.temporal.ChronoField;
+import java.time.temporal.TemporalAccessor;
+import java.time.temporal.TemporalQueries;
import java.util.Calendar;
import java.util.Date;
import java.util.TimeZone;
@@ -58,6 +66,14 @@ public class DateUtil {
// for format which start with "[DBNum1]" or "[DBNum2]" or "[DBNum3]" could be a Chinese date
private static final Pattern date_ptrn5 = Pattern.compile("^\\[DBNum(1|2|3)\\]");
+ private static final DateTimeFormatter dateTimeFormats = new DateTimeFormatterBuilder()
+ .appendPattern("[dd MMM[ yyyy]][[ ]h:m[:s] a][[ ]H:m[:s]]")
+ .appendPattern("[[yyyy ]dd-MMM[-yyyy]][[ ]h:m[:s] a][[ ]H:m[:s]]")
+ .appendPattern("[M/dd[/yyyy]][[ ]h:m[:s] a][[ ]H:m[:s]]")
+ .appendPattern("[[yyyy/]M/dd][[ ]h:m[:s] a][[ ]H:m[:s]]")
+ .parseDefaulting(ChronoField.YEAR_OF_ERA, Calendar.getInstance().get(Calendar.YEAR))
+ .toFormatter();
+
/**
* Given a Date, converts it into a double representing its internal Excel representation,
* which is the number of days since 1/1/1900. Fractional days represent hours, minutes, and seconds.
@@ -724,4 +740,20 @@ public class DateUtil {
}
return result;
}
+
+ public static Double parseDateTime(String str){
+ TemporalAccessor tmp = dateTimeFormats.parse(str.replaceAll("\\s+", " "));
+ LocalTime time = tmp.query(TemporalQueries.localTime());
+ LocalDate date = tmp.query(TemporalQueries.localDate());
+ if(time == null && date == null) return null;
+
+ double tm = 0;
+ if(date != null) {
+ Date d = Date.from(date.atStartOfDay().atZone(ZoneId.systemDefault()).toInstant());
+ tm = DateUtil.getExcelDate(d);
+ }
+ if(time != null) tm += 1.0*time.toSecondOfDay()/SECONDS_PER_DAY;
+
+ return tm;
+ }
}
diff --git a/src/testcases/org/apache/poi/ss/formula/eval/TestOperandResolver.java b/src/testcases/org/apache/poi/ss/formula/eval/TestOperandResolver.java
index 72fb29ca63..c329672265 100644
--- a/src/testcases/org/apache/poi/ss/formula/eval/TestOperandResolver.java
+++ b/src/testcases/org/apache/poi/ss/formula/eval/TestOperandResolver.java
@@ -20,6 +20,9 @@ package org.apache.poi.ss.formula.eval;
import junit.framework.AssertionFailedError;
import junit.framework.TestCase;
+import java.util.LinkedHashMap;
+import java.util.Map;
+
/**
* Tests for <tt>OperandResolver</tt>
*
@@ -74,4 +77,29 @@ public final class TestOperandResolver extends TestCase {
assertNull(OperandResolver.parseDouble(value));
}
}
+
+ public void testCoerceDateStringToNumber() throws EvaluationException {
+ Map<String, Double> values = new LinkedHashMap<>();
+ values.put("2019/1/18", 43483.);
+ values.put("01/18/2019", 43483.);
+ values.put("18 Jan 2019", 43483.);
+ values.put("18-Jan-2019", 43483.);
+
+ for (String str : values.keySet()) {
+ assertEquals(OperandResolver.coerceValueToDouble(new StringEval(str)), values.get(str), 0.00001);
+ }
+ }
+
+ public void testCoerceTimeStringToNumber() throws EvaluationException {
+ Map<String, Double> values = new LinkedHashMap<>();
+ values.put("00:00", 0.0);
+ values.put("12:00", 0.5);
+ values.put("15:43:09", 0.654965278);
+ values.put("15:43", 0.654861111);
+ values.put("3:43 PM", 0.654861111);
+
+ for (String str : values.keySet()) {
+ assertEquals(OperandResolver.coerceValueToDouble(new StringEval(str)), values.get(str), 0.00001);
+ }
+ }
}
diff --git a/src/testcases/org/apache/poi/ss/formula/functions/BaseTestFunctionsFromSpreadsheet.java b/src/testcases/org/apache/poi/ss/formula/functions/BaseTestFunctionsFromSpreadsheet.java
index 88b52493ea..7b899350c1 100644
--- a/src/testcases/org/apache/poi/ss/formula/functions/BaseTestFunctionsFromSpreadsheet.java
+++ b/src/testcases/org/apache/poi/ss/formula/functions/BaseTestFunctionsFromSpreadsheet.java
@@ -78,6 +78,8 @@ public abstract class BaseTestFunctionsFromSpreadsheet {
public int formulasRowIdx;
@Parameter(value = 4)
public HSSFFormulaEvaluator evaluator;
+ @Parameter(value = 5)
+ public int precisionColumnIndex;
@@ -92,7 +94,7 @@ public abstract class BaseTestFunctionsFromSpreadsheet {
HSSFSheet sheet = workbook.getSheetAt(sheetIdx);
processFunctionGroup(data, sheet, SS.START_TEST_CASES_ROW_INDEX, filename);
}
-
+
workbook.close();
return data;
@@ -101,6 +103,14 @@ public abstract class BaseTestFunctionsFromSpreadsheet {
private static void processFunctionGroup(List<Object[]> data, HSSFSheet sheet, final int startRowIndex, String filename) {
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet.getWorkbook());
+ int precisionColumnIndex = -1;
+ HSSFRow precisionRow = sheet.getWorkbook().getSheetAt(0).getRow(11);
+ HSSFCell precisionCell = precisionRow == null ? null : precisionRow.getCell(0);
+ if(precisionCell != null && precisionCell.getCellType() == CellType.NUMERIC){
+ precisionColumnIndex = (int)precisionCell.getNumericCellValue();
+ }
+
+
String currentGroupComment = "";
final int maxRows = sheet.getLastRowNum()+1;
for(int rowIndex=startRowIndex; rowIndex<maxRows; rowIndex++) {
@@ -131,7 +141,7 @@ public abstract class BaseTestFunctionsFromSpreadsheet {
testName = evalCell.getCellFormula();
}
- data.add(new Object[]{testName, filename, sheet, rowIndex, evaluator});
+ data.add(new Object[]{testName, filename, sheet, rowIndex, evaluator, precisionColumnIndex});
}
fail("Missing end marker '" + SS.TEST_CASES_END_MARKER + "' on sheet '" + sheet.getSheetName() + "'");
}
@@ -141,7 +151,8 @@ public abstract class BaseTestFunctionsFromSpreadsheet {
HSSFRow r = sheet.getRow(formulasRowIdx);
HSSFCell evalCell = r.getCell(SS.COLUMN_INDEX_EVALUATION);
HSSFCell expectedCell = r.getCell(SS.COLUMN_INDEX_EXPECTED_RESULT);
-
+ HSSFCell precisionCell = r.getCell(precisionColumnIndex);
+
CellReference cr = new CellReference(sheet.getSheetName(), formulasRowIdx, evalCell.getColumnIndex(), false, false);
String msg = String.format(Locale.ROOT, "In %s %s {=%s} '%s'"
, filename, cr.formatAsString(), evalCell.getCellFormula(), testName);
@@ -175,7 +186,9 @@ public abstract class BaseTestFunctionsFromSpreadsheet {
case FORMULA: // will never be used, since we will call method after formula evaluation
fail("Cannot expect formula as result of formula evaluation: " + msg);
case NUMERIC:
- assertEquals(expectedCell.getNumericCellValue(), actualValue.getNumberValue(), 0.0);
+ double precision = precisionCell != null && precisionCell.getCellType() == CellType.NUMERIC
+ ? precisionCell.getNumericCellValue() : 0.0;
+ assertEquals(expectedCell.getNumericCellValue(), actualValue.getNumberValue(), precision);
break;
case STRING:
assertEquals(msg, expectedCell.getRichStringCellValue().getString(), actualValue.getStringValue());
@@ -197,6 +210,12 @@ public abstract class BaseTestFunctionsFromSpreadsheet {
HSSFSheet sheet = workbook.getSheetAt(0);
String specifiedClassName = sheet.getRow(2).getCell(0).getRichStringCellValue().getString();
assertEquals("Test class name in spreadsheet comment", clazz.getName(), specifiedClassName);
+
+ HSSFRow precisionRow = sheet.getRow(11);
+ HSSFCell precisionCell = precisionRow == null ? null : precisionRow.getCell(0);
+ if(precisionCell != null && precisionCell.getCellType() == CellType.NUMERIC){
+
+ }
}
/**
diff --git a/src/testcases/org/apache/poi/ss/formula/functions/TestDateTimeToNumberFromSpreadsheet.java b/src/testcases/org/apache/poi/ss/formula/functions/TestDateTimeToNumberFromSpreadsheet.java
new file mode 100755
index 0000000000..87f709d91d
--- /dev/null
+++ b/src/testcases/org/apache/poi/ss/formula/functions/TestDateTimeToNumberFromSpreadsheet.java
@@ -0,0 +1,29 @@
+/* ====================================================================
+ 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.junit.runners.Parameterized.Parameters;
+
+import java.util.Collection;
+
+public class TestDateTimeToNumberFromSpreadsheet extends BaseTestFunctionsFromSpreadsheet {
+ @Parameters(name="{0}")
+ public static Collection<Object[]> data() throws Exception {
+ return data(TestDateTimeToNumberFromSpreadsheet.class, "DateTimeToNumberTestCases.xls");
+ }
+} \ No newline at end of file
diff --git a/test-data/spreadsheet/DateTimeToNumberTestCases.xls b/test-data/spreadsheet/DateTimeToNumberTestCases.xls
new file mode 100755
index 0000000000..7c235e64fd
--- /dev/null
+++ b/test-data/spreadsheet/DateTimeToNumberTestCases.xls
Binary files differ