Incorrect evaluating Named Ranges

[kusnir]

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?

Return correct value from range defined by Named Range.

From test:

• B7 should by 6
• B8 should by 2
• B9 should by 2

What is the current behavior?

Return 0 or #N/A value

From test:

• B7 is 0
• B8 is empty
• B8 is #N/A

What are the steps to reproduce?

Create a Xls file:

1. Create new file
2. Fill data:

• A1: TEST1
• A2: TEST2
• A3: TEST3
• A5: TEST2
• B1: 3
• B2: 2
• B3: 1

3. Create Named Range

• NAMED_RANGE: =Sheet1!$A$1:$B$3
• NAMED_VALUES_RANGE: =Sheet1!$B$1:$B$3

4. Fill data:

• A7: =SUM(B1:B3)
• B7: =SUM(NAMED_VALUES_RANGE)
• A8: =VLOOKUP(A2;A1:B3;2;FALSE)
• B8: =VLOOKUP(A2;NAMED_RANGE;2;FALSE)
• A9: =VLOOKUP("TEST2";A1:B3;2;FALSE)
• B9: =VLOOKUP("TEST2";NAMED_RANGE;2;FALSE)

5. Save as test.xls

<?php
chdir(__DIR__);
set_include_path(__DIR__);
require_once 'vendor/autoload.php';

$composerData = json_decode(file_get_contents(__DIR__ . '/composer.lock'), true);
$phpspreadsheet = array_values(array_filter($composerData['packages'], function ($value, $key) {
    return $value['name'] == 'phpoffice/phpspreadsheet';
}, ARRAY_FILTER_USE_BOTH))[0];
echo '<h2>PHP ' . phpversion() . ', PHPOffice / PhpSpreadsheet ' . $phpspreadsheet['version'] . '</h2>';

$filePath = __DIR__ . '/test.xls';
echo '<strong>' . basename($filePath) . '</strong>';

$inputFileType = 'Xls';
$objReader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($filePath);
$sheets = $objReader->listWorksheetNames($filePath);
foreach ($sheets as $sheet_index => $sheet_name) {
    // Get worksheet dimensions
    $sheet = $objPHPExcel->getSheet($sheet_index);
    $highestRow = $sheet->getHighestRow();
    $highestColumn = $sheet->getHighestColumn();

    if ($highestRow > 1) {
        // Loop through each row of the worksheet in turn
        echo '<h2>' . $sheet_name . '</h2>';
        echo '<table border="1">';
        for ($row = 1; $row <= $highestRow; $row ++) {
            if ($row == 1) {
                echo '<tr>';
                echo '<th></th>';
                $highestColumnIndex = array_values(\PhpOffice\PhpSpreadsheet\Cell\Coordinate::indexesFromString($highestColumn . '1'))[0];
                for ($index = 1; $index <= $highestColumnIndex; $index++) {
                    echo '<th>' . \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($index) . '</th>';
                }
                echo '</tr>';
            }

            // Read a row of data into an array
            $formulas = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, false, true);
            $values = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, true, true);

            echo '<tr>';
            echo '<th>' . $row . '</th>';
            if ($values) {
                foreach (array_keys($values[0]) as $k) {
                    //$values[0][$k] = str_replace("\n", "; ", $values[0][$k]);
                    echo '<td>';
                    echo '<small style="color: #999;">' . $formulas[0][$k] . '</small><br/>';
                    echo $values[0][$k];
                    echo '</td>';
                }
            }
            echo '</tr>';
        }
        echo '</table>';
    }
}

echo '<h2>Named Ranges</h2>';
foreach ($objPHPExcel->getNamedRanges() as $range) {
    /* @var \PhpOffice\PhpSpreadsheet\NamedRange $range */
    echo $range->getName() . ': ' . $range->getRange() . '<br>';

}

Output HTML:

PHP 7.4.27, PHPOffice / PhpSpreadsheet 1.23.0

test.xls

Sheet1

	A	B
1	TEST1 TEST1	3 3
2	TEST2 TEST2	2 2
3	TEST3 TEST3	1 1
4
5	TEST2 TEST2
6
7	=SUM(B1:B3) 6	=SUM(NAMED_VALUES_RANGE) 0
8	=VLOOKUP(A5,A1:B3,2,FALSE) 2	=VLOOKUP(A5,NAMED_RANGE,2,FALSE)
9	=VLOOKUP("TEST2",A1:B3,2,FALSE) 2	=VLOOKUP("TEST2",NAMED_RANGE,2,FALSE) #N/A

Named Ranges

NAMED_RANGE: A1:B3
NAMED_VALUES_RANGE: B1:B3

What features do you think are causing the issue

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

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

xls

Which versions of PhpSpreadsheet and PHP are affected?

PhpSpreadsheet Ver : V1.23.0
PHP Ver: 7.4.27

[MarkBaker]

Thanks for raising this issue, and for your detailed example making it very easy to verify, and to recreate the problem.

In version 1.15.0 we fixed an issue with named ranges in formula that was always treating cell references as absolute references, even when they were relative references (Excel adjusts the range when relative references are used in named ranges in formulae). The Calculation Engine processes these adjustments correctly; but it is important that named ranges are correctly defined as relative or absolute.

The problem here is that the named ranges are defined as absolute, but that the Xls Reader is converting them to relative ranges. Before 1.15.0 this didn't matter, because the Calculation Engine treated all named ranges as absolute; but since that fix to the Calculation Engine it does make a difference.

The solution is to prevent the Xls Reader from converting absolute ranges to relative ranges, but to treat them exactly as they are defined.

[MarkBaker]

P.S. Nice use of reading the composer.lock file to extract the PhpSpreadsheet versions; I'll start using that technique myself