From b7930fc65cadb685a49ec79e45c101a16146cd3f Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Sat, 6 Jun 2020 09:30:30 +0000 Subject: [PATCH] =?utf8?q?[github-181]=20make=20Value=20function=20work=20?= =?utf8?q?with=20arrays.=20Thanks=20to=20Mi=C5=82osz=20Rembisz.=20This=20c?= =?utf8?q?loses=20#181?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1878541 13f79535-47bb-0310-9956-ffa450edef68 --- .../poi/ss/formula/functions/Value.java | 324 +++++++++--------- .../apache/poi/hssf/usermodel/TestBugs.java | 15 + .../spreadsheet/TestValueAsArrayFunction.xls | Bin 0 -> 5632 bytes 3 files changed, 183 insertions(+), 156 deletions(-) create mode 100644 test-data/spreadsheet/TestValueAsArrayFunction.xls 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.

* * Syntax:
VALUE(text)
- * + *

* 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 #VALUE! 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 null 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 null 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 new file mode 100644 index 0000000000000000000000000000000000000000..2b9b11f9caf81ffb9eda1805434c0e6164d24481 GIT binary patch literal 5632 zcmeHLU2IfE6h3qJr?>yx(pD%?w^gLjmb5fMjKOXxs3aN);7<&REM*I|OWU+HQRB~| z@T4Rf9(*A&nEE6r#snWQar?%DMqe=T=b>O?H2AO@j3KdIzwg|;+soQ5yH<^up6!`A zbLPyPGiT1sncFWuubDpmLE~k~fqTU#v*}U^diVykooRnKJy2?67lQ%0Q28mZe7*ZGBO=cE zr!+3EfVWRZ85@1kcz^icf~{&p)G#)a2+}Q}jGO|uO5RuQ$69XD=z~y1!kba?MRLtV z)b#vZH0w~p zr*%ytsC8|^ul2G-iPm+AKzhH%|8bn53mC}LzUOc(HqrGO&bPsD%Gf}Wy(f%Zl_z-1 zr*`Y|gDKwTyapIIj6pAUOL>Y};1irCTaFrJ4LgZbD0W1eerBFP-msBBY~%&|H>8f= z5y{LsM9kC$+z9MWBiq<=40@Pt`t5ok2p5=L2o_V4b9IGoxVkD}-a(kn!(OANhA>@6 z->R!uHZ}Gu8CXLHO1n9Vo+C_wwPCKCRl*QWb346$Mv>FcXFTclC3)JV8^X2e@nwp)&O}XU}mBzD9hc#Qw)&%Z5bAHTV(@fGNqs_7c zwiqj@qV+O^z2g#@|3zCgph0`tN7W5F&rh<8o0NJ`XS7q?5LJiWs1{$U6YXm8r&^U? znsSzmh6gMu({YSRezn|Z)yjGOT#b07R&zer0Z>8e5Q6Z~TnNA*!wkzo46EY6;6>xZ zFwPLN9Umo~DemW+=i$Nrv61nSfr;?;XZmCD@I7~T?2R6X#p5wewOa=V2As52oZ753 z7UU?+a*88nv2F%QF3?fa$KU$p`p(0lci*t3ZS99&aaW!P(#hK-lfKvJPII9Q?7b2(=L%rXe>-ds)h`f0Ax$iE52Kgt* zXFv5>mp;36YQxIV$v0tt`}Oy>ffvZ(?E{ZD8z(0+(lWO00?qr05C*cJYi550$Tn%N zxp#E*(b(Ad;K=aYc&?Rk*0pIvXLl#P_uqrR^ZQTzmvaW@TQPl_VBi2A!`J7S4B^Ga z@6aKKSwvZj9pE0|63{bP=+Ihy?Q#$Mp!W!3bSG?hs?gzCIqK(nc>W35js=L-=-c7; z8|aNoYys(oyUL*7(d?89OFO-{qhews1Z0mqjrAYF%a`B&JMdqC0o@aFCh!U#0VhAS zy83wo7rOp1WY4%$24ovF^s5aq{SQQO_H6zO>%YUJ-!)@VRk8l}et$1Yu{(G%`u_l8 C5*V)l literal 0 HcmV?d00001 -- 2.39.5