PHP Fatal error: Allowed memory size of 536870912 bytes exhausted

[keehao]

This is:

- [ ] a bug report

What is the expected behavior?

PHP Fatal error: Allowed memory size of 536870912 bytes exhausted

What is the current behavior?

the 'test.xlsx' is 70M, and has 2,000,000 rows data

What are the steps to reproduce?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php

$inputFileName =  '/test.xlsx';
$reader = IOFactory::createReader('Xlsx');
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load($inputFileName);
$worksheet = $spreadsheet->getActiveSheet();

Which versions of PhpSpreadsheet and PHP are affected?

php7.4 , latest phpspreadsheet version

[eigan]

Hey @keehao. Could you provide the test.xlsx file?

[polppol]

Also happen to me since 1.10.0 and newer
(no problem at all in version 1.9.0)

in my case template file
_ORDER_ASSISTANT_4.xlsx
that contains 1 sheet and it will copy to 2 sheets(max) and fill about 200 rows(no more than this) from

$arrayDatas
example_array.txt

my example php:

<?php

function filled_excel($arrayDatas,$templates = "excel.xlsx")
{
$objReader = IOFactory::createReader('Xlsx');
$objPHPExcel = $objReader->load($templates);

$currentSheetIDX = 0;
$sheet = $objPHPExcel->setActiveSheetIndex($currentSheetIDX);

for($i=0;$i<sizeof($arrayDatas)-1;$i++)
{// MAX size is 2 so only 1 or 2 worksheets
	$copySheet = $sheet->copy();
	$copySheet->setTitle($fileTitle ."_". rand());
	$objPHPExcel->addSheet($copySheet);
	unset($copySheet);
}

foreach ($arrayDatas as $arrayData)
{//for each datasheet will have MAX 200 lines only

	/* INFO HEADER*/
	$headInfo = 'E1';

	/* DATA ROW */
	$baseRow = 7; // sample data in line $baseRow - 1

	/* FOOTER HEADER */
	$footerColc = 'A';
	$footerRow = 9;
	
	$sheet = $objPHPExcel->setActiveSheetIndex($currentSheetIDX++);
	$header_txt = new RichText();

	$header_txt->createText("Gen: ".date("Y-m-d H:i:s")." | Group: ".$arrayData['p_group']." | Table: $arrayData[confID]");
	$sheet->setCellValue($headInfo, $header_txt);
	
	$currentRowCount = @sizeof($arrayData['rows']);
	if($currentRowCount>0)
	{
		$sheet->insertNewRowBefore($baseRow,$currentRowCount);
		foreach($arrayData['rows'] as $i => $dataRow)
		{// MAX 200 rows
			$row = $baseRow + $i;
			if ($dataRow['p_code_po'] == "") $item_code = $dataRow['p_code'];
			else $item_code = $dataRow['p_code_po'];

			//$item_desc
			$item_desc = new RichText();

			if ($dataRow['p_description_po'] == "") $item_desc->createText($dataRow['p_description']);
			else $item_desc->createText($dataRow['p_description_po']);

			$item_p_order_note = new RichText();
			$item_p_order_note->createText($dataRow['p_order_note']);
			$wROW = ($row - 1);
			$sheet
				//->insertNewRowBefore($row,1)
				->setCellValue('A' . $wROW, $i + 1)
				->setCellValue('B' . $wROW, $item_code)
				->setCellValue('C' . $wROW, $item_desc)
				->setCellValue('D' . $wROW, $dataRow['p_amount_e'])
				->setCellValue('E' . $wROW, $dataRow['total_incomming'])
				->setCellValue('F' . $wROW, $dataRow['avg_9m'])
				->setCellValue('G' . $wROW, $dataRow['avg_6m'])
				->setCellValue('H' . $wROW, $dataRow['avg_3m'])
				->setCellValue('I' . $wROW, $dataRow['forecast_remain_mo'])
				->setCellValue('J' . $wROW, $dataRow['forecast_order_recommend'])
				->setCellValue('K' . $wROW, '')
				->setCellValue('L' . $wROW, '')
				->setCellValue('M' . $wROW, $dataRow['p_ea_per_ctn'])
				->setCellValue('N' . $wROW, $item_p_order_note)//


				->setCellValue('O' . $wROW, $dataRow['alert_group'])
				->setCellValue('P' . $wROW, $dataRow['p_cbm_per_ctn'])
				->setCellValue('Q' . $wROW, $dataRow['p_kg_per_ctn'])
				->setCellValue('R' . $wROW, "=(L$wROW*P$wROW)+((P$wROW/M$wROW)*K$wROW)")//autocalc amount
				->setCellValue('S' . $wROW, "=(L$wROW*Q$wROW)+((Q$wROW/M$wROW)*K$wROW)") //autocalc amount

				->setCellValue('T' . $wROW, $dataRow['price_pre_ea']) //autocalc amount
				->setCellValue('U' . $wROW, "=(K$wROW*T$wROW)+((T$wROW*M$wROW)*L$wROW)") //autocalc amount
			;

			$sheet->getStyle('C' . ($row - 1))->getAlignment()->setWrapText(true);
			$sheet->getStyle('N' . ($row - 1))->getAlignment()->setWrapText(true);

			$footerRow++;
			//break;

		}
	}
}

//remove dummy row LAST
$sheet->removeRow($row,1);

// Rename worksheet
$sheet->setTitle($fileTitle."(".$arrayData['confID'].")");
}

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

$memory_get_peak_usage = (memory_get_peak_usage(true) / 1024 / 1024);
$outFileName = $fileTitle."_".$p_group."_".date("Y-m-d").".xlsx";

//////////////////////////////////////////////////////////
// Redirect output to a client’s web browser 
//////////////////////////////////////////////////////////

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header("Content-Disposition: attachment;filename=\"$outFileName\"");
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0

$objWriter = IOFactory::createWriter($objPHPExcel, 'Xlsx');
$objWriter->setPreCalculateFormulas(true);
$objWriter->save('php://output');

$objPHPExcel->disconnectWorksheets();
$objPHPExcel->garbageCollect();
unset($objWriter, $objPHPExcel);
exit;
}

[brainz80]

@polppol looking at your example_data.txt, I believe that your problem might be the same as mine #1792 . I have a version that fixes my problem available here: https://github.com/brainz80/PhpSpreadsheet .

My problem was that numbers that had many decimals would cause a function inside PHPSpreadsheet to endlessly loop - I fixed this by adding a second condition to that loop.

[polppol]

@brainz80 Thanks! I will try this

[ajpgtech]

try this workaround

    $writer = new Xlsx($outputSpreadsheet);
    $writer->setPreCalculateFormulas(false);
    $writer->save($outFileName);

[Danbardo]

Did you guys ever find a solution to this?
I only need to read spreadsheets.
The peak memory usage for a 2MB spreadsheet was 434MB.
The peak memory for the same file with all but 30 rows deleted (files size 82KB) was 652MB.
I think the issue may be that a while background has been applied to all rows and PhpSpreadsheet is parsing them, even though they're empty.
This solved the partially problem for me:
$reader->setReadDataOnly(true); $reader->setReadEmptyCells(false);
Now the peak memory usage for a 2MB spreadsheet was 22MB.
The peak memory for the same file with all but 30 rows deleted (files size 82KB) was 2MB.

[polppol]

Did you guys ever find a solution to this? I only need to read spreadsheets. The peak memory usage for a 2MB spreadsheet was 434MB. The peak memory for the same file with all but 30 rows deleted (files size 82KB) was 652MB. I think the issue may be that a while background has been applied to all rows and PhpSpreadsheet is parsing them, even though they're empty. This solved the partially problem for me: $reader->setReadDataOnly(true); $reader->setReadEmptyCells(false); Now the peak memory usage for a 2MB spreadsheet was 22MB. The peak memory for the same file with all but 30 rows deleted (files size 82KB) was 2MB.

currently I use v. 1.9.0 which is not have this problem

[boily]

For read only you can try with:
$reader->setReadDataOnly(true);