clone $clone->getSheetByName "Sheet does not exist"

[vobizzz]

• a bug report

What is the expected behavior?

Fatal error: Uncaught PhpOffice\PhpSpreadsheet\Exception: Sheet does not exist. in /Users/dave/Herd/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Spreadsheet.php:675
Stack trace:
#0 /Users/dave/Herd/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Worksheet/Worksheet.php(828): PhpOffice\PhpSpreadsheet\Spreadsheet->getIndex(Object(PhpOffice\PhpSpreadsheet\Worksheet\Worksheet))
#1 /Users/dave/Herd/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Spreadsheet.php(794): PhpOffice\PhpSpreadsheet\Worksheet\Worksheet->rebindParent(Object(PhpOffice\PhpSpreadsheet\Spreadsheet))
#2 /Users/dave/Herd//src/Views/Ajax/print/service/meetrapport.php(25): PhpOffice\PhpSpreadsheet\Spreadsheet->addExternalSheet(Object(PhpOffice\PhpSpreadsheet\Worksheet\Worksheet))
#10 /Applications/Herd.app/Contents/Resources/valet/server.php(167): require('/Users/dave/Her...')
#11 {main}
thrown in /Users/dave/Herd/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Spreadsheet.php on line 675

What is the current behavior?

I just update PHP from 7.4.6 to 8.4 and updated PHPSpreadsheet from 1.29.6 to 3.7.0.
And now my script gives errors and i think this is a change in PHPSpreadsheet, documentation did not change about getting sheets from external spreadsheet.
Sheet name is 100% correct.

What are the steps to reproduce?

My code:

<?php
$spreadsheet_clone  = \PhpOffice\PhpSpreadsheet\IOFactory::load($file_meetrapport);
$clonedWorksheet = clone $spreadsheet_clone->getSheetByName('inspectielijst');
$spreadsheet->addExternalSheet($clonedWorksheet);
$clonedWorksheet->setTitle('Inspectielijst ' . $i_unit);

$sheet = $spreadsheet->getSheetByName('Inspectielijst '.$i_unit);
?>

Worked fine for every PHPSpreadsheet version i worked with but now with the update it gives an error, checked the documentation and it is still the same and should work.
I think that version 3.7.0 that there has been some changes that this stoped working.
Check if PHPSpreadsheet could read the sheet name and directly add it to my code but it does not work.

<?php
$spreadsheet_clone  = \PhpOffice\PhpSpreadsheet\IOFactory::load($file_meetrapport);

$reader1 = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
$reader1->setReadDataOnly(TRUE);
$spreadsheet1 = $reader1->load($file_meetrapport);
print $spreadsheet1->getSheetNames()[0];

$clonedWorksheet = clone $spreadsheet_clone->getSheetByName( $spreadsheet1->getSheetNames()[0] );
$spreadsheet->addExternalSheet($clonedWorksheet);
$clonedWorksheet->setTitle('Inspectielijst ' . $i_unit);
				
$sheet = $spreadsheet->getSheetByName('Inspectielijst '.$i_unit);
?>

I tried to get sheet by index but it olso gives errors.
I now tried a different method without "addExternalSheet" and it worked!
The problem is with "addExternalSheet"

<?php
$clonedWorksheet = clone $spreadsheet_clone->getSheetByName('inspectielijst');
$clonedWorksheet->setTitle('Inspectielijst ' . $i_unit);
$spreadsheet->addSheet($clonedWorksheet);
$sheet = $spreadsheet->getSheetByName('Inspectielijst '.$i_unit);
?>

What features do you think are causing the issue

Version 3.7.0
"addExternalSheet" method

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

Which versions of PhpSpreadsheet and PHP are affected?

3.7.0 / 8.4

[vobizzz]

"addExternalSheet" method works with PHP 8.4 PHPSpreadsheet version 2.3.5.
Looks like 3.7.0 PHPSpreadsheet problem with the function "addExternalSheet"

[oleibman]

I am having trouble distinguishing original from clone in your example. So I have broken it down as follows:

use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XlsxReader;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as XlsxWriter;

$infile = 'issue.4301.xlsx';
$reader = new XlsxReader();
$originalSpreadsheet = $reader->load($infile);

$clonedWorksheet = clone $originalSpreadsheet->getSheetByName('Sheet1');
$clonedWorksheet->setTitle('cloned');
$originalSpreadsheet->addsheet($clonedWorksheet);

$clonedSpreadsheet = new Spreadsheet();
$clonedSpreadsheet->removeSheetByIndex(0); // optional
$clonedSpreadsheet->addExternalSheet($clonedWorksheet);

$writer = new XlsxWriter($clonedSpreadsheet);
$outfile = 'issue.4301.cloned.xlsx';
$writer->save($outfile);

When I execute this code with 3.7.0, I get the expected result - no errors, and issue.4301.cloned.xlsx contains the cloned worksheet. Do you see an error when you execute this code? If not, can you suggest any modifications to my code which will demonstrate the problem?

[oleibman]

Looking at your failing example, I would change the following statements:

$clonedWorksheet = clone $spreadsheet_clone->getSheetByName( $spreadsheet1->getSheetNames()[0] );
$spreadsheet->addExternalSheet($clonedWorksheet); // I believe you meant spreadsheet1
$clonedWorksheet->setTitle('Inspectielijst ' . $i_unit);

To:

$clonedWorksheet = clone $spreadsheet_clone->getSheetByName( $spreadsheet1->getSheetNames()[0] ); // unchanged
$clonedWorksheet->setTitle('Inspectielijst ' . $i_unit); // moved up
$spreadsheet_clone->addSheet($clonedWorksheet); // added
$spreadsheet1->addExternalSheet($clonedWorksheet); // moved down

In other words, after the clone, first change the title, then add the clone to the spreadsheet from which it is derived (otherwise it isn't really an external sheet), then add the external sheet to the spreadsheet where you want it.

[vobizzz]

Hi oleibman,
Sorry for my late response.
But both examples work, I find it strange that it always worked in the previous versions.
Thank you !