Excel Dynamic Arrays (Avoid Adding At-Signs to Formulas)

[oleibman]

This has come up a number of times, most recently with issue #3901, and also issue #3659, and issue #1834. It will certainly come up more often in days to come. Excel is changing formulas which PhpSpreadsheet has output as =UNIQUE(A1:A19); Excel is processing the formula as it were =@UNIQUE(A1:A19). This behavior is explained, in part, by https://github.com/PHPOffice/PhpSpreadsheet/pull/3659#issuecomment-1663040464. It is doing so in order to ensure that the function returns only a single value rather than an array of values, in case the spreadsheet is being processed (or possibly was created) by a less current version of Excel which cannot handle the array result.

PhpSpreadsheet follows Excel to a certain extent; it defaults to returning a single calculated value when an array would be returned. Further, its support for outputting an array even when that default is overridden is incomplete. I am not prepared to do everything that Excel does for the array functions (details below), but this PR is a start in that direction. If the default is changed via:

use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
Calculation::setArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);

When that is done, getCalculatedValue will return an array (no code change necessary). However, Writer/Xlsx will now be updated to look at that value, and if an array is returned in that circumstance, will indicate in the Xml that the result is an array and will include a reference to the bounds of the array. This gets us close, although not completely there, to what Excel does, and may be good enough for now. Excel will still mess with the formula, but now it will treat it as {=UNIQUE(A1:A19)}. This means that the spreadsheet will now look correct; there will be superficial differences, but all cells will have the expected value.

Technically, the major difference between what PhpSpreadsheet will output now, and what Excel does on its own, is that Excel supplies values in the xml for all the cells in the range. That would be difficult for PhpSpreadsheet to do; that could be a project for another day. Excel will treat the output from PhpSpreadsheet as "Array Formulas" (a.k.a. CSE (control shift enter) formulas because you need to use that combination of keys to manually enter them in older versions of Excel). Current versions of Excel will instead use "Dynamic Array Formulas". Dynamic Array Formulas can be changed by the user; Array Formulas need to be deleted and re-entered if you want to change them. I don't know what else might have to change to get Excel to use the latter for PhpSpreadsheet formulas, and I will probably not even try to look now, saving it for a future date.

Unit testing of this change uncovered a bug in Calculation::calculateCellValue. That routine saves off ArrayReturnType, and may change it, and is supposed to restore it. But it does not do the restore if the calculation throws an exception. It is changed to do so.

This is:

• [ ] a bugfix
• [x] a new feature
• [ ] refactoring
• [ ] additional unit tests

Checklist:

• [x] Changes are covered by unit tests
  • [x] Changes are covered by existing unit tests
  • [x] New unit tests have been added
• [x] Code style is respected
• [x] Commit message explains why the change is made (see https://github.com/erlang/otp/wiki/Writing-good-commit-messages)
• [ ] CHANGELOG.md contains a short summary of the change and a link to the pull request if applicable
• [x] Documentation is updated as necessary

Why this change is needed?

Provide an explanation of why this change is needed, with links to any Issues (if appropriate). If this is a bugfix or a new feature, and there are no existing Issues, then please also create an issue that will make it easier to track progress with this PR.

[MarkBaker]

There was a lot of code that I'd implemented in https://github.com/PHPOffice/PhpSpreadsheet/pull/2787 that was designed to provide this full support for array formulae, including the handling of ranged returns and support for dynamic ranging with all the relevant tests and some of the new dynamic array functions. Unfortunately it got too wrapped with other issues and resolutions making it difficult to extract just those parts of the code, and did require a change to the Read Filters that made it a BC break.

[oleibman]

@MarkBaker Thanks, I will look over your code. We took very different approaches. I'll see if I can can reconcile some of the differences. Did you ever figure what was needed to make Excel open the PhpSpreadsheet-generated file so that the array functions are treated as dynamic rather than CSE?

[jr212]

The solution above doesn't work for me. My test files(php and a json for the data) can be found on my website https://janr.be/excel.zip. I leave it there for ±1month

[oleibman]

@jr212 Thank you for the sample code. Please try again against this PR, which I have changed based on your example. That demonstrated at least 2 problems with the existing code. One, relatively easily handled, was that, since your code used auto-sized columns, the PhpSpreadsheet code wound up formatting the array results, and the formatting code was not expecting array inputs. I will note the array calculation supplies a value only to the first cell of the array, so the auto-sizing is ineffective anyhow. I will think about this some more; perhaps it is something we can live with, or perhaps the code can do better.

The second problem is a little more troublesome. A formula like =sheet!cell winds up returning a 1-element array (row->column->value). With "normal" array handling, this winds up reduced to value. But, with RETURN_ARRAY_AS_ARRAY, it remains as an array. I have added code to recognize this situation and return just the value part, but I'm not entirely convinced that this change won't fail due to false positives or negatives.

Finally, even though the Excel spreadsheet generated by your code is now correct (I think - please verify), I don't think that getCalculatedValue for cell A2 on sheet 'Gesorteerd verlorenpunten' agrees with what Excel shows.

All in all, your example is very useful. But it may cause me to leave this change in draft status for quite a bit longer than I had planned.

[jr212]

I did the test but nothing changed ☹ I composed twice with

• composer require --prefer-source phpoffice/phpspreadsheet
• composer require phpoffice/phpspreadsheet:dev-master

If that’s wrong remember that I know about nothing of composer.

I did redownloaded the PhpSpreadsheet-2.0-Development.zip code

Jan

[oleibman]

@jr212 It is lucky then that I didn't realize your initial problem was with Composer, otherwise I might not have noticed the code problems. I am also not expert in Composer, but I think the following should work. Assume that you have a main directory called "git" (change to whatever is appropriate). Go to that directory and issue the following command:

git clone -b atsign --single-branch https://github.com/oleibman/PhpSpreadsheet.git atsign3

This will create a new directory "git/atsign3". Change to that directory and run:

composer install --prefer-source

It would not surprise me in the least if there is a simpler way to do this, but this should work.

[oleibman]

Merged master to eliminate a no-longer-valid dependency in composer.lock, not as prep work before installing.

[jr212]

I've finally succeeded to build everything 😊 Never worked with git before but no problem there. Test is now complete, with success and in production. Remains only the width of columns. (I can live with that) You wrote before you look at the header of the column (first cell) but people’s names are bigger than only “Name” or in nl-be “Naam”

Questions now • What files/folder are needed for all functions? I’ve placed now the vendor and src folder but that is 6.98 GiB. The file PhpSpreadsheet_2.0\vendor\phpstan\phpstan.git\objects\pack\pack-35b3e82d34e6138656d21f8ef47f303a6a20f372.pack alone is 4.8 Gib. • Do you still need my test code • Is this normal behaviour? Above when opening the file all is 0 except the first cell. After click "allow edit" (or something like that) all is fine(recalculated).

My complements for all the hard work in this project and the help to me.

Jan

[oleibman]

@jr212 I believe you can get a much smaller directory by using the following install command rather than the one I initially suggested:

composer install --no-dev

Please bear in mind that the code you will be using is some way from being "production ready". I will be glad to continue to work with you, but this code may never make it into production, and, even if it does, I don't know how soon that might happen.

On Windows systems, when a spreadsheet is downloaded from a browser, the behavior you described is normal.

I don't need you to maintain the zip file you uploaded any longer. However, I will eventually need to add a test case based on what it showed me. Do you have a problem with my basing that test case on the data and code you supplied?

[jr212]

No problem its anonymised.

[jr212]

There are 2 new problems. Still related I think therefore I use the same ticket. Code and example to find https://janr.be/excel/excel.zip. free to use.

1. When running the code the file is created but I got an error from Excel.(sorry can't translate) but the file seems fine.
2. In the function doTS there are 2 functions. the one with CHOOSECOLS doesn't work. Not implemented yet?

Op ma 22 apr 2024 om 09:05 schreef @.***>:

No problem its anonymised.

-- Met vriendelijke groeten Jan @.***

[oleibman]

@jr212 Your spreadsheet is no longer available at the address you supplied, probably because I took too long to download it. If you have something you want me to look at, please make it available again.

[jr212]

Vind it on https://janr.be/excel.zip

[oleibman]

I think I have finally cracked how to prevent Excel from changing the formula to CSE format. It's hideous.

• Spreadsheet includes a metadata.xml file which includes a dynamicArrayProperties tag.
• File workbook.xml.rels has a reference to metadata.xml.
• File [ContentTypes].xml has a reference to metadata.xml.
• A cell with a formula which returns an array result has attribute cm="1".

I think it's probably doable, but it might be fragile, especially if other features need to be enabled in this way.

[oleibman]

I think that if I can figure out SPILL, it will be time to move this out of draft status.

[oleibman]

@MarkBaker @PowerKiKi As you have seen, I have moved this PR from draft to ready status. My plan is to install it in a month, so let's take July 18 as a tentative date. You probably want to schedule a new point release for soon before that. (And decide if this merits a new release.) I will probably tweak some code between now and then, but it's basically ready and most of the updates I make now will be to documentation, for which I will draw heavily on PR #2787.

This is a big change, but, because it is opt-in (need to set array return type), I don't think it will be as risky as some other big changes. It is not completely risk-free - I did have to change some nominally unrelated code because it will sometimes affect cached cells, which is a common problem.

The main difference between this and 2787 is that there is no need for an extra parameter for $cell->setValue() and related functions. This keeps it a non-breaking change. I don't feel that there's a strong use-case for making some cells generate array results and others generate scalar results. If you think otherwise, we may have to scrap this approach altogether.

[oleibman]

@jr212 @infojunkie If you have the opportunity to test with the latest version of this PR, please do so and let me know of any problems.

[infojunkie]

Thanks @oleibman I've been experimenting with your branch for a few days now. I'm encountering behaviors that I'm not sure about concerning spill errors and the concatenation functionality, so I need to isolate them into simpler use cases before I give feedback.

[jr212]

@jr212 @infojunkie If you have the opportunity to test with the latest version of this PR, please do so and let me know of any problems.

I will. Just let me know how to build this time. remember last time I worked with the wrong build :) Git and composer

[oleibman]

@jr212

git clone -b atsign --single-branch https://github.com/oleibman/PhpSpreadsheet.git yourdirectoryname
cd yourdirectoryname
composer install --no-dev

[oleibman]

Spill operator (trailing #) is not (yet) part of this PR. Then again, neither is leading @, which is usable with or without array functions. I will continue to ponder over both; I don't necessarily consider either a showstopper, provided that they are documented. Per the work that @MarkBaker has done in 2787, it looks like Excel has a particularly nasty treatment for the spill operator, changing the function arguments when writing/reading (and the implementation of the required pseudo-function is incomplete in that PR). This is much more difficult than just adding _xlfn to the function name, a previous "innovation".

[oleibman]

I think I have a solution for the SPILL operator, building on Mark's work. It should be available tonight or tomorrow. The leading at-sign is harder because there are legitimate uses of it which we do handle already (e.g. for tables), and we need to make sure not to break those.

[infojunkie]

When I tested the branch up to https://github.com/PHPOffice/PhpSpreadsheet/pull/3962/commits/8609b78e53af482a71e0e161555bceffbda2b2e6, I got an error #SPILL! on my spreadsheet - that's what I meant.

[oleibman]

@infojunkie The #SPILL! error could be correct, or it could be a bug in the code. I need more information.

[jr212]

My test done. Work but:: When opening the file I have 2 errors. Both on formula filter. After opening the file Excel itself corrects the values

Also CHOOSECOLS still add the @ to the formula

[oleibman]

@jr212 CHOOSECOLS still gets an at-sign because we have not yet implemented it, so PhpSpreadsheet does not know, and therefore can't tell Excel, that it's an array function. It is too useful for me at this stage that CHOOSECOLS remains unimplemented. I can look into implementing it after this work is done.

If you upload your file with formula filter errors, I can take a look at it. I might also need to see your code, but let's just start with the file. If it's not a problem for it to be publicly accessible, you should just be able to drag and drop it into a Github comment, without having to store it in some other web-accessible location.

[jr212]

Ok I will not work with choosecolumns/rows for now. At the moment I use 3 cols and hide the middle:)

File uploaded but please remove after "no longer needed" In case the upload was bad you can also find it https://www.janr.be/git/test.xlsx

code with little adaptions / just relevant lines

setCellValue('A2', '=FILTER(Partijen!I3:K' . $maxlijn . ',' . ($u ? '(Partijen!J3:J' . $maxlijn . '=FILTER(Punten,W="Uitgesteld"))' : '') . ($u && $a ? ' + ' : '') . ($a ? '(Partijen!J3:J' . $maxlijn . '=FILTER(Punten,W="Afgebroken"))' : '') . ',"Geen ' . ($u ? 'uitgestelde' : '') . ($u && $a ? ' of ' : '') . ($a ? 'afgebroken' : '') . ' partijen")' ); $ws->setSelectedCell('A1'); } function doTS($ws, $maxlijn) { $ws->setCellValue('A2', '=FILTER(Partijen!I3:K' . $maxlijn . ',(LEFT(Partijen!I3:I' . $maxlijn . ',3)"bye")*(Partijen!I3:I' . $maxlijn . '"")*(LEFT(Partijen!K3:K' . $maxlijn . ',3)"bye")*(Partijen!J3:J' . $maxlijn . '=""),{"Alles","","gespeeld"})'); $ws->setSelectedCell('A1'); } [test.xlsx](https://github.com/user-attachments/files/15904424/test.xlsx) $maxLijn=44 $wsUA = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'Uitgesteld-Afgebroken'); $wsTS = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'Nog te spelen'); doUA($wsUA, true, true, $maxLijn); doTS($wsTS, $maxLijn); ?>

[oleibman]

@jr212 Thank you for the sample file. I have deleted the link to it, as you requested. I believe my latest push will solve your filter errors problem. Please download the code again (same procedure as before) and let me know the results of your testing.

[jr212]

Done the test again. All is fine :) Thanks a lot. I hope to help you in the future. The files on the website are gone.

[infojunkie]

I'm not sure I'm testing properly, but I'm seeing unexpected results with the concatenation operator &.

Attached is a sheet that uses RANDARRAY as a function that spills to a range of cells. It also has extra cells that access cells A1 and A3 in the spill range, both via direct reference and via concatenation in a string.

test-dynamic-array-formulas-excel.zip

Here are results for: Microsoft Excel online, Google Sheets, PHPOffice/PhpSpreadsheet/tree/master, oleibman/PhpSpreadsheet/tree/atsign:

Microsoft Excel online

Google Sheets

Consistent with Excel.

PHPOffice/PhpSpreadsheet/tree/master

As expected, subsequent cells remain empty.

Testing cache value for cell Sheet1!A12
Evaluating formula for cell Sheet1!A12
Formula for cell Sheet1!A12 is A1
Sheet1!A12 => Evaluating Cell A1 in worksheet Sheet1
Sheet1!A12 => Testing cache value for cell Sheet1!A1
Sheet1!A12 => Evaluating formula for cell Sheet1!A1
Sheet1!A12 => Formula for cell Sheet1!A1 is _xlfn.RANDARRAY(10,1, 0, 10)
Sheet1!A12 -> Sheet1!A1 => Evaluating Function RANDARRAY() with 4 arguments
Sheet1!A12 -> Sheet1!A1 => Evaluating RANDARRAY ( 10, 1, 0, 10 )
Sheet1!A12 -> Sheet1!A1 => Evaluation Result for RANDARRAY() function call is a matrix with a value of { 3.18373218327; 3.716706137041; 0.91684012250828; 8.1127239661816; 4.2095383276276; 9.0700122290617; 7.7011181124026; 3.909267440396; 0.7926840012859; 4.7961185615492 }
Sheet1!A12 => Evaluation Result for cell 'Sheet1'!A1 in worksheet Sheet1 is a floating point number with a value of 3.18373218327
Testing cache value for cell Sheet1!A13
Evaluating formula for cell Sheet1!A13
Formula for cell Sheet1!A13 is "Hello: "&A1
Sheet1!A13 => Evaluating Cell A1 in worksheet Sheet1
Sheet1!A13 => Testing cache value for cell Sheet1!A1
Sheet1!A13 => Retrieving value for cell Sheet1!A1 from cache
Sheet1!A13 => Evaluation Result for cell 'Sheet1'!A1 in worksheet Sheet1 is a floating point number with a value of 3.18373218327
Sheet1!A13 => Evaluating "Hello: " & 3.18373218327
Sheet1!A13 => Evaluation Result is a string with a value of "Hello: 3.18373218327"
Testing cache value for cell Sheet1!A14
Evaluating formula for cell Sheet1!A14
Formula for cell Sheet1!A14 is A3
Sheet1!A14 => Evaluating Cell A3 in worksheet Sheet1
Sheet1!A14 => Evaluation Result for cell 'Sheet1'!A3 in worksheet Sheet1 is an empty string
Testing cache value for cell Sheet1!A15
Evaluating formula for cell Sheet1!A15
Formula for cell Sheet1!A15 is "Hello: "&A3
Sheet1!A15 => Evaluating Cell A3 in worksheet Sheet1
Sheet1!A15 => Evaluation Result for cell 'Sheet1'!A3 in worksheet Sheet1 is an empty string
Sheet1!A15 => Evaluating "Hello: " & ""
Sheet1!A15 => Evaluation Result is a string with a value of "Hello: "
/home/kratib/src/quartech/workbc-cc-refactor/src/scripts/spreadsheets/test-dynamic-array-formulas.php:31:
array(4) {
  'A12' =>
  array(1) {
    [1] =>
    array(1) {
      'A' =>
      double(3.18373218327)
    }
  }
  'A13' =>
  array(1) {
    [0] =>
    array(1) {
      [0] =>
      string(20) "Hello: 3.18373218327"
    }
  }
  'A14' =>
  array(1) {
    [3] =>
    array(1) {
      'A' =>
      string(0) ""
    }
  }
  'A15' =>
  array(1) {
    [0] =>
    array(1) {
      [0] =>
      string(7) "Hello: "
    }
  }
}

oleibman/PhpSpreadsheet/tree/atsign

In this branch, A3 is correctly interpreted as being part of the dynamic array, and is correctly concatenated as a scalar value. However, A1 is being considered as an array in both cases.

I was expecting the concatenation operator to return a string on your branch - never an array. This seems consistent with Excel and Sheets.

Also, I am confused about the role of $calculation->setArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY); in this new reality of dynamic arrays and spill behavior.

Testing cache value for cell Sheet1!A12
Evaluating formula for cell Sheet1!A12
Formula for cell Sheet1!A12 is A1
Sheet1!A12 => Evaluating Cell A1 in worksheet Sheet1
Sheet1!A12 => Testing cache value for cell Sheet1!A1
Sheet1!A12 => Evaluating formula for cell Sheet1!A1
Sheet1!A12 => Formula for cell Sheet1!A1 is RANDARRAY(10,1, 0, 10)
Sheet1!A12 -> Sheet1!A1 => Evaluating Function RANDARRAY() with 4 arguments
Sheet1!A12 -> Sheet1!A1 => Evaluating RANDARRAY ( 10, 1, 0, 10 )
Sheet1!A12 -> Sheet1!A1 => Evaluation Result for RANDARRAY() function call is a matrix with a value of { 7.6483603323104; 7.411232407862; 6.3681442972124; 9.2015676476069; 2.415484274931; 5.6990465501785; 3.3821493263273; 7.7272928495553; 8.1819864167748; 4.2299572025565 }
Sheet1!A12 => Evaluation Result for cell 'Sheet1'!A1 in worksheet Sheet1 is a matrix with a value of { { 7.6483603323104; 7.411232407862; 6.3681442972124; 9.2015676476069; 2.415484274931; 5.6990465501785; 3.3821493263273; 7.7272928495553; 8.1819864167748; 4.2299572025565 } }
Testing cache value for cell Sheet1!A13
Evaluating formula for cell Sheet1!A13
Formula for cell Sheet1!A13 is "Hello: "&A1
Sheet1!A13 => Evaluating Cell A1 in worksheet Sheet1
Sheet1!A13 => Testing cache value for cell Sheet1!A1
Sheet1!A13 => Retrieving value for cell Sheet1!A1 from cache
Sheet1!A13 => Evaluation Result for cell 'Sheet1'!A1 in worksheet Sheet1 is a matrix with a value of { { 7.6483603323104; 7.411232407862; 6.3681442972124; 9.2015676476069; 2.415484274931; 5.6990465501785; 3.3821493263273; 7.7272928495553; 8.1819864167748; 4.2299572025565 } }
Sheet1!A13 => Evaluating "Hello: " & { { 7.6483603323104; 7.411232407862; 6.3681442972124; 9.2015676476069; 2.415484274931; 5.6990465501785; 3.3821493263273; 7.7272928495553; 8.1819864167748; 4.2299572025565 } }
PHP Warning:  Array to string conversion in /home/kratib/src/infojunkie/PhpSpreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php on line 4816
PHP Stack trace:
PHP   1. {main}() /home/kratib/src/quartech/workbc-cc-refactor/src/scripts/spreadsheets/test-dynamic-array-formulas.php:0
PHP   2. PhpOffice\PhpSpreadsheet\Calculation\Calculation->calculate($cell = class PhpOffice\PhpSpreadsheet\Cell\Cell { private mixed $value = '="Hello: "&A1'; private $calculatedValue = 'Hello: 1.33065599911115'; private string $dataType = 'f'; private ?PhpOffice\PhpSpreadsheet\Collection\Cells $parent = class PhpOffice\PhpSpreadsheet\Collection\Cells { private Psr\SimpleCache\CacheInterface $cache = class PhpOffice\PhpSpreadsheet\Collection\Memory\SimpleCache3 { ... }; private ?PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $parent = class PhpOffice\PhpSpreadsheet\Worksheet\Worksheet { ... }; private ?PhpOffice\PhpSpreadsheet\Cell\Cell $currentCell = class PhpOffice\PhpSpreadsheet\Cell\Cell { ... }; private ?string $currentCoordinate = 'A1'; private bool $currentCellIsDirty = FALSE; private array $index = [...]; private string $cachePrefix = '�jm\n\fB+:' }; private int $xfIndex = 0; private mixed $formulaAttributes = NULL; private PhpOffice\PhpSpreadsheet\Cell\IgnoredErrors $ignoredErrors = class PhpOffice\PhpSpreadsheet\Cell\IgnoredErrors { private bool $numberStoredAsText = FALSE; private bool $formula = FALSE; private bool $twoDigitTextYear = FALSE; private bool $evalError = FALSE } }) /home/kratib/src/quartech/workbc-cc-refactor/src/scripts/spreadsheets/test-dynamic-array-formulas.php:28
PHP   3. PhpOffice\PhpSpreadsheet\Calculation\Calculation->calculateCellValue($cell = class PhpOffice\PhpSpreadsheet\Cell\Cell { private mixed $value = '="Hello: "&A1'; private $calculatedValue = 'Hello: 1.33065599911115'; private string $dataType = 'f'; private ?PhpOffice\PhpSpreadsheet\Collection\Cells $parent = class PhpOffice\PhpSpreadsheet\Collection\Cells { private Psr\SimpleCache\CacheInterface $cache = class PhpOffice\PhpSpreadsheet\Collection\Memory\SimpleCache3 { ... }; private ?PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $parent = class PhpOffice\PhpSpreadsheet\Worksheet\Worksheet { ... }; private ?PhpOffice\PhpSpreadsheet\Cell\Cell $currentCell = class PhpOffice\PhpSpreadsheet\Cell\Cell { ... }; private ?string $currentCoordinate = 'A1'; private bool $currentCellIsDirty = FALSE; private array $index = [...]; private string $cachePrefix = '�jm\n\fB+:' }; private int $xfIndex = 0; private mixed $formulaAttributes = NULL; private PhpOffice\PhpSpreadsheet\Cell\IgnoredErrors $ignoredErrors = class PhpOffice\PhpSpreadsheet\Cell\IgnoredErrors { private bool $numberStoredAsText = FALSE; private bool $formula = FALSE; private bool $twoDigitTextYear = FALSE; private bool $evalError = FALSE } }, $resetLog = *uninitialized*) /home/kratib/src/infojunkie/PhpSpreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php:3435
PHP   4. PhpOffice\PhpSpreadsheet\Calculation\Calculation->_calculateFormulaValue($formula = '="Hello: "&A1', $cellID = 'A13', $cell = class PhpOffice\PhpSpreadsheet\Cell\Cell { private mixed $value = '="Hello: "&A1'; private $calculatedValue = 'Hello: 1.33065599911115'; private string $dataType = 'f'; private ?PhpOffice\PhpSpreadsheet\Collection\Cells $parent = class PhpOffice\PhpSpreadsheet\Collection\Cells { private Psr\SimpleCache\CacheInterface $cache = class PhpOffice\PhpSpreadsheet\Collection\Memory\SimpleCache3 { ... }; private ?PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $parent = class PhpOffice\PhpSpreadsheet\Worksheet\Worksheet { ... }; private ?PhpOffice\PhpSpreadsheet\Cell\Cell $currentCell = class PhpOffice\PhpSpreadsheet\Cell\Cell { ... }; private ?string $currentCoordinate = 'A1'; private bool $currentCellIsDirty = FALSE; private array $index = [...]; private string $cachePrefix = '�jm\n\fB+:' }; private int $xfIndex = 0; private mixed $formulaAttributes = NULL; private PhpOffice\PhpSpreadsheet\Cell\IgnoredErrors $ignoredErrors = class PhpOffice\PhpSpreadsheet\Cell\IgnoredErrors { private bool $numberStoredAsText = FALSE; private bool $formula = FALSE; private bool $twoDigitTextYear = FALSE; private bool $evalError = FALSE } }, $ignoreQuotePrefix = *uninitialized*) /home/kratib/src/infojunkie/PhpSpreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php:3482
PHP   5. PhpOffice\PhpSpreadsheet\Calculation\Calculation->processTokenStack($tokens = [0 => ['type' => 'Value', 'value' => '"Hello: "', 'reference' => NULL], 1 => ['type' => 'Cell Reference', 'value' => '\'Sheet1\'!A1', 'reference' => '\'Sheet1\'!A1'], 2 => ['type' => 'Binary Operator', 'value' => '&', 'reference' => NULL]], $cellID = 'A13', $cell = class PhpOffice\PhpSpreadsheet\Cell\Cell { private mixed $value = '="Hello: "&A1'; private $calculatedValue = 'Hello: 1.33065599911115'; private string $dataType = 'f'; private ?PhpOffice\PhpSpreadsheet\Collection\Cells $parent = class PhpOffice\PhpSpreadsheet\Collection\Cells { private Psr\SimpleCache\CacheInterface $cache = class PhpOffice\PhpSpreadsheet\Collection\Memory\SimpleCache3 { ... }; private ?PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $parent = class PhpOffice\PhpSpreadsheet\Worksheet\Worksheet { ... }; private ?PhpOffice\PhpSpreadsheet\Cell\Cell $currentCell = class PhpOffice\PhpSpreadsheet\Cell\Cell { ... }; private ?string $currentCoordinate = 'A1'; private bool $currentCellIsDirty = FALSE; private array $index = [...]; private string $cachePrefix = '�jm\n\fB+:' }; private int $xfIndex = 0; private mixed $formulaAttributes = NULL; private PhpOffice\PhpSpreadsheet\Cell\IgnoredErrors $ignoredErrors = class PhpOffice\PhpSpreadsheet\Cell\IgnoredErrors { private bool $numberStoredAsText = FALSE; private bool $formula = FALSE; private bool $twoDigitTextYear = FALSE; private bool $evalError = FALSE } }) /home/kratib/src/infojunkie/PhpSpreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php:3696
Sheet1!A13 => Evaluation Result is a string with a value of "Hello: Array"
Testing cache value for cell Sheet1!A14
Evaluating formula for cell Sheet1!A14
Formula for cell Sheet1!A14 is A3
Sheet1!A14 => Evaluating Cell A3 in worksheet Sheet1
Sheet1!A14 => Evaluation Result for cell 'Sheet1'!A3 in worksheet Sheet1 is a floating point number with a value of 6.3681442972124
Testing cache value for cell Sheet1!A15
Evaluating formula for cell Sheet1!A15
Formula for cell Sheet1!A15 is "Hello: "&A3
Sheet1!A15 => Evaluating Cell A3 in worksheet Sheet1
Sheet1!A15 => Evaluation Result for cell 'Sheet1'!A3 in worksheet Sheet1 is a floating point number with a value of 6.3681442972124
Sheet1!A15 => Evaluating "Hello: " & 6.3681442972124
Sheet1!A15 => Evaluation Result is a string with a value of "Hello: 6.3681442972124"
/home/kratib/src/quartech/workbc-cc-refactor/src/scripts/spreadsheets/test-dynamic-array-formulas.php:31:
array(4) {
  'A12' =>
  array(1) {
    [1] =>
    array(1) {
      'A' =>
      array(10) {
        ...
      }
    }
  }
  'A13' =>
  array(1) {
    [0] =>
    array(1) {
      [0] =>
      string(12) "Hello: Array"
    }
  }
  'A14' =>
  array(1) {
    [3] =>
    array(1) {
      'A' =>
      double(6.3681442972124)
    }
  }
  'A15' =>
  array(1) {
    [0] =>
    array(1) {
      [0] =>
      string(22) "Hello: 6.3681442972124"
    }
  }
}

Driver code

<?php

// Uses my fork of PhpSpreadsheet to add custom functions
// https://github.com/infojunkie/PhpSpreadsheet
//

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Calculation\Calculation;

if (!isset($argv[1]) || !file_exists($argv[1])) {
  die("Usage: php {$argv[0]} /path/to/sheet.xlsx\n");
}

$spreadsheet = IOFactory::load($argv[1]);
$calculation = $spreadsheet->getCalculationEngine();
$calculation->getDebugLog()->setWriteDebugLog(true);
$calculation->getDebugLog()->setEchoDebugLog(true);
//$calculation->setArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);

$sheet = $spreadsheet->getActiveSheet();
$result = [];
foreach (range(12, 15) as $row) {
  foreach (['A'] as $col) {
    $coord = "{$col}{$row}";
    $cell = $sheet->getCell($coord);
    $result[$coord] = $calculation->calculate($cell);
  }
}
var_dump($result);

Thanks!