Repository: PHPOffice/PhpSpreadsheet (php)

Issue:
Consequetive "=" in a value passed taken as datatype FORMULA which throws an error.
This is:

```
- [Y] 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?
Assume a cell value is "=============".
It considers datatype for this "FORMULA" which is wrong and throws error.
It should consider it as STRING.


version 0.2.9 PHPOffice
src/PhpSpreadsheet/Cell/DefaultValueBinder.php LINE 58 
````
    } elseif ($pValue[0] === '=' && strlen($pValue) > 1) {
            return DataType::TYPE_FORMULA;
    }
````

### What is the current behavior?

````  
Report Data!L1457 -> Formula Error: An unexpected error occured Traces:#0 
/var/app/current/_protected/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Worksheet/Worksheet.php(756): PhpOffice\PhpSpreadsheet\Cell\Cell->getCalculatedValue()

````

### What are the steps to reproduce?

Just pass "===============" as any cell value.


### Which versions of PhpSpreadsheet and PHP are affected?
version 0.2.9 PHPOffice


Pull request description:
Change Style Without Affecting Current Cell/Sheet, and Invalid Formulas
Fix #1310, which was closed as stale in 2020, but which I will now reopen. Supersedes PR #1311 (@jaiminmoslake7020), from which I will remove the stale label but leave closed. The issue and the PR were too limited  - they detected that the use of two equal signs at the start of a string made for an invalid formula, but there are variations, trivial and otherwise, which might also be detected. Using `setValue` with a string which starts with an equal sign will now attempt to parse (not evaluate) the formula; for certain situations in which the parser throws an exception, the string will be treated as a string rather than a formula. An example where it will still be treated as a formula is a 3D range reference, where the problem is not that it can't be parsed, but rather that the formula isn't supported (see unit test Calculation/Engine/RangeTest::test3dRangeEvaluation). Allowing such a formula might cause problems later on, but that is already what happens.

A string beginning with an equal sign but which isn't treated as a formula will automatically set the `quotePrefix` attribute to `true`; all other `setValue` attempts will set it to `false`. This avoids the problem of a lingering value causing problems later on.

It has long been a matter of discontent that setting a style can change the selected cells. A new method is added to `Worksheet`:
```php
applyStylesFromArray(string $coordinate, array $styleArray)
```
This will attempt to guarantee that the active sheet in the current spreadsheet, and the selected cells in the current worksheet, remain undisturbed after the call. The setting of `quotePrefix` above is the first use of the new method.

This is:

- [ ] a bugfix
- [x] 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 12b595933..ccb6c3af1 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -10,6 +10,7 @@ and this project adheres to [Semantic Versioning](https://semver.org).
 ### Added
 
 - Xlsx Reader Optionally Ignore Rows With No Cells. [Issue #3982](https://github.com/PHPOffice/PhpSpreadsheet/issues/3982) [PR #4035](https://github.com/PHPOffice/PhpSpreadsheet/pull/4035)
+- Means to change style without affecting current cell/sheet. [PR #4073](https://github.com/PHPOffice/PhpSpreadsheet/pull/4073)
 - Option for CSV output file to have varying numbers of columns for each row. [Issue #1415](https://github.com/PHPOffice/PhpSpreadsheet/issues/1415) [PR #4076](https://github.com/PHPOffice/PhpSpreadsheet/pull/4076)
 
 ### Changed
@@ -38,6 +39,7 @@ and this project adheres to [Semantic Versioning](https://semver.org).
 - Problem rendering line chart with missing plot label. [PR #4074](https://github.com/PHPOffice/PhpSpreadsheet/pull/4074)
 - More RTL in Xlsx/Html Comments [Issue #4004](https://github.com/PHPOffice/PhpSpreadsheet/issues/4004) [PR #4065](https://github.com/PHPOffice/PhpSpreadsheet/pull/4065)
 - Empty String in sharedStrings. [Issue #4063](https://github.com/PHPOffice/PhpSpreadsheet/issues/4063) [PR #4064](https://github.com/PHPOffice/PhpSpreadsheet/pull/4064)
+- Treat invalid formulas as strings. [Issue #1310](https://github.com/PHPOffice/PhpSpreadsheet/issues/1310) [PR #4073](https://github.com/PHPOffice/PhpSpreadsheet/pull/4073)
 
 ## 2024-05-11 - 2.1.0
 
diff --git a/src/PhpSpreadsheet/Calculation/Calculation.php b/src/PhpSpreadsheet/Calculation/Calculation.php
index 2f13a1634..c5dd47310 100644
--- a/src/PhpSpreadsheet/Calculation/Calculation.php
+++ b/src/PhpSpreadsheet/Calculation/Calculation.php
@@ -4065,7 +4065,7 @@ class Calculation
             $opCharacter = $formula[$index]; //    Get the first character of the value at the current index position
 
             // Check for two-character operators (e.g. >=, <=, <>)
-            if ((isset(self::$comparisonOperators[$opCharacter])) && (strlen($formula) > $index) && (isset(self::$comparisonOperators[$formula[$index + 1]]))) {
+            if ((isset(self::$comparisonOperators[$opCharacter])) && (strlen($formula) > $index) && isset($formula[$index + 1], self::$comparisonOperators[$formula[$index + 1]])) {
                 $opCharacter .= $formula[++$index];
             }
             //    Find out if we're currently at the beginning of a number, variable, cell/row/column reference,
diff --git a/src/PhpSpreadsheet/Cell/Cell.php b/src/PhpSpreadsheet/Cell/Cell.php
index 0f0075bca..8cf2cf6c1 100644
--- a/src/PhpSpreadsheet/Cell/Cell.php
+++ b/src/PhpSpreadsheet/Cell/Cell.php
@@ -255,6 +255,7 @@ class Cell implements Stringable
     public function setValueExplicit(mixed $value, string $dataType = DataType::TYPE_STRING): self
     {
         $oldValue = $this->value;
+        $quotePrefix = false;
 
         // set the value according to data type
         switch ($dataType) {
@@ -267,6 +268,10 @@ class Cell implements Stringable
                 // no break
             case DataType::TYPE_STRING:
                 // Synonym for string
+                if (is_string($value) && strlen($value) > 1 && $value[0] === '=') {
+                    $quotePrefix = true;
+                }
+                // no break
             case DataType::TYPE_INLINE:
                 // Rich text
                 if ($value !== null && !is_scalar($value) && !($value instanceof Stringable)) {
@@ -312,6 +317,7 @@ class Cell implements Stringable
         $this->updateInCollection();
         $cellCoordinate = $this->getCoordinate();
         self::updateIfCellIsTableHeader($this->getParent()?->getParent(), $this, $oldValue, $value);
+        $this->getWorksheet()->applyStylesFromArray($cellCoordinate, ['quotePrefix' => $quotePrefix]);
 
         return $this->getParent()?->get($cellCoordinate) ?? $this;
     }
diff --git a/src/PhpSpreadsheet/Cell/DefaultValueBinder.php b/src/PhpSpreadsheet/Cell/DefaultValueBinder.php
index 6f75a90a8..f36934ed3 100644
--- a/src/PhpSpreadsheet/Cell/DefaultValueBinder.php
+++ b/src/PhpSpreadsheet/Cell/DefaultValueBinder.php
@@ -3,6 +3,8 @@
 namespace PhpOffice\PhpSpreadsheet\Cell;
 
 use DateTimeInterface;
+use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
+use PhpOffice\PhpSpreadsheet\Calculation\Exception as CalculationException;
 use PhpOffice\PhpSpreadsheet\Exception as SpreadsheetException;
 use PhpOffice\PhpSpreadsheet\RichText\RichText;
 use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
@@ -68,6 +70,23 @@ class DefaultValueBinder implements IValueBinder
             throw new SpreadsheetException("unusable type $gettype");
         }
         if (strlen($value) > 1 && $value[0] === '=') {
+            $calculation = new Calculation();
+            $calculation->disableBranchPruning();
+
+            try {
+                if (empty($calculation->parseFormula($value))) {
+                    return DataType::TYPE_STRING;
+                }
+            } catch (CalculationException $e) {
+                $message = $e->getMessage();
+                if (
+                    $message === 'Formula Error: An unexpected error occurred'
+                    || str_contains($message, 'has no operands')
+                ) {
+                    return DataType::TYPE_STRING;
+                }
+            }
+
             return DataType::TYPE_FORMULA;
         }
         if (preg_match('/^[\+\-]?(\d+\\.?\d*|\d*\\.?\d+)([Ee][\-\+]?[0-2]?\d{1,3})?$/', $value)) {
diff --git a/src/PhpSpreadsheet/Cell/StringValueBinder.php b/src/PhpSpreadsheet/Cell/StringValueBinder.php
index 6ff258d93..d86cdabd3 100644
--- a/src/PhpSpreadsheet/Cell/StringValueBinder.php
+++ b/src/PhpSpreadsheet/Cell/StringValueBinder.php
@@ -8,7 +8,7 @@ use PhpOffice\PhpSpreadsheet\RichText\RichText;
 use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
 use Stringable;
 
-class StringValueBinder implements IValueBinder
+class StringValueBinder extends DefaultValueBinder implements IValueBinder
 {
     protected bool $convertNull = true;
 
@@ -87,12 +87,9 @@ class StringValueBinder implements IValueBinder
             $cell->setValueExplicit($value, DataType::TYPE_BOOL);
         } elseif ((is_int($value) || is_float($value)) && $this->convertNumeric === false) {
             $cell->setValueExplicit($value, DataType::TYPE_NUMERIC);
-        } elseif (is_string($value) && strlen($value) > 1 && $value[0] === '=' && $this->convertFormula === false) {
+        } elseif (is_string($value) && strlen($value) > 1 && $value[0] === '=' && $this->convertFormula === false && parent::dataTypeForValue($value) === DataType::TYPE_FORMULA) {
             $cell->setValueExplicit($value, DataType::TYPE_FORMULA);
         } else {
-            if (is_string($value) && strlen($value) > 1 && $value[0] === '=') {
-                $cell->getStyle()->setQuotePrefix(true);
-            }
             $cell->setValueExplicit((string) $value, DataType::TYPE_STRING);
         }
 
diff --git a/src/PhpSpreadsheet/Worksheet/Worksheet.php b/src/PhpSpreadsheet/Worksheet/Worksheet.php
index 8adada4c1..0bb64ba59 100644
--- a/src/PhpSpreadsheet/Worksheet/Worksheet.php
+++ b/src/PhpSpreadsheet/Worksheet/Worksheet.php
@@ -3673,4 +3673,19 @@ class Worksheet implements IComparable
             }
         }
     }
+
+    public function applyStylesFromArray(string $coordinate, array $styleArray): bool
+    {
+        $spreadsheet = $this->parent;
+        if ($spreadsheet === null) {
+            return false;
+        }
+        $activeSheetIndex = $spreadsheet->getActiveSheetIndex();
+        $originalSelected = $this->selectedCells;
+        $this->getStyle($coordinate)->applyFromArray($styleArray);
+        $this->selectedCells = $originalSelected;
+        $spreadsheet->setActiveSheetIndex($activeSheetIndex);
+
+        return true;
+    }
 }


Test patch:
diff --git a/tests/PhpSpreadsheetTests/Calculation/Engine/RangeTest.php b/tests/PhpSpreadsheetTests/Calculation/Engine/RangeTest.php
index fbac0ff91..aa7bc529d 100644
--- a/tests/PhpSpreadsheetTests/Calculation/Engine/RangeTest.php
+++ b/tests/PhpSpreadsheetTests/Calculation/Engine/RangeTest.php
@@ -4,6 +4,7 @@ declare(strict_types=1);
 
 namespace PhpOffice\PhpSpreadsheetTests\Calculation\Engine;
 
+use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
 use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
 use PhpOffice\PhpSpreadsheet\NamedRange;
 use PhpOffice\PhpSpreadsheet\Spreadsheet;
@@ -13,13 +14,23 @@ class RangeTest extends TestCase
 {
     private string $incompleteMessage = 'Must be revisited';
 
-    private Spreadsheet $spreadSheet;
+    private ?Spreadsheet $spreadSheet = null;
 
-    protected function setUp(): void
+    protected function getSpreadsheet(): Spreadsheet
     {
-        $this->spreadSheet = new Spreadsheet();
-        $this->spreadSheet->getActiveSheet()
+        $spreadsheet = new Spreadsheet();
+        $spreadsheet->getActiveSheet()
             ->fromArray(array_chunk(range(1, 240), 6), null, 'A1', true);
+
+        return $spreadsheet;
+    }
+
+    protected function tearDown(): void
+    {
+        if ($this->spreadSheet !== null) {
+            $this->spreadSheet->disconnectWorksheets();
+            $this->spreadSheet = null;
+        }
     }
 
     /**
@@ -27,6 +38,7 @@ class RangeTest extends TestCase
      */
     public function testRangeEvaluation(string $formula, int|string $expectedResult): void
     {
+        $this->spreadSheet = $this->getSpreadsheet();
         $workSheet = $this->spreadSheet->getActiveSheet();
         $workSheet->setCellValue('H1', $formula);
 
@@ -64,8 +76,20 @@ class RangeTest extends TestCase
         ];
     }
 
+    public function test3dRangeParsing(): void
+    {
+        // This test shows that parsing throws exception.
+        // Next test shows that formula is still treated as a formula
+        //     despite the parse failure.
+        $this->expectExceptionMessage('3D Range references are not yet supported');
+        $calculation = new Calculation();
+        $calculation->disableBranchPruning();
+        $calculation->parseFormula('=SUM(Worksheet!A1:Worksheet2!B3');
+    }
+
     public function test3dRangeEvaluation(): void
     {
+        $this->spreadSheet = $this->getSpreadsheet();
         $workSheet = $this->spreadSheet->getActiveSheet();
         $workSheet->setCellValue('E1', '=SUM(Worksheet!A1:Worksheet2!B3)');
 
@@ -78,6 +102,7 @@ class RangeTest extends TestCase
      */
     public function testNamedRangeEvaluation(array $ranges, string $formula, int $expectedResult): void
     {
+        $this->spreadSheet = $this->getSpreadsheet();
         $workSheet = $this->spreadSheet->getActiveSheet();
         foreach ($ranges as $id => $range) {
             $this->spreadSheet->addNamedRange(new NamedRange('GROUP' . ++$id, $workSheet, $range));
@@ -116,6 +141,7 @@ class RangeTest extends TestCase
      */
     public function testUTF8NamedRangeEvaluation(array $names, array $ranges, string $formula, int $expectedResult): void
     {
+        $this->spreadSheet = $this->getSpreadsheet();
         $workSheet = $this->spreadSheet->getActiveSheet();
         foreach ($names as $index => $name) {
             $range = $ranges[$index];
@@ -144,6 +170,7 @@ class RangeTest extends TestCase
         if ($this->incompleteMessage !== '') {
             self::markTestIncomplete($this->incompleteMessage);
         }
+        $this->spreadSheet = $this->getSpreadsheet();
 
         $workSheet = $this->spreadSheet->getActiveSheet();
         $this->spreadSheet->addNamedRange(new NamedRange('COMPOSITE', $workSheet, $composite));
diff --git a/tests/PhpSpreadsheetTests/Cell/AdvancedValueBinderTest.php b/tests/PhpSpreadsheetTests/Cell/AdvancedValueBinderTest.php
index d3f0ad664..d8de97a33 100644
--- a/tests/PhpSpreadsheetTests/Cell/AdvancedValueBinderTest.php
+++ b/tests/PhpSpreadsheetTests/Cell/AdvancedValueBinderTest.php
@@ -6,6 +6,7 @@ namespace PhpOffice\PhpSpreadsheetTests\Cell;
 
 use PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder;
 use PhpOffice\PhpSpreadsheet\Cell\Cell;
+use PhpOffice\PhpSpreadsheet\Cell\DataType;
 use PhpOffice\PhpSpreadsheet\Cell\IValueBinder;
 use PhpOffice\PhpSpreadsheet\Settings;
 use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
@@ -232,4 +233,31 @@ class AdvancedValueBinderTest extends TestCase
             ["Hello\nWorld", true],
         ];
     }
+
+    /**
+     * @dataProvider formulaProvider
+     */
+    public function testFormula(string $value, string $dataType): void
+    {
+        $spreadsheet = new Spreadsheet();
+        $sheet = $spreadsheet->getActiveSheet();
+
+        $sheet->getCell('A1')->setValue($value);
+        self::assertSame($dataType, $sheet->getCell('A1')->getDataType());
+        if ($dataType === DataType::TYPE_FORMULA) {
+            self::assertFalse($sheet->getStyle('A1')->getQuotePrefix());
+        } else {
+            self::assertTrue($sheet->getStyle('A1')->getQuotePrefix());
+        }
+
+        $spreadsheet->disconnectWorksheets();
+    }
+
+    public static function formulaProvider(): array
+    {
+        return [
+            'normal formula' => ['=SUM(A1:C3)', DataType::TYPE_FORMULA],
+            'issue 1310' => ['======', DataType::TYPE_STRING],
+        ];
+    }
 }
diff --git a/tests/PhpSpreadsheetTests/Cell/StringValueBinderTest.php b/tests/PhpSpreadsheetTests/Cell/StringValueBinderTest.php
index 43ac2fbd1..71006fff8 100644
--- a/tests/PhpSpreadsheetTests/Cell/StringValueBinderTest.php
+++ b/tests/PhpSpreadsheetTests/Cell/StringValueBinderTest.php
@@ -211,13 +211,19 @@ class StringValueBinderTest extends TestCase
         $cell->setValue($value);
         self::assertSame($expectedValue, $cell->getValue());
         self::assertSame($expectedDataType, $cell->getDataType());
+        if ($expectedDataType === DataType::TYPE_FORMULA) {
+            self::assertFalse($sheet->getStyle('A1')->getQuotePrefix());
+        } else {
+            self::assertTrue($sheet->getStyle('A1')->getQuotePrefix());
+        }
         $spreadsheet->disconnectWorksheets();
     }
 
     public static function providerDataValuesSuppressFormulaConversion(): array
     {
         return [
-            ['=SUM(A1:C3)', '=SUM(A1:C3)', DataType::TYPE_FORMULA, false],
+            'normal formula' => ['=SUM(A1:C3)', '=SUM(A1:C3)', DataType::TYPE_FORMULA],
+            'issue 1310' => ['======', '======', DataType::TYPE_STRING],
         ];
     }
 
diff --git a/tests/data/Cell/DefaultValueBinder.php b/tests/data/Cell/DefaultValueBinder.php
index b9f3d51e1..bd4295c0b 100644
--- a/tests/data/Cell/DefaultValueBinder.php
+++ b/tests/data/Cell/DefaultValueBinder.php
@@ -83,4 +83,7 @@ return [
         's',
         '1234567890123459012345689012345690',
     ],
+    'Issue 1310 Multiple = at start' => ['s', '======'],
+    'Issue 1310 Variant 1' => ['s', '= ====='],
+    'Issue 1310 Variant 2' => ['s', '=2*3='],
 ];