Sheet index being changed when multiple sheets are used in formula

[yobokkie]

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?

When calculating the value of a cell that uses a cell on another sheet in the workbook, the next cell lookup should stay on the original sheet, so that an implicitly set request to set the active sheet remains current.

What is the current behavior?

When running a formula that looks up a value on another sheet in the workbook, and that value in turn looks up a value on the original sheet, it seems that the active sheet is being set to that sheet, instead of returning to the original sheet that was active.

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

// Create new Spreadsheet object
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('testsheet.xlsx');
$cell = 'A1';
$spreadsheet->setActiveSheetIndex(0);
echo 'sheetindex ' . $spreadsheet->getActiveSheetIndex() . '<br/>';
echo 'value ' . $spreadsheet->getActiveSheet()->getCell($cell)->getCalculatedValue() . '<br/>';
echo 'sheetindex ' . $spreadsheet->getActiveSheetIndex();

In order to replicate the issue, a spreadsheet called 'testsheet' should be created, with 2 sheets. In the first sheet, there should be three cells filled in with cell A1 looking up a value on the other sheet. The second sheet should have a formula that looks up and adds the other two values from the first sheet.

The expected output after this should be that the sheet index is still zero, but it will instead echo out the value of the other sheets index, showing that it has changed internally.
testsheet.xlsx

sheetindex 0
value 246
sheetindex 1

Which versions of PhpSpreadsheet and PHP are affected?

Tested on phpspreadsheet 1.12 and php 7.4.6

[yobokkie]

I am reasonably certain this issue was not present on older versions of phpspreadsheet, as I discovered it after upgrading and suddenly formulas and lookups that used to work, stopped working. I eventually resorted to explicitly setting the sheet before each command, something I never needed to do before.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[oleibman]

Fixed by PR #1500.