Phpexcel a memory overflow error solution for exporting large amounts of data _php tips

Phpexcel saves the Read cell information in memory, we can

code as follows:

Phpexcel_settings::setcachestoragemethod ()

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

1, the cell data is serialized and stored in memory

 code as follows:

phpexcel_cachedobjectstoragefactory::cache_in_memory_serialized;

2, after the cell serialization and then gzip compression, and then save in memory

 code as follows:

Phpexcel_cachedobjectstoragefactory::cache_in_memory_gzip;

3, cached in the temporary disk file, the speed may be slower

code as follows:

Phpexcel_cachedobjectstoragefactory::cache_to_discisam;

4, Save in Php://temp

code as follows:

Phpexcel_cachedobjectstoragefactory::cache_to_phptemp;

5, save in the Memcache

code as follows:

Phpexcel_cachedobjectstoragefactory::cache_to_memcache

Example:

4th the way:

 code as follows:

$cacheMethod = phpexcel_cachedobjectstoragefactory:: cache_to_phptemp;
$cacheSettings = Array (' memorycachesize ' => ' 8MB ')
);
Phpexcel_settings::setcachestoragemethod ($cacheMethod, $cacheSettings);

The 5th kind:

 code as follows:

$cacheMethod = Phpexcel_cachedobjectstoragefactory::cache_to_memcache;
$cacheSettings = Array (' memcacheserver ' => ' localhost '),
' Memcacheport ' => 11211,
' CacheTime ' => 600
);
Phpexcel_settings::setcachestoragemethod ($cacheMethod, $cacheSettings);

The other way

The first method, you can consider the way to generate multiple sheet, do not need to generate multiple Excel files, according to your total amount of data to calculate how many rows each sheet export, the following is Phpexcel generate multiple sheet methods:

Faces are phpexcel generating multiple sheet methods:

 code as follows:

$sheet = $objPHPExcel->getactivesheet ();
$sheet->setcellvalue (' A1 ', $x);
$sheet->setcellvalue (' B1 ', $y);

The second method, you can consider Ajax to batch export, do not need to refresh the page every time.

 code as follows:

<a href= "#" id= "Export" >export to excel</a>
$ (' #export '). Click (function () {
$.ajax ({
URL: "export.php",
Data:getdata (),//This place you can also get in PHP, general read database
Success:function (response) {
Window.location.href = Response.url;
}
})
});

code as follows:

<?php
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);
?>

Data volume is very large, we recommend the second method, Ajax to export data, the above method is simple to a process, specifically you add!