aboutsummaryrefslogtreecommitdiffstats
path: root/src/testcases
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 /src/testcases
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
Diffstat (limited to 'src/testcases')
-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
3 files changed, 80 insertions, 4 deletions
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