v3.17 is not maintained - please try v5.1.0

Use DataFormatter to format the cell - the result is stored in double format and that can lead to rounding issues (but this is also required to match how Excel works)

(In reply to PJ Fanning from comment #1)
> v3.17 is not maintained - please try v5.1.0

Thanks,
but my system can't use more than 3.17.

(In reply to PJ Fanning from comment #2)
> Use DataFormatter to format the cell - the result is stored in double format
> and that can lead to rounding issues (but this is also required to match how
> Excel works)

The problem does not occur when getting the value, but after calculating the sheet and saving the Excel file, when opening Excel file with Micro Excel, the invalid value calculated by POI is displayed.
1. calculating all cell of sheet(POI)
2. saving the Excel file(POI)
3. opening Excel file with Micro Excel(Micro Excel)
4. invalid value of a cell(value:2490399)
5. Double-click on the cell and press enter and the value is displayed correctly(value:2490400) 
So the formatting method is not available.

One workaround is to call `workbook.setForceFormulaRecalculation(true);` - this will get excel to recalculate all the formulas when it loads the excel file.

We are in the middle of a release so I don't want to merge any changes until that is done.

I have reproduced this issue and it actually happens in the `880000000*0.84900%` part. The multiplication is done using 2 java doubles and the result is not exact. The correct result is 7471200.0 but the POI java code produces something more like 7471199.999999.

I hacked the multiply code as below but it is a hack. The idea is to try to round doubles that are very close to integers to those integers.


     public static final Function MultiplyEval = new TwoOperandNumericOperation() {
         @Override
         protected double evaluate(double d0, double d1) {
-            return d0*d1;
+            BigDecimal bd = new BigDecimal(d0).multiply(new BigDecimal(d1));
+            BigDecimal possibleInt = bd.setScale(8, RoundingMode.HALF_UP);
+            if (possibleInt.doubleValue() == possibleInt.setScale(0, RoundingMode.HALF_UP).doubleValue()) {
+                return possibleInt.doubleValue();
+            } else {
+                return bd.doubleValue();
+            }
         }
     };

https://github.com/apache/poi/pull/293 is an attempt to fix this

While this issue does show some issues with how POI multiplies numbers and small rounding issues that occur - I think this would be a lot less of a problem if the user used the ROUND function instead of INT.

INT(1.99999) comes out as 1 but ROUND(1.99999, 0) is 2 - ROUND is more forgiving of rounding issues.

(In reply to PJ Fanning from comment #8)
> While this issue does show some issues with how POI multiplies numbers and
> small rounding issues that occur - I think this would be a lot less of a
> problem if the user used the ROUND function instead of INT.
> 
> INT(1.99999) comes out as 1 but ROUND(1.99999, 0) is 2 - ROUND is more
> forgiving of rounding issues.

I am soory!
The ROUNDDOWN function is known to solve this issue, but the Excel file is used as a contract and cannot be modified.

I want a fixed version of this issue of ver3.17, is it possible?

(In reply to caoxj from comment #9)
> The ROUNDDOWN function is known to solve this issue, but the Excel file is
> used as a contract and cannot be modified.

if the contract is based on Excel formulas, which includes truncations by design, it might be worth to communicate this error possibility with the parties involved ...

> I want a fixed version of this issue of ver3.17, is it possible?

only in the very rare case of security patches we would consider this, so not this time. but you could try to apply or adapt the patch yourself - just download the 3.17 src zip: https://archive.apache.org/dist/poi/release/src/

Please tell me how to fix V3.17, I will try to build

(In reply to caoxj from comment #11)
> Please tell me how to fix V3.17, I will try to build

get the source from comment #10 and apply/adapt the patch from comment #7.
if you face problems building it, it might be easier for you to upgrade to 5.1.0 ... which we recommend anyway

(In reply to Andreas Beeker from comment #12)
> (In reply to caoxj from comment #11)
> > Please tell me how to fix V3.17, I will try to build
> 
> get the source from comment #10 and apply/adapt the patch from comment #7.
> if you face problems building it, it might be easier for you to upgrade to
> 5.1.0 ... which we recommend anyway

Thank you.
After applying / applying the patch from comment # 7, this formula was OK, but many other formulas became NG.
Can this patch support both 5.x and 3.17?

that comment 7 patch is work in progress - I need to debug issues - if you have any specific examples that fail, report them - I am not working on this at the moment because I have a lot of other stuff in progress

Did you ever try the solution in comment 5?

And I have no intention of trying to patch POI 3.17 - the patch I am working on is for the latest POI release and that alone. If anyone wants to try it out on a separate code base, they are free to do so - but I'm not very interested in helping them to do that.

added r1897066

Thank you very much.
Since the impact is small in a hurry, only INT is supported.

NumericFunction.java

	public static final Function INT = new OneArg() {
		protected double evaluate(double d) {
-			return Math.round(d-0.5);

+			if(d > 0){
+				return MathX.roundDown(d, 0);
+			}else{
+				return MathX.roundUp(d, 0);
+			}
		}
	};

the current INT implementation is correct - see https://support.microsoft.com/en-us/office/int-function-a6c4af9e-356d-4369-ab6a-cb1fd9d343ef - I have added a test to prove it - and verified against my copy of Excel

(In reply to PJ Fanning from comment #18)
> the current INT implementation is correct - see
> https://support.microsoft.com/en-us/office/int-function-a6c4af9e-356d-4369-
> ab6a-cb1fd9d343ef - I have added a test to prove it - and verified against
> my copy of Excel

Thank you very much.