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.
Fix#3557. Borders around merged cells are not handled correctly for Mpdf. Although a perfectly acceptable workaround is suggested in the issue, it would be better if things just worked without the workaround. Html and Dompdf work with the existing code. As it turns out, Tcpdf does not work, but for a different reason than Mpdf.
Mpdf was not working because Mpdf does not honor the `!important` attribute in Css. We can get it working almost perfectly by suppressing `border*:none`; the exception is fairly Byzantine, and I'll be glad to discuss the matter should anyone report a problem with it. At any rate, it's not working now in the exception case, so we won't be any worse off.
Tcpdf was not working because the merging of attributes happened only when `useInlineCss` was not being used, but Tcpdf does use it. Merging of border attributes is now added for useInlineCss.
Xls Reader processing Conditionals interferes with the previously established SelectedCells. Make sure that value is restored.
StopIfTrue should always be set for Xls spreadsheet.
Set NoFormatSet to true unless any of Font, Fill, or Borders is specified in Conditional Style.
In my notes for PR #3372, I mentioned that I could not include some Xls tests because of errors in the software at that time. This PR fixes those errors, so I am adding the missing test, and making the equivalent Xlsx test more comprehensive.
Fix#4039. Excel sometimes stores the location for a Conditional as, say, `B1:B10 C1:C10` rather than `B1:C10`. PhpSpreadsheet does not have a problem with this, but internally stores it as separate Conditionals, one for each range. (There may be more than 2.) User would like it stored as a single Conditional. Since the range is used as the index of an array which holds the conditionals, there is no technical reason why this can't be done. And it does seem like being able to change multiple ranges all at once has some advantages, whether you're doing it in PhpSpreadsheet or in Excel itself.
Making such a change in Xlsx Reader showed no problem in Xlsx Reader and Writer tests. A number of problems did show up in CellMatcherTest, and one in WizardFactoryTest. This was good news, because it meant some of our test spreadsheets used the same type of construction as the test spreadsheet supplied with the issue. So, if additional fixes make the test problems go away, I think no new tests are required. And a smattering of source changes did indeed result in a clean test suite once more.
This is a change, but I don't really think it should break anyone. So I don't think it's necessary to add an option to opt in to the old or new behavior. I could be wrong. I'll leave this ticket open for at least a couple of weeks to see if anyone thinks otherwise.
Fix#4036. The issue was originally reported as #564 (and #811) and fixed for the most part, but this is a variation that was not covered by the original. Cells with html fragments can cause `mime_content_type` to identify the file as `text\html`. Original fix was to ignore mime_content_type when file extension is 'csv' or 'tsv'. However, if the file does not have one of those extensions, it will be rejected by Csv Reader as invalid mimetype. This PR adds text\html to the list of valid mimetypes.
I imagine that this type of problem might occur for other mimetypes. If any of those are reported in future, it might be better to just add a "suppress mimetype" check option, rather than extending the list forever. Html is unusual in that its rules are so lax, which is why it seems appropriate to add it here.
Note that IOFactory may still identify a file as Html even when intended as Csv. The sample associated with this issue does not fall into this category, but one of the unit tests on this ticket does. The file will still be read correctly by Csv Reader, but IOFactory load may cause it to use Html Reader instead.
Fix#3982. A number of issues submitted about Xlsx read performance have a common theme, namely that row 1,048,576 and a few rows before it are defined in the worksheet Xml with no cells attached to them. These might be the work of a third party product. While these extraneous rows do not cause any problems for the cells that are actually used on the worksheet, they can lead to excessive memory use. This PR provides an option for the application to ignore rows with no cells when loading.
Recent changes to the load logic had already made a significant difference to memory consumption and load time. For the spreadsheet attached to issue 3982, which had caused out-of-memory errors on the user's system, peak memory usage was already reduced to 40-odd MB. With the new option, this is drastically reduced again, to just over 9MB. Specifying the new option is very easy:
```php
$reader->setIgnoreRowsWithNoCells(true);
```
Note that there are cases where you might not want this (non-default) behavior. For example, if you set a row height on a row with no cells, the height would be lost with this option. Unfortunately, the extraneous row definitions in the problematic spreadsheets claim to have a custom height, so I can't just use "no custom row styles" as an additional filter.
Xls Writer Conditional Border had been creating corrupt spreadsheets. This was mainly because a pack statement that should have specified `V` instead specified `v`. Even changing that, the logic was still slightly wrong on write, and missing altogether on read. This PR corrects the write problems and adds the missing read code. It also adds italic and strikethrough support for Xls Writer Conditional Font italic and strikethrough (read code was already in place). With this, Xls Conditional Writer is completely supported except for NumberFormat. Xls does support that, but I cannot figure out how from the available documentation.
oleibman