Force formula re-calculation on file open from Excel needs a new attribute when writing to xlsx

[tmarti]

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?

Xlsx files written using this library should have its formulas recalculated when opening the file from Excel.

What is the current behavior?

The formulas do not get recalculated.

What are the steps to reproduce?

1. Create a very simple spreadsheet from Excel with:
   • a formula on A1 => '=A2*2'.
   • a value on A2 => 0
2. Save the file from Excel
3. Now open this file with the library and set value = 3 on A2
4. Save a new xlsx file from the library
5. Open the new xslx with Excel
6. The formula on A1 is not updated

I have seen that the library will set these attributes on the generated xl/workbook.xml inside the xslx compressed file:

<x:calcPr calcId="999999" calcMode="auto" fullCalcOnLoad="1" calcCompleted="0"/>

BUT if I tamper the file with C# library OpenXML and follow the steps from the accepted answer from the post, which suggests doing this:

spreadSheet.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
spreadSheet.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;

Then the generated xl/workbook.xml file has following flags instead:

<x:calcPr calcId="999999" calcMode="auto" fullCalcOnLoad="1" calcCompleted="0" forceFullCalc="1" />

Notice the additional forceFullCalc="1" attribute.

If I modify the file src/PhpSpreadsheet/Writer/Xlsx/Workbook.php and change this...

    private function writeCalcPr(XMLWriter $objWriter, $recalcRequired = true)
    {
        $objWriter->startElement('calcPr');
        //    Set the calcid to a higher value than Excel itself will use, otherwise Excel will always recalc
        //  If MS Excel does do a recalc, then users opening a file in MS Excel will be prompted to save on exit
        //     because the file has changed
        $objWriter->writeAttribute('calcId', '999999');
        $objWriter->writeAttribute('calcMode', 'auto');
        //    fullCalcOnLoad isn't needed if we've recalculating for the save
        $objWriter->writeAttribute('calcCompleted', ($recalcRequired) ? 1 : 0);
        $objWriter->writeAttribute('fullCalcOnLoad', ($recalcRequired) ? 0 : 1);
        $objWriter->endElement();
    }

... to this (changes in bold)...

    private function writeCalcPr(XMLWriter $objWriter, $recalcRequired = true)
    {
        $objWriter->startElement('calcPr');
        //    Set the calcid to a higher value than Excel itself will use, otherwise Excel will always recalc
        //  If MS Excel does do a recalc, then users opening a file in MS Excel will be prompted to save on exit
        //     because the file has changed
        $objWriter->writeAttribute('calcId', '999999');
        $objWriter->writeAttribute('calcMode', 'auto');
        //    fullCalcOnLoad isn't needed if we've recalculating for the save
        $objWriter->writeAttribute('calcCompleted', ($recalcRequired) ? 1 : 0);
        $objWriter->writeAttribute('fullCalcOnLoad', ($recalcRequired) ? 0 : 1);
        **$objWriter->writeAttribute('forceFullCalc', ($recalcRequired) ? 0 : 1);**
        $objWriter->endElement();
    }

Everything works as expected.

Which versions of PhpSpreadsheet and PHP are affected?

The latset version of PhpSpreadsheet

[rtek]

Yes found this problem too with excel 2016 on windows 10. This fix def needs to make it in. Here is the custom writer I used to work around...

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx\Workbook;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as DefaultXlsx;

class Xlsx extends DefaultXlsx
{
    public function __construct(Spreadsheet $spreadsheet)
    {
        parent::__construct($spreadsheet);

        //replace private writer part with our writer
        $refl = new \ReflectionClass(DefaultXlsx::class);
        $prop = $refl->getProperty('writerParts');
        $prop->setAccessible(true);
        $writers = $prop->getValue($this);

        $writers['workbook'] = new class($this) extends Workbook {
            public function writeWorkbook(Spreadsheet $spreadsheet, $recalcRequired = false) {
                $xml = parent::writeWorkbook($spreadsheet, $recalcRequired);
                return substr_replace($xml, ' forceFullCalc="1" ', stripos($xml, 'calcId="'), 0);
            }
        };

        $prop->setValue($this,$writers);
    }
}

[criztovyl]

Could you maybe provide a PR that fixes this?

[tmarti]

Ok, will try it in the next few days, thanks for the feedback 👍

[tmarti]

Created PR #515 related to this issue.