Array instead of scalar result returned

[SlowFox71]

It took me a while to break down understand what is happening here and I hope I got it correctly. If an Excel contains a formula with certain array functions, e.g. MIN(FILTER(HSTACK(A2), HSTACK(A2)<>0,0)) the result will look like

<f t="array" ref="A1">MIN(_xlfn._xlws.FILTER(_xlfn.HSTACK(A2), _xlfn.HSTACK(A2)<>0,0))</f>

I don't understand the idea of the array attribute here since MIN returns a scalar. But it is there and this is a problem, because of this piece of code in Cell/Cell.php

 if (
     !is_array($result)
     && $calculation->getInstanceArrayReturnType() === Calculation::RETURN_ARRAY_AS_ARRAY
     && $oldAttributesT === 'array'
     && ($oldAttributesRef === $coordinate || $oldAttributesRef === "$coordinate:$coordinate")
 ) {
     $result = [$result];
 }

At least in this case the conversion from scalar to array is wrong. The excel file used to recreate the XML is attached.

test.xlsx

[oleibman]

I think we are in agreement about the symptom here, but I need to confirm. The attribute t="array" is added by Excel, not by PhpSpreadsheet, correct?

I agree that this, like lots of other things in Excel's internal workings, seems strange. However, you haven't explained why this is a problem for you. Please elaborate.

PhpSpreadsheet's treatment is necessary for a situation like the following. Put 1 in A1, A2, A3, A4; and put =UNIQUE(A1:A4) in C1. Here, the result, as yours, is 1*1 array. However, change A2 to 2, and the result in C1 is now expanded. I don't know that I will be able to distinguish a MIN function (with its scalar result) from a UNIQUE function (with its array result) if Excel tells me that both results are arrays.

[SlowFox71]

Yes, the attribute is added by Excel.

What happened: I process files which should contain scalars only - in former times CSV files only. Some clever people discovered that they are allowed to use functions (as I access the cells with ->getCalculatedValue()) so they did and files got increasingly complex. There was a strange error these days giving me "empty" cells where Excel did show a value. After a lot of tracing I finally discovered that array-attribute to be the problem.

I can of course use brute-force to revert that array again. But - as PhpSpreadsheet as well as my own backend are designed to work for generic cases, I think that ->getCalculatedValue() should return scalars as such. I understand that it is somewhat difficult to distinguish correctly (and I neither understand why excel sets that attribute, not how it distinguishes between scalars and arrays nor why this flag is needed at all if it can distinguish anyway).

[SlowFox71]

Perhaps a component resultType in FunctionArray would be needed? Though this is certainly not a short term fix.

[oleibman]

Sorry, I think you are going to have to take up this problem with Microsoft. I have tried some workarounds =@MIN(...), =INDEX(MIN(...)),1,1), but the array attribute continues to bleed through whenever Excel thinks it should.

There is no point in a resultType attribute. MIN already will not return an array result. Aha, you think, can't I just check for =MIN( at the beginning of the formula? Aside from having to account for whitespace, I have to account for constructions like:

=SQRT(B17)+MIN(FILTER(whatever)) // should probably be a scalar
=UNIQUE(A1:A4) + MIN(FILTER(whatever)) // should probably be an array

And these are, of course, child's play compared to the complex formulas that I regularly see in other issues. I shudder to think what needs to happen when an IF function is added to the mix.

One workaround that might succeed for you is to load the spreadsheet and then save it with

$writer->setPreCalculateFormulas(false);

Of course, a brute-force function to eliminate 1-cell (or all) array declarations is probably not that difficult. If you want to try submitting a PR to accomplish that, I can help.

[SlowFox71]

Personally I have my workaround for now: If a result is a 1-component array I transform it back to scalar. This might fail at some point in the future, but for now and for my purposes I don't see any legit use of 1-component arrays.

But: I had to patch Shared/Date.php as well, because Date::isDateTime() uses is_numeric($cell->getCalculatedValue()) which of course fails with array results. Not so nice, but I can still live with that.

As for resultType: No, I did not mean to simply checking the left-most function, but rather keeping track of types in Calculation::processTokenStack() throughout the calculation. This would be quite a thing to implement, and if I think about it, a callable would be required instead of a constant (as in UNIQUE(A1:A4) + MIN(FILTER(whatever)) the types of the +-operands define the type of the final result). But as far as I can see for the moment, there is no other way to behave like Excel behaves.

[oleibman]

I'm sorry I can't help you with your main problem. But I can certainly help with isDateTime. Look for a PR in a day or two.

[oleibman]

If you can test with PR #4562, please do so, and let me know if it meets your needs (for Shared/Date).

[SlowFox71]

Absolutely, yes - thanks.

[oleibman]

4562 is now merged. I will close this issue some time in the next few days, but will leave it open to give you a chance to add any closing comments first.

[SlowFox71]

I think that's it for the time being - thanks for explaining the details and for the fix of isDateTime. As Microsoft most likely won't care about that, the difference will remain, but now at least I am aware of it.