PHPOffice
Formula Parsing Error Using Union Arguments
Reopening https://github.com/PHPOffice/PhpSpreadsheet/issues/503 as requested. The attachment contains a formula using a union argument which is working fine at least in my Excel365 (but has been working with previous versions of Excel as well; union arguments can be used wherever ranges are allowed).
Interesting. Not sure what I was doing wrong. Thank you for opening the issue anew. Not sure when I might have an idea on how to solve it, but it is definitely an (obscure) issue.
@slowfox71 Here's a good laugh for you. I actually solved this problem while working on PR #4596. But, as I refined that PR, I found that some of the code I was introducing was no longer being executed in the test suite, so I commented it out. Search for "The following code may be a better choice" in Calculation/Calculation. Your example actually reaches the critical point, and, if I comment out the return statement there and uncomment the new code, I think it returns the correct result. Expect a new PR soon.
@SlowFox71 Please test with PR #4657 if possible, with a view to adding more test cases. I won't be doing much work on it over the next couple of weeks, and it needs more tests.
Thanks for taking care of that, and... well, shit happens :-)
It took me a while to have a look at it (the original cause for the issue has been circumvented), but when I did so now, I faced some errors. In Calculation/Calculation.php you have the line
$cellUnion = array_merge($operand1, $operand2);
However, $operand1 and/or $operand2 are scalar, if single cells are used (at least that's what I got in that simple test case, I am not at all familiar with the code). So I suggest to change it into something like:
$cellUnion = array();
if (is_array($operand1)) $cellUnion = array_merge($cellUnion, $operand1);
else $cellUnion[] = $operand1;
if (is_array($operand2)) $cellUnion = array_merge($cellUnion, $operand2);
else $cellUnion[] = $operand2;
This works at least for me right here and now. I have yet to create some more and more complex examples to fully understand what is done.
Please let me know a test case where things fail without your change and succeed with your change.
Back to draft status. Example from https://github.com/PHPOffice/PhpSpreadsheet/discussions/1950 does not work.
In that example, we are trying to evaluate SUM((A1,A2),(B1,B2)). The parser thinks SUM only has 1 argument (A1,A2) and that the B1,B2 part is not part of the formula. Fixing that may be beyond my pay grade :-(
Sorry, I am extremely late to any party at the moment. I used the attached file and got array_merge(): Argument #1 must be of type array, null given when trying to get the calculated value of A5 (which should be "#NV", yes, but still).
But it seems that my comment is obsolete by now anyway. Problem is I don't really understand the code (yet?). I have created a couple of LR parsers a decade ago an might be able to write a yacc-ruleset for it, but this is a completely different approach.
[wrong attachment deleted]
I am nowhere close to a solution.
Thank you for the new file, In it, A5 is hidden, and a string, not a formula. Might you have been seeing the problem on cell H13? Not that I have any idea what to do about it, just want to keep the commentary accurate.
Ups, wrong file - I reduced it, but accidentally saved it in a different folder. This should be the correct one.
If I find some time (but well...) I'll try to get into that logic.