Move testVlookupWholeColumnRange and providerVlookupWholeColumnRange
from ParseFormulaTest to the correct location VLookupTest, which
extends AllSetupTeardown. Adapted to use $this->getSpreadsheet()
instead of managing the Spreadsheet lifecycle manually.
This PR will be merged at the end of this month. As the README says, we maintain support for Php Versions for six months beyond their end of life. That time has now arrived for 8.1.
Too many errors when Dependabot tried. These seem mostly to involve `implode`, whose use is often embedded in other function calls. For that reason, these changes include a higher proportion than usual of `// @phpstan-ignore-line`.
Fix#1453, which went stale in 2000, which I unstaled in 2024, and which I finally got to. The code, and several unit tests, was substantially generated by AI, my first foray into that frontier.
Trait `ArrayEnabled` is not able to handle this function because it has too many array parameters. I would have liked to update it to handle this, but that seemed too difficult. I have let the code do its own array handling; it looks a little kludgey but seems to do the job. I may return to this at some point.
Current tests use only direct calls. Using them in spreadsheet context would be better.
There were some minor bugs. Excel treats an argument consisting of a single-cell as if it were a range; PhpSpreadsheet, which currently returns the content of the single cell, is changed to match that behavior, which is important if one of the other arguments causes a VALUE error.
Excel can do a column sort in SORTBY, despite the fact that Microsoft doesn't even mention that possibility in its documentation. I found documentation of that possibility on a 3rd-part site https://exceljet.net/functions/sortby-function, confirmed it in Excel, and took my best guess as to how to implement it. The existing test member for SORTBY included one such test, but it succeeds mostly by accident. The new member has a more reliable test.
They aren't quite interchangeable. Both are used in the test suite, with no indication of why one or the other. I think we'd be best off being consistent. Based on the names, I think `_calculateFormulaValue` was intended as a private, or at least internal, method, so favor `calculateFormula`. I do not intend to rename or re-categorize `_calculateFormulaValue`, just remove its usage when it isn't clearly warranted.
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.
The original issue leading to this PR was fixed by correctly treating a token in Calculator as a function rather than a defined name. However, it *should* have worked even when treating it as a defined name. There was a problem because Calculator was raising an exception for a missing defined name rather than returning `#NAME?`. It is now changed to return the error.
That change initially had some adverse affects for functions ROW, ROWS, COLUMN, and COLUMNS. Those are fixed to handle the change correctly. As a bonus, a test for each which had been commented out, because it didn't work, is now uncommented and works correctly. One test for ISFORMULA and one for ISREF were also changed - the old expected result did not reflect Excel's behavior and the new one does.
Sheet title and Defined Name matching used `strtoupper` to achieve case-insensitive compares. This handles only ASCII characters. They are changed to use a conversion routine which handles non-ASCII UTF-8 character sets.
Fix#4558. Fix#4530. 4558 deals with a problem with COUNTA. Excel does not count empty cells, but it does count literal nulls (usually empty parameters) in the argument list. PhpSpreadsheet has till now not created empty cells during calculation, and so winds up treating them as literals. It is changed to create the cell when appropriate.
4530 has a similar problem. When INDIRECT winds up evaluating an uninitialized cell, it treats it as a null literal, leading to incorrect results. It is now changed to create the missing cell.
A number of functions, almost all of them Financial, have a related problem. They test for a null literal when initializing some of their parameters, but they should be testing for null literal or cell containing null. They are changed to do the right thing.
SUMIF and related functions are slightly affected by this change, and are changed so that they are no longer affected.
New tests have been added. Only one existing test had to change. MergeBehaviorTest for `[12, '=5+1', '=A1/A2']`, where cell A2 was uninitialized formerly returned one row, but now returns a second all-null row because A2, by virtue of being used in a calculation, now winds up defined. I do not consider this a significant difference.
TOCOL and TOROW were introduced to Excel in 2024, and will now be supported by PhpSpreadsheet. The documentation says that, under certain circumstances, "blanks" will be ignored. This seems demonstrably wrong. In the right circumstance, Excel will ignore nulls, not blanks. Further, when it decides to not ignore the nulls, it changes them to 0, which also seems insufficiently documented. PhpSpreadsheet will behave as Excel does.
I discovered some minor problems and some missing test conditions for the TRANSPOSE function while testing these. Those are now fixed.
Fix#4485. VSTACK and HSTACK were introduced to Excel in 2024, and will now be supported by PhpSpreadsheet. Special thanks to @SlowFox71, who posted some code to implement VSTACK in the linked issue. I didn't have to change much between that version and the one in this ticket.
Excel has at least one idiosyncrasy with these functions. If you try to stack 2 tables with, say, `=VSTACK(TABLE1, TABLE2)`, Excel will silently change it to `=VSTACK(TABLE1[], TABLE2[])` and all will be well. This would be difficult for PhpSpreadsheet to do. The problem is that, when Excel reads the formula without the square brackets, it calculates it as a `#NAME?` error. This is baffling, and all the more so because you can "correct" it by editing the formula *without changing anything*, hit enter, and the formula will magically work again. So, if you plan to stack tables using PhpSpreadsheet, you're probably best off using the square brackets.
Its first parameter is typed as `array`. Like all Excel spreadsheet functions, it should be `mixed` to avoid TypeErrors. If an argument other than an array is supplied, Excel will return VALUE, and now PhpSpreadsheet will do so as well. This had not been unit-tested. In fact, all the unit tests take place outside of spreadsheet context. New tests in spreadsheet context are added.
When the Dynamic Array PR #3962 was introduced, it left the default as Return Array as Value. At some point, the default should be changed to Return Array as Array. This would, of course, be a breaking change, one which will not be part of Release 4. However, it will possibly be part of Release 5.
Rather than relying on the default setting, this PR explicitly sets Return Array as Value when tests require that setting. This will make it easier to identify potential breaks when the default is changed. The entire test suite will now succeed with either setting as default.
In making these changes, a few minor problems were discovered with how Array as Array is handled. These are fixed with this PR.
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.
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.
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.
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).
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.
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.
While we might never be able to have 100% of our code strict, we can at
the very least do it for all of our tests. This ensures that our tests
are using our API with the types as intended by the test author, and not
silently be cast to what our API requires.
And quite a bit more manual changes. The idea is that typing of our
tests can be a bit more loose, so we assume PHPDoc is mostly correct. If
that happens to be wrong, it should be caught by the tests themselves.
* Correct Re-computation of Relative Addresses in Defined Names
Fix#3661. Insertion or deletion of rows or columns can cause changes to the ranges for Defined Names. In fact, only the absolute parts of such ranges should be adjusted, while the relative parts should be left alone. Otherwise, as the original issue documents, the adjustment to the relative portion winds up being double-counted when the Defined Name is referenced in a formula. The major part of this change is to ReferenceHelper and CellReferenceHelper to not adjust relative addresses for Defined Names. An additional small change is needed in the Calculation engine to `recursiveCalculationCell` when a Defined Formula is being calculated.
In a sense, this is a breaking change, but for an obscure use case which (a) was wrong, and (b) is unlikely to be of importance. Some of the tests in ReferenceHelperTest were wrong and are now corrected, with the results being cross-checked against Excel.
When a Defined Name using relative addressing is defined in Excel, the result is treated as relative to the active cell on the sheet in which the name is defined. PhpSpreadsheet treats it as relative to cell A1. I think that is a reasonable treatment, and will not change its behavior to match Excel's - that would definitely be a breaking change of some consequence.
An interesting use of relative address in a defined name is demonstrated at https://excelguru.ca/always-refer-to-the-cell-above/. Note that the steps there involve setting the selected cell to A2 before defining the name. When that spreadsheet is stored, the actual definition of the range is `A1048576`. Likewise, adding a defined name for the cell to the left would be stored as `XFD1`. This seems a little fragile, but Ods, which I believe does not have the same row and column limits as Excel, certainly treats these values the same as Excel. This particular construction is formally unit-tested. Note, however, that although using these Defined Names as a formula on their own works just fine, a construction like `=SUM(A1:CellAbove)`, as suggested in the article, seems to put PhpSpreadsheet calculation engine in a loop. In the likely event that I can't solve that before I merge this change, I will open a new issue to that effect when I do merge it. Note that this can be handled without defined names as `=SUM(A$1:INDIRECT(ADDRESS(ROW()-1,COLUMN())))`. PhpSpreadsheet will handle this as a cell formula, but not yet as a Named Formula.
The tests show a breakdown evaluating `=ProductTotal` (product of 2 formulas using defined names with relative addresses) on the sheet on which it is defined, but it works from a different sheet. The usual debugging techniques show me why this is happening, but I can't see how to overcome it. As above, if I can't solve it before I merge, I will open a new issue.
For those situations where I intend to open a new issue, tests are added but are marked Incomplete. Because of those, I will leave this PR in draft status for 2 weeks before moving forward with it.
* Fix productTotal Problem
Need to restore current cell after evaluating defined name.
Fix#3624. Use the same logic as elsewhere in the same module to invoke `imagesavealpha` when appropriate. (I confess that I do not understand the use case where you would not use imagesavealpha.) The fix was easy; writing a test was not. Google to the rescue.
* Cleanup for Phpstan, PHP-CS-Fixer Upgrades
Dependabot wanted to upgrade, but changes resulted in new "errors". Fix them.
* Scrutinizer
It says value must be numeric; Phpstan says it might not be. Try to reconcile them.
Fix#3561. PhpSpreadsheet VLOOKUP/HLOOKUP allow the specification of index_number (third parameter) as an array when lookup_value (first parameter) is supplied as an array. Change to permit index_number as an array even when lookup_value is not an array, as Excel does.
Successor to PR #3523. There are 494 single-line changes (`public function provider` to `public static function provider`) in this PR. None of these were made manually; they were all created with the following script (adapted from
https://stackoverflow.com/questions/25909820/how-to-recursively-iterate-through-files-in-php):
```php
$dir = 'C:/git/unit10prep2/tests/PhpSpreadsheetTests';
$it = new RecursiveDirectoryIterator($dir);
// Loop through files
foreach(new RecursiveIteratorIterator($it) as $file) {
if ($file->getExtension() === 'php') {
$contents = file_get_contents($file);
$new = preg_replace('/public function (\\w*)([Pp])rovider/', 'public static function $1$2rovider', $contents);
if ($new !== $contents) {
echo "changing $file\n";
file_put_contents($file, $new);
}
}
}
```
After this PR, there will be one more, with a small number of test changes, and enabling PhpUnit 10 for Php 8.1+.
* PhpUnit 10 Compatibility Part 1
This is not a change to move to PhpUnit 10. There is no compelling reason to do so at this time, although it is bound to happen eventually. There are a staggering number of problems (somewhere around 3,000) with the current test suite under PhpUnit 10; this is an attempt to get ahead of the curve by addressing them now.
Method `setOutputCallback` has gone away. This affects only Helper/SampleTest. It appears that `ob_start` and its allies provide an effective equivalent. FWIW, the absence of `setOutputCallback` is a good indication of whether or not PhpUnit 10 is in use, and I will use that fact in a few tests.
Class `ComplexAssert` with no constructor, and always used with `new ComplexAssert()`, extends `TestCase`. Apparently, the constructor for TestCase requires an argument, and PhpUnit 10 complains about not supplying one. Adding an empty constructor to ComplexAssert avoids this problem.
There are two very minor source changes, to Calculation/Calculation and Reader/Xlsx, where problems were exposed with PhpUnit 10 that had not been previously been exposed. AFAIK, these are the only source changes required; the rest of the changes are to test members.
The bulk of the problems are because PhpUnit 10 insists that provider methods be static. Most of those can be changed by a script without any further action; those changes will constitute the Part 2 counterpart of this PR. In this PR you will find the exceptional cases that can't be automated for one reason or another. The tests for Database functions have mild complications that are easily handled. Most of the other provider changes in this PR are because the method names didn't follow an established pattern ('provider' isn't part of the method name); those are also easily handled manually. Modifying the following tests provided significant challenges:
- Writer/Xls/WorkbookTest testAddColor
- Worksheet/Table/TableTest testSetRangeValidRange
The handling of warning messages issued by the code differs in PhpUnit 10. According to the change log, "This means that using PHP functionality which triggers E_DEPRECATED, E_NOTICE, E_STRICT, or E_WARNING or calling code which triggers E_USER_DEPRECATED, E_USER_NOTICE, or E_USER_WARNING can no longer hide a bug in your code." To me, the effect of that change seems to be exactly the opposite - such messages were available to the test with PhpUnit 9 (so we could test for them), and are no longer available (so we can't). I haven't even succeeded with a custom error message handler as part of the script. I will continue to investigate, but, for now, will skip some tests under PhpUnit 10 for the following:
- Shared/OleTest testChainedWriteMode and testChainedBadPath
- Reader/Html/HtmlLoadStringTest testLoadInvalidString
- Reader/Html/HtmlTest testBadHtml
* Scrutinize, and Parent Construct
Parent construct suggested by @MarkBaker.
* Redo Tests Dependent on Warning Messages
Warning (and other) messages are handled differently in PhpUnit 10 than in earlier versions.
* Coerce Bool to Int for Unary Operation on Arrays
Fix#3389. It seems some functionality was left behind when JAMA was eliminated (PR #3260). In particular, it is apparently a known trick to use double negation on boolean values as arguments to functions like SUMPRODUCT.
* Fix 3396
Treat booleans in arrays as int for mathematical operators as well.
* Edge Case
When array operand was neither numeric nor boolean, PhpSpreadsheet had always been evaluating the operand as #NUM!. It will now propagate an error string like #DIV/0!, and treat non-error strings as #VALUE!, consistent with Excel.
DIReports
oleibman
Adrien Crivelli