Repository: PHPOffice/PhpSpreadsheet (php)

Issue:
Problem using serialized spreadsheet instance - static private members become null causing crash
This is:

```
- [x] a bug report
```
- We think this is a bug, but if serialization is not explicitly supported/envisioned - it could be considered a feature request

### What is the expected behavior?
After loading complex spreadsheets for processing - we serialize the Spreadheet instance and save it in a cache for performance (as the raw load is taking a few seconds depending on the sheet and we are trying to keep the execution time down for a microservice call) - the unserialize is very quick.

### What is the current behavior?
This used to work, however when we went from an older version to 1.14.1 the behaviour changed as an instance of Calculation is on the Spreadsheet instance, which when loaded normally from loading a SS file - it populated the private static member on the Calculation class "private static $referenceHelper" in the constructor [ self::$referenceHelper = ReferenceHelper::getInstance(); ].
The problem is that after serialized and then the serialized version is unserialized back into objects - the class instance of Calculation is there inflated on spreadsheet instance - so it uses it - BUT that instance has self::$referenceHelper as null, as serialize does not touch statics and constructors are not invoked for unserialize - the class however assumes the instance has the private static member populated.

This results in a break when a get calculated value call on a cell results in the evaluateDefinedName method being invoked - it breaks on :
"$definedNameValue = self::$referenceHelper->updateFormulaReferencesAnyWorksheet( ..."

In our application we have applied a workaround which checks is_null immediately prior and instantiates it if needed.  To keep us moving forward.

### What are the steps to reproduce?

1. Load a spreadsheet with a named range in a formula on a cell
2. Serialize the ss and save out (we do to a cache folder)
- new session
3. Load the ss from cached serialized object from file using unserialize
4. get calculated value on cell with formula

### Which versions of PhpSpreadsheet and PHP are affected?

We noticed it when we moved to 1.14.1 and is still a problem in 1.15.0


Cell object `formulaAttributes` property stores SimpleXMLElement preventing serialization
This is:

```
- [ ] a bug report
- [X] a feature request
- [X] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)
```

### What is the expected behavior?
For example, if you want to cache a PhpSpreadsheet object using redis or apcu to avoid re-reading an excel file again and again, you need to be able to serialize a PhpSpreadsheet object. Unfortunately, PHP can't serialize a SimpleXMLElement and you will receive an exception `Serialization of 'SimpleXMLElement' is not allowed`.

The culprit is the `formulaAttributes` private property of class  `PhpOffice\PhpSpreadsheet\Cell\Cell`.
This property is used by a getter and a setter in this class but doesn't really seems to be used (yet!) anywhere else in the project. 

There is just one use of the setter in the Xslx reader and some commented code in the `Worksheet` class in `writeCellFormula` method.

### What is the current behavior?

I think it would be a good idea to transform this SimpleXMLElement object containing the attributes of the `c` (cell) XML tag into a real basic PHP array. So, the `formulaAttributes` property would be a basic array instead of a SimpleXMLElement.

### What are the steps to reproduce?

Create an excel file with value `1` in A1 and `=A1+1` formula in A2, then extend the formula to 10 rows.
This will create a shared formula. This way some `c` XML tag will have both `t="shared"` and `s="(int)"` attributes.

Then, read the Excel file and try to serialize the resulting `PhpOffice\PhpSpreadsheet\Spreadsheet` object with:

`serialize($spreadsheet)`

### Which versions of PhpSpreadsheet and PHP are affected?

1.15.0


Pull request:
Allow Spreadsheet Serialization
Fix #4324. Serialization was explicity forbidden by PR #3199. This was in response to several issues, and concern that the Spreadsheet object contained non-serializable properties. This PR restores the ability to serialize a spreadsheet. Json serialization remains unsupported.

Fix #1757, closed in Nov. 2023 but just reopened. At the time, Cell property `formulaAttributes` was stored as a SimpleXmlElement. Dynamic arrays PR #3962 defined that property as `null|array<string, string>` in the doc block. However, it left the formal Php type for the property as `mixed`. This PR changes the formal type to `?array`.

Fix #1741, closed in Dec. 2020 but just reopened. Calculation property `referenceHelper` was defined as static, and, since static properties don't take part in serialization, this caused a problem after unserialization. There are at least 3 trivial ways to deal with this - make it an instance property, reinitialize it when unserialized using a wakeup method, or remove the property altogether. This PR uses the last of those 3.

Calculation does have other static properties. Almost all of these deal with locale. So serialize/unserialize might wind up using a default locale when non-default is desired (but not necessarily required). If that is a problem for end-users, it will be a new one, and I will work on a solution if and when the time comes.

Static property `returnArrayAsType` is potentially problematic. However, instance property `instanceArrayReturnType` is the preferred method of handling this, and using that will avoid any problems.

Issue #932 also dealt with serialization. I do not have the wherewithal to investigate that issue. If it is not solved by this and the earlier PR's, I will have to leave it to others to re-raise it.

Spreadsheet `copy` is now simplified to use serialize followed by unserialize. Formal tests are added. In addition, I have made a number of informal tests on very complicated spreadsheets, and it has performed correctly for all of them.

This is:

- [ ] a bugfix
- [x] a new feature
- [ ] refactoring
- [ ] additional unit tests

Checklist:

- [x] Changes are covered by unit tests
  - [ ] 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 ab7eff414..6d0bcdc35 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -19,6 +19,7 @@ and this project adheres to [Semantic Versioning](https://semver.org).
 ### Added
 
 - Pdf Charts and Drawings. [Discussion #4129](https://github.com/PHPOffice/PhpSpreadsheet/discussions/4129) [Discussion #4168](https://github.com/PHPOffice/PhpSpreadsheet/discussions/4168) [PR #4327](https://github.com/PHPOffice/PhpSpreadsheet/pull/4327)
+- Allow spreadsheet serialization. [Discussion #4324](https://github.com/PHPOffice/PhpSpreadsheet/discussions/4324) [Issue #1741](https://github.com/PHPOffice/PhpSpreadsheet/issues/1741) [Issue #1757](https://github.com/PHPOffice/PhpSpreadsheet/issues/1757) [PR #4326](https://github.com/PHPOffice/PhpSpreadsheet/pull/4326)
 
 ### Removed
 
diff --git a/src/PhpSpreadsheet/Calculation/Calculation.php b/src/PhpSpreadsheet/Calculation/Calculation.php
index 484386a2c..effc63a48 100644
--- a/src/PhpSpreadsheet/Calculation/Calculation.php
+++ b/src/PhpSpreadsheet/Calculation/Calculation.php
@@ -133,11 +133,6 @@ class Calculation
      */
     public ?string $formulaError = null;
 
-    /**
-     * Reference Helper.
-     */
-    private static ReferenceHelper $referenceHelper;
-
     /**
      * An array of the nested cell references accessed by the calculation engine, used for the debug log.
      */
@@ -2895,7 +2890,6 @@ class Calculation
         $this->cyclicReferenceStack = new CyclicReferenceStack();
         $this->debugLog = new Logger($this->cyclicReferenceStack);
         $this->branchPruner = new BranchPruner($this->branchPruningEnabled);
-        self::$referenceHelper = ReferenceHelper::getInstance();
     }
 
     private static function loadLocales(): void
@@ -5745,11 +5739,14 @@ class Calculation
         $recursiveCalculationCellAddress = $recursiveCalculationCell->getCoordinate();
 
         // Adjust relative references in ranges and formulae so that we execute the calculation for the correct rows and columns
-        $definedNameValue = self::$referenceHelper->updateFormulaReferencesAnyWorksheet(
-            $definedNameValue,
-            Coordinate::columnIndexFromString($cell->getColumn()) - 1,
-            $cell->getRow() - 1
-        );
+        $definedNameValue = ReferenceHelper::getInstance()
+            ->updateFormulaReferencesAnyWorksheet(
+                $definedNameValue,
+                Coordinate::columnIndexFromString(
+                    $cell->getColumn()
+                ) - 1,
+                $cell->getRow() - 1
+            );
 
         $this->debugLog->writeDebugLog('Value adjusted for relative references is %s', $definedNameValue);
 
diff --git a/src/PhpSpreadsheet/Cell/Cell.php b/src/PhpSpreadsheet/Cell/Cell.php
index ef1d44289..03119915d 100644
--- a/src/PhpSpreadsheet/Cell/Cell.php
+++ b/src/PhpSpreadsheet/Cell/Cell.php
@@ -63,7 +63,7 @@ class Cell implements Stringable
      *
      * @var null|array<string, string>
      */
-    private mixed $formulaAttributes = null;
+    private ?array $formulaAttributes = null;
 
     private IgnoredErrors $ignoredErrors;
 
diff --git a/src/PhpSpreadsheet/Spreadsheet.php b/src/PhpSpreadsheet/Spreadsheet.php
index 44cf47e41..d0a352a5f 100644
--- a/src/PhpSpreadsheet/Spreadsheet.php
+++ b/src/PhpSpreadsheet/Spreadsheet.php
@@ -7,15 +7,12 @@ use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
 use PhpOffice\PhpSpreadsheet\Cell\IValueBinder;
 use PhpOffice\PhpSpreadsheet\Document\Properties;
 use PhpOffice\PhpSpreadsheet\Document\Security;
-use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XlsxReader;
 use PhpOffice\PhpSpreadsheet\Shared\Date;
-use PhpOffice\PhpSpreadsheet\Shared\File;
 use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
 use PhpOffice\PhpSpreadsheet\Style\Style;
 use PhpOffice\PhpSpreadsheet\Worksheet\Iterator;
 use PhpOffice\PhpSpreadsheet\Worksheet\Table;
 use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
-use PhpOffice\PhpSpreadsheet\Writer\Xlsx as XlsxWriter;
 
 class Spreadsheet implements JsonSerializable
 {
@@ -1063,17 +1060,7 @@ class Spreadsheet implements JsonSerializable
      */
     public function copy(): self
     {
-        $filename = File::temporaryFilename();
-        $writer = new XlsxWriter($this);
-        $writer->setIncludeCharts(true);
-        $writer->save($filename);
-
-        $reader = new XlsxReader();
-        $reader->setIncludeCharts(true);
-        $reloadedSpreadsheet = $reader->load($filename);
-        unlink($filename);
-
-        return $reloadedSpreadsheet;
+        return unserialize(serialize($this));
     }
 
     public function __clone()
@@ -1542,14 +1529,6 @@ class Spreadsheet implements JsonSerializable
         }
     }
 
-    /**
-     * @throws Exception
-     */
-    public function __serialize(): array
-    {
-        throw new Exception('Spreadsheet objects cannot be serialized');
-    }
-
     /**
      * @throws Exception
      */
diff --git a/src/PhpSpreadsheet/Worksheet/Worksheet.php b/src/PhpSpreadsheet/Worksheet/Worksheet.php
index 5172e253a..44ede2add 100644
--- a/src/PhpSpreadsheet/Worksheet/Worksheet.php
+++ b/src/PhpSpreadsheet/Worksheet/Worksheet.php
@@ -377,7 +377,6 @@ class Worksheet
     public function __wakeup(): void
     {
         $this->hash = spl_object_id($this);
-        $this->parent = null;
     }
 
     /**


Test patch:
diff --git a/tests/PhpSpreadsheetTests/SpreadsheetSerializeTest.php b/tests/PhpSpreadsheetTests/SpreadsheetSerializeTest.php
new file mode 100644
index 000000000..8128929e4
--- /dev/null
+++ b/tests/PhpSpreadsheetTests/SpreadsheetSerializeTest.php
@@ -0,0 +1,96 @@
+<?php
+
+declare(strict_types=1);
+
+namespace PhpOffice\PhpSpreadsheetTests;
+
+use PhpOffice\PhpSpreadsheet\Exception as SpreadsheetException;
+use PhpOffice\PhpSpreadsheet\Helper\Sample;
+use PhpOffice\PhpSpreadsheet\NamedRange;
+use PhpOffice\PhpSpreadsheet\Spreadsheet;
+use PHPUnit\Framework\Attributes;
+use PHPUnit\Framework\TestCase;
+
+class SpreadsheetSerializeTest extends TestCase
+{
+    private ?Spreadsheet $spreadsheet = null;
+
+    protected function tearDown(): void
+    {
+        if ($this->spreadsheet !== null) {
+            $this->spreadsheet->disconnectWorksheets();
+            $this->spreadsheet = null;
+        }
+    }
+
+    public function testSerialize(): void
+    {
+        $this->spreadsheet = new Spreadsheet();
+        $sheet = $this->spreadsheet->getActiveSheet();
+        $sheet->getCell('A1')->setValue(10);
+
+        $serialized = serialize($this->spreadsheet);
+        $newSpreadsheet = unserialize($serialized);
+        self::assertInstanceOf(Spreadsheet::class, $newSpreadsheet);
+        self::assertNotSame($this->spreadsheet, $newSpreadsheet);
+        $newSheet = $newSpreadsheet->getActiveSheet();
+        self::assertSame(10, $newSheet->getCell('A1')->getValue());
+        $newSpreadsheet->disconnectWorksheets();
+    }
+
+    public function testNotJsonEncodable(): void
+    {
+        $this->spreadsheet = new Spreadsheet();
+
+        $this->expectException(SpreadsheetException::class);
+        $this->expectExceptionMessage('Spreadsheet objects cannot be json encoded');
+        json_encode($this->spreadsheet);
+    }
+
+    /**
+     * These tests are a bit weird.
+     * If prepareSerialize and readSerialize are run in the same
+     *    process, the latter's assertions will always succeed.
+     *    So to demonstrate that the
+     *    problem is solved, they need to run in separate processes.
+     *    But then they can't share the file name. So we need to send
+     *    the file to a semi-hard-coded destination.
+     */
+    private static function getTempFileName(): string
+    {
+        $helper = new Sample();
+
+        return $helper->getTemporaryFolder() . '/spreadsheet.serialize.test.txt';
+    }
+
+    public function testPrepareSerialize(): void
+    {
+        $this->spreadsheet = new Spreadsheet();
+        $sheet = $this->spreadsheet->getActiveSheet();
+        $this->spreadsheet->addNamedRange(new NamedRange('summedcells', $sheet, '$A$1:$A$5'));
+        $sheet->setCellValue('A1', 1);
+        $sheet->setCellValue('A2', 2);
+        $sheet->setCellValue('A3', 3);
+        $sheet->setCellValue('A4', 4);
+        $sheet->setCellValue('A5', 5);
+        $sheet->setCellValue('C1', '=SUM(summedcells)');
+        $ser = serialize($this->spreadsheet);
+        $this->spreadsheet->disconnectWorksheets();
+        $outputFileName = self::getTempFileName();
+        self::assertNotFalse(
+            file_put_contents($outputFileName, $ser)
+        );
+    }
+
+    #[Attributes\RunInSeparateProcess]
+    public function testReadSerialize(): void
+    {
+        $inputFileName = self::getTempFileName();
+        $ser = (string) file_get_contents($inputFileName);
+        unlink($inputFileName);
+        $this->spreadsheet = unserialize($ser);
+        $sheet = $this->spreadsheet->getActiveSheet();
+        self::assertSame('=SUM(summedcells)', $sheet->getCell('C1')->getValue());
+        self::assertSame(15, $sheet->getCell('C1')->getCalculatedValue());
+    }
+}
diff --git a/tests/PhpSpreadsheetTests/SpreadsheetTest.php b/tests/PhpSpreadsheetTests/SpreadsheetTest.php
index 5fbeb4fa4..f6556a536 100644
--- a/tests/PhpSpreadsheetTests/SpreadsheetTest.php
+++ b/tests/PhpSpreadsheetTests/SpreadsheetTest.php
@@ -291,22 +291,4 @@ class SpreadsheetTest extends TestCase
         self::assertEquals($countXfs + $index, $sheet3->getCell('A2')->getXfIndex());
         self::assertEquals($countXfs + $index, $sheet3->getRowDimension(2)->getXfIndex());
     }
-
-    public function testNotSerializable(): void
-    {
-        $this->spreadsheet = new Spreadsheet();
-
-        $this->expectException(Exception::class);
-        $this->expectExceptionMessage('Spreadsheet objects cannot be serialized');
-        serialize($this->spreadsheet);
-    }
-
-    public function testNotJsonEncodable(): void
-    {
-        $this->spreadsheet = new Spreadsheet();
-
-        $this->expectException(Exception::class);
-        $this->expectExceptionMessage('Spreadsheet objects cannot be json encoded');
-        json_encode($this->spreadsheet);
-    }
 }
diff --git a/tests/PhpSpreadsheetTests/Worksheet/CloneTest.php b/tests/PhpSpreadsheetTests/Worksheet/CloneTest.php
index b66a9dd8d..415aebade 100644
--- a/tests/PhpSpreadsheetTests/Worksheet/CloneTest.php
+++ b/tests/PhpSpreadsheetTests/Worksheet/CloneTest.php
@@ -44,12 +44,18 @@ class CloneTest extends TestCase
 
     public function testSerialize1(): void
     {
-        // If worksheet attached to spreadsheet, can't serialize it.
-        $this->expectException(SpreadsheetException::class);
-        $this->expectExceptionMessage('cannot be serialized');
         $spreadsheet = new Spreadsheet();
         $sheet1 = $spreadsheet->getActiveSheet();
-        serialize($sheet1);
+        $sheet1->getCell('A1')->setValue(10);
+        $serialized = serialize($sheet1);
+        $newSheet = unserialize($serialized);
+        self::assertInstanceOf(Worksheet::class, $newSheet);
+        self::assertSame(10, $newSheet->getCell('A1')->getValue());
+        self::assertNotEquals($newSheet->getHashInt(), $sheet1->getHashInt());
+        self::assertNotNull($newSheet->getParent());
+        self::assertNotSame($newSheet->getParent(), $sheet1->getParent());
+        $newSheet->getParent()->disconnectWorksheets();
+        $spreadsheet->disconnectWorksheets();
     }
 
     public function testSerialize2(): void
@@ -57,8 +63,8 @@ class CloneTest extends TestCase
         $sheet1 = new Worksheet();
         $sheet1->getCell('A1')->setValue(10);
         $serialized = serialize($sheet1);
-        /** @var Worksheet */
         $newSheet = unserialize($serialized);
+        self::assertInstanceOf(Worksheet::class, $newSheet);
         self::assertSame(10, $newSheet->getCell('A1')->getValue());
         self::assertNotEquals($newSheet->getHashInt(), $sheet1->getHashInt());
     }