Author: fanningpj
Date: Thu Feb 3 10:52:49 2022
New Revision: 1897718
URL: http://svn.apache.org/viewvc?rev=1897718&view=rev
Log:
[bug-62857] fix DOLLAR function
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/NumericFunction.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/NumericFunction.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/NumericFunction.java?rev=1897718&r1=1897717&r2=1897718&view=diff
==============================================================================
---
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/NumericFunction.java
(original)
+++
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/NumericFunction.java
Thu Feb 3 10:52:49 2022
@@ -21,6 +21,10 @@ import static org.apache.poi.ss.formula.
import org.apache.poi.ss.formula.eval.*;
+import java.text.DecimalFormat;
+import java.text.NumberFormat;
+import java.util.Locale;
+
public abstract class NumericFunction implements Function {
private static final double ZERO = 0.0;
@@ -89,10 +93,20 @@ public abstract class NumericFunction im
return VALUE_INVALID;
}
- // TODO - DOLLAR() function impl is NQR
- // result should be StringEval, with leading '$' and thousands
separators
- // current junits are asserting incorrect behaviour
- return new NumberEval(val);
+ StringBuilder decimalPlacesFormat = new StringBuilder();
+ if (nPlaces > 0) {
+ decimalPlacesFormat.append('.');
+ }
+ for (int i = 0; i < nPlaces; i++) {
+ decimalPlacesFormat.append('0');
+ }
+ StringBuilder decimalFormatString = new StringBuilder();
+ decimalFormatString.append("$#,##0").append(decimalPlacesFormat)
+
.append(";($#,##0").append(decimalPlacesFormat).append(')');
+
+ DecimalFormat df = new
DecimalFormat(decimalFormatString.toString());
+
+ return new StringEval(df.format(val));
}catch (EvaluationException e) {
return e.getErrorEval();
}
Modified:
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java?rev=1897718&r1=1897717&r2=1897718&view=diff
==============================================================================
---
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java
(original)
+++
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java
Thu Feb 3 10:52:49 2022
@@ -22,6 +22,7 @@ import org.apache.poi.hssf.usermodel.HSS
import org.junit.jupiter.api.Test;
import static org.apache.poi.ss.util.Utils.assertDouble;
+import static org.apache.poi.ss.util.Utils.assertString;
final class TestNumericFunction {
@@ -50,4 +51,18 @@ final class TestNumericFunction {
assertDouble(fe, cell, "SIGN(-0.00001)", -1.0, 0);
}
+ @Test
+ void testDOLLAR() {
+ HSSFWorkbook wb = new HSSFWorkbook();
+ HSSFCell cell = wb.createSheet().createRow(0).createCell(0);
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+
//https://support.microsoft.com/en-us/office/dollar-function-a6cd05d9-9740-4ad3-a469-8109d18ff611
+ assertString(fe, cell, "DOLLAR(1234.567,2)", "$1,234.57");
+ assertString(fe, cell, "DOLLAR(-1234.567,0)", "($1,235)");
+ //TODO need to fix code to handle next case
+ //assertString(fe, cell, "DOLLAR(-1234.567,-2)", "($1,200)");
+ assertString(fe, cell, "DOLLAR(-0.123,4)", "($0.1230)");
+ assertString(fe, cell, "DOLLAR(99.888)", "$99.89");
+ assertString(fe, cell, "DOLLAR(123456789.567,2)", "$123,456,789.57");
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
