Excel "CELL" function not recalculating after spreadhsheet opened by phpspreadsheets

[twistedsymphony]

This is:

- [x] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

The Function should either retain it's original calculated value or recalculate

What is the current behavior?

the function returns "#VALUE!"

What are the steps to reproduce?

Use phpspreadsheet to open the file: test.xlsx
this file include a cell function

=CONCATENATE("Prefix ",MID(CELL("filename"),FIND("]",CELL("filename"))+1,255), " Suffix")

Then use phpspreadsheet to output the file; creating a new file. The newly output file will show the formula as un-calculated. (there may be some other requirements but the behavior can be observed in the included simplified example)

test.xlsx before opening:

ouput.xlsx produced by phpspreadsheet:

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php

require __DIR__ . '/vendor/autoload.php';

$sheetReader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
$sheetReader = $sheetReader->load('test.xlsx');

$sheetWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($sheetReader, 'Xlsx');

header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment; filename=\"output.xlsx\"");
$sheetWriter->save('php://output');

Which versions of PhpSpreadsheet and PHP are affected?

This was observed on php 7.4 and phpspreadsheet 1.21.0

[twistedsymphony]

I suspect the issue is related to the fact that the CELL("filename") function returns the actual filename and file path in addition to the sheet name and the file name (perhaps?) does not exist when the spreadsheet is stored in memory without an explicit reference location on disc. Thus when phpspreadsheet recalculates it's unable to properly calculate this value and the #VALUE! error gets saved as the result.

I came across this issue when upgrading an old phpExcel application to phpSpreadsheet and it functioned properly with phpExcel on php5.6

[oleibman]

The CELL function is not currently implemented in PhpSpreadsheet.

[MarkBaker]

Note that PHPExcel also never implemented the CELL{} function

[twistedsymphony]

Note that PHPExcel also never implemented the CELL{} function

That's true, however PHPExcel seems to have ignored the previously calculated CELL functions and left the result in place while PHP Spreadsheet wipes out the previously calculated data. This is the root of the problem I'm currently experiencing.