Xls Glitches #3403
Comments
|
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. |
|
@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. |
oleibman
added a commit
to oleibman/PhpSpreadsheet
that referenced
this issue
May 14, 2024
Fix PHPOffice#4025. With Conditional Formatting, Excel can change Font, Fill, Borders, and NumberFormat. It does not support Alignment nor Protection, at least not natively. This PR removes all code supporting Conditional Alignment or Protection from Xlsx and Xls Writer. Attached to the issue report is a spreadsheet where a conditionally formatted merged cell shows its value twice when Alignment is included with the Conditional Style; it is fixed by this PR. It is not entirely certain which particular combination of CF, Alignment, Merge, AutoFilter, and right-to-left sheet alignment trigger this problem. I am sure that it shows up for Vertical Alignment Center, and I am sure that Xls Writer (which is somewhat buggy anyhow regarding CF support - see issue PHPOffice#3403) has the same problem. The now-unused Xls code is commented out rather than deleted, for documentation purposes.
11 tasks
oleibman
added a commit
to oleibman/PhpSpreadsheet
that referenced
this issue
May 16, 2024
While researching another problem, I noticed that font color was not working as expected for Xls Conditional Formats, at least not when a "non-standard" color is used. In such cases, the color might wind up being rendered as black. The reason is as follows. Xls Writer includes a color palette which is dynamically generated from the (non-Conditional) styles used in the workbook. Any colors used in the workbook are indexes to this dynamic palette. However, Conditional colors use a static palette found in class ColorMap to determine the index, so the determination of index will often not find a match, and, if a match is found, it is not necessarily correct. (Also, the ColorMap method was case-sensitive and needs to be insensitive.) In order to correct this, the `addColor` method in Xls Writer Workbook needs to be accessible to the Conditional logic which is found in Xls Writer Worksheet. This is accomplished by passing the Workbook in the Worksheet's constructor, and changing the method to public, and changing Conditional Font to use this method rather than ColorMap. The logic for Conditional Fill colors is similarly changed. Although Xls Conditional Fill has appeared to just not work, I was finally able to figure out the problem. Excel Xls Conditional Fill with fill type Solid requires that the fill color be specified as startColor, and that endColor be omitted. Our conditional samples used endColor, and are now changed to use startColor instead; the same is true for our online documentation, and for some tests. Xlsx continues to work as expected, and now Xls does at least some of the time. If the condition is one that Excel Xls does not recognize (e.g. cell contains), it will, of course, not work. A surprising situation that also doesn't work is the use of ISODD or ISEVEN in formulas. Those are "add-in functions" which are handled differently than other functions, and I'm not sure how to support them. I will document this in issue PHPOffice#3403. Samples 08_Conditional_Formatting(_2) had produced corrupt Xls versions. This turned out to be because the code was using hash codes to avoid having to write out duplicate conditionals; this is often a good idea, but not in this case. Allowing the duplicates fixes the corruption problem. Conditional Border colors also ought to figure in this change, but the current code does not support Border colors, and I have not yet been able to figure out how to implement it (BIFF format can be very messy to figure out). With this change, I could delete ColorMap altogether. However, it is a public class with a static public method, so maybe someone is using it for a purpose I'm not familiar with. I will just deprecate it.
11 tasks
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This is:
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.
The text was updated successfully, but these errors were encountered: