Backport of #4241. Some security batches caused a minor break in Drawings, forcing `setWorksheet` to come after `setPath`. Although the problem is easily fixed in user code, this was not an intended change. Some slight recoding restores the earlier functionality where the order of calls was not important, without sacrificing the security gains.
A bit messier than expected. Current version of symfony/process (used only in dev) has a security advisory. There is no version that can be upgraded to which supports Php8.0. However, a downgrade to V5 will work.
When running with Php8.4, PhpUnit references E_STRICT, which is deprecated in 8.4. I don't understand why this doesn't happen with release1291. Be that as it may, we can't upgrade Phpunit to V10 because we need to support Php8.0. So, monkey around with tests/bootstrap.php to avoid problems.
Fix#3995. Fix#866. Fix#1681. Php DOM loadhtml defaults to character set ISO-8859-1, but our data is UTF-8. So Html Reader alters its html so that loadhtml will not misinterpret characters outside the ASCII range. This works for UTF-8, but breaks other charsets. However, loadhtml uses the correct non-default charset when charset is specified in a meta tag, or when the html starts with a BOM. So, it is sufficient for us to alter the non-ASCII characters only when (a) the data does not start with a BOM, and (b) there is no charset tag.
This will allow us to use:
- UTF-8 files or snippets without BOM, with or without charset
- UTF-8 files with BOM (charset should not be specified and will be ignored if it is)
- UTF-16 files with BOM (charset should not be specified and will be ignored if it is)
- all charsets which are ASCII-compatible for 0x00-0x7f when the charset is declared. This applies to ASCII itself, many Windows and Mac charsets, all of ISO-8859, and most CJK and other-language-specific charsets.
We cannot use:
- UTF-16BE or UTF-16LE declared in a meta tag
- UTF-32, with or without a BOM (browser recommendation is to not support UTF-32, and most browsers do not support it)
- unknown (to loadhtml) or non-ASCII-compatible charsets (EBCDIC?)
I will note that the way I detect the `charset` attribute is imperfect (e.g. might find it in text rather than a meta tag). I think we'd need to write a browser to get it perfect. Anyhow, it is about the same as XmlScanner's attempt to find the `encoding` attribute, and, if it's good enough there, it ought to be good enough here.
Scrutinizer balked at quotes around key 'Unit Name'; no reason not to use 'UnitName' instead.
Having done that, several ConvertUOM tests seemed a little unconvincing. Replaced them with improved versions.
Improve documentation within program by making explicit what types of values are allowed for variables described as "mixed". In order to avoid broken functionality, this is done mainly through doc-blocks. This will get us closer to Phpstan Level 9, but many changes will be needed before we can consider that.
Executable code change is to add 2 new methods to Cell - `getValueString` and `getCalculatedValueString`, which are the same as the methods without the String suffix, except that they always return a string result. These are invoked at several places in the code where it makes sense, in particular by Writer code.
Fix#4001. Thanks to @SlowFox71 who reported the problem and developed most of the solution. This PR adds Rich Text support to the XML reader. The Xml Spreadsheet stores Rich Text as Html tags, children of the ss:Data tag using a specific namespace. These can be parsed into a RichText object using existing method Helper/Html::toRichTextObject. There are 2 items which need special attention.
First, for attributes like bold or italic, Excel uses the appropriate Html tag (e.g. `<B>`). However, for an attribute like color, Excel uses `<Font html:Color="#FF0000">`, with a prefix on the Color tag. PhpSpreadsheet's Html parser cannot cope with the prefix. The parser is changed to strip `html:` from attribute names for the Font tag.
The example cited by the user used a `<BR />` to indicate a line break in the data. However, it appears that, at least some of the time, Excel will instead use ` ` to indicate a line break. The existing parser reduces one or more whitespace characters in the text to a single space, and so ` ` will wind up disappearing. I am not sure why the existing code does this, but I do know that I am not willing to break it. Instead, I've added an optional boolean parameter `$preserveWhiteSpace` to `toRichTextObject`. If false (default), the existing logic will be used; but if true, substitution for whitespace characters in the text will not happen.
Fix#4004. RTL text can be included in a comment, and will display correctly, but the comment as a whole will be left-aligned. There already exists an `alignment` property for Comment, but it is unused. This PR will allow that property to be set, and to be read from and written to an Xlsx spreadsheet when possible.
The Xml tags that govern this property are found, unusually, in a drawing Vml file. The important property is `x:TextHAlign`, which can be set to Left, Right, Center, Justified, or Distributed. There are other tags which seemed like they were relevant to this problem, but I don't think they actually matter:
```xml
<v:textbox style='mso-direction-alt:auto'>
<div style='text-align:right;direction:rtl'></div>
</v:textbox>
```
Fix#3999. Fix#4000. Fix#4002. Several bug reports and feature requests for Xml Reader arrived practically simultaneously. They are all small and hit the same code modules, so I have bundled them together in one PR.
- `loadSpreadsheetFromString` might try to open a file with a falsy name (like '0'), which results in an exception with a misleading message (or a completely unexpected result if a file with that name exists). Code will still throw an exception, but the message will no longer be misleading, and no file I/O will be attempted.
- function `trySimpleXmlLoadString` is deprecated. It should never have been implemented with public visibility, and the fact that it was made the fix above a little more difficult than it would otherwise have been. It is replaced with a private equivalent.
- Style reader function `parseStyles` will now use a better namespace-aware method of reading its Xml data. Peculiarly, the Xml for the Style elements can either include or not a namespace prefix. This is probably because the global namespace and the styles namespace are the same. The existing prefix-based code does not recognize their equivalence, but the new namespace-based code does. Xml Reader continues to use prefix-based code in several other places.
- Border line styles with Weight omitted or equal to 0 have been treated as no border, but they should be treated as 'hair' thickness.
- Support for Zoom is added to Xml Reader.
- In support of the above, new properties (and getters and setters) zoomScalePageLayoutView and zoomScaleSheetLayoutView are added to Worksheet/SheetView. (As far as I can tell, Excel does not support Sheet Layout View for Xml spreadsheets).
- Support is added for those new properties in Xlsx Reader and Writer.
- Xls Reader and Writer seem to work okay without changes. There is one test where Xls shows a different value for one of the properties than Xml or Xlsx, but the spreadsheet looks okay and I don't see any practical consequences of the difference.
- PageBreak support is added to Xml Reader.
- Code for writing out Column Page Breaks in Xlsx Writer was wrong (and, unsurprisingly, untested). A one-line change fixes it, and tests are added.
Fix#3988. Excel allows a table to have some colums with filter buttons exposed and some with filter buttons hidden. However you cannot do this in "native" Excel - VBA is required for this feature. PhpSpreadsheet is supposed to be able to handle this, but Xlsx/Writer/Table had a bug. The `filterColumn` tags in the xml should be children of the `autoFilter` tag, but were generated as siblings. Moving one statement fixes that problem. Fixing that exposed another problem - autoFilter `showHideRows` was not properly recognizing that a filter could exist without any rules, which is what happens when a table filter button is hidden. Another simple change fixes that problem.
The parent issue correctly points out the problem for Column D in samples/Table/01_Table. However, that sample also has a problem with Column A - unlike columns B and C, there is a filter appled to column A, so its dropdown button should appear different than those in B and C. That problem is also corrected with the fixes above. I also added some formal tests based on that sample.
Fix#3954. In response to a Security Incident, a package was added to the project to sanitize the Html for comments attached to a cell. We have tried at least 2 different packages for this purpose, and users have raised legitimate concerns about both.
I believe that adding a sanitizer package, although it addresses the problem, was overkill. Cell comments are a RichText object, and Html Writer already handled RichText cell *values*. Values did not figure in the Incident, and they surely would have done so if they were a problem because it is a lot easier to set up test cases for values than for comments. RichText values were not a problem because they were sanitized with `htmlspecialchars`; if comments were to use the same code that RichText was already using, it would likewise be safely sanitized. As an added bonus, the existing code for comments only uses the plaintext value, but the values code would allow the comments to be styled, just as they are for Xlsx.
This is a breaking change - I don't think it will affect a lot of users, but there may be some. It is worth noting that the comment block before function `writeComment` has a link explaining what is being done. That link mentions only styling elements, not other possibilities like hyperlinks. At any rate, if people are putting styling tags (e.g. `<b>`) in their comments for this purpose, those will no longer work; the styling needs to be applied to the RichText elements. That will keep the user code the same regardless of the format of the intended output, which is certainly a good thing, but it is a break. If people are trying to insert non-styling tage (e.g. `<a>`), those will no longer work, just as there is no way to do it for Xlsx (although Excel itself may convert the raw text to a hyperlink, but that's out of scope, at least for now). I also note that, even with the current code, I haven't yet found a way to keep the Html comment in place long enough to actually click on any hyperlinks.
The main package being dropped brings several other packages along with it, so the project as a whole will have a slightly lighter footprint than before.
The existing `XssVulnerability` test cases are all preserved. Although the final result of the sanitizing changes, it can easily be seen that the results are all harmless.
Fix#3951. When an Excel sheet is protected, even when sorting is explicitly allowed without a password, sorts are permitted only on "protected ranges" within the sheet. PhpSpreadsheet already supports protected ranges, and only minor tinkering is necessary for that (e.g. the protected range can have, but does not require, a password). The more important part of this change is documenting the far-from-intuitive way that Excel handles this. To that end, documentation is updated, and a new sample is added.
A new class, `Worksheet\ProtectedRange` is added in place of the string array which had been used. `Worksheet::getProtectedCells` is deprecated in favor of the new `Worksheet::getProtectedCellRanges`.
Tcpdf issues warnings when processing hidden rows. Some time ago, Mpdf was having problems in the same situation; this was resolved by not writing out hidden rows in the html which Mpdf uses to generate its file. The same solution can be easily applied to Tcpdf.
Neither Tcpdf nor Mpdf handles hidden columns. Dompdf doesn't have a problem with either rows or columns. At any rate, the solution for Tcpdf/Mpdf is to not write out the data in the hidden columns. It is more difficult to implement this for columns than for rows, but this PR should do the trick.
Html writer generally uses `display:none` for the data in suppressed columns. This works, but is technically incorrect - the "approved" method is `visibility:collapse` on the col element. However, Firefox doesn't handle that correctly (open bug was filed over a decade ago), and, since all browsers seem to handle the existing implementation, it is left alone.
Among other considerations, this PR is a necessary precursor for supporting printArea in Html/Pdf should we decide to do that (issue #3941).
Writer/Html protected property `$isMPdf` is deprecated with this PR in favor of testing for `instanceof Mpdf`.
Fix#3923. Cells which have not yet been allocated cause problems when they need to be moved due to a column/row insert/delete. Code had been added in ReferenceHelper earlier to create missing cells, but only in the last data column. It needs to change to create the missing cells:
- for columns, in rows 1 to "highest data row" for columns "before column" to "highest data column".
- for rows, in columns A to "highest data column" for rows "before row" to "highest data row".
Excel supports the following notations for cell styles:
- `[$-F800]` and `[$-x-sysdate]` will format the date according to what appears to be the user's system long date format.
- `[$-F400]` and `[$-x-systime]`, will format the time according to what appears to be the user's system long time format.
- Builtin style 14 will format date according to what appears to be the user's system short date format.
- Builtin style 22 will format date and time according to the user's preference. It appears that the date portion is formatted according to the user's system short date format, but the time portion is formatted according to a format which is neither system short time format nor system long time format, so I'm not sure how this preference is set.
For F800, sysdate, F400, and systime, any other characters in the style are ignored, except that, if you have more than one of this type of block in the style, Excel will treat it as corrupt (error message on open and style changed to General).
Support is added for the new codes. In addition, note that the value displayed in the cell may differ in different environments. To give the PhpSpreadsheet programmer an opportunity to emulate what the intended audience will most often see, properties `shortDateFormat` (default value is builtin 14), `longDateFormat` (default value is `dddd, mmmm d, yyyy`), `dateTimeFormat` (defaults to builtin 22), and `timeFormat` (default is `FORMAT_DATE_TIME2`), with corresponding setters and getters, are added to Style/NumberFormat. Note that, if these properties are set to some other value in PhpSpreadsheet, it will not affect the values in the cell or the style - it is merely a convenience for the programmer. It will, however, affect column width if autosize is specified for the column. If the programmer does not alter any of the new properties, the output should be unchanged from before for builtins 14 and 22.
The new styles are also recognized by the `TEXT` function. In this case, the cell's calculated value may differ from user to user.
Note that this is a small subset of adding locale information to styles. No attempt is made to support any of the other possibilities - locale data will continue to be passed through to the spreadsheet, but PhpSpreadsheet will discard it before attempting to generate the formatted value of a cell.
oleibman