How can I resolve “Allowed memory size exhausted”

Question

I´m working with phpspreadsheet and I want to modify an xlsx file with 4 sheets. I only want to insert data in 2 sheets, but I want to copy all 4 sheets to the new xlsx file. When I do that I get this error:

Fatal error: Allowed memory size of 536870912 bytes exhausted (tried to allocate 20480 bytes)

This is because one of that sheets is to heavy, but that heavy sheet I only want to copy it, not modify it. I have tried loading this:

ini_set('memory_limit', -1);

But it doesn´t work for me, because it goes out of defined runtime (more than 120 seconds).

I have also try this:

$inputFileType = 'Xls';
$inputFileName = './sampleData/example1.xls';
$sheetnames = ['Data Sheet #1','Data Sheet #3'];

/**  Create a new Reader of the type defined in $inputFileType  **/
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
/**  Advise the Reader of which WorkSheets we want to load  **/
$reader->setLoadSheetsOnly($sheetnames);
/**  Load $inputFileName to a Spreadsheet Object  **/
$spreadsheet = $reader->load($inputFileName);

But this only copies to the new file the specified sheets.

EDIT: I have tried to copy the sheets that I don´t need to edit with the following code:

$spreadsheet1 =\PhpOffice\PhpSpreadsheet\IOFactory::load("./sampleData/example1.xls");
$clonedWorksheet = clone $spreadsheet1->getSheetByName('Data Sheet #2 ');
$clonedWorksheet->setTitle('Test');
$spreadsheet->addSheet($clonedWorksheet);

But now I get another error:

Fatal error: Uncaught Error: Call to a member function getCell() on null in C:\xampp\htdocs\OfferConfigurator\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Calculation\Calculation.php:2785 Stack trace: #0 C:\xampp\htdocs\OfferConfigurator\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Cell\Cell.php(262): PhpOffice\PhpSpreadsheet\Calculation\Calculation->calculateCellValue(Object(PhpOffice\PhpSpreadsheet\Cell\Cell), true) #1 C:\xampp\htdocs\OfferConfigurator\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Writer\Xlsx\Worksheet.php(1077): PhpOffice\PhpSpreadsheet\Cell\Cell->getCalculatedValue() #2 C:\xampp\htdocs\OfferConfigurator\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Writer\Xlsx\Worksheet.php(1027): PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet->writeCell(Object(PhpOffice\PhpSpreadsheet\Shared\XMLWriter), Object(PhpOffice\PhpSpreadsheet\Worksheet\Worksheet), 'M7', Array) #3 C:\xampp\htdocs\OfferConfigurator\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Writer\Xlsx\Worksheet.php(76): PhpOffice in C:\xampp\htdocs\OfferConfigurator\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Calculation\Calculation.php on line 2785

I think that this is because with clone you can´t copy formulas. Can this be the reason? Is it another solution to copy a sheet with formulas?

Answer 1

Finally I have found the solution. The problem was on the writer and the formulas, so have add this line to the code and now it works well:

$writer->setPreCalculateFormulas(false);

So the all the code for the writer now is:

$writer = new Xlsx($spreadsheet);
$writer->setPreCalculateFormulas(false);
$writer->save('test.xlsx');