Script can't clear memory to load multiples sheets

Topics: Developer Forum, User Forum
Aug 2, 2011 at 6:13 PM
Edited Aug 2, 2011 at 8:01 PM

Hi,

Im trying to make a script that reads an Excel file, identifies its sheets, and salves each sheet in a new Excel file. I made a loop using

 

$objPHPExcel->disconnectWorksheets();

unset($objPHPExcel);

 

that clear memory after each interaction, and loads a new sheet. But, for some reason, this is not working. I've noticed that the use of disconnectWorksheets and unset(object) actually doesn't make any diference in the memory used, and the script still stop at the same point if I don't use it, showing the Fatal error: Allowed memory size of 67108864 bytes exhausted (tried to allocate 262145 bytes).

 

Here is the detailed code:

 

 

<?php

error_reporting(E_ALL);

date_default_timezone_set('Europe/London');

/** PHPExcel_IOFactory */
require_once '../Classes/PHPExcel/IOFactory.php';

//file name
$inputFileName = 'datafile1.xls';

//identificate filetype
$inputFileType = 'Excel5';

//Create an object to read the file
$objReader = PHPExcel_IOFactory::createReader($inputFileType);

echo date('H:i:s') . " Indexing file... <br>";

//Catch sheets names
$sheets = $objReader->listWorksheetNames($inputFileName);
$n_of_sheets = count($sheets);

//echo info about the sheets
echo date('H:i:s') . " File indexed! <br>";
echo "File has ".$n_of_sheets." sheets:<br>";
foreach ($sheets as $key=>$value):
	echo "\t".$value."<br>";
endforeach;


// Echo memory peak usage
echo "<br>".date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB\r\n";

//Can't use it because dates becomes unformatted.
//$objReader->setReadDataOnly(true);

echo "<br>The script will try to read the sheets separately and save them in separeted files. Working...<br><br>";

foreach ($sheets as $key=>$value):
	//sheet being imported
	$objReader->setLoadSheetsOnly($value);
	
	try {
		//Load sheet
		echo date('H:i:s') . " Loading sheet ".$value."...<br>";
		$objPHPExcel = $objReader->load($inputFileName);
		echo date('H:i:s') . " Sheet ".$value." loaded! <br>";
		
		//Write the file
		echo date('H:i:s') . " Saving sheet on file  ".str_replace('.xls', '', $inputFileName)."_".$value.".xlsx<br>";
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
		$savedir=__DIR__."/".str_replace('.xls', '', $inputFileName)."_".$value.".xlsx";
		$objWriter->save($savedir);
		echo date('H:i:s')." Sheet ".$value." saved successfully!<br><br>";

		//Clear memory
		$objPHPExcel->disconnectWorksheets(); 
   		unset($objPHPExcel);

   		echo "<br>".date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB\r\n";
	} catch(Exception $e) {
		die('Error loading file: '.$e->getMessage());
	}
	
endforeach;



// Echo memory peak usage
echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB\r\n";

// Echo done
echo date('H:i:s') . " Done writing files.\r\n";

?>

 

I can't understand why memory isn't being cleared, but also I don't know if I'm using it right or if is really possible to do what I'm trying to. I was thinking about pass the sheets names by $_SESSION and load the php file one time for each sheet, automatically one after other, so it divides memory consumption, but I don't know if it works that way.

Please, help me!

Aug 2, 2011 at 8:23 PM
Edited Aug 2, 2011 at 8:24 PM

I was trying to solve the problem my self, and I found something really strange: I commented the lines that creates and uses the objWriter, to reduces the operations performed. I know that it doesn't make any sense, because now the script just open a sheet and then the sheet is discarded when I clear up the memory to get more space and read another sheet, but I was just analising the behavior of PHPExcel. 

The strange part, at least for me, is that after I remove the write code, the memory consumption increased instead of decrease. I don't know why. I just commented the lines right under the comment //Write the file.

 

Every help is welcome. ;D

Aug 11, 2011 at 11:31 AM

I am having exactly the same issue.

I only need to read the file, so I'm using the chunkFilter to save on memory, after each chunk I'm trying to disconnecWorksheets, and unset($objPHPExcel) but this does not seem to affect the memory usage in the slightest, meaning I end up with the same number of rows read, as I would if I was just reading the whole file. This seems like a bug of some sort. if anyone knows what might be up, please let me know :)

I've also tried gc_collect_cycles(); to force garbage collection, but it's not helping :(