Repository: PHPOffice/PhpSpreadsheet (php)

Issue:
Bug in "Trunc" class
This is:

```
- [X] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)
```

### What is the expected behavior?

PhpOffice\PhpSpreadsheet\Calculation\MathTrig\Trunc::evaluate(1.01, 1);  // expected: 1.0

### What is the current behavior?

PhpOffice\PhpSpreadsheet\Calculation\MathTrig\Trunc::evaluate(1.01, 1);  // current: 1.01

### What are the steps to reproduce?

```php
<?php

require __DIR__ . '/vendor/autoload.php';

echo PhpOffice\PhpSpreadsheet\Calculation\MathTrig\Trunc::evaluate(1.01, 1);

```
### What features do you think are causing the issue

- [ ] Reader
- [ ] Writer
- [ ] Styles
- [ ] Data Validations
- [X] Formula Calculations
- [ ] Charts
- [ ] AutoFilter
- [ ] Form Elements

### Does an issue affect all spreadsheet file formats? If not, which formats are affected?

Formats that use formulas.

### Which versions of PhpSpreadsheet and PHP are affected?

All?


Pull request:
New Algorithm for TRUNC, ROUNDUP, and ROUNDDOWN
Fix #4113. TRUNC isn't always producing the expected result. There was a promising algorithm at https://stackoverflow.com/questions/4668628/truncate-float-numbers-with-php from user Juan. It works through Php8.3, but failed in Php8.4 (more on this later). User Savageman on the same page has a solution that needs work, but, once the work had taken place, it works on Php8.1-8.4.

The ROUNDUP and ROUNDDOWN functions were adversely affected by Php8.4, probably for the same reasons as Juan's TRUNC suggestion. I put a kludge in place for them some time ago, but I wasn't happy with it. The solution used for TRUNC here suggested a change to the ROUNDUP and ROUNDDOWN code that would no longer require the kludge. The change to those functions now works more cleanly on Php8.1-8.4.

This is:

- [x] a bugfix
- [ ] a new feature
- [ ] refactoring
- [ ] additional unit tests

Checklist:

- [x] Changes are covered by unit tests
  - [x] Changes are covered by existing unit tests
  - [x] New unit tests have been added
- [x] Code style is respected
- [x] Commit message explains **why** the change is made (see https://github.com/erlang/otp/wiki/Writing-good-commit-messages)
- [ ] CHANGELOG.md contains a short summary of the change and a link to the pull request if applicable
- [ ] Documentation is updated as necessary

### Why this change is needed?

Provide an explanation of why this change is needed, with links to any Issues (if appropriate).
If this is a bugfix or a new feature, and there are no existing Issues, then please also create an issue that will make it easier to track progress with this PR.


Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index f35fe11ec..e89705b17 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -28,6 +28,7 @@ and this project adheres to [Semantic Versioning](https://semver.org).
 - Html Reader Preserve Unicode Whitespace. [Issue #1284](https://github.com/PHPOffice/PhpSpreadsheet/issues/1284) [PR #4106](https://github.com/PHPOffice/PhpSpreadsheet/pull/4106)
 - RATE Function Floating Point Number of Periods. [PR #4107](https://github.com/PHPOffice/PhpSpreadsheet/pull/4107)
 - Parameter Name Change Xlsx Writer Workbook. [Issue #4108](https://github.com/PHPOffice/PhpSpreadsheet/issues/4108) [PR #4111](https://github.com/PHPOffice/PhpSpreadsheet/pull/4111)
+- New Algorithm for TRUNC, ROUNDUP, ROUNDDOWN. [Issue #4113](https://github.com/PHPOffice/PhpSpreadsheet/issues/4113) [PR #4115](https://github.com/PHPOffice/PhpSpreadsheet/pull/4115)
 
 ## 2024-07-29 - 2.2.1
 
diff --git a/src/PhpSpreadsheet/Calculation/MathTrig/Round.php b/src/PhpSpreadsheet/Calculation/MathTrig/Round.php
index a573f2afd..d2aa1c0b1 100644
--- a/src/PhpSpreadsheet/Calculation/MathTrig/Round.php
+++ b/src/PhpSpreadsheet/Calculation/MathTrig/Round.php
@@ -10,8 +10,6 @@ class Round
 {
     use ArrayEnabled;
 
-    private const ROUNDING_ADJUSTMENT = (PHP_VERSION_ID < 80400) ? 0 : 1e-14;
-
     /**
      * ROUND.
      *
@@ -69,11 +67,22 @@ class Round
             return 0.0;
         }
 
+        $digitsPlus1 = $digits + 1;
         if ($number < 0.0) {
-            return round($number - 0.5 * 0.1 ** $digits + self::ROUNDING_ADJUSTMENT, $digits, PHP_ROUND_HALF_DOWN);
+            if ($digitsPlus1 < 0) {
+                return round($number - 0.5 * 0.1 ** $digits, $digits, PHP_ROUND_HALF_DOWN);
+            }
+            $result = sprintf("%.{$digitsPlus1}F", $number - 0.5 * 0.1 ** $digits);
+
+            return round((float) $result, $digits, PHP_ROUND_HALF_DOWN);
+        }
+
+        if ($digitsPlus1 < 0) {
+            return round($number + 0.5 * 0.1 ** $digits, $digits, PHP_ROUND_HALF_DOWN);
         }
+        $result = sprintf("%.{$digitsPlus1}F", $number + 0.5 * 0.1 ** $digits);
 
-        return round($number + 0.5 * 0.1 ** $digits - self::ROUNDING_ADJUSTMENT, $digits, PHP_ROUND_HALF_DOWN);
+        return round((float) $result, $digits, PHP_ROUND_HALF_DOWN);
     }
 
     /**
@@ -105,11 +114,23 @@ class Round
             return 0.0;
         }
 
+        $digitsPlus1 = $digits + 1;
         if ($number < 0.0) {
-            return round($number + 0.5 * 0.1 ** $digits - self::ROUNDING_ADJUSTMENT, $digits, PHP_ROUND_HALF_UP);
+            if ($digitsPlus1 < 0) {
+                return round($number + 0.5 * 0.1 ** $digits, $digits, PHP_ROUND_HALF_UP);
+            }
+            $result = sprintf("%.{$digitsPlus1}F", $number + 0.5 * 0.1 ** $digits);
+
+            return round((float) $result, $digits, PHP_ROUND_HALF_UP);
+        }
+
+        if ($digitsPlus1 < 0) {
+            return round($number - 0.5 * 0.1 ** $digits, $digits, PHP_ROUND_HALF_UP);
         }
 
-        return round($number - 0.5 * 0.1 ** $digits + self::ROUNDING_ADJUSTMENT, $digits, PHP_ROUND_HALF_UP);
+        $result = sprintf("%.{$digitsPlus1}F", $number - 0.5 * 0.1 ** $digits);
+
+        return round((float) $result, $digits, PHP_ROUND_HALF_UP);
     }
 
     /**
diff --git a/src/PhpSpreadsheet/Calculation/MathTrig/Trunc.php b/src/PhpSpreadsheet/Calculation/MathTrig/Trunc.php
index 44aedd2ca..f36f14d70 100644
--- a/src/PhpSpreadsheet/Calculation/MathTrig/Trunc.php
+++ b/src/PhpSpreadsheet/Calculation/MathTrig/Trunc.php
@@ -13,6 +13,11 @@ class Trunc
      * TRUNC.
      *
      * Truncates value to the number of fractional digits by number_digits.
+     * This will probably not be the precise result in the unlikely
+     * event that the number of digits to the left of the decimal
+     * plus the number of digits to the right exceeds PHP_FLOAT_DIG
+     * (or possibly that value minus 1).
+     * Excel is unlikely to do any better.
      *
      * @param array|float $value Or can be an array of values
      * @param array|int $digits Or can be an array of values
@@ -34,15 +39,27 @@ class Trunc
             return $e->getMessage();
         }
 
-        $digits = floor($digits);
+        if ($value == 0) {
+            return $value;
+        }
 
-        // Truncate
-        $adjust = 10 ** $digits;
+        if ($value >= 0) {
+            $minusSign = '';
+        } else {
+            $minusSign = '-';
+            $value = -$value;
+        }
+        $digits = (int) floor($digits);
+        if ($digits < 0) {
+            $result = (float) (substr(sprintf('%.0F', $value), 0, $digits) . str_repeat('0', -$digits));
 
-        if (($digits > 0) && (rtrim((string) (int) ((abs($value) - abs((int) $value)) * $adjust), '0') < $adjust / 10)) {
-            return $value;
+            return ($minusSign === '') ? $result : -$result;
         }
+        $decimals = (floor($value) == (int) $value) ? (PHP_FLOAT_DIG - strlen((string) (int) $value)) : $digits;
+        $resultString = ($decimals < 0) ? sprintf('%F', $value) : sprintf('%.' . $decimals . 'F', $value);
+        $regExp = '/([.]\\d{' . $digits . '})\\d+$/';
+        $result = $minusSign . (preg_replace($regExp, '$1', $resultString) ?? $resultString);
 
-        return ((int) ($value * $adjust)) / $adjust;
+        return (float) $result;
     }
 }


Test patch:
diff --git a/tests/PhpSpreadsheetTests/Calculation/Functions/MathTrig/TruncTest.php b/tests/PhpSpreadsheetTests/Calculation/Functions/MathTrig/TruncTest.php
index beaf0a831..e82d7ccd9 100644
--- a/tests/PhpSpreadsheetTests/Calculation/Functions/MathTrig/TruncTest.php
+++ b/tests/PhpSpreadsheetTests/Calculation/Functions/MathTrig/TruncTest.php
@@ -47,4 +47,36 @@ class TruncTest extends AllSetupTeardown
             'matrix' => [[[3.14, 3.141], [3.14159, 3.14159265]], '3.1415926536', '{2, 3; 5, 8}'],
         ];
     }
+
+    /**
+     * @dataProvider providerTooMuchPrecision
+     */
+    public function testTooMuchPrecision(mixed $expectedResult, float|int|string $value, int $digits = 1): void
+    {
+        // This test is pretty screwy. Possibly shouldn't even attempt it.
+        // At any rate, these results seem to indicate that PHP
+        // maximum precision is PHP_FLOAT_DIG - 1 digits, not PHP_FLOAT_DIG.
+        // If that changes, at least one of these tests will have to change.
+        $sheet = $this->getSheet();
+        $sheet->getCell('E1')->setValue($value);
+        $sheet->getCell('E2')->setValue("=TRUNC(E1,$digits)");
+        $result = $sheet->getCell('E2')->getCalculatedValue();
+        self::assertSame($expectedResult, (string) $result);
+    }
+
+    public static function providerTooMuchPrecision(): array
+    {
+        $max64Plus1 = 9223372036854775808;
+        $stringMax = (string) $max64Plus1;
+
+        return [
+            '2 digits less than PHP_FLOAT_DIG' => ['1' . str_repeat('0', PHP_FLOAT_DIG - 4) . '1.2', 10.0 ** (PHP_FLOAT_DIG - 3) + 1.2, 1],
+            '1 digit less than PHP_FLOAT_DIG' => ['1' . str_repeat('0', PHP_FLOAT_DIG - 3) . '1', 10.0 ** (PHP_FLOAT_DIG - 2) + 1.2, 1],
+            'PHP_FLOAT_DIG' => ['1.0E+' . (PHP_FLOAT_DIG - 1), 10.0 ** (PHP_FLOAT_DIG - 1) + 1.2, 1],
+            '1 digit more than PHP_FLOAT_DIG' => ['1.0E+' . PHP_FLOAT_DIG, 10.0 ** PHP_FLOAT_DIG + 1.2, 1],
+            '32bit exceed int max' => ['3123456780', 3123456789, -1],
+            '64bit exceed int max neg decimals' => [$stringMax, $max64Plus1, -1],
+            '64bit exceed int max pos decimals' => [$stringMax, $max64Plus1, 1],
+        ];
+    }
 }
diff --git a/tests/data/Calculation/MathTrig/ROUNDDOWN.php b/tests/data/Calculation/MathTrig/ROUNDDOWN.php
index 3a65c13f2..fed45cd44 100644
--- a/tests/data/Calculation/MathTrig/ROUNDDOWN.php
+++ b/tests/data/Calculation/MathTrig/ROUNDDOWN.php
@@ -33,4 +33,5 @@ return [
     [0, 'B1, 0'],
     ['exception', ''],
     ['exception', '35.51'],
+    'negative number and precision' => [-31400, '-31415.92654, -2'],
 ];
diff --git a/tests/data/Calculation/MathTrig/ROUNDUP.php b/tests/data/Calculation/MathTrig/ROUNDUP.php
index 9014a9e3c..9683d54d4 100644
--- a/tests/data/Calculation/MathTrig/ROUNDUP.php
+++ b/tests/data/Calculation/MathTrig/ROUNDUP.php
@@ -33,4 +33,5 @@ return [
     [0, 'B1, 0'],
     ['exception', ''],
     ['exception', '35.51'],
+    'negative number and precision' => [-31500, '-31415.92654, -2'],
 ];
diff --git a/tests/data/Calculation/MathTrig/TRUNC.php b/tests/data/Calculation/MathTrig/TRUNC.php
index 40fa52160..a92d502b2 100644
--- a/tests/data/Calculation/MathTrig/TRUNC.php
+++ b/tests/data/Calculation/MathTrig/TRUNC.php
@@ -11,6 +11,7 @@ return [
     [-31415.92654, '-31415.92654, 10'],
     [31415.92, '31415.92654, 2'],
     [31400, '31415.92654, -2'],
+    'negative number and precision' => [-31400, '-31415.92654, -2'],
     [0, '31415.92654, -10'],
     [0, '-31415.92654, -10'],
     [12000, '12345.6789, -3'],
@@ -32,4 +33,23 @@ return [
     [-3, 'A4'],
     [-5, 'A5'],
     [0, 'B1'],
+    'issue4113' => [1.0, '1.01, 1'],
+    'issue4113 negative' => [-1.0, '-1.01, 1'],
+    'issue4113 additional' => [10.04, '10.04, 2'],
+    'issue4113 additional negative' => [-10.04, '-10.04, 2'],
+    'issue4113 small fraction keep all' => [0.04, '0.04, 2'],
+    'issue4113 small negative fraction keep all' => [-0.04, '-0.04, 2'],
+    'issue4113 small fraction lose some' => [0.0, '0.01, 1'],
+    'issue4113 small negative fraction lose some' => [0.0, '-0.001, 1'],
+    'issue4113 example 3' => [-43747, '-43747.99122596, 0'],
+    'issue4113 example 3 positive' => [43747, '43747.99122596, 0'],
+    'issue4113 example 4' => [-9.11, '-9.1196419, 2'],
+    'issue4113 example 5' => [-42300.65, '-42300.65099338, 3'],
+    'issue4113 example 6 variant 1' => [0.000012, '0.0000123, 6'],
+    'issue4113 example 6 variant 2' => [0.0000123, '0.0000123, 8'],
+    'issue4113 example 6 variant 3' => [-0.000012, '-0.0000123, 6'],
+    'issue4113 example 6 variant 4' => [-0.0000123, '-0.0000123, 8'],
+    'issue4113 example 6 variant 5' => [0.000012, '1.23E-5, 6'],
+    'issue4113 example 6 variant 6' => [-0.0000123, '-1.23E-5, 8'],
+    'exceed 32-bit int max' => [3123456780, '3123456789, -1'],
 ];