phpExcel exported a large number of data memory overflow error solution

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

 
PHPExcel_Settings::setCacheStorageMethod()

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

1. Serialize the cell data and save it in memory

 
PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized; 

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

 
PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip; 

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

 
PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;

4. Save in php://temp

 
PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp; 

5. Save in memcache

PHPExcel_CachedObjectStorageFactory::cache_to_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(); 
$sheet->setCellValue('A1',$x);  
$sheet->setCellValue('B1',$y);

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() {  
    $.ajax({  
        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;  
        }  
    })  
});

 
PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized; 

0

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!