Author: fanningpj
Date: Sat Jun 6 09:30:30 2020
New Revision: 1878541
URL: http://svn.apache.org/viewvc?rev=1878541&view=rev
Log:
[github-181] make Value function work with arrays. Thanks to Miłosz Rembisz.
This closes #181
Added:
poi/trunk/test-data/spreadsheet/TestValueAsArrayFunction.xls (with props)
Modified:
poi/trunk/src/java/org/apache/poi/ss/formula/functions/Value.java
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
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=1878541&r1=1878540&r2=1878541&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 Jun
6 09:30:30 2020
@@ -30,177 +30,189 @@ import java.time.DateTimeException;
* Implementation for Excel VALUE() function.<p>
*
* <b>Syntax</b>:<br> <b>VALUE</b>(<b>text</b>)<br>
- *
+ * <p>
* Converts the text argument to a number. Leading and/or trailing whitespace
is
* ignored. Currency symbols and thousands separators are stripped out.
* Scientific notation is also supported. If the supplied text does not convert
* properly the result is <b>#VALUE!</b> error. Blank string converts to zero.
*/
-public final class Value extends Fixed1ArgFunction {
+public final class Value extends Fixed1ArgFunction implements ArrayFunction {
- /** "1,0000" is valid, "1,00" is not */
- private static final int MIN_DISTANCE_BETWEEN_THOUSANDS_SEPARATOR = 4;
- private static final Double ZERO = Double.valueOf(0.0);
-
- public ValueEval evaluate(int srcRowIndex, int srcColumnIndex,
ValueEval arg0) {
- ValueEval veText;
- try {
- veText = OperandResolver.getSingleValue(arg0,
srcRowIndex, srcColumnIndex);
- } catch (EvaluationException e) {
- return e.getErrorEval();
- }
- String strText = OperandResolver.coerceValueToString(veText);
- Double result = convertTextToNumber(strText);
- if(result == null) result = parseDateTime(strText);
- if (result == null) {
- return ErrorEval.VALUE_INVALID;
- }
- return new NumberEval(result.doubleValue());
- }
-
- /**
- * TODO see if the same functionality is needed in {@link
OperandResolver#parseDouble(String)}
- *
- * @return <code>null</code> if there is any problem converting the text
- */
- public static Double convertTextToNumber(String strText) {
- boolean foundCurrency = false;
- boolean foundUnaryPlus = false;
- boolean foundUnaryMinus = false;
- boolean foundPercentage = false;
-
- int len = strText.length();
- int i;
- for (i = 0; i < len; i++) {
- char ch = strText.charAt(i);
- if (Character.isDigit(ch) || ch == '.') {
- break;
- }
- switch (ch) {
- case ' ':
- // intervening spaces between '$', '-',
'+' are OK
- continue;
- case '$':
- if (foundCurrency) {
- // only one currency symbols is
allowed
- return null;
- }
- foundCurrency = true;
- continue;
- case '+':
- if (foundUnaryMinus || foundUnaryPlus) {
- return null;
- }
- foundUnaryPlus = true;
- continue;
- case '-':
- if (foundUnaryMinus || foundUnaryPlus) {
- return null;
- }
- foundUnaryMinus = true;
- continue;
- default:
- // all other characters are illegal
- return null;
- }
- }
- if (i >= len) {
- // didn't find digits or '.'
- if (foundCurrency || foundUnaryMinus || foundUnaryPlus)
{
- return null;
- }
- return ZERO;
- }
-
- // remove thousands separators
-
- boolean foundDecimalPoint = false;
- int lastThousandsSeparatorIndex = Short.MIN_VALUE;
-
- StringBuilder sb = new StringBuilder(len);
- for (; i < len; i++) {
- char ch = strText.charAt(i);
- if (Character.isDigit(ch)) {
- sb.append(ch);
- continue;
- }
- switch (ch) {
- case ' ':
- String remainingTextTrimmed =
strText.substring(i).trim();
+ /**
+ * "1,0000" is valid, "1,00" is not
+ */
+ private static final int MIN_DISTANCE_BETWEEN_THOUSANDS_SEPARATOR = 4;
+ private static final Double ZERO = Double.valueOf(0.0);
+
+ public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval
arg0) {
+ ValueEval veText;
+ try {
+ veText = OperandResolver.getSingleValue(arg0, srcRowIndex,
srcColumnIndex);
+ } catch (EvaluationException e) {
+ return e.getErrorEval();
+ }
+ String strText = OperandResolver.coerceValueToString(veText);
+ Double result = convertTextToNumber(strText);
+ if (result == null) result = parseDateTime(strText);
+ if (result == null) {
+ return ErrorEval.VALUE_INVALID;
+ }
+ return new NumberEval(result.doubleValue());
+ }
+
+ @Override
+ public ValueEval evaluateArray(ValueEval[] args, int srcRowIndex, int
srcColumnIndex) {
+ if (args.length != 1) {
+ return ErrorEval.VALUE_INVALID;
+ }
+ return evaluateOneArrayArg(args[0], srcRowIndex, srcColumnIndex,
(valA) ->
+ evaluate(srcRowIndex, srcColumnIndex, valA)
+ );
+ }
+
+ /**
+ * TODO see if the same functionality is needed in {@link
OperandResolver#parseDouble(String)}
+ *
+ * @return <code>null</code> if there is any problem converting the text
+ */
+ public static Double convertTextToNumber(String strText) {
+ boolean foundCurrency = false;
+ boolean foundUnaryPlus = false;
+ boolean foundUnaryMinus = false;
+ boolean foundPercentage = false;
+
+ int len = strText.length();
+ int i;
+ for (i = 0; i < len; i++) {
+ char ch = strText.charAt(i);
+ if (Character.isDigit(ch) || ch == '.') {
+ break;
+ }
+ switch (ch) {
+ case ' ':
+ // intervening spaces between '$', '-', '+' are OK
+ continue;
+ case '$':
+ if (foundCurrency) {
+ // only one currency symbols is allowed
+ return null;
+ }
+ foundCurrency = true;
+ continue;
+ case '+':
+ if (foundUnaryMinus || foundUnaryPlus) {
+ return null;
+ }
+ foundUnaryPlus = true;
+ continue;
+ case '-':
+ if (foundUnaryMinus || foundUnaryPlus) {
+ return null;
+ }
+ foundUnaryMinus = true;
+ continue;
+ default:
+ // all other characters are illegal
+ return null;
+ }
+ }
+ if (i >= len) {
+ // didn't find digits or '.'
+ if (foundCurrency || foundUnaryMinus || foundUnaryPlus) {
+ return null;
+ }
+ return ZERO;
+ }
+
+ // remove thousands separators
+
+ boolean foundDecimalPoint = false;
+ int lastThousandsSeparatorIndex = Short.MIN_VALUE;
+
+ StringBuilder sb = new StringBuilder(len);
+ for (; i < len; i++) {
+ char ch = strText.charAt(i);
+ if (Character.isDigit(ch)) {
+ sb.append(ch);
+ continue;
+ }
+ switch (ch) {
+ case ' ':
+ String remainingTextTrimmed = strText.substring(i).trim();
// support for value[space]%
if (remainingTextTrimmed.equals("%")) {
- foundPercentage= true;
+ foundPercentage = true;
break;
}
if (remainingTextTrimmed.length() > 0) {
- // intervening spaces not
allowed once the digits start
- return null;
- }
- break;
- case '.':
- if (foundDecimalPoint) {
- return null;
- }
- if (i - lastThousandsSeparatorIndex <
MIN_DISTANCE_BETWEEN_THOUSANDS_SEPARATOR) {
- return null;
- }
- foundDecimalPoint = true;
- sb.append('.');
- continue;
- case ',':
- if (foundDecimalPoint) {
- // thousands separators not
allowed after '.' or 'E'
- return null;
- }
- int distanceBetweenThousandsSeparators
= i - lastThousandsSeparatorIndex;
- // as long as there are 3 or more
digits between
- if (distanceBetweenThousandsSeparators
< MIN_DISTANCE_BETWEEN_THOUSANDS_SEPARATOR) {
- return null;
- }
- lastThousandsSeparatorIndex = i;
- // don't append ','
- continue;
-
- case 'E':
- case 'e':
- if (i - lastThousandsSeparatorIndex <
MIN_DISTANCE_BETWEEN_THOUSANDS_SEPARATOR) {
- return null;
- }
- // append rest of strText and skip to
end of loop
- sb.append(strText.substring(i));
- i = len;
- break;
+ // intervening spaces not allowed once the digits start
+ return null;
+ }
+ break;
+ case '.':
+ if (foundDecimalPoint) {
+ return null;
+ }
+ if (i - lastThousandsSeparatorIndex <
MIN_DISTANCE_BETWEEN_THOUSANDS_SEPARATOR) {
+ return null;
+ }
+ foundDecimalPoint = true;
+ sb.append('.');
+ continue;
+ case ',':
+ if (foundDecimalPoint) {
+ // thousands separators not allowed after '.' or 'E'
+ return null;
+ }
+ int distanceBetweenThousandsSeparators = i -
lastThousandsSeparatorIndex;
+ // as long as there are 3 or more digits between
+ if (distanceBetweenThousandsSeparators <
MIN_DISTANCE_BETWEEN_THOUSANDS_SEPARATOR) {
+ return null;
+ }
+ lastThousandsSeparatorIndex = i;
+ // don't append ','
+ continue;
+
+ case 'E':
+ case 'e':
+ if (i - lastThousandsSeparatorIndex <
MIN_DISTANCE_BETWEEN_THOUSANDS_SEPARATOR) {
+ return null;
+ }
+ // append rest of strText and skip to end of loop
+ sb.append(strText.substring(i));
+ i = len;
+ break;
case '%':
foundPercentage = true;
break;
- default:
- // all other characters are illegal
- return null;
- }
- }
- if (!foundDecimalPoint) {
- if (i - lastThousandsSeparatorIndex <
MIN_DISTANCE_BETWEEN_THOUSANDS_SEPARATOR) {
- return null;
- }
- }
- double d;
- try {
- d = Double.parseDouble(sb.toString());
- } catch (NumberFormatException e) {
- // still a problem parsing the number - probably out of
range
- return null;
- }
+ default:
+ // all other characters are illegal
+ return null;
+ }
+ }
+ if (!foundDecimalPoint) {
+ if (i - lastThousandsSeparatorIndex <
MIN_DISTANCE_BETWEEN_THOUSANDS_SEPARATOR) {
+ return null;
+ }
+ }
+ double d;
+ try {
+ d = Double.parseDouble(sb.toString());
+ } catch (NumberFormatException e) {
+ // still a problem parsing the number - probably out of range
+ return null;
+ }
double result = foundUnaryMinus ? -d : d;
- return foundPercentage ? result/100. : result;
- }
+ return foundPercentage ? result / 100. : result;
+ }
- public static Double parseDateTime(String pText) {
+ public static Double parseDateTime(String pText) {
- try {
- return DateUtil.parseDateTime(pText);
- } catch (DateTimeException e) {
- return null;
- }
+ try {
+ return DateUtil.parseDateTime(pText);
+ } catch (DateTimeException e) {
+ return null;
+ }
- }
+ }
}
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=1878541&r1=1878540&r2=1878541&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 Sat Jun
6 09:30:30 2020
@@ -2903,6 +2903,21 @@ public final class TestBugs extends Base
public void test63819() throws IOException {
simpleTest("63819.xls");
}
+
+ /**
+ * Test that VALUE behaves properly as array function and its result is
handled by aggregate function
+ */
+ @Test
+ public void testValueAsArrayFunction() throws IOException {
+ try (final Workbook wb =
openSampleWorkbook("TestValueAsArrayFunction.xls")) {
+ wb.getCreationHelper().createFormulaEvaluator().evaluateAll();
+ Sheet sheet = wb.getSheetAt(0);
+ Row row = sheet.getRow(0);
+ Cell cell = row.getCell(0);
+ assertEquals(6.0, cell.getNumericCellValue(), 0.0);
+ }
+ }
+
// 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/test-data/spreadsheet/TestValueAsArrayFunction.xls
URL:
http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/TestValueAsArrayFunction.xls?rev=1878541&view=auto
==============================================================================
Binary file - no diff available.
Propchange: poi/trunk/test-data/spreadsheet/TestValueAsArrayFunction.xls
------------------------------------------------------------------------------
svn:mime-type = application/vnd.ms-excel
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
