PHPExcel Clone Worksheet - Efficient use of memory

Question

I need to clone the first worksheet a few times, accordingly to the amount of rows, but something may be wrong.

The code is:

public function downloadFile()
{
    date_default_timezone_set('America/Sao_Paulo');

    if(file_exists("xpto.xlsx")){

        $objPHPExcel = PHPExcel_IOFactory::load("xpto.xlsx");

        $sheets = 3;//3 is enough to throw the error
        for($i = 0; $i<$sheets; $i++){

            $objClonedWorksheet = clone $objPHPExcel->getSheet(0);

            $objClonedWorksheet->setTitle('Sheet ' . $i);

            $objClonedWorksheet->setCellValue('A1', 'Test ' . $i);

            $objPHPExcel->addSheet($objClonedWorksheet);
        }

        $objPHPExcel->setActiveSheetIndex(0);

        $filename = 'file.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        ob_end_clean();
        $ret = $objWriter->save('php://output');

        exit;
    }
}

But I got an exhausted memory error. Than I tried the most commented solution (that is actually an workaround) that is to add

ini_set('memory_limit', '-1');

I added this line just after the load function and it worked, but I don't think it is a good solution to use on a SaaS application. I don't even think most hosts (AWS, for example) will allow me to use that.

I also tried to clone the sheet before the for loop, but when use addSheet, I realized that this function doesn't create a new object and when I change the name of the sheet (by the second iteration of the for loop), it changes the last sheet created, throwing an "already existing sheet with the same name" error.

Trying to use one of the links @rhazen listed, I changed the for loop to:

    $objFromSheet = $objPHPExcel->getSheet(0);

    $sheets = 3;
    for($i = 1; $i<=$sheets; $i++){

        $objToSheet = $objPHPExcel->createSheet($i);
        foreach($objFromSheet->getRowIterator() as $row){
            $cellIterator = $row->getCellIterator();
            $cellFrom = $cellIterator->current();
            $cellTo = $objToSheet->getCell($cellFrom->getCoordinate());
            $cellTo->setXfIndex($cellFrom->getXfIndex());
            $cellTo->setValue($cellFrom->getValue());
        }
    }

But it seems not to work either. Is there a misunderstanding about Iterator or XfIndex?

Given that the worksheet with its collection of cells takes a lot of memory, it's not surprising that cloning the worksheet increases the memory usage of your script. Consider using [cell caching](https://github.com/PHPOffice/PHPExcel/blob/develop/Documentation/markdown/Overview/04-Configuration-Settings.md) to reduce the memory footprint — Mark Baker, Jul 11 '16 at 08:28
This could be usefull: http://stackoverflow.com/questions/34774486/cloning-the-content-and-styling-of-one-sheet-to-another-php-excel and this one: http://stackoverflow.com/questions/4817651/phpexcel-runs-out-of-256-512-and-also-1024mb-of-ram — rhazen, Jul 11 '16 at 08:28

score 0 · Answer 1 · answered Jul 11 '16 at 09:45

0

The solution is in the edited question. Thanks for those who helped.

answered Jul 11 '16 at 09:45

Carlos Ost

492
7
22

PHPExcel Clone Worksheet - Efficient use of memory

1 Answers1