Do array results spill to other cells?

[infojunkie]

I may be doing something wrong, so I'm wondering whether the result of array functions (e.g. SORT) spills to subsequent cells.

Here's an example:

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

$spreadsheet = IOFactory::load("spill.xlsx");
$calculation = $spreadsheet->getCalculationEngine();
$calculation->getDebugLog()->setWriteDebugLog(true);
$calculation->getDebugLog()->setEchoDebugLog(true);
$calculation->clearCalculationCache();
$cell = $spreadsheet->getActiveSheet()->getCell('A1');
$cell->setValue(100);
$cell = $spreadsheet->getActiveSheet()->getCell('B1');
$result = $calculation->calculate($cell);
var_dump($result);
for ($i=1; $i<=6; $i++) {
  $coord = "B$i";
  $cell = $spreadsheet->getActiveSheet()->getCell($coord);
  echo $coord . ' : ' . $cell->getValue() . ' => ' . $cell->getCalculatedValue() . "\n";
}

In this file, a list of values in column A is sorted in B1, and spills in the B column.

Then, in the code, I modify A1 from 10 to 100, which I expect should change the B values.

But that doesn't happen, or I don't know how to make it happen.

When I run the code, I get the following:

Testing cache value for cell Sheet1!B1
Evaluating formula for cell Sheet1!B1
Formula for cell Sheet1!B1 is SORT(A1:A6)
Sheet1!B1 => Evaluating Cell A1 in worksheet Sheet1
Sheet1!B1 => Evaluation Result for cell 'Sheet1'!A1 in worksheet Sheet1 is an integer number with a value of 100
Sheet1!B1 => Evaluating Cell A6 in worksheet Sheet1
Sheet1!B1 => Evaluation Result for cell 'Sheet1'!A6 in worksheet Sheet1 is a floating point number with a value of 5
Sheet1!B1 => Evaluating Range "'Sheet1'!A1" : "'Sheet1'!A6"
Sheet1!B1 => Evaluation Result is a matrix with a value of { 100; 35345; 235235; 734; 85; 5 }
Sheet1!B1 => Evaluating Function SORT() with 1 argument
Sheet1!B1 => Evaluating SORT ( { 100; 35345; 235235; 734; 85; 5 } )
Sheet1!B1 => Evaluation Result for SORT() function call is a matrix with a value of { 5; 85; 100; 734; 35345; 235235 }
array(6) {
  [0]=>
  array(1) {
    [0]=>
    float(5)
  }
  [1]=>
  array(1) {
    [0]=>
    float(85)
  }
  [2]=>
  array(1) {
    [0]=>
    int(100)
  }
  [3]=>
  array(1) {
    [0]=>
    float(734)
  }
  [4]=>
  array(1) {
    [0]=>
    float(35345)
  }
  [5]=>
  array(1) {
    [0]=>
    float(235235)
  }
}
Testing cache value for cell Sheet1!B1
Retrieving value for cell Sheet1!B1 from cache
B1 : =SORT(A1:A6) => 5
B2 : 10 => 10
B3 : 85 => 85
B4 : 734 => 734
B5 : 35345 => 35345
B6 : 235235 => 235235

So whereas the SORT calculation result at B1 is correct, it seems to not spill to the other cells in the column. Is this expected?

[oleibman]

Yes, it is expected, we do not yet have good support for array results. I have PR #3962 in draft status; you might want to test against it, in conjunction with setting ArrayReturnType in your code. This gives the full array result when you execute getCalculatedValue against B1; it does not, however, show any changes when executing getCalculatedValue against the other cells, at least not yet (that's one of the reasons why it's still in draft status). However, if you were to save the spreadsheet, you would see the expected values in the other cells when you open it.

[infojunkie]

Thanks, that explains everything. I will follow that PR.

[oleibman]

I have just added code to populate the other cells to that PR. As the commit notes explain, there is still work to do in that area, but it's an important step in the right direction.

[infojunkie]

I confirm that running your branch yields the following, expected result:

Testing cache value for cell Sheet1!B1
Retrieving value for cell Sheet1!B1 from cache
PHP Warning:  Array to string conversion in /home/kratib/src/infojunkie/sheetgraph/spill.php on line 22
B1 : =SORT(A1:A6) => Array
B2 : 85 => 85
B3 : 100 => 100
B4 : 734 => 734
B5 : 35345 => 35345
B6 : 235235 => 235235

The PHP warning is caused by B1 returning an array calculated value instead of a scalar.

[oleibman]

Release 3.3.0 implements dynamic array functionality.