Prevent XEE by hiding custom entities by using single quote to
declare a non-UTF-8 encoding.
XML standard, https://www.w3.org/TR/xml/#NT-EncodingDecl, allows single
quote to declare encoding, but we did not support it. Instead, we
incorrectly fell back on the default of UTF-8. That incorrectly kept the
XML as non-UTF-8, and thus prevented our regexp-based custom entity
detection mechanism to work.
Fix#4112. Direct cause is that `applyStylesFromArray` tries to save and restore `activeSheetIndex`. However, if activeSheetIndex is -1, indicating no active sheet, the restore should not be attempted. Code is changed to test before attempting to restore.
The actual problem, however, is that user specified a sheet number for `addSheet`. That method will set activeSheetIndex most of the time, but this was a gap - when the supplied sheet number (0 in this case) is greater than activeSheetIndex (-1 in this case), it was leaving activeSheetIndex as -1. It is changed to set activeSheetIndex to 0 when activeSheetIndex is negative.
Fix#4099. Ods Reader was expecting there to always be `header-style` and `footer-style` tags when `page-layout` tag is present, but these need not exist. It seemed like there might be other exposures along this line in `readPageSettingStyles`; rather than waiting for a problem report to show up for each, the code is updated to use `->item(0)` in place of `[0]` when appropriate, and make use of the nullsafe `?->` operator introduced with Php8.
Fix#912, opened in Feb. 2019, and closed as stale in Apr. 2019, and which I have re-opened to be closed properly by this PR. Another "better late than never". Original issue says that print options should not affect ShowGridlines, which seems true enough. Aside from that, the existing code isn't quite correct anyhow. Excel looks for 2 attributes, one of which must be explicitly set to true and the other of which must not be explicitly set to false, in order to determine whether PrintGridlines should be set. PhpSpreadsheet is changed to do the same. This could be treated as a BC break for the unusual situation described in the issue, but it seems more like a bug fix to me.
Fix#296, another entry in our magical history tour (closed as stale in 2018). Excel allows you to use a name defined on another worksheet by prefixing the sheet name, even when the scope of the defined name is its worksheet rather than the entire workbook.
Fix#476. Another in the "better late than never" series, closed as stale in June 2018. Xlsx Writer expects cells containing RichText to have DataType `TYPE_INLINE`; but the spreadsheet associated with the issue has the cell defined as `TYPE_STRING`. Change Writer to handle RichText TYPE_STRING appropriately.
Fix#460. Another in the "better late than never" series, closed as stale in June 2018. Ods Writer and Ods Reader handle booleans differently; what is worse, neither of them do it correctly. They will now match the behavior of LibreOffice. Reporter said that part of the xml would vary depending on locale; I believe that part is never actually used, but I do emulate that behavior.
While researching issue #1551, I came across some minor problems.
When a spreadsheet does not have a title, which is often the case for spreadsheets created with Excel (note that this is not the case for spreadsheets created with PhpSpreadsheet), if you try to save it as Html, it throws an exception. It will now use the sheet title of the active sheet as a title in this case.
When writing an Html spreadsheet using `useInlineCss(true)`, gridlines are not handled properly. This is addressed by adding `class=gridlines gridlinesp` to the cell's `td` tag, and by suppressing any border attributes which would be styled as `none #000000`. It would be unusual to turn off gridlines for specific cells, but that can still be accomplished by using `Border::BORDER_NONE` in conjunction with any color other than `#000000` - see new test `testHideSomeGridlines`.
Fix#64 (really!), closed as stale in December 2017, another in our "better late than never" series. Excel's INDEX function doesn't really behave quite as described. If a single row is used as an argument, either in literal form `{item1, item2, item3}` or expressed as a range `A1:A6`, INDEX is happy to evaluate the array as if each entry were a row rather than a single item. PhpSpreadsheet is changed to do likewise.
INDEX also returned `#REF!` when it would normally return an array (which would often be reduced to its leftmost topmost entry later). This code is deleted, invalidating one existing test, and INDEX will now operate like other functions which can return arrays.
Fix#804, opened in Dec. 2018, and closed as stale in Feb. 2019, and which I have re-opened to be closed properly by this PR. Better late than never, I suppose. A third party generated an ODS spreadsheet which PhpSpreadsheet could not read. By way of explanation, the xml in the file contained lots of whitespace between tags, which is wonderful for those humans among us who have to analyze it; but PhpSpreadsheet was not prepared for it. It is now.
Fix#4081. Ods Reader was not reading entire contents of comment. On further inspection, Ods Writer also was not handling comments completely correctly. Ods comments are recorded as `text:p` children of `office:annotation` elements. A newline is inserted between successive `text:p` elements. The `text:p` element itself can have as descendants (at least):
- raw text
- `text:span` elements
- `text:line-break` elements, which also causes the insertion of a newline
Ods Writer is changed to use a single `text:p` with multiple span/linebreak elements. Ods Reader is changed to process in their entirety either that form, or multiple `text:p` elements. Styling of the individual elements of the comment is permitted in Ods. That has not been supported till now by PhpSpreadsheet, and this PR will not address that situation - Ods Reader hast little style support, and this would hardly be the most urgent case where it is missing.
Fix#2581 (not obvious - see next paragraph for explanation). This continues the work of PR #2902 (and also PR #3467) to have errors propagated through function calculations rather than treating them as strings. All text functions, and the concatenation operator, are addressed in this PR.
In the original issue, the spreadsheet being loaded uses the result of an unimplemented function as an argument to another function. When `getCalculatedValue` is used on the cell in question, the result is returned as `#VALUE!`. If the cell had just contained a function call to the unimplemented function, getCalculatedValue would have recognized the situation and returned oldCalculatedValue as the result. Not perfect, but good enough most of the time. User would like oldCalculatedValue returned here as well, which seems like a reasonable request.
PhpSpreadsheet always returns `#Not Yet Implemented` as the result for a function which it knows about but which is not yet implemented. That is the key to the `Cell` class being able to substitute oldCalculatedValue in the first place. However, in order to do that for the issue in question, that result has to be propagated to any functions for which the result is an argument. I don't want to add unimplemented to the list of known error codes, but I am willing to add a parameter to `ErrorValue::isError` to indicate whether that value should be considered an error (default is "no").
The first use of that new parameter would be by the text functions. They go through a common Helper routine, so it is pretty easily implemented. And, as it turns out, most of the text functions do not currently propagate errors, e.g. if A1 results in a value error, `=LEFT(A1,2)` will result in `#V` rather than `#VALUE!`. With this PR, they will now be handled correctly.
Supersedes PR #1415 by @AndrewMonty, which went stale in May 2020, and which is not directly usable due to changes between now and then. Fix#1414, which also went stale; I will remove the stale status and reopen the issue pending the merging of this PR.
Add an option to CSV Writer so that it writes the cells for a row only through the highest data column used in the row, rather than through the highest data column used in the worksheet.
Fix#1310, which was closed as stale in 2020, but which I will now reopen. Supersedes PR #1311 (@jaiminmoslake7020), from which I will remove the stale label but leave closed. The issue and the PR were too limited - they detected that the use of two equal signs at the start of a string made for an invalid formula, but there are variations, trivial and otherwise, which might also be detected. Using `setValue` with a string which starts with an equal sign will now attempt to parse (not evaluate) the formula; for certain situations in which the parser throws an exception, the string will be treated as a string rather than a formula. An example where it will still be treated as a formula is a 3D range reference, where the problem is not that it can't be parsed, but rather that the formula isn't supported (see unit test Calculation/Engine/RangeTest::test3dRangeEvaluation). Allowing such a formula might cause problems later on, but that is already what happens.
A string beginning with an equal sign but which isn't treated as a formula will automatically set the `quotePrefix` attribute to `true`; all other `setValue` attempts will set it to `false`. This avoids the problem of a lingering value causing problems later on.
It has long been a matter of discontent that setting a style can change the selected cells. A new method is added to `Worksheet`:
```php
applyStylesFromArray(string $coordinate, array $styleArray)
```
This will attempt to guarantee that the active sheet in the current spreadsheet, and the selected cells in the current worksheet, remain undisturbed after the call. The setting of `quotePrefix` above is the first use of the new method.
This change is extracted from PR #2787 by @MarkBaker. That change mostly deals with array functions, and that part will be superseded by PR #3962. However, this part of 2787 is not included in 3962.
Fix#1036 (closed as stale in 2019 and just reopened). Excel spreadsheets can have either of 2 base dates, 1900 or 1904, and the numeric value of any date cells will vary depending on which base date is in use. PhpSpreadsheet has, till now, handled that as a static property of Shared/Date. This does not work well if two spreadsheets with different base dates are open simultaneously. The code is changed to store the base date as a property of the spreadsheet when an Xls/Xlsx spreadsheet is loaded, and use that property when saving an Xls/Xlsx spreadsheet. Any call to `getCalculatedValue` or `getFormattedValue` will temporarily set the Shared/Date value to that of the spreadsheet, and restore it at completion. In order to avoid a BC break, the Xls and Xlsx readers will continue to populate the Shared/Date value as before.
Following up from PR #4006. There is an additional RTL property available. It controls the placement of bidirectional neutral characters (mainly punctuation), as opposed to strong (alphabetic characters) or weak (numeric characters), especially at the beginning or end of a line. The new Comment property textboxDirection will be used for that purpose.
In a discussion in issue #4004 following the implementation of the PR, the comment was mixed RTL and LTR, and this led to some formatting problems. The user was able to overcome these with the timely insertion of Unicode directional control characters, but it would be preferable to have it happen automatically, which this change will permit. However, the use of these control characters cannot be entirely done away with. In the new test case, if one of the all-English lines ended with, say, a colon, it would not display correctly; LRM (left-to-right mark) after the colon would be needed. Likewise, one or two of the comment lines with mixed RTL and LTR (discussed in the issue) is not formatted correctly, and might require LRO/PDF or equivalent.
Fix#4063. Fix#1560. Fix#1293. PhpSpreadsheet is not accounting for an empty string in Xlsx sharedStrings.xml.The code which parses it in Reader/Xlsx looks for a `t` or `r` tag descending from `si`, but, in this case, the tag is coded as `<si/>`, with neither t nor r tag descending. An else clause is added to set the string to empty string in this case.
I was surprised that this had not turned up before, and a search through the archives found at least 2 earlier reports from 4 years ago. Those had been marked stale; the stale indicator is removed, and the issues are re-opened, to be closed when this PR is merged.
Continuing work started with PR #4016 and PR #4026. 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.
This change has more executable code changes than its predecessor. I will wait longer than normal before merging it to allow for additional testing.
Dependabot suggested some changes this month which required an unusual effort to implement successfully. With the elimination of Php 8.0 as a supported environment, it became possible to use Phpunit 10 rather than 9. Among other considerations, the configuration file for Phpunit is changed. I preserved the Phpunit 9 version under a different name. Aside from the configuration change, several other changes needed to be made to accommodate the change:
- Fix#3993. A peculiar problem indeed. One of the reporters said it had something to do with mocking, but I couldn't duplicate it. But Phpunit 10 revealed the problem in one test (Reader/Xlsx/AutoFilterTest), and that was sufficient for me to apply the trivial source code change to Worksheet/Worksheet.
- Some extra stringency on data providers required extra work in Calculation/CalculationFunctionListTest.
- There was a misplaced label in Calculation/ParseFormulaTest. Likewise in the data member CellGetRangeBoundaries.
- More stringency required changes to data members Shared/Trend/ExponentialBestFit and Shared/Trend/LinearBestFit.
- Issue3982Test testLoadAllRows seemed to go into a memory-acquiring loop in 10 that was not evident in 9. This particular test uses a lot of memory by design, but was included only to establish a base level for the other tests in that member. I feel it is acceptable to skip it for 10.
Phpstan errors with a new release are not unusual. One of the problems this time around was, however, unusual - it is fixed when Phpstan runs under Php8.3, but not for earlier Php releases. Our tools currently use Php 8.1. It is on my to-do list to get Phpstan and other test tools running under 8.3 before 8.1 goes EOL. There are also some configuration file changes needed for Phpstan.
Php-cs-fixer has been taking an increasingly long time to run. They've added an experimental option to permit it to run its checks in parallel. I've changed its configuration to use that option.
Fix#4049. Some possible options were not included for read or write. In addition, although it isn't well documented, it appears that 2-color scale always has 2 cvfo entries in Xml in order minimum/maximum, and 3-color scale always has 3 entries in order minimum/midpoint/maximum.
Provide a means to convert a range, possibly with unions and possibly with intersections, into something that both Excel and PhpSpreadsheet can handle. Intersections are changed into unions of the individual cells which they comprise. With this change, Xls Writer now handles intersections (previously it would have thrown an Exception or created a corrupt worksheet if this was attempted), and Xlsx Writer works correctly (it seemed to before, but Excel didn't understand what it wrote). Worksheet::getConditionalRange and ::getConditionalStyles would previously have thrown an Exception when presented with an intersection, and will no longer do so.
**NOTE:** Intersection support is limited to Conditional ranges. Use of intersections in other contexts will usually not achieve the desired result.
Adrien Crivelli
oleibman