PHPExcel export large amount of data timeout and memory error solution

Exporting excel with PHP is something that many children have encountered. It is really convenient to use the phpexcel class, but it is not that simple when exporting big data. It is often accompanied by some timeout or memory overflow problems. Here are some methods to introduce to you. Learn and make progress together. . .

PHPExcel is a very powerful PHP open source class for processing Excel, but a big problem is that it takes up too much memory. Starting from 1.7.3, it supports setting the cell cache method, but it is recommended to use the currently stable version 1.7 .6, because previous versions have bugs to varying degrees, the following is its official document:

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 you to reduce the memory usage for large workbooks, although at a cost of speed to access cell data.

PHPExcel uses 1k/cell of memory on average, so large documents will consume memory very quickly. The cell caching mechanism allows PHPExcel to cache small cell objects in memory on disk or in APC, memcache or Wincache. Although it will take some time to read the data, it can help you reduce memory consumption.

PHPExcel1.76. The official documentation writes

By default, PHPExcel still holds all cell objects in memory, but you can specify alternatives. To enable cell caching, you must call the PHPExcel_Settings::setCacheStorageMethod() method, passing in the caching method that you wish to use.

By default, PHPExcel still saves cell objects in memory, but you can customize it. You can use the PHPExcel_Settings::setCacheStorageMethod() method and pass the caching method as a parameter to this method to set the caching method.

Php code:

$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory;

PHPExcel_Settings::setCacheStorageMethod($cacheMethod);

PHPExcel1.7.6 official document writes

setC acheStorageMethod() will return a boolean true on success, false on failure (for example if trying to cache to APC when APC is not enabled). The

setCacheStorageMethod() method will return a BOOL variable to indicate whether the setting is successful (for example, if APC cannot be used, you set to use APC cache , will return false)

PHPExcel1.7.6 official documentation writes

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

Each worksheet will have an independent cache. When a worksheet is instantiated, it will automatically cache according to the settings or configuration. create. Once you start reading a file or you have created your first worksheet, you cannot change the caching method.

PHPExcel1.7.6 official documentation writes

Currently, the following caching methods are available.

Currently, the following caching methods are available:

Php code:

PHPExcel_CachedObjectStorageFactory::cache_in_memory;

PHPExcel1. 7.6 The official documentation writes

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

The default. If you don't initialize any caching method, then this is the method that PHPExcel will use. Any caching method, PHPExcel will use the memory caching method.

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

Php code:

PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;

PHPExcle1.7.6 official documentation writes

Using this caching method, cells are held in PHP memory as an array of serialized objects, which reduces the memory footprint with minimal performance overhead.

Using this caching method, cells will be stored in memory in a serialized manner, which is a high-performance solution to reduce memory usage.

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

Php code:

PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;

PHPExcel1.7.6 official documentation writes

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

Similar to the serialization method, this method performs gzip compression after serialization and then puts it into the memory. This time it further reduces the memory usage, but it will be a little slower when reading and writing.

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

Php code:

PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;

PHPExcel1.7.6 official document writes

When using cache_to_discISAM all cells are held in a temporary disk file, with only an index to their location in that file maintained in PHP memory. This is 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. , all cells will be saved in a temporary disk file, and only their position in the file will be saved in PHP's memory. This will be slower than any in-memory cache, but it can show Significantly reduces memory usage. The temporary disk file will be automatically deleted when the script ends.

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

Php code:

PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;

PHPExcel1.7.6 official document writes

Like cache_to_discISAM, when using cache_to_phpTemp all cells are held in the php:// temp I/O stream, with only an index to 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 storing the data when a certain memory limit is reached. The default is 1 MB, but you can change this when initialising cache_to_phpTemp.

Similar to cache_to_discISAM, when using cache_to_phpTemp, all cells will still exist in php://temp I/ O streams, only their positions are saved in PHP's memory. PHP's php://memory wrapper saves data in memory. php://temp behaves similarly, but when the size of the stored data exceeds the memory limit, the data will be saved in a temporary file. The default size is 1MB. , but you can modify it during initialization:

Php code:

$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;

$cacheSettings = array( ' memoryCacheSize ' => '8MB' );

PHPExcel_Settings::setCacheS torageMethod($ cacheMethod, $cacheSettings);

PHPExcel1.7.6 official documentation writes

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

php://temp file will be automatically deleted when the script ends.

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

Php code:

PHPExcel_CachedObjectStorageFactory::cache_to_apc;

PHPExcle1.7.6 official document writes

When using cache_to_apc, cell objects are maintained in APC with only an index maintained in PHP memory to identify that the cell exists. By default, an APC cache timeout of 600 seconds is used, which should be enough for most applications: although it is possible to change this when initialising cache_to_APC.

When using cache_to_apc, the cell Saved in APC, only the index is saved in memory. The default APC cache timeout is 600 seconds, which is sufficient for most applications. Of course, you can also modify it during initialization:

Php code:



$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_APC;

$cacheSettings = array( 'cacheTime' => 600 );

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

PHPExcel1.7.6 official documentation writes

When your script terminates all entries will be cleared from APC, regardless of the cacheTime value, so it cannot be used for persistent storage using this mechanism.

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

Php code:



PHPExcel_CachedObjectStorageFactory::cache_to_memcache

PHPExcel1.7.6 official document writes

When using cache_to_memcache, cell objects are maintained in memcache with only an index maintained in PHP memory to identify that the cell exists.

By default, PHPExcel looks for a memcache server on localhost at port 11211. It also sets a memcache timeout limit of 600 seconds. If you are running memcache on a different server or port, then you can change these defaults when you initialise cache_to_memcache:

When using cache_to_memory, the cell object is saved in memcache and only the index is saved in memory. By default, PHPExcel will look for the memcache service on localhost and port 11211, with a timeout of 600 seconds. If you run the memcache service on other servers or other ports, you can modify it during initialization:

Php code:

$cacheMethod = PHPExcel_CachedObjectStorageFactory ::cache_to_memcache;

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

'memcachePort' => 11211,

'cacheTime' => 600

);

PHPEx cel_Settings::setCacheStorageMethod($cacheMethod , $cacheSettings);

From the perspective of initialization settings, MS does not yet support the polling method of multiple memcache servers, which is a pity.

PHPExcel1.7.6 official documentation writes

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

When your script terminates, all entries will be cleared from memcache, regardless of the cacheTime value, so The data will be cleared from memcache (ignoring the cache time), and this mechanism cannot be used for persistent storage.

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

Php code:

PHPExcel_CachedObjectStorageFactory::cache_to_wincache;

PHPExcel1.7.6 official document writes

When using cache_to_wincache, cell objects are maintained in Wincache with only an index maintained in PHP memory to identify that the cell exists. By default, a Wincache cache timeout of 600 seconds is used, which should be enough for most applications: although it is possible to change this when initialising cache_to_wincache.

Use cache_towincache method, cell object It will be saved in Wincache, and only the index will be saved in memory. By default, the Wincache expiration time is 600 seconds, which is enough for most applications. Of course, it can also be modified during initialization:

Php code:

$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_wincache;

$cacheSettings = array( 'cacheTime' => 600 );

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

PHPExcel official documentation 1.7.6 wrote

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

PHPExcel is still relatively powerful. The biggest problem is the memory usage. When will PHPExcel have a lightweight version? The large-scale version does not require so many fancy functions, it only needs to export the most common data version!

The above content is a relatively good article that LZ found on the Internet. Although it did not completely solve my problem, I feel that it is well written. I would like to take this as a note and hope it can be helpful to the children's shoes I see!