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

Writer\Xlsx() produce broken files #4200

Closed
1 of 8 tasks
madorin opened this issue Oct 24, 2024 · 6 comments · Fixed by #4212
Closed
1 of 8 tasks

Writer\Xlsx() produce broken files #4200

madorin opened this issue Oct 24, 2024 · 6 comments · Fixed by #4212

Comments

@madorin
Copy link

madorin commented Oct 24, 2024

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)

I have an xlsx excel file template, open it, fill some cells and using Writer\Xlsx to save the output

Used template: template.xlsx
Generated broken output: output.xlsx

What is the expected behavior?

To generate an excel compatible file.

What is the current behavior?

It produces a broken file. However, LibreOffice opens it fine.
Xls writer, (without "x" at the end) works fine.

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


# Read
$reader = new Spreadsheet\Reader\Xlsx();
$workbook = $reader->load('path/to/template.xlsx');
unset($reader);

# Write
$writer = new Spreadsheet\Writer\Xlsx($workbook);
ob_start();
$writer->save('php://output');
$content = ob_get_clean();
unset($writer);
header('Content-Disposition: attachment; filename="report.xlsx"');
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Length: ' . strlen($content));
header('Content-Transfer-Encoding: binary');
header('Cache-Control: must-revalidate');
echo $content;
unset($content);

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.

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 Writer is affected, XLS writer works fine.

Which versions of PhpSpreadsheet and PHP are affected?

v3.3, v2.3

In earlier v1.x versions it worked fine. Not sure when it was broken.

@oleibman
Copy link
Collaborator

oleibman commented Oct 25, 2024

Confirmed. This looks like it's going to be a bear of a problem to solve. Excel's diagnostic messages are usually devoid of any useful information, and this case is no exception. It says it has detected invalid Xml, which is not true (otherwise LibreOffice would also not be able to read the file). The output spreadsheet has many files, and I am so far unable to devise a plan for figuring out just what is causing the problem.

Workaround 1 - you can open the output spreadsheet in LibreOffice and save it to a different spreadsheet in Xlsx format. Excel can open that file.

Workaround 2 - you can remove all the notes (comments) from the spreadsheet before making your copy. Excel can read the result. This could indicate that there is something wrong with the comment files, but that's not a guarantee. I don't see anything wrong with any of them. Furthermore, if I take the readable spreadsheet and add any note to it and try to load/save that file, the result is again corrupt. I don't see this problem with other files with comments - something is "special" about yours.

Workaround 3 - you can save the file as Ods. You can open this file successfully in Libre Office. Excel complains when you try to open it in Excel, but, if you let it make its "corrections", it seems okay.

Incomplete workarounds - you can write the file as Xls, and it is readable, but the comments are gone (no support in PhpSpreadsheet for writing comments to Xls - open item in issue #3403). You can write the file as Ods, but, again, the comments are gone (I need to research whether this is unimplemented or a bug).

@agaluf
Copy link

agaluf commented Nov 1, 2024

This seems to be the same issue as the one reported in #4145 - I’m almost certain the bug was introduced by something in 1.29.

@oleibman
Copy link
Collaborator

oleibman commented Nov 1, 2024

@agaluf Thank you - that was a very valuable clue! It appears that, in the worksheet xml, the ignoredErrors tag (introduced after 1.28) must precede the legacyDrawing tag, but the corrupt output spreadsheet in this example has legacyDrawing first. (Xml doesn't care, and Excel doesn't document.) The same is true of the output file using your sample file from 4145, except that the tag there is drawing rather than legacyDrawing. Expect a fix for both issues next week.

@madorin
Copy link
Author

madorin commented Nov 1, 2024

@oleibman , i was able to reduce it to an excel file with two cells,
template.xlsx

When opening, the excel show this error:
Replaced Part: /xl/worksheets/sheet1.xml part with XML error. Load error. Line 2, column 1904.
Removed Records: Style from /xl/styles.xml part (Styles)

In excel file, we have at this position
image

Hope it helps, I don't know the Xlsx specs, and if the "ignoredErrors" is a right tag.

@madorin
Copy link
Author

madorin commented Nov 1, 2024

@oleibman , thanks for hint with *Drawing and ignoredErrors precedence!

Seems the write order fix the issue:

image

I tested it with my template, and it goes fine! Waiting for an official fix...

@oleibman
Copy link
Collaborator

oleibman commented Nov 1, 2024

@madorin Thank you for the additional sample. It has the same problem as the others, and will be fixed soon.

oleibman added a commit to oleibman/PhpSpreadsheet that referenced this issue Nov 1, 2024
Fix PHPOffice#4200. Fix PHPOffice#4145. Although the Xml is valid, Excel insists that worksheet.xml specifies `ignoredErrors` (introduced with 1.29.0) before `legacyDrawing` or `drawing`.
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.

5 participants
@agaluf @oleibman @madorin and others