Repository: PHPOffice/PhpSpreadsheet (php)

Issue:
"forceFullCalc" is not necessary in current Excel
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?

If I create a file in PhpSpreadsheet without calculating it (`setPreCalculateFormulas(false)`), when I open it in excel it will calculate automatically and I'm hoping there won't be a "Calculate" button.

https://github.com/PHPOffice/PhpSpreadsheet/blob/b35de99c1600f58940d33221810b187d53745dc1/src/PhpSpreadsheet/Writer/Xlsx/Workbook.php#L163

I wasn't able to trace the reason why "forceFullCalc" was added, but it wasn't there in the PHPExcel era.
Since "calcMode" is output as "auto", I think it's correct that "Calculate" is not displayed.

### What is the current behavior?

When you open the created file in Excel, it will be `Calculate` (manual recalculation).
![image](https://github.com/user-attachments/assets/a95cbffc-d68b-447b-85fe-262d4b4d8349)

### What are the steps to reproduce?

Please provide a [Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) of code that exhibits the issue without relying on an external Excel file or a web server:

```php
<?php

require __DIR__ . '/vendor/autoload.php';

// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

// add code that show the issue here...
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', '=1+1');
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, \PhpOffice\PhpSpreadsheet\IOFactory::WRITER_XLSX);
$writer->setPreCalculateFormulas(false);
$writer->save(__DIR__ . '/sample.xlsx');
```

Open the file you created in Excel.

If this is an issue with reading a specific spreadsheet file, then it may be appropriate to provide a sample file that demonstrates the problem; but please keep it as small as possible, and sanitize any confidential information before uploading.

- [sample.original.xlsx](https://github.com/user-attachments/files/18070277/sample.original.xlsx)
- [sample.remove-forceFullCalc.xlsx](https://github.com/user-attachments/files/18070356/sample.remove-forceFullCalc.xlsx)

### What features do you think are causing the issue

- [ ] Reader
- [x] Writer
- [ ] Styles
- [ ] Data Validations
- [ ] Formula Calculations
- [ ] Charts
- [ ] AutoFilter
- [ ] Form Elements

### Does an issue affect all spreadsheet file formats? If not, which formats are affected?

xlsx ( & xlsm )

### Which versions of PhpSpreadsheet and PHP are affected?

PhpSpreadsheet: 3.6.0
PHP: 8.2.13
Excel: Office 2019 & Office365

Pull request:
Add forceFullCalc Option to Xlsx Writer
Fix #4269. In response to issue #456 PR #515, `forceFullCalc` was added to workbook.xml whenever `preCalculateFormulas` was set to false. It is not clear why this should have been needed; attempts to reproduce the error in the original 6.5-year-old issue are unable to reproduce it today. Nevertheless, it is, or was, there for a reason.

Today, the forceFullCalc option sets an option where formulas *might* not be recalculated when a cell used in the formula changes. I have not succeeded in finding a situation where it doesn't automatically recalculate, but it probably exists on complicated spreadsheets. To overcome this possibility, Excel offers a button which can be used to recalculate on demand. By itself, this might not be a terrible problem. However, it seems to come with the strange property that any spreadsheets opened at the same time as a forceFullCalc spreadsheet operate as if they too specified forceFullCalc. That *is* a problem, especially since users when closing a spreasheet affected in this way will be prompted to save it even when they haven't changed anything.

I am not willing to make a BC change at this time, although I might consider it in future (PR #4240). For now, I am adding a new property `forceFullCalc` with setter (no getter needed) to Xlsx Writer. That property can be `null` (default, in which case the Xml attribute is set as today), or `false` or `true` (in which case the Xml attribute will be set to the Writer attribute). I think that, when `preCalculateFormulas` is set to false, the calling application should give consideration to setting `forceFullCalc` to false as well. All other situations should just use the default.

This is:

- [x] a bugfix
- [ ] 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
- [x] 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 c06cdf589..a484cfba9 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -25,6 +25,7 @@ and this project adheres to [Semantic Versioning](https://semver.org).
 
 ### Fixed
 
+- Add forceFullCalc option to Xlsx Writer. [Issue #4269](https://github.com/PHPOffice/PhpSpreadsheet/issues/4269) [PR #4271](https://github.com/PHPOffice/PhpSpreadsheet/pull/4271)
 -  More context options may be needed for http(s) image. [Php issue 17121](https://github.com/php/php-src/issues/17121) [PR #4276](https://github.com/PHPOffice/PhpSpreadsheet/pull/4276)
 -  Several fixed to ODS Writer. [Issue #4261](https://github.com/PHPOffice/PhpSpreadsheet/issues/4261) [PR #4263](https://github.com/PHPOffice/PhpSpreadsheet/pull/4263) [PR #4264](https://github.com/PHPOffice/PhpSpreadsheet/pull/4264) [PR #4266](https://github.com/PHPOffice/PhpSpreadsheet/pull/4266)
 
diff --git a/docs/topics/reading-and-writing-to-file.md b/docs/topics/reading-and-writing-to-file.md
index 19fe8a8ed..28bc3b173 100644
--- a/docs/topics/reading-and-writing-to-file.md
+++ b/docs/topics/reading-and-writing-to-file.md
@@ -169,6 +169,12 @@ $writer->save("05featuredemo.xlsx");
 **Note** Formulas will still be calculated in any column set to be autosized
 even if pre-calculated is set to false
 
+**Note** Prior to release 3.7.0, the use of this feature will cause Excel to be used in a mode where opening a sheet saved in this manner *might* not automatically recalculate a cell's formula when a cell used it the formula changes. Furthermore, that behavior might be applied to all spreadsheets open at the time. To avoid this behavior, add the following statement after `setPreCalculateFormulas` above:
+```php
+$writer->setForceFullCalc(false);
+```
+In a future release, the property's default may change to `false` and that statement may no longer be required.
+
 #### Office 2003 compatibility pack
 
 Because of a bug in the Office2003 compatibility pack, there can be some
diff --git a/src/PhpSpreadsheet/Writer/Xlsx.php b/src/PhpSpreadsheet/Writer/Xlsx.php
index a9d06db58..b74d81050 100644
--- a/src/PhpSpreadsheet/Writer/Xlsx.php
+++ b/src/PhpSpreadsheet/Writer/Xlsx.php
@@ -140,6 +140,8 @@ class Xlsx extends BaseWriter
 
     private bool $useDynamicArray = false;
 
+    private ?bool $forceFullCalc = null;
+
     /**
      * Create a new Xlsx Writer.
      */
@@ -342,7 +344,7 @@ class Xlsx extends BaseWriter
         $zipContent['xl/styles.xml'] = $this->getWriterPartStyle()->writeStyles($this->spreadSheet);
 
         // Add workbook to ZIP file
-        $zipContent['xl/workbook.xml'] = $this->getWriterPartWorkbook()->writeWorkbook($this->spreadSheet, $this->preCalculateFormulas);
+        $zipContent['xl/workbook.xml'] = $this->getWriterPartWorkbook()->writeWorkbook($this->spreadSheet, $this->preCalculateFormulas, $this->forceFullCalc);
 
         $chartCount = 0;
         // Add worksheets
@@ -747,4 +749,20 @@ class Xlsx extends BaseWriter
     {
         $this->useDynamicArray = $this->preCalculateFormulas && Calculation::getInstance($this->spreadSheet)->getInstanceArrayReturnType() === Calculation::RETURN_ARRAY_AS_ARRAY && !$this->useCSEArrays;
     }
+
+    /**
+     * If this is set when a spreadsheet is opened,
+     * values may not be automatically re-calculated,
+     * and a button will be available to force re-calculation.
+     * This may apply to all spreadsheets open at that time.
+     * If null, this will be set to the opposite of $preCalculateFormulas.
+     * It is likely that false is the desired setting, although
+     * cases have been reported where true is required (issue #456).
+     */
+    public function setForceFullCalc(?bool $forceFullCalc): self
+    {
+        $this->forceFullCalc = $forceFullCalc;
+
+        return $this;
+    }
 }
diff --git a/src/PhpSpreadsheet/Writer/Xlsx/Workbook.php b/src/PhpSpreadsheet/Writer/Xlsx/Workbook.php
index 24a2eeb06..c907e100b 100644
--- a/src/PhpSpreadsheet/Writer/Xlsx/Workbook.php
+++ b/src/PhpSpreadsheet/Writer/Xlsx/Workbook.php
@@ -15,10 +15,11 @@ class Workbook extends WriterPart
      * Write workbook to XML format.
      *
      * @param bool $preCalculateFormulas If true, formulas will be calculated before writing
+     * @param ?bool $forceFullCalc If null, !$preCalculateFormulas
      *
      * @return string XML Output
      */
-    public function writeWorkbook(Spreadsheet $spreadsheet, bool $preCalculateFormulas = false): string
+    public function writeWorkbook(Spreadsheet $spreadsheet, bool $preCalculateFormulas = false, ?bool $forceFullCalc = null): string
     {
         // Create XML writer
         if ($this->getParentWriter()->getUseDiskCaching()) {
@@ -57,7 +58,7 @@ class Workbook extends WriterPart
         (new DefinedNamesWriter($objWriter, $spreadsheet))->write();
 
         // calcPr
-        $this->writeCalcPr($objWriter, $preCalculateFormulas);
+        $this->writeCalcPr($objWriter, $preCalculateFormulas, $forceFullCalc);
 
         $objWriter->endElement();
 
@@ -148,7 +149,7 @@ class Workbook extends WriterPart
      *
      * @param bool $preCalculateFormulas If true, formulas will be calculated before writing
      */
-    private function writeCalcPr(XMLWriter $objWriter, bool $preCalculateFormulas = true): void
+    private function writeCalcPr(XMLWriter $objWriter, bool $preCalculateFormulas, ?bool $forceFullCalc): void
     {
         $objWriter->startElement('calcPr');
 
@@ -160,7 +161,11 @@ class Workbook extends WriterPart
         //    fullCalcOnLoad isn't needed if we will calculate before writing
         $objWriter->writeAttribute('calcCompleted', ($preCalculateFormulas) ? '1' : '0');
         $objWriter->writeAttribute('fullCalcOnLoad', ($preCalculateFormulas) ? '0' : '1');
-        $objWriter->writeAttribute('forceFullCalc', ($preCalculateFormulas) ? '0' : '1');
+        if ($forceFullCalc === null) {
+            $objWriter->writeAttribute('forceFullCalc', $preCalculateFormulas ? '0' : '1');
+        } else {
+            $objWriter->writeAttribute('forceFullCalc', $forceFullCalc ? '1' : '0');
+        }
 
         $objWriter->endElement();
     }


Test patch:
diff --git a/tests/PhpSpreadsheetTests/Writer/Xlsx/Issue4269Test.php b/tests/PhpSpreadsheetTests/Writer/Xlsx/Issue4269Test.php
new file mode 100644
index 000000000..ffe9e30ae
--- /dev/null
+++ b/tests/PhpSpreadsheetTests/Writer/Xlsx/Issue4269Test.php
@@ -0,0 +1,65 @@
+<?php
+
+declare(strict_types=1);
+
+namespace PhpOffice\PhpSpreadsheetTests\Writer\Xlsx;
+
+use PhpOffice\PhpSpreadsheet\Shared\File;
+use PhpOffice\PhpSpreadsheet\Spreadsheet;
+use PhpOffice\PhpSpreadsheet\Writer\Xlsx as XlsxWriter;
+use PHPUnit\Framework\Attributes\DataProvider;
+use PHPUnit\Framework\TestCase;
+
+class Issue4269Test extends TestCase
+{
+    private string $outputFile = '';
+
+    protected function tearDown(): void
+    {
+        if ($this->outputFile !== '') {
+            unlink($this->outputFile);
+            $this->outputFile = '';
+        }
+    }
+
+    #[DataProvider('validationProvider')]
+    public function testWriteArrayFormulaTextJoin(
+        bool $preCalculateFormulas,
+        ?bool $forceFullCalc,
+        string $expected
+    ): void {
+        $spreadsheet = new Spreadsheet();
+        $sheet = $spreadsheet->getActiveSheet();
+        $sheet->setCellValue('A1', '=A2*2');
+        $sheet->setCellValue('A2', 0);
+
+        $writer = new XlsxWriter($spreadsheet);
+        $writer->setPreCalculateFormulas($preCalculateFormulas);
+        if ($forceFullCalc !== null) {
+            $writer->setForceFullCalc($forceFullCalc);
+        }
+        $this->outputFile = File::temporaryFilename();
+        $writer->save($this->outputFile);
+        $spreadsheet->disconnectWorksheets();
+
+        $file = 'zip://';
+        $file .= $this->outputFile;
+        $file .= '#xl/workbook.xml';
+        $data = file_get_contents($file);
+        if ($data === false) {
+            self::fail('Unable to read file');
+        } else {
+            self::assertStringContainsString($expected, $data);
+        }
+    }
+
+    public static function validationProvider(): array
+    {
+        return [
+            'normal case' => [true, null, 'calcMode="auto" calcCompleted="1" fullCalcOnLoad="0" forceFullCalc="0"'],
+            'issue 456' => [false, null, 'calcMode="auto" calcCompleted="0" fullCalcOnLoad="1" forceFullCalc="1"'],
+            'better choice for no precalc' => [false, false, 'calcMode="auto" calcCompleted="0" fullCalcOnLoad="1" forceFullCalc="0"'],
+            'unlikely use case' => [true, true, 'calcMode="auto" calcCompleted="1" fullCalcOnLoad="0" forceFullCalc="1"'],
+        ];
+    }
+}