Repository: PHPOffice/PhpSpreadsheet (php)

Issue:
cell values changed in a cloned ws reflect in the original one also
This is:

```
- [x] a bug report
- [ ] a feature request
```

### What is the expected behavior?

value in worksheet 'Condendes B' cell A1 is set as `=sum('Detailed B'!A1:A10)` and should remain that way

### What is the current behavior?

value in worksheet 'Condendes B' cell A1 is changes in `=sum('Detailed Total'!A1:A10)` after the worksheet object is cloned and cell A1 is changed in the cloned object

### What are the steps to reproduce?

Please provide a [Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve) of code that exhibits the issue without relying on an external Excel file or a web server:

```php
<?php

require_once 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$xlsx = new Spreadsheet();
$xlsx->removeSheetByIndex(0);

$availableWs = [];

// this are set to a sum of values from sheets we will created 
// some line below
$worksheet = $xlsx->createSheet();
$worksheet->setTitle('Condensed A');
$worksheet->getCell("A1")->setValue("=sum('Detailed A'!A1:A10)");
$worksheet->getCell("A2")->setValue(rand(1, 30));
$availableWs[] = 'Condensed A';

$worksheet = $xlsx->createSheet();
$worksheet->setTitle('Condensed B');
$worksheet->getCell("A1")->setValue("=sum('Detailed B'!A1:A10)");
$worksheet->getCell("A2")->setValue(rand(1, 30));
$availableWs[] = 'Condensed B';

// at this point the value in worksheet 'Condendes B' cell A1 is
// =sum('Detailed B'!A1:A10)

// worksheet in question is cloned and totals are attached
$totalWs = clone $xlsx->getSheet($xlsx->getSheetCount() - 1);
$totalWs->setTitle('Condensed Total');
$formula = '=';
foreach ($availableWs as $ws) {
    $formula .= sprintf("+'%s'!A2", $ws);
}
$totalWs->getCell("A1")->setValue("=sum('Detailed Total'!A1:A10)");
$totalWs->getCell("A2")->setValue($formula);
$xlsx->addSheet($totalWs);

$availableWs = [];

$worksheet = $xlsx->createSheet();
$worksheet->setTitle('Detailed A');
for ($step = 1; $step <= 10; $step++) {
    $worksheet->getCell("A{$step}")->setValue(rand(1, 30));
}
$availableWs[] = 'Detailed A';

$worksheet = $xlsx->createSheet();
$worksheet->setTitle('Detailed B');
for ($step = 1; $step <= 10; $step++) {
    $worksheet->getCell("A{$step}")->setValue(rand(1, 30));
}
$availableWs[] = 'Detailed B';

$totalWs = clone $xlsx->getSheet($xlsx->getSheetCount() - 1);
$totalWs->setTitle('Detailed Total');

for ($step = 1; $step <= 10; $step++) {
    $formula = '=';
    foreach ($availableWs as $ws) {
        $formula .= sprintf("+'%s'!A%s", $ws, $step);
    }
    $totalWs->getCell("A{$step}")->setValue($formula);
}
$xlsx->addSheet($totalWs);

// the value in worksheet 'Condendes B' cell A1 is
// =sum('Detailed Total'!A1:A10)

$writer = new Xlsx($xlsx);
$writer->save('hello world.xlsx');
```


### Which versions of PhpSpreadsheet and PHP are affected?
1.4
php 7.2.6 win & linux



Pull request description:
Retitling Cloned Worksheets
Fix #641 (marked stale in 2018, but now reopened). When a sheet's title is changed, PhpSpreadsheet updates references to the old sheet name found in formulas. Which is a good idea when the sheet is attached to the spreadsheet, but a bad idea when it isn't (often because it has been cloned without re-attaching to the spreadsheet). This PR continues to change formulas in the former case, but will no longer do so for the latter.

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 00f19a8cd..1d78534a3 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -26,6 +26,7 @@ and this project adheres to [Semantic Versioning](https://semver.org).
 ### Fixed
 
 - Xlsx Reader Shared Formula with Boolean Result. Partial solution for [Issue #4280](https://github.com/PHPOffice/PhpSpreadsheet/issues/4280) [PR #4281](https://github.com/PHPOffice/PhpSpreadsheet/pull/4281)
+- Retitling cloned Worksheets. [Issue #641](https://github.com/PHPOffice/PhpSpreadsheet/issues/641) [PR #4302](https://github.com/PHPOffice/PhpSpreadsheet/pull/4302)
 
 ## 2024-12-26 - 3.7.0
 
diff --git a/composer.lock b/composer.lock
index cc706e608..3025170cb 100644
--- a/composer.lock
+++ b/composer.lock
@@ -1221,16 +1221,16 @@
         },
         {
             "name": "mitoteam/jpgraph",
-            "version": "10.4.4",
+            "version": "10.4.3",
             "source": {
                 "type": "git",
                 "url": "https://github.com/mitoteam/jpgraph.git",
-                "reference": "9ad8e2fcc30f765c788a28543e9705fb541d499f"
+                "reference": "f0db97108aec23a3bbb34721365931af992b83b3"
             },
             "dist": {
                 "type": "zip",
-                "url": "https://api.github.com/repos/mitoteam/jpgraph/zipball/9ad8e2fcc30f765c788a28543e9705fb541d499f",
-                "reference": "9ad8e2fcc30f765c788a28543e9705fb541d499f",
+                "url": "https://api.github.com/repos/mitoteam/jpgraph/zipball/f0db97108aec23a3bbb34721365931af992b83b3",
+                "reference": "f0db97108aec23a3bbb34721365931af992b83b3",
                 "shasum": ""
             },
             "require": {
@@ -1270,9 +1270,9 @@
             ],
             "support": {
                 "issues": "https://github.com/mitoteam/jpgraph/issues",
-                "source": "https://github.com/mitoteam/jpgraph/tree/10.4.4"
+                "source": "https://github.com/mitoteam/jpgraph/tree/10.4.3"
             },
-            "time": "2025-01-01T05:39:20+00:00"
+            "time": "2024-12-01T06:36:31+00:00"
         },
         {
             "name": "mpdf/mpdf",
diff --git a/docs/topics/recipes.md b/docs/topics/recipes.md
index c768c49f7..8b27155d9 100644
--- a/docs/topics/recipes.md
+++ b/docs/topics/recipes.md
@@ -925,8 +925,6 @@ getStyle('A1:M500'), rather than styling the cells individually in a
 loop. This is much faster compared to looping through cells and styling
 them individually.
 
-**Tip** If you are styling entire row(s) or column(s), e.g. getStyle('A:A'), it is recommended to use applyFromArray as described below rather than setting the styles individually as described above.
-
 There is also an alternative manner to set styles. The following code
 sets a cell's style to font bold, alignment right, top border thin and a
 gradient fill:
diff --git a/src/PhpSpreadsheet/Spreadsheet.php b/src/PhpSpreadsheet/Spreadsheet.php
index 943db95cc..f40a8889a 100644
--- a/src/PhpSpreadsheet/Spreadsheet.php
+++ b/src/PhpSpreadsheet/Spreadsheet.php
@@ -514,7 +514,7 @@ class Spreadsheet implements JsonSerializable
     public function createSheet(?int $sheetIndex = null): Worksheet
     {
         $newSheet = new Worksheet($this);
-        $this->addSheet($newSheet, $sheetIndex);
+        $this->addSheet($newSheet, $sheetIndex, true);
 
         return $newSheet;
     }
@@ -535,8 +535,20 @@ class Spreadsheet implements JsonSerializable
      * @param Worksheet $worksheet The worksheet to add
      * @param null|int $sheetIndex Index where sheet should go (0,1,..., or null for last)
      */
-    public function addSheet(Worksheet $worksheet, ?int $sheetIndex = null): Worksheet
+    public function addSheet(Worksheet $worksheet, ?int $sheetIndex = null, bool $retitleIfNeeded = false): Worksheet
     {
+        if ($retitleIfNeeded) {
+            $title = $worksheet->getTitle();
+            if ($this->sheetNameExists($title)) {
+                $i = 1;
+                $newTitle = "$title $i";
+                while ($this->sheetNameExists($newTitle)) {
+                    ++$i;
+                    $newTitle = "$title $i";
+                }
+                $worksheet->setTitle($newTitle);
+            }
+        }
         if ($this->sheetNameExists($worksheet->getTitle())) {
             throw new Exception(
                 "Workbook already contains a worksheet named '{$worksheet->getTitle()}'. Rename this worksheet first."
diff --git a/src/PhpSpreadsheet/Worksheet/Worksheet.php b/src/PhpSpreadsheet/Worksheet/Worksheet.php
index 546a6ffe5..a3ee4818b 100644
--- a/src/PhpSpreadsheet/Worksheet/Worksheet.php
+++ b/src/PhpSpreadsheet/Worksheet/Worksheet.php
@@ -321,6 +321,7 @@ class Worksheet
     {
         // Set parent and title
         $this->parent = $parent;
+        $this->hash = spl_object_id($this);
         $this->setTitle($title, false);
         // setTitle can change $pTitle
         $this->setCodeName($this->getTitle());
@@ -349,7 +350,6 @@ class Worksheet
         $this->autoFilter = new AutoFilter('', $this);
         // Table collection
         $this->tableCollection = new ArrayObject();
-        $this->hash = spl_object_id($this);
     }
 
     /**
@@ -869,7 +869,7 @@ class Worksheet
             // Syntax check
             self::checkSheetTitle($title);
 
-            if ($this->parent) {
+            if ($this->parent && $this->parent->getIndex($this, true) >= 0) {
                 // Is there already such sheet name?
                 if ($this->parent->sheetNameExists($title)) {
                     // Use name, but append with lowest possible integer
@@ -899,7 +899,7 @@ class Worksheet
         // Set title
         $this->title = $title;
 
-        if ($this->parent && $this->parent->getCalculationEngine()) {
+        if ($this->parent && $this->parent->getIndex($this, true) >= 0 && $this->parent->getCalculationEngine()) {
             // New title
             $newTitle = $this->getTitle();
             $this->parent->getCalculationEngine()


Test patch:
diff --git a/tests/PhpSpreadsheetTests/Chart/ChartsDynamicTitleTest.php b/tests/PhpSpreadsheetTests/Chart/ChartsDynamicTitleTest.php
index 2546d60de..7aa484d9c 100644
--- a/tests/PhpSpreadsheetTests/Chart/ChartsDynamicTitleTest.php
+++ b/tests/PhpSpreadsheetTests/Chart/ChartsDynamicTitleTest.php
@@ -34,6 +34,7 @@ class ChartsDynamicTitleTest extends AbstractFunctional
         $writer->setIncludeCharts(true);
     }
 
+    #[\PHPUnit\Framework\Attributes\RunInSeparateProcess]
     public function testDynamicTitle(): void
     {
         // based on samples/templates/issue.3797.2007.xlsx
diff --git a/tests/PhpSpreadsheetTests/Worksheet/Issue641Test.php b/tests/PhpSpreadsheetTests/Worksheet/Issue641Test.php
new file mode 100644
index 000000000..bbb6c8bf0
--- /dev/null
+++ b/tests/PhpSpreadsheetTests/Worksheet/Issue641Test.php
@@ -0,0 +1,82 @@
+<?php
+
+declare(strict_types=1);
+
+namespace PhpOffice\PhpSpreadsheetTests\Worksheet;
+
+use PhpOffice\PhpSpreadsheet\Spreadsheet;
+use PHPUnit\Framework\TestCase;
+
+class Issue641Test extends TestCase
+{
+    /**
+     * Problem cloning sheet referred to in formulas.
+     */
+    public function testIssue641(): void
+    {
+        $xlsx = new Spreadsheet();
+        $xlsx->removeSheetByIndex(0);
+        $availableWs = [];
+
+        $worksheet = $xlsx->createSheet();
+        $worksheet->setTitle('Condensed A');
+        $worksheet->getCell('A1')->setValue("=SUM('Detailed A'!A1:A10)");
+        $worksheet->getCell('A2')->setValue(mt_rand(1, 30));
+        $availableWs[] = 'Condensed A';
+
+        $worksheet = $xlsx->createSheet();
+        $worksheet->setTitle('Condensed B');
+        $worksheet->getCell('A1')->setValue("=SUM('Detailed B'!A1:A10)");
+        $worksheet->getCell('A2')->setValue(mt_rand(1, 30));
+        $availableWs[] = 'Condensed B';
+
+        // at this point the value in worksheet 'Condensed B' cell A1 is
+        // =SUM('Detailed B'!A1:A10)
+
+        // worksheet in question is cloned and totals are attached
+        $totalWs1 = clone $xlsx->getSheet($xlsx->getSheetCount() - 1);
+        $totalWs1->setTitle('Condensed Total');
+        $xlsx->addSheet($totalWs1);
+        $formula = '=';
+        foreach ($availableWs as $ws) {
+            $formula .= sprintf("+'%s'!A2", $ws);
+        }
+        $totalWs1->getCell('A1')->setValue("=SUM('Detailed Total'!A1:A10)");
+        $totalWs1->getCell('A2')->setValue($formula);
+
+        $availableWs = [];
+
+        $worksheet = $xlsx->createSheet();
+        $worksheet->setTitle('Detailed A');
+        for ($step = 1; $step <= 10; ++$step) {
+            $worksheet->getCell("A{$step}")->setValue(mt_rand(1, 30));
+        }
+        $availableWs[] = 'Detailed A';
+
+        $worksheet = $xlsx->createSheet();
+        $worksheet->setTitle('Detailed B');
+        for ($step = 1; $step <= 10; ++$step) {
+            $worksheet->getCell("A{$step}")->setValue(mt_rand(1, 30));
+        }
+        $availableWs[] = 'Detailed B';
+
+        $totalWs2 = clone $xlsx->getSheet($xlsx->getSheetCount() - 1);
+        $totalWs2->setTitle('Detailed Total');
+        $xlsx->addSheet($totalWs2);
+
+        for ($step = 1; $step <= 10; ++$step) {
+            $formula = '=';
+            foreach ($availableWs as $ws) {
+                $formula .= sprintf("+'%s'!A%s", $ws, $step);
+            }
+            $totalWs2->getCell("A{$step}")->setValue($formula);
+        }
+
+        self::assertSame("=SUM('Detailed A'!A1:A10)", $xlsx->getSheetByName('Condensed A')?->getCell('A1')?->getValue());
+        self::assertSame("=SUM('Detailed B'!A1:A10)", $xlsx->getSheetByName('Condensed B')?->getCell('A1')?->getValue());
+        self::assertSame("=SUM('Detailed Total'!A1:A10)", $xlsx->getSheetByName('Condensed Total')?->getCell('A1')?->getValue());
+        self::assertSame("=+'Detailed A'!A1+'Detailed B'!A1", $xlsx->getSheetByName('Detailed Total')?->getCell('A1')?->getValue());
+
+        $xlsx->disconnectWorksheets();
+    }
+}