Phpexcel exporting a large number of data timeouts and memory error resolution methods

Transferred from: http://lhdst-163-com.iteye.com/blog/2149653

PHP export Excel Relatively many children's shoes have met, the use of Phpexcel class is also really convenient, but the export of big data when it is not so simple, often accompanied by some timeouts or memory overflow problems, the following will introduce some methods, common learning, common progress ...

Phpexcel is a powerful PHP open source class that handles Excel, but a big problem is that it takes up too much memory, Starting with 1.7.3, it supports setting up the cell cache, but it is recommended to use the current stable version 1.7.6, because the previous version will have a different degree of bug, the following is its official documentation:

PHPExcel1.7.6 Official Document writes

Phpexcel uses an average of about 1k/cell in your worksheets, so large workbooks can quickly use up available memory. Cell caching provides a mechanism that allows phpexcel to maintain the cell objects in a smaller size of memory, on disk, Or in APC, memcache or Wincache, rather than in PHP memory. This allows-to-reduce the memory usage for large workbooks, although at a cost of speed to access cell data.

Phpexcel averages the memory using 1k/cells, so large documents can cause memory consumption to be very fast. The cell cache mechanism allows Phpexcel to cache small cell objects in memory on disk or Apc,memcache or wincache, although it consumes some time to read the data, but can help you reduce memory consumption.

PHPExcel1.76. Official documentation writes

By default, the Phpexcel still holds all cells objects in memory, but can specify alternatives.   To enable cell caching, must call the Phpexcel_settings::setcachestoragemethod () method, passing in the caching method That's wish to use.

By default, Phpexcel still saves the cell object in memory, but you can customize it. You can use the Phpexcel_settings::setcachestoragemethod () method to pass caching as a parameter to this method to set the caching mode.

PHP Code:

$cacheMethod = phpexcel_cachedobjectstoragefactory::cache_in_memory;

Phpexcel_settings::setcachestoragemethod ($cacheMethod);

PHPExcel1.7.6 Official Document writes

Setcachestoragemethod () would return a Boolean true on success, false on failure (for example if trying to cache to APC When APC was not enabled).

The Setcachestoragemethod () method returns a bool variable to indicate whether the setting was successful (for example, if APC is not available, you set the APC cache to return false)

PHPExcel1.7.6 Official Document writes

A separate cache is maintained for each individual worksheet, and are automatically created when the worksheet is Instan Tiated based on the caching method and settings so you have configured. You cannot change the configuration settings once and started to read a workbook, or has created your first wo Rksheet.

Each worksheet will have a separate cache, and when an worksheet is instantiated, it is automatically created based on how the settings or configurations are cached. Once you start reading a file or you have created the first worksheet, you cannot change the way the cache is changed.

PHPExcel1.7.6 Official Document writes

Currently, the following caching methods is available.

Currently, there are several ways to use the following caching methods:

PHP Code:

Phpexcel_cachedobjectstoragefactory::cache_in_memory;

PHPExcel1.7.6 Official Document writes

The default. If you don ' t initialise any caching method, then this is the method of that Phpexcel would use. Cell objects is maintained in PHP memory as at present.

By default, if you do not initialize any of the caching methods, Phpexcel will use the way the memory is cached.

===============================================

PHP Code:

phpexcel_cachedobjectstoragefactory::cache_in_memory_serialized;

PHPExcle1.7.6 Official Document writes

Using This caching method, cells is held in PHP memory as an array of serialized objects, which reduces the memory foo Tprint with minimal performance overhead.

Using this caching method, cells are stored in memory in a serialized manner, which is a scenario that reduces memory utilization performance.

===============================================

PHP Code:

Phpexcel_cachedobjectstoragefactory::cache_in_memory_gzip;

PHPExcel1.7.6 Official Document writes

Like Cache_in_memory_serialized, the method holds cells in PHP memory as an array of serialized objects, but-gzipped t o Reduce the memory usage still further, although access to read or write a cell is slightly slower.

Similar to serialization, this method, after serialization, is then gzip compressed and then put into memory, which further reduces the use of memory, but there are some slow reads and writes.

===========================================================

PHP Code:

Phpexcel_cachedobjectstoragefactory::cache_to_discisam;

PHPExcel1.7.6 Official Document writes

When using the Cache_to_discisam all cells is held in a temporary disk file with a index to their Ile maintained in PHP memory. This was slower than any of the cache_in_memory methods, but significantly reduces the memory footprint. The temporary disk file is automatically deleted when your script terminates.

When using Cache_to_discisam this way, all cells will be saved in a temporary disk file, only their location in the file in PHP memory, which is slower than any kind of cache in memory, but can significantly reduce the use of memory. Temporary disk files are automatically deleted at the end of the script run.

===========================================================

PHP Code:

Phpexcel_cachedobjectstoragefactory::cache_to_phptemp;

PHPExcel1.7.6 Official Document writes

Like Cache_to_discisam, when using cache_to_phptemp all cells is held in the php://temp I/O stream, with a index t o their location maintained in PHP memory. In PHP, the Php://memory wrapper stores data in the memory:php://temp behaves similarly, but uses a temporary file for s Toring the data when a certain memory limit is reached. The default is 1 MB, and you can change this when initialising cache_to_phptemp.

Similar to Cache_to_discisam, when using cache_to_phptemp, all cells are still present in the php://temp I/O stream, and only their location is stored in PHP memory. PHP's Php://memory wrapper stores the data in memory, Php://temp behaves similarly, but when the stored data size exceeds the memory limit, the data is saved in a temporary file, the default size is 1MB, but you can modify it at initialization time:

PHP Code:

$cacheMethod = phpexcel_cachedobjectstoragefactory:: cache_to_phptemp;

$cacheSettings = Array (' memorycachesize ' = ' 8MB ');

Phpexcel_settings::setcachestoragemethod ($cacheMethod, $cacheSettings);

PHPExcel1.7.6 Official Document writes

The Php://temp file is automatically deleted when your script terminates.

The Php://temp file is automatically deleted at the end of the script.

===========================================================

PHP Code:

PHPEXCEL_CACHEDOBJECTSTORAGEFACTORY::CACHE_TO_APC;

PHPExcle1.7.6 Official Document writes

When using CACHE_TO_APC, cell objects is maintained in APC with only a index maintained in PHP memory to identify that The cell exists. By default, a APC cache timeout of seconds is used, which should being enough for most applications:although it's POS Sible to Initialising CACHE_TO_APC.

When CACH_TO_APC is used, the cell is saved in APC, and the index is saved only in memory. APC caches the default timeout time of 600 seconds, which is sufficient for most applications, and of course you can modify it at initialization time:

PHP Code:



$cacheMethod = PHPEXCEL_CACHEDOBJECTSTORAGEFACTORY::CACHE_TO_APC;

$cacheSettings = Array (' cacheTime ' = 600);

Phpexcel_settings::setcachestoragemethod ($cacheMethod, $cacheSettings);

PHPExcel1.7.6 Official Document writes

When your script terminates all entries would be a cleared from APC, regardless of the cacheTime value, so it cannot is use D for persistent storage using the this mechanism.

When the script finishes running, all the data is clear from the APC (ignoring the cache time) and cannot be used as a persistent cache.

===========================================================

PHP Code:



Phpexcel_cachedobjectstoragefactory::cache_to_memcache

PHPExcel1.7.6 Official Document writes

When using Cache_to_memcache, cell objects is maintained in Memcache with only a index maintained in PHP memory to Iden Tify that the cell exists.

By default, the Phpexcel looks for a memcache server on localhost at Port 11211. It also sets a memcache timeout limit of seconds. If you is running memcache on a different server or port and then you can change these defaults when you initialise cache_ To_memcache:

When Cache_to_memory is used, the cell object is saved in Memcache, and the index is saved in memory only. By default, Phpexcel will look for the memcache service on localhost and Port 11211, time out for 600 seconds, and if you run the Memcache service on a different server or other port, you can modify it at initialization:

PHP Code:

$cacheMethod = Phpexcel_cachedobjectstoragefactory::cache_to_memcache;

$cacheSettings = Array (' memcacheserver ' = ' localhost ',

' Memcacheport ' = 11211,

' CacheTime ' = 600

);

Phpexcel_settings::setcachestoragemethod ($cacheMethod, $cacheSettings);

From the initial setting of the formal view, MS also does not support multiple Memcache server polling method, it is regrettable.

PHPExcel1.7.6 Official Document writes

When your script terminates all entries'll be cleared from memcache, regardless of the cacheTime value, so it cannot b E used for persistent storage using the this mechanism.

When the script ends, all data is emptied from memcache (ignoring cache time) and cannot be persisted using this mechanism.

===========================================================

PHP Code:

Phpexcel_cachedobjectstoragefactory::cache_to_wincache;

PHPExcel1.7.6 Official Document writes

When using Cache_to_wincache, cell objects is maintained in Wincache with only a index maintained in PHP memory to IDE Ntify that the cell exists. By default, a Wincache cache timeout of seconds was used, which should be enough for most applications:although it I s possible to initialising Cache_to_wincache.

Using the Cache_towincache method, the Cell object is saved in Wincache, the index is saved in memory only, and by default the Wincache expires at 600 seconds, which is sufficient for most applications and can of course be modified at initialization time:

PHP Code:

$cacheMethod = Phpexcel_cachedobjectstoragefactory::cache_to_wincache;

$cacheSettings = Array (' cacheTime ' = 600);

Phpexcel_settings::setcachestoragemethod ($cacheMethod, $cacheSettings);

Phpexcel Official Document 1.7.6 wrote

When your script terminates all entries'll be cleared from Wincache, regardless of the cacheTime value, so it cannot b E used for persistent storage using the this mechanism.

Phpexcel export a large number of data timeouts and memory error resolution (RPM)