Fix#4773. Mpdf is applying cell style only to the first line of a multi-line string. Explanation - Html Writer replaces newlines with `<br />` by calling `nl2br`, at least that was the intention. However, `nl2br` doesn't replace - it prepends. This doesn't do any harm in Html, Dompdf, or Tcpdf. However, Mpdf is known to be subject to occasional regexp backtracking errors when parsing large blocks of html. To avoid this, we chunk the html by splitting it at newlines, but this causes the styling to be lost for rich text elements following a newline.
The solution to this problem seems easy enough - replace `nl2br` with a routine that replaces rather than prepends. Unfortunately, Html Reader needs the newlines intact in order to parse things correctly. So a solution where the `nl2br` calls are used for non-Mpdf and using a substitute routine for Mpdf ought to work. However, doing my testing uncovered another bug - rich text with newlines can result in multiple `<br />` tags (see new Html test). The solution to this is a bit kludgey, but it does seem to fix both the Mpdf problem and the newly discovered Html problem. It will also tend to avoid the very minor problem of generating different line endings on Windows systems.
Fix#606 (marked stale in 2018, but now reopened). User reports that, under some circumstances, `oldCalculatedValue` will be used as the result when an unsupported function is part of a formula (this seems like a sensible treatment), but, in other cases, the unsupported function is ignored, or has an unpredictable effect on the result.
To explain a little further, an unsupported function will return a "magic" value which will eventually be replaced by `oldCalculatedValue` (or null if there is none). However, the magic value is not being propagated. In the issue, there are 3 places where this propagation is currently omitted - when performing a binary comparison, when evaluating the condition in the `IF` function, and when evaluating the `SUM` function. This PR causes propagation to happen in all of those places, and a few others (MIN, MINA, MAX, MAXA, ROWS, COLUMNS). I would not be surprised to have missed one or more of the other situations.
Fix#3889. Fix#2464. After evaluating HYPERLINK function specifying cell address(es) rather than literal(s), the calculated hyperlink is attached to the wrong cell. I probably should have figured this out sooner - this is just another instance of the common problem of trying to assign a cell to a variable and then using the variable after a delay during which the "current" cell may have changed. The problem is resolved by storing the cell's worksheet and coordinate on entry to HYPERLINK, then restoring those before manipulating the hyperlink before returning to the caller.
An additional problem with hyperlinks is that assigning a new value to a cell has not cleared any hyperlink associated with the cell. This will now happen, if needed, whenever `setValue` or `setValueExplicit` is called.
A third problem is unaddressed. A (fairly unrealistic) formula like:
```php
$sheet->getCell('A4')->setValue('=LEN(HYPERLINK("http://www.example.com", "Example"))');
```
winds up attaching a hyperlink to the cell when its value is calculated, and it probably shouldn't. However, @TobiasBg reports in 2464 that Excel for Mac does the same thing, and I've just confirmed that Excel 365 on Windows does likewise. Furthermore, the hyperlink which Excel creates is not usable ("Cannot open the specified file"), whereas the link that PhpSpreadsheet creates is usable. So we're not doing any worse than Excel, and arguably doing better. I'm satisfied.
Thank you to @raziel057, who pointed out this problem while doing some research on a PR on which he is working. The code in AxisShadowTest generates a spreadsheet which, if written to disk, will be treated by Excel as corrupt. Since the test doesn't actually generate a spreadsheet, it isn't entirely invalid, but it should do a better job of presenting usable code.
Chart axes have a shadow property, which has many sub-properties. One of these is `size`, which also has sub-properties. Two of those are `kx` and `ky`, representing the skew angle of the shadow. These must be < 90 degrees and > -90 degrees (strangely *not* <= and >=) (see https://ooxml.info/docs/20/20.1/20.1.10/20.1.10.23/). If the supplied value is outside that range, Excel treats the chart as corrupt. That is the main problem with AxisShadowTest - kx was supplied as `-94`. This problem is easily solved in the test by setting it to a valid value instead. And, of course, Chart Writer is changed to not output the value if it is out of range.
Size also has 2 other properties `sx` and `sy`, where a value of 1 indicates 100% of normal size. The test sets `sy` to `254`, a not-particularly sane value. I am fairly certain that `2.54` was intended. I do not believe Excel treats `254` as corrupt, but the test is also changed to supply a sensible value. I imagine that there is a maximum value that Excel will allow, but I don't see it documented anywhere. The UI restricts it to 2 (200%), but larger values are accepted in the xml, so, at least for now, I allow anything.
I have to admit that the Excel UI puzzles me with respect to these properties. There is a single value that can be set for `size` (i.e. no separate property for `sx` and `sy`), and I don't see any way to set the skew angle. There is a settable `angle` property, but that refers to a different shadow property `direction`. At any rate, Excel seems to respond to changes in the xml for all of `sx`, `sy`, `kx`, and `ky`, so we will continue to support them.
Fix#918, which went stale in 2019 and is now reopened. Writing out a RichText object with no text to Xls creates a file which Excel considers corrupt. Treating such an object as a null string rather than a RichText object when writing eliminates the problem.
Fix#4776. DefaultValueBinder treats strings that look like scientific notation as floats. However, if Php evaluates them as `INF`, this can cause problems for Excel. A one-line change to DefaultValueBinder avoids this situation. Tests are added for DefaultValueBinder, and also for StringValueBinder and AdvancedValueBinder, although those extend DefaultValueBinder and needed no code changes.
Fix#1319, which went stale in 2020, and is now reopened. Rowspan is not showing up when saving some spreadsheets as Html. There is some logic concerning property `isSpannedRow` which indicates that this is deliberate, but I have no idea what purpose it serves. It leads directly to this issue, and also adversely affects (in a minor way) some samples (e.g. 17a_Html), where merged cells maintain their text but lose their top and bottom padding. After discarding this logic, the reported problem goes away, the samples look better, and no tests fail. So, whatever its purpose, it wasn't tested for. This PR permanently removes the property, and adds a little additional bulletproofing. A minor accommodation is needed for Tcpdf in one place.
This PR probably deletes more lines from Html Writer than it adds. It would not surprise me if this is one of those unusual cases where Coveralls reports a decrease in coverage despite the fact that all the new code is covered.
This attribute is written by Xlsx Writer, using the `tooltip` attribute, introduced by PR #904 in 2019. It is ignored by Excel, but may be used by Google Sheets (see issue #807). If it is used by anyone, it should be supported by Xlsx Reader, and exist as its own attribute in the `Hyperlink` class. Writer will continue to use tooltip if the new attribute isn't set.
Fix#820, which went stale in 2019 and is now reopened. Another weird Excel implementation detail. When SUBTOTAL's first argument is 101 through 111, it excludes all hidden rows. When that argument is 1 through 11, it excludes all rows which are hidden due to an auto-filter, but includes rows which were manually hidden. PhpSpreadsheet had not been distinguishing between the two types of "hidden-ness". Add a new property to RowDimension to indicate whether it is hidden due to an auto-filter, and use that property when evaluating SUBTOTAL 1-11.
Fix#993, which went stale in 2019 and is now reopened. It was not explicitly stated in the documentation, but Xlsx supports hyperlinks when clicking on a drawing. User tried to use the same format for internal hyperlink that is documented for cell internal hyperlink, and that failed (corrupt spreadsheet, hyperlink doesn't work). That was because Xlsx uses `#` rather than `sheet://`. For drawings, Xlsx Writer will now automatically convert `sheet://` to `#`, and Xlsx Reader will now convert the other way. No change is needed for cell hyperlinks. Documentation is now updated, including a statement that drawing hyperlinks work only for Xlsx.
Using a default style looks better than leaving them unstyled. User has ability to ignore unstyled tables as before, but I don't know why one would want to.
A dependency checker found the following minor problems:
- `TIMEVALUE` uses the wrong case for `DateTime`.
- The `filter` extension is used but is not declared as a dependency.
- The `exif` extension is used but is not declared as a dependency.
Php tolerates the first, but the solution is so trivial that it might as well be applied.
You have to go out of your way to not include `filter` - is it almost always builtin. Nevertheless, it shouldn't hurt to explicitly declare it.
`exif` is used in only one place, and, if it's not available there, we fall back to `GD`, which *is* a dependency. Getting rid of the `exif` portion should be harmless.
Fix#647, which went stale in 2019 and is now reopened.
Fix#849, which went stale in 2019 and is now reopened.
Html Reader currently echoes any warning messages from Dom::loadHtml when it parses Html input. These are generally not needed, and may cause PhpSpreadsheet to fail needlessly. A new property `$suppressLoadWarnings` is added. It will additionally default to `null`, which will avoid any BC breaks. However, the default will be changed to `true` in a future release. Should the caller be interested in any messages which are suppressed when `true` is in effect, these are available through new method `getLibxmlMessages` after the Html has been loaded.
Fix#954, which went stale 6 years ago and which I have now reopened. The `setLocale` method in `Calculation` and `Settings` does not affect the 3 localizable properties in `StringHelper` - `currencyCode`, `decimalSeparator`, and `thousandsSeparator`. One way to work around this problem is to set those properties separately; this is probably how most people handle it. Another way is to call Php's own `setlocale` function; this does not require any foreknowledge of what the values need to be, but it comes with baggage (data is maintained at process level rather than thread level), so its use is discouraged.
This PR adds a new `setLocale` method to `StringHelper`. It sets the 3 properties and the `Calculation` language all at once. It depends on the `Intl` extension, which is a recommendation but not a formal requirement for PhpSpreadsheet; the method will return `false` if `Intl` is not available or it thinks the supplied locale is not valid.
oleibman
Thomas Lallement
Thomas Lallement