Error copying sheets in phpexcel

Question

I am trying to extract some sheets in phpexcel as follows (see https://stackoverflow.com/a/10587576/813801 for reference).

        $newObjPHPExcel = new PHPExcel();
        $newObjPHPExcel->removeSheetByIndex(0); //remove first default sheet

        foreach ($sheets as $sheetIndex) {
            echo "trying-$sheetIndex\n";
            $workSheet = $objPHPExcel->getSheet($sheetIndex);
            echo "done-$sheetIndex\n";
            $newObjPHPExcel->addExternalSheet($workSheet);
        }

(sheets is an array of indexes which are within the bounds of the sheet. I checked with listWorksheetInfo)

If I comment out the the last line $newObjPHPExcel->addExternalSheet($workSheet);

The getSheet method works fine. Otherwise, I get an error:

Fatal error: Uncaught exception 'PHPExcel_Exception' with message 'Your requested sheet index: 2 is out of bounds. The actual number of sheets is 1.' in /Xls/PHPExcel/PHPExcel.php:577

Why should newObjPHPExcel interfere with objPHPExcel?

UPDATE: I found a workaround which seems to work. not sure why the other version did not work though.

        $newObjPHPExcel = new PHPExcel();
        $newObjPHPExcel->removeSheetByIndex(0); //remove first default sheet

        foreach ($sheets as $sheetIndex) {
            echo "trying-$sheetIndex\n";
            $workSheet[] = $objPHPExcel->getSheet($sheetIndex);
            echo "done-$sheetIndex\n";
        }

        foreach ($workSheet as $obj)
            $newObjPHPExcel->addExternalSheet($obj);

Mark Baker Mark Baker · Accepted Answer · 2014-12-25T13:09:30

The addExternalSheet() method actually removes the worksheet from the old workbook and moves it to the new one, but your iterator over the old workbook collection of sheets still believes that it contains that worksheet when it no longer does.

In your second "workround" code, you're not removing the sheets from the old workbook until after you've completed iterating over the first loop, you're simply setting an array of pointers to the sheets, then iterating over that array, so the pointer array doesn't care that the sheets are being moved from one workbook to another, they all still exists, so no error.

An alternative might be to use the worksheet iterator against the old workbook, which should update cleanly as the sheets are removed.

Error copying sheets in phpexcel

1 Answers