Solution to memory overflow error when phpExcel exports large amounts of data

Solution to memory overflow error when phpExcel exports large amounts of data_PHP Tutorial

WBOY

Release： 2016-07-13 17:06:01

Original

944 people have browsed it

We often use phpExcel to import or import xls files, but if the exported data is relatively large at one time, a memory overflow error will occur. Let me summarize the solutions below.

phpExcel saves the read cell information in memory, we can pass

The code is as follows

Copy code

代码如下	复制代码
PHPExcel_Settings::setCacheStorageMethod()

PHPExcel_Settings::setCacheStorageMethod()

To set different caching methods, the purpose of reducing memory consumption has been achieved!

1. Serialize the cell data and save it in memory

代码如下	复制代码
PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;

The code is as follows	Copy code
PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;

代码如下	复制代码
PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;

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

The code is as follows

Copy code

PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;

代码如下	复制代码
PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;

3. Caching in temporary disk files may be slower

The code is as follows

Copy code

代码如下	复制代码
PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;

PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;

4. Save in php://temp

The code is as follows	Copy code
PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;

5. Save in memcache

PHPExcel_CachedObjectStorageFactory::cache_to_memcache

代码如下	复制代码
$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp; $cacheSettings = array( ' memoryCacheSize ' => '8MB' ); PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

Example: Medium 4:

The code is as follows	Copy code
$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp; $cacheSettings = array( ' memoryCacheSize ' => '8MB' ); PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

Type 5:

The first method, you can consider generating multiple sheets. You don’t need to generate multiple excel files. Calculate how many rows each sheet exports based on the total amount of your data. The following is how PHPExcel generates multiple sheets:

The code is as follows

代码如下	复制代码
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_memcache; $cacheSettings = array( 'memcacheServer' => 'localhost', 'memcachePort' => 11211, 'cacheTime' => 600 ); PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

Copy code

$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_memcache;

$cacheSettings = array( 'memcacheServer' => 'localhost',

‘memcachePort’ => 11211,

'cacheTime' => 600

代码如下

复制代码

$sheet = $objPHPExcel->getActiveSheet();

$sheet->setCellValue('A1',$x);

$sheet->setCellValue('B1',$y);

);

PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

代码如下

复制代码

export to Excel

$('#export').click(function() {

$.ajax({

url: "export.php",

data: getData(), //这个地方你也可以在php里获取，一般读数据库

success: function(response){

window.location.href = response.url;

}

})

});

代码如下

复制代码

//export.php

$data = $_POST['data'];

$xls = new PHPExcel();

$xls->loadData($formattedData);

$xls->exportToFile('excel.xls');

$response = array(

'success' => true,

'url' => $url

);

header('Content-type: application/json');

echo json_encode($response);

Other methods

The following is how PHPExcel generates multiple sheets:

The code is as follows

Copy code

$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 every time.

The code is as follows

Copy code

export to Excel $('#export').click(function() { $.ajax({ url: "export.php", data: getData(), //You can also get this place in php, generally read the database success: function(response){ window.location.href = response.url; } } }) });

The code is as follows

Copy code

<🎜>//export.php <🎜> <🎜>$data = $_POST['data'];<🎜> <🎜>$xls = new PHPExcel(); <🎜> <🎜>$xls->loadData($formattedData); $xls->exportToFile('excel.xls'); $response = array( 'success' => true, 'url' => $url ); header('Content-type: application/json'); echo json_encode($response); ?> If the amount of data is large, it is recommended to use the second method, ajax, to export the data. The above method simply gives a process, you can add the details yourself!