phpExcel exported a large number of data memory overflow error solution

  • 2020-05-30 19:44:55
  • OfStack

phpExcel stores the read cell information in memory, which we can access


To set up different caching methods, has achieved the goal of reducing memory consumption!

1. Serialize the cell data and save it in memory


2. Serialize the cells, compress them with Gzip, and save them in memory


3, cache in a temporary disk file, the speed may be slow 1


4. Save in php://temp


5. Save in memcache


For example:

Method 4:

$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;  
$cacheSettings = array( ' memoryCacheSize '  => '8MB'  
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings); 

5 a:

$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_memcache;  
$cacheSettings = array( 'memcacheServer'  => 'localhost',  
                        'memcachePort'    => 11211,  
                        'cacheTime'       => 600  
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

Other ways

For the first method, you can consider generating multiple sheet instead of multiple excel files. Calculate how many lines are exported for each sheet according to the total amount of your data. The following is how to generate multiple sheet methods for PHPExcel:

Faces are PHPExcel to generate multiple sheet methods:

$sheet = $objPHPExcel->getActiveSheet(); 

For the second method, you can consider ajax to export in batches without refreshing the page each time.

<a href="#" id="export">export to Excel</a> 
$('#export').click(function() {  
        url: "export.php",   
        data: getData(),  // This is the place where you can stay php Get in, 1 General read database   
        success: function(response){  
            window.location.href = response.url;  


If there is a large amount of data, it is suggested to use the second method, ajax, to export the data. The above method simply gives a process, and you can supplement it by yourself!

Related articles: