Bug 62857 - DOLLAR function not implemented entirely
Summary: DOLLAR function not implemented entirely
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 4.0.0-FINAL
Hardware: PC Linux
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Keywords: Beginner
Depends on:
Reported: 2018-10-26 09:27 UTC by Sven
Modified: 2022-02-05 12:29 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
Description Sven 2018-10-26 09:27:06 UTC
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.
Comment 1 Dominik Stadler 2020-01-01 20:24:46 UTC
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.
Comment 2 PJ Fanning 2022-02-03 10:53:54 UTC
added r1897718 - still 1 edge case to support and may need to fix old tests that rely on old (wrong) behaviour
Comment 3 PJ Fanning 2022-02-03 11:14:40 UTC
and r1897719
Comment 4 PJ Fanning 2022-02-03 14:33:34 UTC
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.
Comment 5 PJ Fanning 2022-02-03 15:29:44 UTC
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.
Comment 6 PJ Fanning 2022-02-05 12:28:59 UTC
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.