Fix#4128. PR #4073 introduced applyStylesFromArray method, which allowed setting styles without affecting selectedCells or activeSheet. The first use of this method was in Cell setValueExplicit to set quotePrefix appropriately. The new method did not preserve activeCell. I'm not sure why that should matter, but this seems to have caused a problem for Excel 2016. This seems to be a bug in Excel, one which is fixed in newer releases. However, PhpSpreadsheet can avoid the problem by preserving activeCell as well as selectedCells and activeSheet. This PR makes that change.
The latest release seems to not want you to give a class element both a Php type and a doc-block type. I used the "fix" operand to delete the redundant doc-block declarations, with no other changes. So there should be no change to executable code.
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#4113. TRUNC isn't always producing the expected result. There was a promising algorithm at https://stackoverflow.com/questions/4668628/truncate-float-numbers-with-php from user Juan. It works through Php8.3, but failed in Php8.4 (more on this later). User Savageman on the same page has a solution that needs work, but, once the work had taken place, it works on Php8.1-8.4.
The ROUNDUP and ROUNDDOWN functions were adversely affected by Php8.4, probably for the same reasons as Juan's TRUNC suggestion. I put a kludge in place for them some time ago, but I wasn't happy with it. The solution used for TRUNC here suggested a change to the ROUNDUP and ROUNDDOWN code that would no longer require the kludge. The change to those functions now works more cleanly on Php8.1-8.4.
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.
Suggested by PR #848 from @markkimsal. The RATE calculation had already been corrected, so that part of the PR was unnecessary, however one of the tests included a floating point value for Number of Periods, which Excel permits. PhpSpreadsheet till now expected that parameter to be an integer. This is trivially changed with some tests added.
Fix#1284, which was closed as stale in 2019, but which I will now reopen. Html Reader converts *Unicode* whitespace characters in a DOM text node to space. However, Html treats only space, tab, CR, LF, vertical tab, and form-feed as whitespace. Using a regular expression with the `u` (Unicode) modifier causes a number of other characters to be converted to space inappropriately. The issue mentions "ideographic space" in particular, stating that it is used for formatting and should be preserved. "Non-breaking space" is also used in the same way and should also be preserved. An exception is made for a text node consisting of a single non-breaking space, since that is used as a placeholder by Html Writer; my own guess is that this is the reason why the Unicode modifier was used in the first place.
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.
oleibman
Adrien Crivelli