diff options
| -rw-r--r-- | poi/src/main/java/org/apache/poi/ss/formula/functions/NumericFunction.java | 22 | ||||
| -rw-r--r-- | poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java | 15 |
2 files changed, 33 insertions, 4 deletions
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/NumericFunction.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/NumericFunction.java index 557fbc7893..54369afa2e 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/functions/NumericFunction.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/NumericFunction.java @@ -21,6 +21,10 @@ import static org.apache.poi.ss.formula.eval.ErrorEval.VALUE_INVALID; 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 implements Function { 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(); } diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java index 2f08587adc..35d82acd57 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java @@ -22,6 +22,7 @@ import org.apache.poi.hssf.usermodel.HSSFWorkbook; 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"); + } } |