QuotePrefix Style being incorrectly applied when referencing a named cell in another sheet

[fdjohnston]

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?

A simple formula on Sheet1 referencing a named cell on Sheet2 should calculate properly. However, the Formula Engine produces a #VALUE error if there is a quote prefixed value anywhere on Sheet2.
Expected behaviour is that the formula should calculate properly.

What is the current behavior?

A #VALUE error is produced by the formula engine if there is a quote prefixed value anywhere on Sheet2.

What are the steps to reproduce?

See attached Excel file for a minimal viable example spreadsheet.

<?php

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

// Create a new Reader of the type that has been identified
$reader = IOFactory::createReader( 'Xlsx' );

// Load file into a Spreadsheet Object
$currentSpreadsheet = $reader->load('Test.xlsx');
$actualWorksheet = $currentSpreadsheet->getSheetByName( 'Sheet1' );
$array = $actualWorksheet->toArray( null, true, false, false );
var_dump($array);
die();

Test.xlsx

Note that adding

$reader->setReadDataOnly(TRUE);

To the above resolves the issue, however I need this to be set to FALSE.

Oddly, if the cell containing the quoted value in the example XLSX is deleted, the issue persists until the entire row is deleted.

What features do you think are causing the issue

• Reader
• Writer
• Styles
• Data Validations
• Formula Calculations
• Charts
• AutoFilter
• Form Elements

I think the root issue is a problem with the XLSX reader. I haven't fully debugged that code yet, but I suspect it is somewhere in the if block starting on line 596 of phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php. Within this if block, quotePrefix is set in two different for loops. I suspect the issue exists in those for loops, or in whatever code builds the $xfTags and $cellXfTags arrays. Perhaps they are being linked incorrectly.

Commenting out the first if block in _calculateFormulaValue in phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php also appears to fix the issue, which is why I believe that quotePrefix is being set incorrectly.

My note above regarding the issue persisting until the entire row is deleted leads me to believe that the XLSX reader is incorrectly assigning cell styles in some cases, and that is leading to the quotePrefix attribute being incorrectly set.

Does an issue affect all spreadsheet file formats? If not, which formats are affected?

I have only tried XLSX files.

Which versions of PhpSpreadsheet and PHP are affected?

PHP 7.4, PhpSpreadsheet 1.27

[oleibman]

Agree that you have a problem. However, it is definitely in the Calculation engine, not the Reader. The following minor modification of your code demonstrates the problem without involving Reader:

use PhpOffice\PhpSpreadsheet\NamedRange;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

$spreadsheet = new Spreadsheet;
$sheet1 = $spreadsheet->getActiveSheet();
$sheet1->setTitle('Sheet1');
$sheet2 = $spreadsheet->createSheet();
$sheet2->setTitle('Sheet2');
$sheet2->getCell('A1')->setValue('July 2019');
$sheet2->getStyle('A1')->setQuotePrefix(true);
$sheet2->getCell('A2')->setValue(3);
$spreadsheet->addNamedRange(new NamedRange('FM', $sheet2, '$A$2'));
$sheet1->getCell('A1')->setValue('=(A2+FM)');
$sheet1->getCell('A2')->setValue(38.42);

$array = $sheet1->toArray( null, true, false, false );
var_dump($array);

Commenting out the quotePrefix statement results in the expected array.

[MarkBaker]

When it fails, the evaluation log is:

[0] => Testing cache value for cell Sheet1!A1
[1] => Evaluating formula for cell Sheet1!A1
[2] => Formula for cell Sheet1!A1 is A2+FM
[3] => Sheet1!A1 => Evaluating Cell A2 in worksheet Sheet1
[4] => Sheet1!A1 => Evaluation Result for cell 'Sheet1'!A2 in worksheet Sheet1 is a floating point number with a value of 38.42
[5] => Sheet1!A1 => Evaluating Defined Name FM
[6] => Sheet1!A1 => Defined Name is a Range with a value of =$A$2
[7] => Sheet1!A1 => Value adjusted for relative references is =$A$2
[8] => Sheet1!A1 => Evaluation Result for Named Range FM is a string with a value of "=$A$2"
[9] => Sheet1!A1 => Evaluating 38.42 + "=$A$2"
[10] => Sheet1!A1 => Evaluation Result is a a #VALUE! error

I believe that this only applies of the quote-prefixed cell is A1 because this is the default cell ID used when evaluating a defined name.

If I modify Owen's code to use cell B1 in sheet 2 instead of cell A1, then the evaluation works correctly

[0] => Testing cache value for cell Sheet1!A1
[1] => Evaluating formula for cell Sheet1!A1
[2] => Formula for cell Sheet1!A1 is A2+FM
[3] => Sheet1!A1 => Evaluating Cell A2 in worksheet Sheet1
[4] => Sheet1!A1 => Evaluation Result for cell 'Sheet1'!A2 in worksheet Sheet1 is a floating point number with a value of 38.42
[5] => Sheet1!A1 => Evaluating Defined Name FM
[6] => Sheet1!A1 => Defined Name is a Range with a value of =$A$2
[7] => Sheet1!A1 => Value adjusted for relative references is =$A$2
[8] => Sheet1!A1 => Sheet2!A1 => Evaluating Cell A2 in worksheet Sheet2
[9] => Sheet1!A1 => Sheet2!A1 => Evaluation Result for cell 'Sheet2'!A2 in worksheet Sheet2 is an integer number with a value of 3
[10] => Sheet1!A1 => Evaluation Result for Named Range FM is an integer number with a value of 3
[11] => Sheet1!A1 => Evaluating 38.42 + 3
[12] => Sheet1!A1 => Evaluation Result is a floating point number with a value of 41.42

Evaluation of a defined name requires a cell reference to link it to the worksheet; and cell A1 was used as a default reference for this because it's guaranteed always to exist in any worksheet.

[fdjohnston]

I believe it's actually an issue with the style for the first row.
Check out the attached XLSX. It produces the problem still, despite having no value in A1. I did have a quote-prefixed value in that cell, but deleted it. It seems like the style for A1 is still showing as quote-prefixed even though the cell is blank.

Test1.xlsx

[MarkBaker]

If the style for row 1 is quote-prefixed; then it will treat cell A1 as being quote-prefixed; I'm about to push a new PR with a potential fix (no unit tests yet).
Would you be able to evaluate your spreadsheets against that PR branch to confirm whether or not it resolves the problem?

[fdjohnston]

@MarkBaker absolutely. I'll try it out as soon as I see the PR.

[fdjohnston]

Wondering if this is the issue the folks in #950 were having.

[MarkBaker]

It's certainly possible that it's linked; though we never had any sample files for that issue to evaluate that problem, and no mention that I can see there in the Issue comments about Defined Name that would have helped narrow it down

[fdjohnston]

Just tried the code in the PR branch and it does appear to fix this issue in both my sample XLSX files and the more complex client file I was working on when I came across the issue.
Thanks again, @MarkBaker for the extremely quick response and analysis.

[MarkBaker]

Thanks for that testing and confirmation

I'll see about adding some unit tests and merging it to master once I've had some dinner