This is not going to be particularly earth-shattering, sorry.
Yesterday someone asked on my favourite source of militant pedantry (Stack Overflow) how to pass a BigDecimal to POI for a spreadsheet cell value. You'll need to take my word for it, as the original question has been deleted (not sure what to make of that). There's a Google-cached version of it here. I dunno how long google caches stuff for though. The gist of the question text is (this is an abridged version of the original:
How to use BigDecimal when I have no implementation that will take itI had a hunt around and came up with this:
Here is my code:
The part I changed for it to error is:
thisCell.setCellValue(javacast("bigdecimal", invoicesDue.amount) );
The setCellValue() method has no overload which will take a BigDecimal.
The field is going to be currency which requires a BigDecimal, however XSSFCell's setCellValue() doesn't have an implementation that will take it. The closest approximation only accepts a Double, which is no good to me because in the documentation it says double shouldn't be used for currency. That being the case, what should I do?
To answer the specific question you have asked: the setCellValue() method has no overload which will take a BigDecimal. Which is what the error is telling you.
A follow-up question you might want to ask is "I need to do [x] which requires a BigDecimal, however XSSFCell's setCellValue() doesn't have an implementation that will take it. The closest approximation only accepts a Double, which is no good to me [because of this reason]. That being the case, what should I do?"
I guess you could update this question, or you could create a new question.
And that's about as far as the Q&A got before the question was deleted.
The problem the questioner was having is that they were - rightly - cautious about using a float for the currency amounts, and floats are not very accurate, so if one needs accuracy, using something like a BigDecimal is better as it (seems to ~... I am no expert) preserve accuracy to an arbitrary degree.
POI will only accept a Double for a numeric, and I also read Excel only works in Doubles too, so there are going to be inherent accuracy constraints here.
It then occurred to me that I had no idea how much accuracy a Double actually has, so set out to find out. I knocked this code together:
What this does is to build three strings that represent numbers:
- one that's an integer only (ie: all digits are to the left of the decimal point)
- one that's a decimal only (ie: all digits are to the right of the decimal point)
- one that's both (ie: digits are on either side of the decimal point)
After that I just tabulate the results, as well as writing them to an XLS file so I can see what happens there as well.
The results are as follows:
(there's an error in this in that I'm implying the "digits" column represents how many digits are in the number, but that's wrong after 9, as I skip 10)
What we see is that a Double has 12 digits of precision, be they on the left, right, or across the decimal point. I "knew" (mostly) the number of digits accuracy it had would be fixed irrespective of where the decimal point was, but wasn't 100% certain, and I certainly didn't know how many digits it was.
The resultant spreadsheet was as follows (converted to a vanilla-looking HTML table):
This is much the same, except note Excel displays that "13th" row in scientific notation, whilst still seemingly preserving the accuracy (judging by the fact that row says "true" and the next one says "false".
From this, I could not be sure if the data "truncation" was coming from CF, or the limit in Excel is the same as in CF. So I just pasted in the "string" column into a new spreadsheet, and the results were as follows:
So Excel runs to 15 digits of accuracy, which is better than CF will manage.
Despite all that, if we go back to the original situation in that the person asking the question was concerned about the accuracy levels for currency amounts: 12 digits will represent a currency value in the tens of billions, accurate to a penny. This will probably be fine for most people's requirements.
There are still potential issues with the inherent flaws with floating point numbers accurately representing decimals, but I think for the purposes of financial calculations, once the data is in Excel, it will be fine.
So my bottom line that I would have given the person had the question lasted long enough for me to update my answer would be "don't worry about it: just use a double".
And at least now I know how accurate CF floating point numbers are. This is one of the good things about helping people with their questions on forums... sometimes one needs to work out what the answer is before answering, so it's a learning exercise for the answerer too!
Time for work...