Memory leak when load excel file

Topics: Developer Forum, User Forum
Sep 17, 2010 at 8:28 AM

Hi everyone,

I've just used phpexcel to read data from an excel file. But the error occurs when load that excel file. I wrote log below. My excel file's  about 130KB.

loading file....
file name: ..../public_html/demo/administrator/components/com_datareport/tempdata/201009172022358.xls
path : ...../public_html/demo/administrator/tempdata/2010082311112936.xls
Fatal error: Allowed memory size of 67108864 bytes exhausted (tried to allocate 114 bytes) in ...../public_html/demo/administrator/includes/PHPExcel/Style.php on line 468

Please help me to solve this problem.

Thank you a lot!

Sep 17, 2010 at 1:55 PM
Edited Sep 17, 2010 at 1:58 PM

Yes, PHPExcel is memory hungry.

File size isn't a good measure for workbook files when working with PHPExcel. The number of rows and columns (ie cells) is more important.

The PHPExcel code itself has a footprint of between 10 and 25MB, depending on which components are being accessed.

At present, each cell in a workbook takes on average 1k of memory (without any caching) or 1.6k on 64-bit PHP - I'll assume 32-bit PHP for the moment - so (for example) a worksheet of 8000 lines with 31 columns (248,000 cells) will be about 242MB. With cell cacheing (such as php://temp or DiskISAM), that can be reduced to about a third, so the 8000 lines by 31 columns will require about 80MB.

As a typical rule of thumb, I'll double that value if you need to write the workbook as well, so that 80MB would become 160MB, plus the code footprint of 10-25M... giving an expected memory requirement of 185MB.

There are a number of options available to help you reduce the memory usage:

Are you using cell caching with PHPExcel?

require_once './Classes/PHPExcel.php'; 
$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp; 
$cacheSettings = array( ' memoryCacheSize ' => '8MB'); 
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings); 
$objReader = PHPExcel_IOFactory::createReader('Excel2007'); 
$objPHPExcel = $objReader->load("test.xlsx"); 

Additionally, there are a host of other techniques for reducing memory usage described in this thread