[BUG] NamedRange issue

[szabizs]

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?

To display populated dropdowns

What is the current behavior?

After upgraing to the newest Laravel and packages, I've lost the functionality of addNamedRange, functionality that was used for a long time, until now.
I am generating a sheet for manufacturers that holds all the data for my dropdown list DataValidation::TYPE_LIST

	public function registerEvents(): array
	{
		return [
			AfterSheet::class => function(AfterSheet $event){
				$event->getDelegate()->setSheetState(Worksheet::SHEETSTATE_VERYHIDDEN);
			}
		];
	}

This is great, and populated with hundreds of manufacturers.
Now with this method, I am referencing that sheet, all the restrictions work, except de data.

/**
 * validation for manufacturers
 */
for ( $i = 2; $i <= 1500; $i ++ ) {
	$sheet->getCell('C' . $i)->getDataValidation()->setType(DataValidation::TYPE_LIST);
	$sheet->getCell('C' . $i)->getDataValidation()->setErrorStyle(DataValidation::STYLE_STOP);
	$sheet->getCell('C' . $i)->getDataValidation()->setAllowBlank( false );
	$sheet->getCell('C' . $i)->getDataValidation()->setShowInputMessage( true );
	$sheet->getCell('C' . $i)->getDataValidation()->setShowErrorMessage( true );
	$sheet->getCell('C' . $i)->getDataValidation()->setShowDropDown( true );
	$sheet->getCell('C' . $i)->getDataValidation()->setErrorTitle( 'Eroare valoare' );
	$sheet->getCell('C' . $i)->getDataValidation()->setError( 'Valoarea nu este acceptată, nu se află în listă.' );
	$sheet->getCell('C' . $i)->getDataValidation()->setPromptTitle( 'Selectează o valoare din lista predefinită' );
	$sheet->getCell('C' . $i)->getDataValidation()->setPrompt( 'Selectează producătorul.' );
	$sheet->getCell('C' . $i)->getDataValidation()->setFormula1( 'manufacturers' ); //note this!
}

/** gathering data from sheet at first index and get cells till its last entry. */
$manufacturers = $sheet->getDelegate()->getParent()->getSheetByName('manufacturers');
$sheet->getParent()->addNamedRange(new NamedRange('manufacturers', $manufacturers, "A1:A" . $manufacturers->getHighestRow())); //populator id!

$manufacturers sheet with data.

As you can see, the restrictions are there, but the data fails to populate the dropdown, but the sheet with data is there.

Proof, that the manufacturers sheet is populated, commented out Worksheet::SHEETSTATE_VERYHIDDEN

If I added the validation formula by referencing manually the sheet, it does work.

$sheet->getCell('C' . $i)->getDataValidation()->setFormula1('manufacturers!$A$1:$A$231'); //note this!
# instead of
$sheet->getCell('C' . $i)->getDataValidation()->setFormula1( 'manufacturers' ); //note this!

Which versions of PhpSpreadsheet and PHP are affected?

1.15.0 - 2020-10-11

[TiberiuD]

I can confirm that I've encountered this issue with PhpSpreadsheet 1.15.0, with the same code working perfectly on 1.13. I'm using PHP 7.3.

Could you look at the Name Manager in the broken spreadsheet and see if the Named Range exists and is correctly created?

I found out that the Named Range was indeed created, but it contained another range than the one specified in the NamedRange constructor. It seems that the named range gets created on a random range of cells.

Let me know if you see similar behaviour or I'll make a separate issue.

[szabizs]

I can confirm that I've encountered this issue with PhpSpreadsheet 1.15.0, with the same code working perfectly on 1.13. I'm using PHP 7.3.

Could you look at the Name Manager in the broken spreadsheet and see if the Named Range exists and is correctly created?

I found out that the Named Range was indeed created, but it contained another range than the one specified in the NamedRange constructor. It seems that the named range gets created on a random range of cells.

Let me know if you see similar behaviour or I'll make a separate issue.

Excelent observation, indeed, the range is built on a different column, in my case C rather than A

Changing the column in the formula to A from C, it populates the dropdown.

[oleibman]

PR #1535 introduced a bugfix which is a breaking change to Named Ranges:

IMPORTANT NOTE: This Introduces a BC break in the handling of named ranges. Previously, a named range cell reference of B2 would be treated identically to a named range cell reference of $B2 or B$2 or $B$2 because the calculation engine treated then all as absolute references. These changes "fix" that, so the calculation engine now handles relative references in named ranges correctly.

Your definition for "manufacturers" uses relative references, and thus is probably affected. Is the problem resolved if you change your code to:

$sheet->getParent()->addNamedRange(new NamedRange('manufacturers', $manufacturers, '$A$1:$A$' . $manufacturers->getHighestRow())); //populator id!

[szabizs]

I can confirm that what @oleibman told, it is working as expected.
That $ sign is subtle, and it was not taken in considerantion by me when reading the updated documentation.

Be aware of the " and the $ sign, it will process it as a variable.

The below snippet is working as it should.

$event->sheet->getDelegate()->getParent()->addNamedRange(
	new NamedRange(
		'manufacturers',
		$manufacturers,
		'$A$1:$A$' . $manufacturers->getHighestRow()
	)
);