Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Error open file Xlsx when modify value #4128

Closed
7 tasks
Fabacks opened this issue Aug 6, 2024 · 13 comments · Fixed by #4132
Closed
7 tasks

Error open file Xlsx when modify value #4128

Fabacks opened this issue Aug 6, 2024 · 13 comments · Fixed by #4132

Comments

@Fabacks
Copy link

Fabacks commented Aug 6, 2024

Good morning,

Since switching to version >= 2.2, the "setCellValue" method causes an error when opening Excel.
The error is : "We found a problem in the content of XXX, but we can try to recover as much content as possible.".
It only produces this with the "Xlsx" format. I tried to reproduce the error with minimal code. But, if you open the file and save without modifying a single line, the file opens correctly.

The function "error_reporting(E_ALL)" gives no error.

Thank you so much

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 are the steps to reproduce?

Execute the code

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';

$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$spreadsheet->getActiveSheet()->setCellValue('D1', 'MyDate');

$path = __DIR__.'/file_example.xlsx';
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save($path);
?>

// add code that show the issue here...

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.
file_example.xlsx
error excel open

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?

Phpspreasheet >=2.2
PHP >= 8.1
Excel 2016
Environment Windows
Langue French

@oleibman
Copy link
Collaborator

oleibman commented Aug 6, 2024

Agree that the file you uploaded is messed up. However, using your code, I am unable to see a problem with 2.2.0, 2.2.1, or master - the workbook opens just fine and is as expected (empty except for cell D1 which has its expected value). I am using Excel 365 rather than 2016, but I doubt that's the problem; similarly English rather than French, and even more doubtful of that as the problem.

@zapato
Copy link

zapato commented Aug 6, 2024

I have the same issue with Excel 2016 but I also cannot reproduce it with Excel Home and Studen 2021.
Seens to be a specific Problem when opening with PhpSpreadsheet generated files with Excel 2021.
Generating the XLSX-files with PhpSpreadsheet 2.1.* works fine.
The opening error is onl shown for files generated with PhpSpreadsheet >=2.2.0 with Excel 2016.

@oleibman
Copy link
Collaborator

oleibman commented Aug 6, 2024

@zapato The contents of the file generated by PhpSpreadsheet don't change depending on the Excel release. @Fabacks uploaded a file which clearly had problems, in Excel 2016 and any other release. Do you have a file which opens okay in Excel 2021 but not 2016?

@zapato
Copy link

zapato commented Aug 6, 2024

@oleibman Opening the uploaded example files with my Excel 2021 shows the following error message and I am not able to open the file.

grafik

A self created XLSX file with the example code dos not show any problem when opening with Excel 2021.

At work I am using Excel 2016 and getting the error message like "We found a problem in the content of XXX, but we can try to recover as much content as possible." with my generated xlsx files. I will try to prepare some more example code to reproduce the problem.

@oleibman
Copy link
Collaborator

oleibman commented Aug 7, 2024

I already noted that the uploaded file is not suitable for any version of Excel. But, since my attempt to reproduce the problem with the supplied code generated a file which appears to usable with any version of Excel (I don't have 2016 but I have access to earlier and later versions), I don't know how to proceed for the moment. Your description of your problem (usable in Excel 2021 but not in 2016) seems to be a different case; I will look forward to your example code.

@oleibman
Copy link
Collaborator

oleibman commented Aug 7, 2024

Rather oddly, LibreOffice Calc is able to open the uploaded file even though Excel can't. I'm not sure how, and that, of course, does not solve the mystery of how the file was created in that particular manner.

@oleibman
Copy link
Collaborator

oleibman commented Aug 7, 2024

And now the mystery seems explainable. If I change the file extension from xlsx to xls, Excel can open it. In fact, like LibreOffice, PhpSpreadsheet can read this file:

$filename = 'issue.4128.orig.xlsx';
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile($filename);
var_dump($reader::class);
$spreadsheet = $reader->load($filename);
var_dump($spreadsheet->getActiveSheet()->getCell('D1')->getValue());

Result:

C:\git\issue.4128b.php:9:
string(35) "PhpOffice\PhpSpreadsheet\Reader\Xls"
C:\git\issue.4128b.php:11:
string(6) "MyDate"

@Fabacks is it possible that you used the Xls writer to save the file with an xlsx extension?

@zapato
Copy link

zapato commented Aug 7, 2024

I used the following example code and generated the following Excel files (PHP 8.3.10 Windows)
<?php
require DIR . '/../vendor/autoload.php';

$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$spreadsheet->getActiveSheet()->setCellValue('D1', 'MyDate');

$path = __DIR__.'/file_example.xlsx';
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save($path);

Using PhpSpreadsheet 2.1.0 - No errors when opening the file with Excel 2016:
file_example_v2.1.0.xlsx

Using PhpSpreadsheet 2.2.1 - Errors when opening the file with Excel 2016:
file_example_v2.2.1.xlsx

Error-Messages (German):
excel_error_message

repair_message

The repair log does not contain any useful content:

<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<logFileName>error334480_01.xml</logFileName>
<summary>Fehler in Datei 'xxxxx\file_example_v2.2.1.xlsx'</summary>
<repairedRecords>
<repairedRecord>Reparierte Datensätze: Ansicht von /xl/worksheets/sheet1.xml-Part</repairedRecord>
</repairedRecords>
</recoveryLog>

@oleibman
Copy link
Collaborator

oleibman commented Aug 7, 2024

Thank you for the sample files. There is a minor difference between the 2 files. While I research, can you add:

$spreadsheet->getActiveSheet()->setSelectedCells('D1');

Is the resulting workbook now usable in Excel 2016?

@zapato
Copy link

zapato commented Aug 7, 2024

Setting the selected cell seems to solve to problem.

$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$spreadsheet->getActiveSheet()->setCellValue('D1', 'MyDate');
$spreadsheet->getActiveSheet()->setSelectedCells('D1');

No matter if the selected cell is another cell. $spreadsheet->getActiveSheet()->setSelectedCells('A1'); is also usable.

oleibman added a commit to oleibman/PhpSpreadsheet that referenced this issue Aug 7, 2024
Fix PHPOffice#4128. PR PHPOffice#4073 introduced applyStylesFromArray method, which allowed setting styles without affecting selectedCells or activeSheet. The first use of this method was in Cell setValueExplicit to set quotePrefix appropriately. The new method did not preserve activeCell. I'm not sure why that should matter, but this seems to have caused a problem for Excel 2016. This seems to be a bug in Excel, one which is fixed in newer releases. However, PhpSpreadsheet can avoid the problem by preserving activeCell as well as selectedCells and activeSheet. This PR makes that change.
@oleibman
Copy link
Collaborator

oleibman commented Aug 7, 2024

@zapato If possible, please try your code (without setSelectedCells) against PR #4132.

@zapato
Copy link

zapato commented Aug 7, 2024

@oleibman Yes, testing PR #4132 with Excel 2016 was successful. No need anymore to explicitly set $spreadsheet->getActiveSheet()->setSelectedCells('A1');.

Info for testing: It is important to always use an new xlsx filname because Excel memorizes the last selected cell from previous opening for the same filename. So you would not get the error message again when opening a (new generated) file with the same filename in which you explicitly had set the selected cell.

Many thanks for your support!

file_example_pr4132.xlsx

@Fabacks
Copy link
Author

Fabacks commented Aug 9, 2024

Hello,

Version 2.2.2 fixes the problem.
Thank you very much

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Development

Successfully merging a pull request may close this issue.

3 participants