Author: yegor
Date: Sat Mar 16 15:41:46 2019
New Revision: 1855662
URL: http://svn.apache.org/viewvc?rev=1855662&view=rev
Log:
Bug 61472: Convert date/time strings to numbers when evaluating formulas
Added:
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestDateTimeToNumberFromSpreadsheet.java
(with props)
poi/trunk/test-data/spreadsheet/DateTimeToNumberTestCases.xls (with props)
Modified:
poi/trunk/src/java/org/apache/poi/ss/formula/eval/OperandResolver.java
poi/trunk/src/java/org/apache/poi/ss/formula/functions/Value.java
poi/trunk/src/java/org/apache/poi/ss/usermodel/DateUtil.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/eval/TestOperandResolver.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/BaseTestFunctionsFromSpreadsheet.java
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/eval/OperandResolver.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/eval/OperandResolver.java?rev=1855662&r1=1855661&r2=1855662&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/eval/OperandResolver.java
(original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/eval/OperandResolver.java Sat
Mar 16 15:41:46 2019
@@ -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>
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/functions/Value.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/Value.java?rev=1855662&r1=1855661&r2=1855662&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/Value.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/Value.java Sat Mar
16 15:41:46 2019
@@ -22,6 +22,9 @@ import org.apache.poi.ss.formula.eval.Ev
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 Fixed1A
}
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 Fixed1A
*
* @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 Fixed1A
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;
+ }
+
+ }
}
Modified: poi/trunk/src/java/org/apache/poi/ss/usermodel/DateUtil.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/DateUtil.java?rev=1855662&r1=1855661&r2=1855662&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/DateUtil.java [UTF-8]
(original)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/DateUtil.java [UTF-8] Sat
Mar 16 15:41:46 2019
@@ -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;
+ }
}
Modified:
poi/trunk/src/testcases/org/apache/poi/ss/formula/eval/TestOperandResolver.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/eval/TestOperandResolver.java?rev=1855662&r1=1855661&r2=1855662&view=diff
==============================================================================
---
poi/trunk/src/testcases/org/apache/poi/ss/formula/eval/TestOperandResolver.java
(original)
+++
poi/trunk/src/testcases/org/apache/poi/ss/formula/eval/TestOperandResolver.java
Sat Mar 16 15:41:46 2019
@@ -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 e
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);
+ }
+ }
}
Modified:
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/BaseTestFunctionsFromSpreadsheet.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/BaseTestFunctionsFromSpreadsheet.java?rev=1855662&r1=1855661&r2=1855662&view=diff
==============================================================================
---
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/BaseTestFunctionsFromSpreadsheet.java
(original)
+++
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/BaseTestFunctionsFromSpreadsheet.java
Sat Mar 16 15:41:46 2019
@@ -78,6 +78,8 @@ public abstract class BaseTestFunctionsF
public int formulasRowIdx;
@Parameter(value = 4)
public HSSFFormulaEvaluator evaluator;
+ @Parameter(value = 5)
+ public int precisionColumnIndex;
@@ -92,7 +94,7 @@ public abstract class BaseTestFunctionsF
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 BaseTestFunctionsF
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 BaseTestFunctionsF
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 BaseTestFunctionsF
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 BaseTestFunctionsF
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 BaseTestFunctionsF
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){
+
+ }
}
/**
Added:
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestDateTimeToNumberFromSpreadsheet.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestDateTimeToNumberFromSpreadsheet.java?rev=1855662&view=auto
==============================================================================
---
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestDateTimeToNumberFromSpreadsheet.java
(added)
+++
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestDateTimeToNumberFromSpreadsheet.java
Sat Mar 16 15:41:46 2019
@@ -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
Propchange:
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestDateTimeToNumberFromSpreadsheet.java
------------------------------------------------------------------------------
svn:eol-style = native
Propchange:
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestDateTimeToNumberFromSpreadsheet.java
------------------------------------------------------------------------------
svn:executable = *
Added: poi/trunk/test-data/spreadsheet/DateTimeToNumberTestCases.xls
URL:
http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/DateTimeToNumberTestCases.xls?rev=1855662&view=auto
==============================================================================
Binary file - no diff available.
Propchange: poi/trunk/test-data/spreadsheet/DateTimeToNumberTestCases.xls
------------------------------------------------------------------------------
svn:executable = *
Propchange: poi/trunk/test-data/spreadsheet/DateTimeToNumberTestCases.xls
------------------------------------------------------------------------------
svn:mime-type = application/vnd.ms-excel
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@poi.apache.org
For additional commands, e-mail: commits-h...@poi.apache.org
