Fix#4600. String incrementation through the `++` operator is deprecated in Php 8.5. Because we make use of that operator to iterate through columns, we are particularly hard hit by that change - unaddressed, it causes over 2,000 errors in our test suite! It is, fortunately, not as difficult as I feared to correct. Replacing the `++` operator with a call to new method `StringHelper::stringIncrement` in 79 statements scattered over 31 source modules (in src, samples, test, and infra) eliminates all the messages in the test suite. It is possible that others are lurking, but I don't know a systematic way of determining if there are others. We'll stick with this for now, and deal with any others as they show up.
This PR will be applied to the master, release390, and release222 branches. It will not be applied to the release210 or release1291 branches, which will now accept security changes only.
These are 5 closely related functions for manipulating arrays. Now that dynamic arrays are part of PhpSpreadsheet, this PR implements those previously-unimplemented functions. Documentation undergoes a very minor change, since they are re-categorized as "Lookup and Reference", which is how Excel classifies them, rather than "Math and Trig".
With PhpUnit 10 came the ability to use Php attributes rather than doc-block annotations for things like "data provider". PhpUnit 11 deprecates the use of annotations, and PhpUnit 12 will not not permit their use. Since PhpUnit 11 requires Php8.2+, we cannot adopt it as long as we support Php8.1, which will continue to be the case for some time. However, there is no penalty for early adoption.
Php-cs-fixer can use:
```
'php_unit_attributes' => ['keep_annotations' => false],
```
This allows us to run `composer fix` to automate all the needed changes. No manual changes were needed for any of the test members.
With this change, PhpUnit 9 can no longer be used with the test suite. File composer.json is updated to reflect that reality, and phpunit9.xml.dist, which has been supplied in case anyone needed to use PhpUnit 9, is no longer required, and is thus deleted. For now, PhpUnit 11 is not being added as a possibility.
No source code is changed in this PR.
Continuing work started with PR #4016, PR #4026, and PR #4060. 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.
After this PR, 231 changes remain, all in src/Calculation.
Fix#1402, another in our "better late than never" series. Stalebot closed it in May 2020, and I have reopened it. @ljcag submitted the issue, and PR #1403 to resolve it, also marked stale. That change is more complicated than this one. Since there were no tests in that PR, and since this PR solves the original problem (test added), I will stick with this version, but will continue to study 1403 before merging.
This is also another in the "Excel doesn't believe in complete documentation" series. (See issue #3802 for a similar example.) Nothing that I have seen in the documentation suggests that a number will not match a numeric string, but that seems clearly to be the case. Despite the lack of complete documentation, PhpSpreadsheet implemented VLOOKUP with that in mind. Unfortunately, it did so by using `is_numeric` on its comparands, and so treats numeric strings as if they were numbers. This PR replaces those tests with `is_int() || is_float()`. The earlier PR used `is_string()` instead as a proxy for "not numeric" but that required other changes.
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.
Till now we have used a static variable/getter/setter to decide what type of result should be returned when a formula is evaluated and an array is the result. This is messy; it would be much better to use an instance variable instead. We cannot eliminate `setArrayReturnType` and `getArrayReturnType` because that would be a BC break. I am considering whether they should be deprecated. In the meantime, I have added a new instance property `instanceArrayReturnType` with getter and setter methods. The property is initially null, and, if it remains so when needed, the static property will be used instead. However, if it is set, its value will be used.
Some examples submitted by @infojunkie have demonstrated that an anchor cell without a spill operator was not being handled consistently with Excel. This means that the calculated value of such a cell needs to be a scalar when using it as part of a formula without the spill operator, but as an array when using it with the spill operator or when just getting the cell's value. This is tricky. My solution seems awfully kludgey to me, but it does seem to work.
I have another change that I will want to make in a day or so. When that change is pushed, I will take this back out of draft status, and will now aim for an install date of about August 6.
This particular commit removes the changing of array return type during calculations. It's been this way for a very long time, but I don't understand why it should have been needed in the first place. It causes problems (you need to be sure to restore the original value even when, for example, you throw an exception during calculation). I am relieved that it caused a problem only for one test member. The TEXTSPLIT function really makes sense only when you are returning arrays as arrays. Its test now needs to set that value explicitly since the Calculation engine is no longer changing it under the covers. No other tests broke as a result of this change.
One other test "broke" as a result of this commit. One of the tests for INDIRECT had been expecting a null value, and the test was commented with "Excel result is 0". The PhpSpreadsheet result is now 0 as well, so this would seem to be a bugfix rather than a breaking change.
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#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.
Spill operator now works both as trailing `#` and ARRAYANCHOR function. `#` is converted to ARRAYANCHOR when writing. I do not think it is important to convert the other way when reading.
Documentation updates have started, but are a work in progress.
SINGLE function is implemented. I believe it works correctly when referring to a cell, but not when referring to a cell range. No attempt is yet made to convert leading `@` to and from SINGLE; I haven't figured out how to do so without interfering with `@` in structured references.
ISREF has problems. At least one of its tests was wrong, and many of those that were right were so accidentally. The code is changed, quite kludgily, so that almost all the tests are now deliberately correct. One very complicated test is incorrect; for now, I will skip it, and will open an issue when this PR is merged.
Implement SPILL for dynamic arrays. Calculating a dynamic array function will result in a SPILL error if it attempts to overlay a non-null cell which was not part of its previous calculation. Furthermore, it will set to null all cells which were part of its previous calculation but which are not part of the current one (i.e. one or both of the dimensions of the calculation is smaller than it had been); this should also apply for spills (whose result is reduced to 1*1).
Excel will stop you from changing the value in any cell in a dynamic array except the formula cell itself. I have not built this particular aspect into PhpSpreadsheet.
As usual, MS has taken some unusual steps here. If the result of a dynamic array calculation is #SPILL!, it will nevertheless be written to the xml as #VALUE!. It recognizes this situation by adding a new `vm` attribute to the cell, and expanding metadata.xml to recognize this.
A new optional parameter `$reduceArrays` is added to `toArray` and related functions. This will reduce a dynamic array to its first cell, which seems more useful than outputing it as an array (default).
The CONCATENATE function has been treated as equivalent to CONCAT. This is not how it is treated in Excel; it is closer to (and probably identical to) the ampersand concatenate operator. The difference manifests itself when any of the arguments is an array (typically a cell range). Code is added to support this difference.
Support for array results is added to Csv Writer, Html Writer, and Ods Reader and Writer. I have not figured out how to get it to work with Xls.
See issue #4062. When calculating an array formula, populate all the cells associated with the result. This is almost the same as Excel's behavior. As yet, there is no attempt to create a #SPILL error, so cells may be inappropriately overwritten. Also, if the array size shrinks (e.g. there are fewer unique values than before), no attempt is made to unpopulate the cells which were in range but are now outside the new dimensions. Spill and unpopulation are somewhat related, and will probably be handled at the same time, but their time has not yet come.
UNIQUE, at least for rows, was treating all cell (calculated) values as strings. This is not the same behavior as Excel, which will preserve datatypes, and treat int 3 and string 3 as unique values. Excel will, however, treat int 3 and float 3.0 as non-unique. Within UNIQUE, private function uniqueByRow is changed to try to preserve the the datatype when executing (it will probably treat 3.0 as int - I don't know how I can, or even if I should attempt to, do better - but no int nor float should be treated as a string).
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.
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.
I check from time to time. There are a number of problems now, mostly due to the elimination of Php 7.4 and replacement of doc-block typing with explicit Php typing.
- Bitwise functions were particularly affected by PR #3718 and PR #3793.
- Chart/Axis and Writer/Xlsx were amusingly affected by PR #3836, which added a scaling option which included an array indexed by the known allowable factors, one of which is 1 trillion, which cannot be represented as an integer on a 32-bit system. Issue3833Test, introduced by the same PR (and not suffering any errors) was expanded to test this value.
- Some minor changes to Reader/Xls and Shared/OLE/PPS to accommodate hex values which are negative in 32-bit but which Php-32 may wind up casting to large floating point numbers; it is not clear to me why these hadn't shown up as problems previously. Possibly this is the result of changes in the most recent Php versions.
- BitAndTest, BitOrTest, BitXorTest and Shared/DateTest were adversely affected by PR #3859 when arguments and/or expected results too large for a 32-bit integer were supplied.
- ImExpTest required a slightly reduced precision for 32-bit. No idea why this hadn't shown up earlier.
Change "mixed" declarations to more accurate types in test members; in particular, change those that would be flagged if we were to run Phpstan at level 9 (we currently run level 8). I may or may not follow up with source code (over 700 level-9 problems remain for src), but, as with strict typing, there is no reason to avoid the effort for test members.
It was necessary to update some doc blocks in src to accommodate this change. However, no executable code is touched.
This was suggested by the investigation of issue #3811. No fix is necessary for the issue. However, two possible code solutions (Php setlocale, which comes with certain design flaws, and StringHelper set(Decimal/Thousands)Separator were suggested, and neither is adequately tested. This PR adds such tests.
Unusually, getting StringHelper Decimal Separator, Thousands Separator, and Currency Code can result in a change to those properties. So, the existing design in several tests where those properties are captured in Setup and restored in Teardown do not work quite as designed. Instead, the ability to set those properties to their default value (null) is added, and the tests re-done to restore the default in Teardown.
The two methods yield the same results when parsing input. However, they diverge when examining output fields through `getFormattedValue`. Such output is currently correct (usually) when using setlocale, but not when using StringHelper. The former works through the 'trick' of using `sprintf(%f)`, which generates a locale-aware string. However, using non-locale-aware `sprintf(%F)` followed by `str_replace` will produce the correct result for both setlocale and StringHelper. One place in the code uses a cast to string, which is incorrect for both methods. Following that up with the same str_replace makes it correct for both. These changes permit, but do not require, the user to avoid setlocale altogether.
It remains an open question whether Settings/Calculation::setLocale should set DecimalSeparator, CurrencySeparator, and CurrencyCode. That makes logical sense, but it would be a breaking change, and having to explicitly set those values when using setLocale does not seem especially burdensome. For now, such a change will not be made.
Fix#3782. A signature of the ReflectionMethod constructor will be deprecated, and PhpSpreadsheet runs afoul of that change in one place. Php8.4 is not yet available in any form, and I am reluctant to make this change until we see that the issue is real (and that this PR fixes it), so am leaving this PR in draft status till then.
I note that one Excel function `PI` is implemented not as a class method in PhpSpreadsheet, but rather as a call to the native Php function `pi`. The ReflectionMethod call is subject to a TypeError in the changed code, but that is already the case. We haven't seen a TypeError because (a) it will arise only if the caller supplies an argument to the function (which must be called with zero arguments), and (b) there are no test cases for that function. The code is slightly cleaned up, and test cases are now added. This is not an important enough problem to rush this PR - the existing code (and the changed code), rather than failing with TypeError, will fail with a CalculationException (wrong number of arguments) before it gets to the TypeError; that is the correct behavior.
Because they tend to clutter our code a lot and unfortunately, there are
lots of false positives. Instead, it would probably be better to deal
with false positives out of band, via the Scrutinizer web UI.
oleibman
Owen Leibman
Adrien Crivelli