formula float point problem

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

formula float point problem

何鹏飞

Hi all,
   I use this code 
     CellValue cellValue = formulaEvaluator.evaluate(cell);
     cellValue.getNumberValue();
  to evaluate formula in xls.   (POI version is 3.9)
  and display value is 21.1, but parsed value is 21.099999999999998.
  Can I get the same value with the display one ?  (I know POI get the 'cached' value in xml, but excel display the different value based on the xml)
  Also I want to scale 2 point . but the value will add two o. For example:  3  -> 3.00

please give me some advice. 

Best regards,
marvin


 



 

Reply | Threaded
Open this post in threaded view
|

Re: formula float point problem

pj.fanning
You can use
https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter.html



--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

formula float point problem

Marvin He
In reply to this post by 何鹏飞

Hi all,

   I use this code 

     CellValue cellValue = formulaEvaluator.evaluate(cell);

     cellValue.getNumberValue();

  to evaluate formula in xls.   (POI version is 3.9)

  and display value is 21.1, but parsed value is 21.099999999999998.

  Can I get the same value with the display one ?  (I know POI get the 'cached' value in xml, but excel display the different value based on the xml)

  Also I want to scale 2 point . but the value will add two o. For example:  3  -> 3.00

 

please give me some advice. 

 

Best regards

marvin

 

Reply | Threaded
Open this post in threaded view
|

Re: formula float point problem

pj.fanning
This is what the DataFormatter class is for - could you try it?



--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: formula float point problem

pj.fanning
You could also use Java DecimalFormat class to format Doubles are Strings
with the decimal precision set. See
https://www.mkyong.com/java/java-display-double-in-2-decimal-points/ 



--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: formula float point problem

Greg Woolsey
In reply to this post by Marvin He
Take a look at the documentation for DataFormatter:

https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter.html

there are tons of examples available via web searches, plus the POI unit
test sources.

On Thu, Oct 11, 2018 at 3:46 AM Marvin He <[hidden email]> wrote:

> Hi all,
>
>    I use this code
>
>      CellValue cellValue = formulaEvaluator.evaluate(cell);
>
>      cellValue.getNumberValue();
>
>   to evaluate formula in xls.   (POI version is 3.9)
>
> [image:
> http://mail.163.com/js6/s?func=mbox:getMessageData&sid=rASZXZvcSBXKmRJLyyccScGTxNPHKMLm&mid=88:1tbiWB-bPluHkST5CAABs7&part=3]
>
>   and display value is 21.1, but parsed value is 21.099999999999998.
>
>   Can I get the same value with the display one ?  (I know POI get the
> 'cached' value in xml, but excel display the different value based on the
> xml)
>
>   Also I want to scale 2 point . but the value will add two o. For
> example:  3  -> 3.00
>
>
>
> please give me some advice.
>
>
>
> Best regards,
>
> marvin
>
>
>