File or sheets lose password for protection after IOFactory::createWriter and save

[kboumedal]

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 current behavior?

File or sheets lose password for protection after IOFactory::createWriter and save

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 = IOFactory::load([Fichier_TEST_MyEcoPlan.xlsx](https://github.com/user-attachments/files/22636812/Fichier_TEST_MyEcoPlan.xlsx));

$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');

$temporaryPathfile = rtrim(sys_get_temp_dir(), \DIRECTORY_SEPARATOR).\DIRECTORY_SEPARATOR.md5(uniqid().mt_rand());

$writer->save($temporaryPathfile);

### What features do you think are causing the issue

- [ ] Reader
- [x] Writer
- [ ] Styles
- [ ] Data Validations
- [ ] Formula Calculations
- [ ] Charts
- [ ] AutoFilter
- [ ] Form Elements

### Which versions of PhpSpreadsheet and PHP are affected?

PhpSpreadsheet 4.5.0, PHP8.3

[oleibman]

I get a 404 when I try to access your spreadsheet. Can you upload it here?

If your file is encrypted (i.e. the entire file is password protected), PhpSpreadsheet cannot handle it. If instead your problem is at a more granular level (e.g. you have a password-protected worksheet within an unencrypted spreadsheet), I will take a look.

[kboumedal]

Fichier_TEST_MyEcoPlan.xlsx Can you access this one ? Would be better, my client says the file is not protected but the workbook is

[oleibman]

Yes, I can access this file. I am having problems with it due to its use of external sheets, but that is not the same problem you are reporting. What release of PhpSpreadsheet and what release of Php are you using? It might also help if you can share the sheet password with me.

[oleibman]

Aha, the formulas which reference the external workbooks are on the charts. Your sample code did not deal with the charts. In order to avoid a problem if you do want to deal with the charts, you need to turn preCalculateFormulas off. Here's my code:

$infile = 'issue.4673.xlsx';
$spreadsheet = IOFactory::load($infile, IReader::LOAD_WITH_CHARTS);
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->setIncludeCharts(true);
$writer->setPreCalculateFormulas(false);
$temporaryPathfile = 'issue.4673.4.xlsx';
$writer->save($temporaryPathfile);
echo "saved $temporaryPathfile\n";

On my system, the protection is preserved for the sheet in question with this code, and it looks basically correct. If I ignore the charts (removing the extra parameter from IOFactory::load and there is no longer a need to disable preCalculate), protection is still preserved for the sheet in question, which looks basically correct except for the absence of charts.

[kboumedal]

Indeed I have the same problem as you with external sheets but it's not the problem my client is referring.
He's actually referring to the loss of password-protection on workbook, not worksheet.

[oleibman]

Thank you for the additional information. Workbook protection now seems to use a more sophisticated algorithm to protect the password, involving several attributes in the Xml which PhpSpreadsheet Reader and Writer do not yet support. I will see what I can do about it.