"forceFullCalc" is not necessary in current Excel

[ogasawara-k]

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.

PhpSpreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Workbook.php

Line 163 in b35de99

$objWriter->writeAttribute('forceFullCalc', ($preCalculateFormulas) ? '0' : '1');

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).

What are the steps to reproduce?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?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
• sample.remove-forceFullCalc.xlsx

What features do you think are causing the issue

• Reader
• 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

[oleibman]

This was added by PR #515 to fix issue #456 in May 2018. Since it addresses a specific problem, I am reluctant to change it. Is the current implementation causing you any problems?

[oleibman]

I could add a forceFullCalc property to the writer, equal to null (default), true, or false. Default would act as today (so no compatibility break), but true/false would override the default. It would mean an extra call for you. Would that meet your needs?

[ogasawara-k]

I did the same thing with reference to #456 . (Since it's long, I've closed it with "details".)

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

---

STEP1

Create a file in Excel (Excel 2019)

• step1.xlsx

STEP2 - 4

Read, change value, and write

<?php

require __DIR__ . '/vendor/autoload.php';

// step2
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader(\PhpOffice\PhpSpreadsheet\IOFactory::READER_XLSX);
$spreadsheet = $reader->load(__DIR__ . '/step1.xlsx');

// step3
$spreadsheet->getActiveSheet()->setCellValue('A2', 3);

// step4
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, \PhpOffice\PhpSpreadsheet\IOFactory::WRITER_XLSX);
$writer->setPreCalculateFormulas(false);
$writer->save(__DIR__ . '/step3.xlsx');

• step3.origiginal.xlsx
• step3.remove-forceFullCalc.xlsx

STEP5 - 6

original

remove-forceFullCalc

The version of Excel confirmed in step 6

• Excel 2019 (Microsoft® Excel® 2019 MSO (Version 2411 Build 16.0.18227.20082) 64-bit)
• Excel 2016 (Microsoft® Excel® 2016 MSO (バージョン 2410 ビルド 16.0.18129.20158) 32 ビット)
• Microsoft 365 (Microsoft® Excel® for Microsoft 365 MSO (バージョン 2411 ビルド 16.0.18227.20082) 64ビット)

It didn't reproduce.

---

The current problem is that if you open even one Book in which "Calculate" is displayed, "Calculate" will also be displayed in other Books until you close Excel.

The whole of Excel goes into recalculation mode, so if you close other workbooks that you haven't changed, a dialog box will appear asking if you want to save them.
I can't force users not to open other workbooks, so I'm in trouble.

If you can also specify whether to output "forceFullCalc" using "setPreCalculateFormulas", you can deal with any problems at your own risk.

[oleibman]

Thank you for teaching me about the details trick. And for all the research. I agree that I can't reproduce the problem. But I am in no doubt that the people reporting it had a genuine problem which forceFullCalc solved. It sounds like you are okay with my proposal for a new property, so I will proceed along those lines.

I confirm your statement that one forceFullCalc affects all open spreadsheets. I find that ... strange.

[LucaRed]

one forceFullCalc affects all open spreadsheets. I find that ... strange.

I'll add this for future reference: the option affects all open spreadsheets because different files may reference each other.

It's the same reason why it's (almost) 2025 and Excel still cannot open two files with the same filename at the same time.

[oleibman]

@LucaRed Thank you for the explanation. It is helpful to at least understand why things work the way they do.