You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by 何鹏飞 <he...@163.com> on 2018/10/09 02:22:24 UTC
FormulaEvaluator float presicion problem
hi yegor,
I'm using FormulaEvaluator from Apache POI to evaluate 2 cells sum :
10.00 (Cell 1) + 0.80 (Cell 2) = 10.80
This sum in Excel shows 10.80 wich is true, but with POI's FormulaEvaluator the result is 10.799999998!
CellValue cellValue = evaluator.evaluate(cell);
This is not working.
Why is this wrong with Apache POI and how can i get the formula well evaluated? I'm not looking to round the result because i'm trying to validate every cell on that excel's two decimals only. Any suggestions?
thanks
Re: FormulaEvaluator float presicion problem
Posted by Vladislav Galas <ga...@apache.org>.
On 2018/10/09 12:39:39, "pj.fanning" <fa...@yahoo.com> wrote:
> Could you try using DataFormatter after using the FormulaEvaluator?
>
> https://stackoverflow.com/questions/21538481/xssf-how-to-get-anything-as-string/21550258#21550258
>
>
>
> --
> Sent from: http://apache-poi.1045710.n5.nabble.com/POI-Dev-f2312866.html
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
> For additional commands, e-mail: dev-help@poi.apache.org
>
>
The problem is deeper. Excel claims to be IEEE754 compatible but in fact coerces doubles to significant 15 digits at least before writing a value to a cell. When operands have differend magnitude, the highest order of magnitude is tracked, and lesser operands are coerced to that magnitude. Therefore, the output is not strictly IEEE754 compatible.
To handle this in my private fork, I passed a PrecisionContext to all operations, tracked magnitude of operands and coerced the result through BigDecimal. But this is a significant change to the evaluation process, and I am not ready to present this fix right away.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: FormulaEvaluator float presicion problem
Posted by "pj.fanning" <fa...@yahoo.com>.
Could you try using DataFormatter after using the FormulaEvaluator?
https://stackoverflow.com/questions/21538481/xssf-how-to-get-anything-as-string/21550258#21550258
--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-Dev-f2312866.html
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org