You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by Bryan Varner <br...@e-gineering.com> on 2015/03/17 14:51:02 UTC
Precision of double calculations...
Greetings,
I’m working with a client making use of POI to do some heavy calculations of workbook formulas in java.
We’re having issues with the precision of some of the math functions (TwoOperandNumericOperation) being based on double math rather than BigDecimal math.
The issue isn’t with the precision of the input or return parameters being doubles, but with rounding / truncation / precision of the math functions.
If the OperationEvaluatorFactory had public methods for manipulating the OperationPtg -> Function map, I could swap in my own implementations that use BigDecimal and not need to worry about patching / forking POI to do it.
Would exposing a way to manipulate that map via a public method in the OperationEvaluatorFactory be something I could get merged upstream?
Regards,
-Bryan Varner
Re: Precision of double calculations...
Posted by Nick Burch <ap...@gagravarr.org>.
On Wed, 18 Mar 2015, Dominik Stadler wrote:
> for a small project of mine I also needed to replace the implementation
> of some POI/Excel functions with different implementations.
I know that Josh did a huge amount of work on formula performance a few
years ago, back when he was more involved. After that, POI is able to
evaluate some very large files quite quickly. I'm not sure we'd want to go
back on that by default
However.... It seems there might be one or two people interested in a much
slower but slightly more accurate WRT rounding set of functions. Maybe we
need to make it an option at evaluation time, and then put in these
BigDecimal related functions / tweaks to functions to kick in for people
who need them?
(Well, assuming we can get quite close to the Excel rounding rules, which
David North has been finding to be hard to achieve in Java!)
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: Precision of double calculations...
Posted by Dominik Stadler <do...@gmx.at>.
Hi,
for a small project of mine I also needed to replace the
implementation of some POI/Excel functions with different
implementations. It is possible, albeit a bit hacky as you need to
place a class into the org.apache.poi...package-namespace so you can
access package-private interfaces. So it's a somewhat ugly workaround,
but has been working fine for me with each release of POI since around
3.8.
Let me know if you would like more details...
Dominik.
On Tue, Mar 17, 2015 at 4:16 PM, Bryan Varner
<br...@e-gineering.com> wrote:
>
> On Mar 17, 2015, at 10:43 AM, Nick Burch <ap...@gagravarr.org>> wrote:
>
> On Tue, 17 Mar 2015, Bryan Varner wrote:
> I’m working with a client making use of POI to do some heavy calculations of workbook formulas in java.
>
> We’re having issues with the precision of some of the math functions (TwoOperandNumericOperation) being based on double math rather than BigDecimal math.
>
> I believe that Excel uses doubles internally as well, no big decimals. The problem is that it doesn't quite use the same rounding as Java does. There's a bit of an explanation of the problems this can cause in this stackoverflow discussion:
> http://stackoverflow.com/questions/28537614/matching-excels-floating-point-in-java
>
> If your problem is that, then I'm not sure that dropping in a big decimal will work, as the Java big decimal's rounding rules don't match the Excel ones
>
> David North has been doing lots of work on / around this area, so we might want to wait for him to chime in
>
> Nick
>
> Thanks Nick for the input. The client has a 3.9 fork they’ve been using internally, but wants to get something functionally identical merged upstream. They’ve switched things to use BigDecimal internally, and made use of http://arxiv.org/abs/0908.3030v2 in their fork to get the numbers they expected.
>
> I think waiting on David to chime in sounds like a good idea.
>
> My client has been over this a _lot_, and they swear their computations are (more) accurate now when compared to Excel. I tend to trust them on this, due to the nature of the workbooks they’re evaluating, and the … critical nature of getting these numbers right for their business…
>
> Being that their code is based off of 3.9, I started looking at ways we could at least make adjustments to the POI code to let us drop in their calculation functions or forward-port to a newer POI release. They do want to contribute upstream if possible, especially if it reduces the long-term maintenance burden. In my digging, I’ve noticed a few other oddities (but I’m looking at the 3.9 release code right now) and low-hanging places to improve things in POI, but my focus right now is still on getting the math results my client says they need.
>
> What’s the best way to contribute back upstream at this point? Are pull requests from github still frowned upon?
>
> -Bryan
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: Precision of double calculations...
Posted by Bryan Varner <br...@e-gineering.com>.
On Mar 17, 2015, at 10:43 AM, Nick Burch <ap...@gagravarr.org>> wrote:
On Tue, 17 Mar 2015, Bryan Varner wrote:
I’m working with a client making use of POI to do some heavy calculations of workbook formulas in java.
We’re having issues with the precision of some of the math functions (TwoOperandNumericOperation) being based on double math rather than BigDecimal math.
I believe that Excel uses doubles internally as well, no big decimals. The problem is that it doesn't quite use the same rounding as Java does. There's a bit of an explanation of the problems this can cause in this stackoverflow discussion:
http://stackoverflow.com/questions/28537614/matching-excels-floating-point-in-java
If your problem is that, then I'm not sure that dropping in a big decimal will work, as the Java big decimal's rounding rules don't match the Excel ones
David North has been doing lots of work on / around this area, so we might want to wait for him to chime in
Nick
Thanks Nick for the input. The client has a 3.9 fork they’ve been using internally, but wants to get something functionally identical merged upstream. They’ve switched things to use BigDecimal internally, and made use of http://arxiv.org/abs/0908.3030v2 in their fork to get the numbers they expected.
I think waiting on David to chime in sounds like a good idea.
My client has been over this a _lot_, and they swear their computations are (more) accurate now when compared to Excel. I tend to trust them on this, due to the nature of the workbooks they’re evaluating, and the … critical nature of getting these numbers right for their business…
Being that their code is based off of 3.9, I started looking at ways we could at least make adjustments to the POI code to let us drop in their calculation functions or forward-port to a newer POI release. They do want to contribute upstream if possible, especially if it reduces the long-term maintenance burden. In my digging, I’ve noticed a few other oddities (but I’m looking at the 3.9 release code right now) and low-hanging places to improve things in POI, but my focus right now is still on getting the math results my client says they need.
What’s the best way to contribute back upstream at this point? Are pull requests from github still frowned upon?
-Bryan
Re: Precision of double calculations...
Posted by Nick Burch <ap...@gagravarr.org>.
On Tue, 17 Mar 2015, Bryan Varner wrote:
> I’m working with a client making use of POI to do some heavy
> calculations of workbook formulas in java.
>
> We’re having issues with the precision of some of the math functions
> (TwoOperandNumericOperation) being based on double math rather than
> BigDecimal math.
I believe that Excel uses doubles internally as well, no big decimals. The
problem is that it doesn't quite use the same rounding as Java does.
There's a bit of an explanation of the problems this can cause in this
stackoverflow discussion:
http://stackoverflow.com/questions/28537614/matching-excels-floating-point-in-java
If your problem is that, then I'm not sure that dropping in a big decimal
will work, as the Java big decimal's rounding rules don't match the Excel
ones
David North has been doing lots of work on / around this area, so we might
want to wait for him to chime in
Nick