FormulaEvaluator float presicion problem

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

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




 
Reply | Threaded
Open this post in threaded view
|

Re: FormulaEvaluator float presicion problem

pj.fanning
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: FormulaEvaluator float presicion problem

Vladislav Galas


On 2018/10/09 12:39:39, "pj.fanning" <[hidden email]> 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: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>

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: [hidden email]
For additional commands, e-mail: [hidden email]