You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2022/12/14 08:27:16 UTC
[Bug 66389] New: POI formula eval result is not same with excel when do an arithmetic operation
https://bz.apache.org/bugzilla/show_bug.cgi?id=66389
Bug ID: 66389
Summary: POI formula eval result is not same with excel when do
an arithmetic operation
Product: POI
Version: 5.2.2-FINAL
Hardware: PC
Status: NEW
Severity: normal
Priority: P2
Component: POI Overall
Assignee: dev@poi.apache.org
Reporter: twitang@hotmail.com
Target Milestone: ---
Created attachment 38449
--> https://bz.apache.org/bugzilla/attachment.cgi?id=38449&action=edit
test method
When I upgrade the POI version to 5.2.2, some formula eval result changed, for
example
formula is "=B1*SQRT(B2)"
when set B1 = 1.2
set B2 = 5.678
Expected :2.85942651592937
Actual :2.85942651592938
It seems that this program convert these 2 args to 15 digits of precision
`before multiply operation` firstly, but Excel convert to 15 digits `after
multiply operation`.
```
public static final Function MultiplyEval = new TwoOperandNumericOperation() {
@Override
protected double evaluate(double d0, double d1) {
BigDecimal bd0 = new BigDecimal(NumberToTextConverter.toText(d0));
BigDecimal bd1 = new BigDecimal(NumberToTextConverter.toText(d1));
return bd0.multiply(bd1).doubleValue();
}
}
```
This test not occured in POI 4.1.2.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 66389] POI formula eval result is not same with excel when do an arithmetic operation
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=66389
PJ Fanning <fa...@yahoo.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
OS| |All
--- Comment #1 from PJ Fanning <fa...@yahoo.com> ---
I'm not convinced. I only have access to the web version of Excel and this
evaluates that formula as 2.859426516.
Using Java to try to reverse engineer Excel's internal maths is a major pain in
the neck.
We'll never make everyone happy and what we are arguing about here is a minor
rounding issue.
You can just get Excel to recalculate all the formulas when it loads any
xls/xlsx files you create with POI and not worry about POI's calculations.
Workbook#setForceFormulaRecalculation(boolean)
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 66389] POI formula eval result is not same with excel when do an arithmetic operation
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=66389
Dominik Stadler <do...@gmx.at> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution|--- |WONTFIX
--- Comment #2 from Dominik Stadler <do...@gmx.at> ---
It seems there is no easy way to fully get rid of such minor rounding errors
and there is the option to ask Excel to recalculate formulas, therefore let's
close this here for now unless we find things to improve later.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org