The evaluation result for function call DOLLAR(1234.567;2) is just the raw numeric value itself. Without dollar sign, without thousands separators and without cutting/extending the value to the given number of decimal places. In Method org.apache.poi.ss.formula.functions.new Var1or2ArgFunction() {...}.evaluate(int, int, ValueEval, ValueEval) for Function DOLLAR there is a comment: // TODO - DOLLAR() function impl is NQR // result should be StringEval, with leading '$' and thousands separators // current junits are asserting incorrect behaviour I think it's useful to document the difference from the expected behavior here in bugzilla, so that others can find it fast(er) when they search for unexpected DOLLAR-behavior.
This would probably be relatively easy thing to implement DOLLAR correctly for anybody new to developing for Apache POI, any takers? Or at least provide changes to unit-tests so an implementation is properly verified.
added r1897718 - still 1 edge case to support and may need to fix old tests that rely on old (wrong) behaviour
and r1897719
I accidentally ran into this. The existing implementation was way off. I implemented the US locale support but to support all locales will be a lot of work. The output is very locale specific. https://docs.microsoft.com/en-us/globalization/locale/currency-formatting gives an idea but only spells out the behaviour for a very small number of locales. My current implementation will use the currency symbol associated with the LocaleUtil user locale and the group and decimal separators for that locale. What it doesn't do is: * put the currency symbol at the end for locales that prefer that way * it currently puts parentheses around negative numbers but this is US specific (possibly some more locales too) - and when you use the negative sign instead, it can appear before or after the currency symbol (if that symbol appears before the number) I'll probably just create a follow up issue that I'll leave up to someone else to do - as I have little interest in researching and implementing the solution for every possible locale. If someone knows of something that is already hidden away in the existing POI number formatting code, I might have a look. Any pointers would be appreciated.
I've started using Java NumberFormat.getCurrencyInstance and this seems close enough to Excel in the small number of locales I've tried. I had to hack the format to get US locale to use parens on negative numbers. I would still expect that many locales will not work as the people from those locales would expect them to.
The modified function works for some of the main locales - but the Java core locale code does not always match Excel and the Java support can vary by JVM version. Users can open new issues if they hit issues for specific locales.