PHP output XML (export Excel) data is truncated when it is too long

When you export Excel by using XML, you can export Excel normally when you find a small number (below 1k), but when you increase the number of exports (several K), you find that the exported Excel data is incomplete.

(In my local is good, online server running on the problem ...) )
Check the exported XML data and find that the content is truncated and sometimes the XML tag is incomplete.

Php-xml-excel

The first suspicion is that the content byte of the output is too long, exceeding the upper limit of the environment configuration. Check the Fastcgi_buffer related settings for output_buffering and Nginx, no problem. As with my local value, the modification has no effect.

After the Google, found that there is a similar situation, is the issue of permissions, so to check nginx error log error.log.
The following error records were found in the export operation:

2016/02/04 09:47:53 [Crit] 19027#0: *967389 mkdir () "/tmp/fastcgi_temp/xxx" failed (13:permission denied) while reading u Pstream, Client:xxx.xxx.xx.xxx

It seems that there is a problem with permissions, Nginx to create a/tmp/fastcgi_temp/xxx directory when exporting, but failed.
Run to/tmp under check, found that there is no fastcgi_temp directory!! Then manually create a new directory/tmp/fastcgi_temp/and set owner www, permission 744.

The problem of the XML being truncated has been resolved by running the export again.

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

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

PHPExcel1.7.6 Official document wrote

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

Phpexcel uses the 1k/cell's memory on average, so large documents can lead to memory consumption too quickly. The cell caching mechanism allows Phpexcel to cache small cell objects in memory on disk or Apc,memcache or Wincache, although it may take some time to read the data, but it can help you reduce memory consumption.

PHPExcel1.76. The official document says

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

By default, Phpexcel still keeps cell objects in memory, but you can customize them. 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 wrote

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

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

PHPExcel1.7.6 Official document wrote

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

Each worksheet will have a separate cache, and when a worksheet is instantiated, it is automatically created according to the set or configured caching method. Once you start reading a file or you have created the first worksheet, you can't change the way the cache is going.

PHPExcel1.7.6 Official document wrote

Currently, the following caching methods are available.

Currently, there are several ways to use caching:

PHP Code:

Phpexcel_cachedobjectstoragefactory::cache_in_memory;

PHPExcel1.7.6 Official document wrote

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

By default, if you do not initialize any caching methods, Phpexcel will use the memory caching method.

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

PHP Code:

phpexcel_cachedobjectstoragefactory::cache_in_memory_serialized;

PHPExcle1.7.6 Official document wrote

Using This caching method, cells are held into 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 way, which is a relatively high performance reduction in memory usage.

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

PHP Code:

Phpexcel_cachedobjectstoragefactory::cache_in_memory_gzip;

PHPExcel1.7.6 Official document wrote

Like cache_in_memory_serialized, this method holds cells into 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, after the gzip compression and then into the memory, which will further reduce the use of memory, but read and write some slow.

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

PHP Code:

Phpexcel_cachedobjectstoragefactory::cache_to_discisam;

PHPExcel1.7.6 Official document wrote

When using the Cache_to_discisam all cells are held in a temporary disk file, with the only ' index to ' their location in ' that F Ile 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.

When you use Cache_to_discisam this way, all cells will be saved in a temporary disk file, and 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 wrote

Like Cache_to_discisam, as using cache_to_phptemp all cells are held in the php://temp I/O stream, with only 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 but you can change this when initialising cache_to_phptemp.

Like Cache_to_discisam this way, when using cache_to_phptemp, all cells will still exist in the php://temp I/O stream, keeping their location in PHP memory only. 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 wrote

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 wrote

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

When using CACH_TO_APC, the cell is saved in the APC and the index is saved in memory only. The APC cache default timeout time of 600 seconds, for most applications is sufficient, of course, you can also modify the initialization:

PHP Code:

$cacheMethod = PHPEXCEL_CACHEDOBJECTSTORAGEFACTORY::CACHE_TO_APC;

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

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

PHPExcel1.7.6 Official document wrote

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

When the script is finished, all 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 wrote

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

By default, Phpexcel looks to a memcache server on localhost at Port 11211. It also sets a memcache timeout limit. If you are are running memcache on a different server or port, then your can change this defaults when you initialise To_memcache:

When you use Cache_to_memory, the Cell object is saved in Memcache and the index is saved in memory only. By default, Phpexcel will look for the memcache service in localhost and port 11211, timeout 600 seconds, and if you are running the Memcache service on another server or other port, you can modify it at initialization time:

PHP Code:

$cacheMethod = Phpexcel_cachedobjectstoragefactory::cache_to_memcache;

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

' Memcacheport ' => 11211,

' CacheTime ' => 600

);

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

From the form of initialization settings, MS does not support multiple Memcache server polling way, more regrettable.

PHPExcel1.7.6 Official document wrote

When your script terminates all entries is cleared from Memcache, regardless to the cacheTime value, so it cannot b E used for persistent storage the using this mechanism.

When the script ends, all the data is emptied from the memcache (ignoring the cache time) and cannot be used for persistent storage.

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

PHP Code:

Phpexcel_cachedobjectstoragefactory::cache_to_wincache;

PHPExcel1.7.6 Official document wrote

When using Cache_to_wincache, the cell objects are maintained in Wincache and only a index maintained in PHP memory to IDE Ntify that the cell exists. By default, a Wincache cache timeout The seconds is used, which should to 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, only the index is saved in memory, and by default the Wincache expires at 600 seconds, which is sufficient for most applications, and can be modified at initialization time as well:

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 is cleared from Wincache, regardless to the cacheTime value, so it cannot b E used for persistent storage the using this mechanism.

Phpexcel or more powerful, the biggest problem is the memory footprint, Phpexcel when a lightweight version, do not need so many fancy features, only need to export the most common version of the data is good!