diff options
| -rw-r--r-- | src/java/org/apache/poi/ss/formula/functions/Value.java | 324 | ||||
| -rw-r--r-- | src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java | 15 | ||||
| -rw-r--r-- | test-data/spreadsheet/TestValueAsArrayFunction.xls | bin | 0 -> 5632 bytes |
3 files changed, 183 insertions, 156 deletions
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 176a776906..d2a1f24fc7 100644 --- a/src/java/org/apache/poi/ss/formula/functions/Value.java +++ b/src/java/org/apache/poi/ss/formula/functions/Value.java @@ -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 { - - /** "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(); +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()); + } + + @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; + } - } + } } diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java b/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java index d0529220d2..7d7650de25 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java @@ -2903,6 +2903,21 @@ public final class TestBugs extends BaseTestBugzillaIssues { 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); diff --git a/test-data/spreadsheet/TestValueAsArrayFunction.xls b/test-data/spreadsheet/TestValueAsArrayFunction.xls Binary files differnew file mode 100644 index 0000000000..2b9b11f9ca --- /dev/null +++ b/test-data/spreadsheet/TestValueAsArrayFunction.xls |