Bug 64620 - Apache POI API evaluation value - Excel value - not matching
Summary: Apache POI API evaluation value - Excel value - not matching
Status: RESOLVED INFORMATIONPROVIDED
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 4.1.2-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2020-07-22 13:04 UTC by Avinash M
Modified: 2021-10-08 20:37 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Avinash M 2020-07-22 13:04:43 UTC
We have entered following formula in the excel
   
   =3000/12*(1-0.8)

The above formula will result to 50.


Evaluating this formula through Apache poi Java library resulting to 49.99999.

As our application runs on numbers, the out put results doesn't match to the expected results.

Please comment.

Thanks!!
Comment 1 west wong 2020-07-27 03:18:51 UTC

new BigDecimal(3000/12*(1-0.8)).setScale(0,RoundingMode.HALF_UP);

The value of newScale denpends on what you need !

I usually use setScale(2,RoundingMode.HALF_UP);
Comment 2 PJ Fanning 2021-10-08 20:37:09 UTC
Excel files store numbers as doubles and doubles are just approximations. If you use the POI DataFormatter class, it will apply a format based on the cell style and this can include rounding the number to the form displayed by Excel.