Named ranges not usable as anchors in OFFSET function

[andvaranaut]

This is:

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

I have noticed that the OFFSET function will not work with a named range as the first argument (base cell/range from which to calculate the offset), even though the usage is valid. Disabling the calculation engine is a workaround.

Looking at the source for the OFFSET function, I think that the problem is that the extractWorksheet function, which handles the $cellAddress (first) argument to OFFSET, does not support parsing a named range.

What is the expected behavior?

OFFSET should be able to use named ranges as the first argument.

What is the current behavior?

Using a named range as the first argument of OFFSET results in an Invalid cell coordinate exception.

What are the steps to reproduce?

<?php

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

use PhpOffice\PhpSpreadsheet\NamedRange;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$excel = new Spreadsheet();
$hoja = $excel->getActiveSheet();
$writer = new Xlsx($excel);

$hoja->setCellValue('A1', 1);
$hoja->setCellValue('A2', 2);

$excel->addNamedRange(new NamedRange('demo', $hoja, '=$A$1'));

$hoja->setCellValue('B1', '=demo');

$hoja->setCellValue('B2', '=OFFSET(demo, 1, 0)');    // This line...
// $writer->setPreCalculateFormulas(false);          // needs this to be uncommented

$writer->save('demo.xlsx');

If you run the code as is, you'll get an exception, even though the formula is correct. If you uncomment the setPrecalculateFormulas line it will work fine, however, as the calculation will not take place. Once you open the file with a spreadsheet (tested with LibreOffice) and force a recalculation, it works fine. Alternatively, if you comment out the setCellValue for B2, the file can be saved correctly with calculations enabled, and the value for B1 will be correctly calculated as 1 (the contents of the named range demo), meaning that the named range is indeed working correctly.

What features do you think are causing the issue

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

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

It probably does, as it's an issue with the calculation engine.

Which versions of PhpSpreadsheet and PHP are affected?

Tested on PHP 7.4.3 with PhpSpreadsheet 1.24.1 (the version installed via composer require phpoffice/phpspreadsheet)

[andvaranaut]

I should mention that OFFSET is likely not the only function affected - within LookupRef there are some functions which make use of Helpers::extractWorksheet, which seems to share the same shortcoming as Offset::extractWorksheet.

Also, digging a bit at the source, perhaps what is lacking is a call to Functions::expandDefinedName within the extractWorksheet functions, although a naïve test didn't work.

[MarkBaker]

it's a static function, so it has no context of the current workbook from which to extract the value of the named range

Not strictly true; the cell object is passed to extractWorksheet()

[andvaranaut]

Not strictly true; the cell object is passed to extractWorksheet()

You are right, of course. Sorry for not noticing, I have edited the issue accordingly.