Formula Parser: Wrong line count for stuff like "MyOtherSheet!A:D"

[lukey78]

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)

What is the expected behavior?

2 Sheets:

"Sheet1" with 2 cols:

KEY1 | =VLOOKUP(A1;Sheet2!A:B;2;0)
KEY2 | =VLOOKUP(A2;Sheet2!A:B;2;0)

"Sheet2" with 2 cols:

KEY0 | Value0
KEY1 | Value1
KEY2 | Value2
KEY3 | Value3

Expected behaviour: Sheet1 looks like this after "getCalculatedValue()" for each cell:

KEY1 | Value1
KEY2 | Value2

What is the current behavior?

KEY1 | Value1
KEY2 | #N/A

What might be the cause of the problem?

The _parseFormula() method in Calculation.php parses the VLOOKUP above into tokens. The A:D reference is changed to become A1:D2.

To calculate the "2" the parser uses $pCellParent->getHighestRow(). BUT: The $pCellParent is the parent of the cell where the formula (VLOOKUP) is inside ("Sheet1"). Actually, it SHOULD get the highest row from the sheet referenced in the formula which is Sheet2, and Sheet2 has 4 lines. Thus, the output should be A1:D4 instead of A1:D2.

My current "quick & dirty" fix. Changing from line 3668:

  //    Column range
 $endRowColRef = ($pCellParent !== null) ? $pCellParent->getHighestRow() : 1048576; //    Max 1,048,576 rows for Excel2007
  $output[count($output) - 1]['value'] = $rangeWS1 . strtoupper($startRowColRef) . '1';
  $val = $rangeWS2 . $val . $endRowColRef;

to:

//    Column range
    $refSheet = $pCellParent;
    $rangeSheetName = \str_replace('!', '', $rangeWS1);
    if ($rangeSheetName !== $pCellParent->getTitle()) {
        $refSheet = $pCell->getWorksheet()->getParent()->getSheetByName($rangeSheetName);
    }
    $endRowColRef = ($pCellParent !== null) ? $refSheet->getHighestRow() : 1048576; //    Max 1,048,576 rows for Excel2007
    $output[count($output) - 1]['value'] = $rangeWS1 . strtoupper($startRowColRef) . '1';
    $val = $rangeWS2 . $val . $endRowColRef;

What are the steps to reproduce?

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';

        $spreadsheet = new Spreadsheet();

        $spreadsheet->addSheet(new Worksheet($spreadsheet, 'Sheet1'))
            ->setCellValue('A1', 'KEY1')
            ->setCellValue('A2', 'KEY2')
            ->setCellValue('B1', '=VLOOKUP(A1,Sheet2!A:B,2,0)')
            ->setCellValue('B2', '=VLOOKUP(A2,Sheet2!A:B,2,0)');

        $spreadsheet->addSheet(new Worksheet($spreadsheet, 'Sheet2'))
            ->setCellValue('A1', 'KEY0')
            ->setCellValue('A2', 'KEY1')
            ->setCellValue('A3', 'KEY2')
            ->setCellValue('A4', 'KEY3')
            ->setCellValue('B1', 'Value0')
            ->setCellValue('B2', 'Value1')
            ->setCellValue('B3', 'Value2')
            ->setCellValue('B4', 'Value3');

        $spreadsheet->setActiveSheetIndexByName('Sheet1');

        $resultForB1 = $spreadsheet->getActiveSheet()->getCell('B1')->getCalculatedValue();
        $resultForB2 = $spreadsheet->getActiveSheet()->getCell('B2')->getCalculatedValue();

        $this->assertEquals('Value1', $resultForB1);
        $this->assertEquals('Value2', $resultForB2); // this fails, actual is '#N/A'

Which versions of PhpSpreadsheet and PHP are affected?

1.9.0

[MarkBaker]

Implemented fix in latest merge to master