search

PHPOffice / PhpSpreadsheet

A pure PHP library for reading and writing spreadsheet files
https://phpspreadsheet.readthedocs.io
MIT License
13.36k stars 3.47k forks source link

Xls Glitches #3403

Open oleibman opened 1 year ago

oleibman commented 1 year ago

This is:

- [x] a bug report
- [ ] a feature request
- [x] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

Recent and not-so-recent work has uncovered some areas where Xls format is not working perfectly. I am opening this ticket to document the issues that I've found. There are certainly others, and resolving them may be very difficult. The BIFF documentation is often incomplete and/or difficult to understand.

Formulas which use defined names are not supported for write (calculated value is substituted for formula, so it's not a complete loss). There is (disabled) code which would nominally support them, but, when the code is enabled, it not only does not work, it creates a corrupt spreadsheet.

Conditional Format Fill - does not work. I suspect that the writer is writing StartColor when it should be writing EndColor and vice versa. Even so, when I hard-code what it writes for Conditional Fill to match a file created directly in Excel, it still does not work. Fixed by PR #4030.

Conditional Format Font - color, bold, and underline work; italic and strikethrough do not. Fixed by PR #4033.

Conditional Format NumberFormat - does not work.

Conditional Format Borders - not only does not work, it creates a corrupt spreadsheet. Even without the corruption, border colors are not supported. Fixed by PR #4033.

Conditional Formatting Samples - all the samples in the ConditionalFormatting directory create non-corrupt results (but, of course, are subject to the problems noted above). However, Basic/08_Conditional_formatting.php and Basic/08_Conditional_formatting_2.php generate corrupt Xls files. Neither of them uses Border formatting, so I'm not sure what the problem is. Fixed by PR #4030.

HyperlinkBase document property is not supported on read. It is simulated on write with the merger of PR #3589.

Custom document properties are not supported for read or write. (Noted in issue #1410 which has gone stale.)

Split screen is not supported. (Added for Xlsx and Xml with PR #3622.)

No support for reading or writing charts.

No support for background image. (Added for Xlsx with PR #3795.)

A default color may be used (e.g. for font color) in place of all but a limited number of recognized colors in Conditional formats. Fixed by PR #4030.

Functions ISODD and ISEVEN are treated by Excel Xls as "Addin functions". PhpSpreadsheet can handle them on read, but is not yet able to handle them on write.

Cell comments can be read but not written.

What is the current behavior?

See description

What are the steps to reproduce?

See description

What features do you think are causing the issue

Does an issue affect all spreadsheet file formats? If not, which formats are affected?

Only Xls is affected.

Which versions of PhpSpreadsheet and PHP are affected?

All.

Buyur commented 1 year ago

Not sure if right place but have an issue with XLS writing in that a lot of the data is bloated with x00 between each character in the headers and content (when looking at the raw file in HexEditor). An example is a file which is around 140kb when generated using the xls writer. When opened and saved with Excel, this shrinks to around 60kb and all these x00s have gone.

oleibman commented 1 year ago

@Buyur Apologies - I did not notice your message till now. The binary zeroes are because Xls stores its strings as UTF16, which requires 2 bytes per character. If your data is mostly ASCII, then, yes, you will see a lot of these. I do not know why opening and saving the file (as Xls) in Excel would make them go away; I might have to see your file (or code) to determine why; even so, it might not help.