What should I do if a 500 error occurs when php exports excel?

Solution to the 500 error when exporting excel in php: Use the [PHPExcel_Settings::setCacheStorageMethod()] method to pass the cache method as a parameter to the method to set the cache.

The operating environment of this article: windows10 system, php 7, thinkpad t480 computer.

I think many friends have encountered the problem of exporting excel. Maybe we usually find it convenient when using the phpexcel class. But you will find that it is not that simple when exporting a large amount of data. The export is often accompanied by some timeout or memory overflow problems. Let’s take a look at the solution below.

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 current The stable version 1.7.6, because previous versions have bugs to varying degrees.

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.

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

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

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

Currently, the following caching methods are available:

Php code:

PHPExcel_CachedObjectStorageFactory::cache_in_memory;

By default, if you do not initialize any caching method, PHPExcel will use the memory cache The way.

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

Php code:

PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;

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

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

Php code:

PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;

Similar to the serialization method, this method performs gzip compression after serialization and then puts it into the memory, which will further reduce the cost. Memory usage, but there will be some slowness when reading and writing.

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

Php code:

PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;

When using cache_to_discISAM, all cells will be saved in a temporary Disk files, only their location in the file is stored in PHP's memory. This is slower than any in-memory cache, but can significantly reduce memory usage. The temporary disk file will be automatically deleted when the script ends.

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

Php code:

PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;

Similar to cache_to_discISAM, when using cache_to_phpTemp, all cells will still have php: //temp I/O streams, only store their positions 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::setCacheStorageMethod($cacheMethod, $cacheSettings);
php://temp文件在脚本结束是会自动删除。

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

Php code:

PHPExcel_CachedObjectStorageFactory::cache_to_apc;

When using cach_to_apc, the cells are saved in APC and 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);

When the script ends, All data will be cleared from the APC (ignoring cache time) and this mechanism cannot be used as a persistent cache.

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

Php code:

PHPExcel_CachedObjectStorageFactory::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
);
PHPExcel_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.

When the script ends, all 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;

使用cache_towincache方式，单元格对象会保存在Wincache中，只在内存中保存索引，默认情况下Wincache过期时间为600秒，对绝大多数应用是足够了，当然也可以在初始化时修改：

Php代码:

$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_wincache;
$cacheSettings = array(’cacheTime’ => 600);
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

PHPExcel还是比较强大的，最大的问题就是内存占用的问题，PHPExcel啥时候能出一个轻量级的版本，不需要那么多花哨的功能，只需要导出最普通的数据的版本就好了！

推荐学习：php培训

The above is the detailed content of What should I do if a 500 error occurs when php exports excel?. For more information, please follow other related articles on the PHP Chinese website!