Sheet does not exist When removing sheet

[Grandpied33]

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 I want to remove sheets from an exsiting file, when saving the following error appear : "Sheet does not exist"

What is the current behavior?

While saving the code stop working and return an error

What are the steps to reproduce?

The code is run inside a Symfony project. I call a PHP file where I can complete my file with data then I want to remove them by using the folowing code

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

public function removeSheets($spreadsheet, $uuid, $path)
    {
        /**
         * @var $worksheet Worksheet
         */

        /**
         * @var $spreadsheet Spreadsheet
         */

        $sheetIndex = $spreadsheet->getIndex(
            $spreadsheet->getSheetByName('Sheet2')
        );
        $spreadsheet->removeSheetByIndex($sheetIndex);
        $sheetIndex = $spreadsheet->getIndex(
            $spreadsheet->getSheetByName('Sheet2')
        );
        $spreadsheet->removeSheetByIndex($sheetIndex);
        $worksheet = $spreadsheet->getActiveSheet();

        try {
            $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
            $writer->save($path.'/'.$uuid.'.xlsx');
        }catch (Exception $e)
        {
            echo $e;
        }
        return $uuid;

    }
<?php

The code stop runnig on saving with no more informations

Which versions of PhpSpreadsheet and PHP are affected?

"phpoffice/phpspreadsheet": "^1.17"

[oleibman]

Did you mean to specify 'Sheet3' rather than 'Shhet3' in the following statement?

        $sheetIndex = $spreadsheet->getIndex(
            $spreadsheet->getSheetByName('Shhet3')
        );

Even without this change, I get an Exception, as I should, on the getIndex statement, not on the save.

[Grandpied33]

Hey there,

THanks for the quick answers.
I've made a typo on 'Sheet', I've correct it.
I've publicly renamed sheet name to avoid customer leak content. TO be sure they are right in my code I copy paste them from what PhpSpreadsheet found as sheets.

I don't understand why you have a an Exception on getIndex and not me.For me on the stackTrace it is locked at the save function

[Grandpied33]

I've tried to take a look at my code :

I can see all the sheeths before deleting them. Then if I dump sheet after the removed some, I Can see there is still one. That's why I can't understand why it broke on save...

[oleibman]

I understand the need to not leak your customer's content, but, since I can't duplicate your symptom, I really need an example of a spreadsheet which is failing in this manner before I can investigate further.

[Grandpied33]

Hey @oleibman

I'm sorry for the delay. I have check with the customer and he is okay to share a file with you.
ExcelExemple.xlsx

And here the function where i delete Sheets :

    public function removeSheets($spreadsheet, $uuid, $path)
    {
        /**
         * @var $worksheet Worksheet
         */

        /**
         * @var $spreadsheet Spreadsheet
         */

        dump($spreadsheet->getAllSheets());
        $sheetIndex = $spreadsheet->getIndex(
            $spreadsheet->getSheetByName('Liste_UO')
        );
        $spreadsheet->removeSheetByIndex($sheetIndex);
        $sheetIndex = $spreadsheet->getIndex(
            $spreadsheet->getSheetByName('BPU')
        );
        $spreadsheet->removeSheetByIndex($sheetIndex);

        try {
            $spreadsheet->getSheetByName('Devis');
            $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
            $writer->save($path.'/'.$uuid.'.xlsx');
        }catch (Exception $e)
        {
            echo $e;
        }
        return $uuid;

    }

Still the error on the save function.
Thank you :)

[oleibman]

Thank you for providing the sample.
After you delete Liste_UO, a number of references on Devis, which you are keeping, become invalid, e.g. the formula for B20 contains a reference to the now-deleted sheet. There is also a defined name on Liste_UO which becomes invalid. It is not yet clear to me which of these is actually the problem, nor what the exact problem is. My hope for a quick-and-dirty workaround (setPreCalculateFormulas(false)) was not successful. Researching.

[Grandpied33]

Thank you for this fast answer.
I have other files where I have to perform the same actions including removed sheets, they are built on the same model. If the problem persist do you think it can be helpful for you ?

[Grandpied33]

Hey there.
Do you know when it will be avaible on the latest release ? or not ?

My company need it for a project and we are ready to support project or to buy support (if you have one) to be ready for the project release.

[PowerKiKi]

Fixed in 1.19.0