Forum

When a sheet is created with phpxlsx, and formulas are involved, the formulas are not calculated when loading the sheet in Excel. Only when the cell is clicked, Excel will recalculate the formulas. This is very undesired behavior.

I know that someone else posted this a while ago, but I think that this should be included in the library. At least consider to make this a setting when initializing a new CreateXlsx object.

My temporary solution is to make the excelWorkbookDOM variable in the CreateXlsx class public, to be able to edit it while working with a CreateXlsx instance. Then I can set forced formula recalculation on open like this:

$xlsx = $this->loadTemplate("template.xlsx");

$calcPr = $xlsx->excelWorkbookDOM->getElementsByTagName("calcPr");
$calcPr->item(0)->setAttribute('fullCalcOnLoad', '1');

$xlsx->saveXlsxAndDownload("excelfile.xlsx");

This should also be set in the default template xml contents in Utilities\XlsxStructureTemplate.php. In the following example I created a new option fullCalcOnLoad and used in the template:

public function getStructure($options = array()) {

        // ...

        // note the new attribute fullCalcOnLoad in the calcPr tag below and a new option fullCalcOnLoad
         $zipXlsx->addContent('xl/workbook.xml',
            '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x15 xr xr6 xr10 xr2" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xmlns:xr6="http://schemas.microsoft.com/office/spreadsheetml/2016/revision6" xmlns:xr10="http://schemas.microsoft.com/office/spreadsheetml/2016/revision10" xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2"><fileVersion appName="xl" lastEdited="7" lowestEdited="7" rupBuild="24131"/><workbookPr defaultThemeVersion="166925"/><mc:AlternateContent xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"><mc:Choice Requires="x15"><x15ac:absPath url="phpxlsx" xmlns:x15ac="http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac"/></mc:Choice></mc:AlternateContent><xr:revisionPtr revIDLastSave="0" documentId="8_{6D9A890A-6C51-4069-A970-BD6252C49E42}" xr6:coauthVersionLast="47" xr6:coauthVersionMax="47" xr10:uidLastSave="{00000000-0000-0000-0000-000000000000}"/><bookViews><workbookView xWindow="864" yWindow="3756" windowWidth="30720" windowHeight="18600" xr2:uid="{F2869A14-26B8-433F-A61D-BE9AF13FD122}"/></bookViews><sheets><sheet name="'.$options['sheetName'].'" sheetId="1" r:id="rId1"/></sheets><calcPr calcId="191029" fullCalcOnLoad="'.$options['fullCalcOnLoad'].'"/><extLst><ext uri="{140A7094-0E35-4892-8432-C4D2E57EDEB5}" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main"><x15:workbookPr chartTrackingRefBase="1"/></ext></extLst></workbook>'
        );

        return $zipXlsx;
}