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.
It pointed out a problem that I would have thought should result in test failure. Code corrected, and documentation updated to describe the unexpected behavior.
Fix#3918, sort of. Xlsx cells use the default style when they omit the `s` tag, or when `s="0"` is specified. LibreOffice does not honor Alignment in the default Style unless the cell explicitly uses the second form, even though it honors other default Styles (e.g. bold font) when `s` is omitted. Gnumeric seems to have the same problem. A bug report has been filed with LibreOffice.
In the meantime, this PR adds to Xlsx Writer an optional boolean property `explicitStyle0` with setter and getter. Default is false, which will continue the current behavior by adding an `s` tag only when the cell uses a non-default style (this is how Excel itself behaves). When set to true, Xlsx Writer will explicity write `s="0"` for all cells using default style. This will allow users to create an Xlsx spreadsheet with default alignment of cells that will show up correctly when the spreadsheet is viewed with LibreOffice. Technically speaking, it is *probably* safe to always use `true`, except that the spreadsheet size will be a bit larger. However, my hope is that this is a temporary measure which can go away when the vendors have had a chance to fix their problems, hence the `false` default.
Fix#3730. File workbook.xml.rels in test file specified absolute addressing for shared strings, styles, and worksheets. Previous changes had addressed absolute addressing for worksheets, but shared strings and styles had not yet been addressed.
Before it was a bug when flooring negative numbers added to the length of
the number for the sprintf mask.
For example -0.091 becomes -9.1 in percentage, floors to -10 which is going to
increase $wholePartSize by 1 and add a leading zero to the result: -09.1% with
format 0.0%.
This happened for negative ranges where floor will increse the length of
the original number.
Fix#3909. SUMPRODUCT is mishandling multi-row ranges. In Calculation/Calculation, `checkMatrixOperands` will often resize its operands. When it does so, it needs to recalculate the dimensions of each. This fixes the reported problem.
Likely cause was PR #3260. That ticket noted the poor coverage of the code being replaced. Tests of the problem in this ticket were absent and are now added. Despite this, I note that `resizeMatricesShrink` is virtually uncovered, and `resizeMatricesExpand` has substantial gaps in its coverage. I have covered some, but not all, of the Expand gaps. I am struggling to come up with examples to fill its remaining gaps and those for Shrink. However, I will merge this fix in about a week even if I don't succeed.
Fix#3907. After row/column insertion/deletion, PhpSpreadsheet updates formulas which include cells which have moved. However, it can mis-identify cell addresses within the formula. Examples:
- `=SUM(A2,'F1 (SETTINGS)'!A1:B1)` It identifes F1 as a cell address.
- `=SUM(A2,'x F1 (SETTINGS)'!A1:B1)` It identifes F1 as a cell address. (This looks the same as the above, but, for technical reasons, it's different.)
- `=SUM(A2,definedname1A1)` It identifes A1 as a cell address.
- Sheet names in formulas are compared case-sensitively, and should be compared insensitively. This can make a difference if the formula includes its own sheet name, e.g. on sheet `Data`, formula `=SUM(DATA!A1:A2)` might have to change, but it will not do so with the existing logic.
The defined name part is fairly straightforward. The regular expressions that identify a cell address just have to be a bit more robust. It was doing a negative look-behind for an alphabetic character or dollar sign; underscore, period, and digits, all of which can be part of a defined name, need to be added to that list.
The other situations need a bit of a kludge, but not one so bad that I'm ashamed of it. The formulas will be altered before analysis so that sheet names are replaced with Unicode FFFD (sheetname does not match current sheet), FFFC (sheetname, enclosed in apostrophes, matches current sheet), and FFFB (sheetname, not enclosed in apostrophes, matches current sheet). This prevents the existing regular expressions from finding a cell address within a sheet name, and makes it easy to restore the original, with or without apostrophes, when the sheet name matches the current sheet and the cell(s) which it qualifies have to be changed.
Tests are added for all the situations mentioned above. No existing tests required changes.
Fix#3900. The code to adjust Decimal and Thousands separators to user-specified choices expects to convert periods to commas and vice versa - hard-coded without taking the user specification into account. This is likely to be the only significant use case, however, as the issue demonstrates, it is not the only possibility. In particular, it will mess up Csv output if decimal separator is kept as period, but thousands separator is set to null string. The code is altered to replace period with the user-selected decimal separator, and comma with the user-selected thousands separator. No existing tests broke as a result of this change in behavior.
This PR resolves about half the statements which we tell Phpstan to ignore. There will still be 23 such annotations spread over 10 source modules for various reasons (too complicated, suspect PhpSpreadsheet code, one Phpstan bug), plus some deliberate errors in the test suite.
The samples have become unwieldy when running them from a browser. In particular, the drop-down lists are fixed size with no scrolling, and many of them are now just too large. I have moved all the Calculation samples up a level, and broken several categories (Basic, Chart, DateTime, Engineering, Financial, and Reader) into several pieces.
Convert-Online (now found in the Engineering category) had a number of different problems which are now resolved. It is the only member with any significant code change.
Fix#3875. Even better, fix#2146, which has been open for 2.5 years.
Empty arguments are improperly placed on the stack; in particular, they are added without `onlyIf` and `onlyIfNot` attributes.This results in problems described in 3875.
IF has a somewhat unexpected design. In Excel, `IF(false, valueIfTrue)` evaluates as `false`, but `IF(false, valueIfTrue,)` evaluates as 0. This means that IF empty arguments should be handled in the same manner as MIN/MAX/MINA/MAXA, but you need to be careful to distinguish empty from omitted.
Also note that IF requires 2 operands - `IF(true)` is an error, but `IF(true,)` evaluates to 0.
oleibman
Nicholas Ruunu
Adrien Crivelli