`SUM` formula not being updated when running multiple `save()` operations

[filecage]

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?

I have a template .xlsx file that contains a SUM formula. I insert multiple rows using PhpSpreadsheet and save the sheet using the Xlsx writer. I expect the sum to be updated when opening the file again in LibreOffice or Excel. In my particular example, I add three rows and expect the sum to be 15.

If this can not be supported or is a known limitation, I expect it to be documented in the writer or calculation engine docs.

What is the current behavior?

The formula does not update the result after the first save operation, resulting in the same being the first column's value only.

What are the steps to reproduce?

I've attached a minimal example template file to be used with my provided script. It looks like this:

and can be downloaded here:
incorrect-sum-template.xlsx

Running the following:

<?php

require_once 'vendor/autoload.php';

$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('incorrect-sum-template.xlsx');
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);

$worksheet = $spreadsheet->getActiveSheet();

$worksheet->setCellValue('A2', 4);
$writer->save('incorrect-sum.xlsx');

$worksheet->setCellValue('A3', 6);
$writer->save('incorrect-sum.xlsx');

$worksheet->setCellValue('A4', 5);
$writer->save('incorrect-sum.xlsx');

results in a new file, looking like this:

This does not happen if I only run the last save operation. However, that is not how I intend to use the script.

Which versions of PhpSpreadsheet and PHP are affected?

1.22.0

[filecage]

I dug a bit deeper and this is related to the Calculator Cache (when flushing it before saving everything works as expected), so it probably is a known limitation.

However, I'll leave this open as I find the documentation on this matter insufficient. It is not clear that multiple save() operations will cause such a behaviour and it is not clear how to handle these situations (flush the cache before saving or disabling the cache overall or maybe it's even faster to re-load the document after saving - or creating a deep clone of the spreadsheet and then saving it again?)

[MarkBaker]

This was a deliberate decision to try and avoid full recalculation in the save() method to help performance if calculations had already been executed by the script. It should, perhaps, be better documented.

[filecage]

@MarkBaker I'm happy to help out with a PR to a documentation update. My solution was to flush the cache before each save() operation and that's what I'd also recommend in the docs, if you're fine with that.

[MarkBaker]

I'm fine with that. And flushing the cache is only necessary if you've modified any of the data between saves.