PHPExcel解決記憶體佔用過大問題-設定儲存格對象緩衝
PHPExcel是一個很強大的處理Excel的PHP開源類,但是很大的一個問題就是它佔用記憶體太大,從1.7.3開始,它支援設定cell的緩衝方式,但是推薦使用目前穩定的版本1.7.6,因為之前的版本都會不同程度的存在bug,以下是其官方文檔:
?
PHPExcel1.7.6官方文檔 寫道
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平均下來使用1k/儲存格的記憶體,因此大的文檔會導致記憶體消耗的也很快。儲存格緩衝機制能夠允許PHPExcel將記憶體中的小的儲存格對象緩衝在磁碟或者APC,memcache或者Wincache中,儘管會在讀取資料上消耗一些時間,但是能夠協助你降低記憶體的消耗。
?
PHPExcel1.76.官方文檔 寫道
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.
?
預設情況下,PHPExcel依然將儲存格對象儲存在記憶體中,但是你可以自訂。你可以使用PHPExcel_Settings::setCacheStorageMethod()方法,將緩衝方式作為參數傳遞給這個方法來設定緩衝的方式。
?
?
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory;PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
?PHPExcel1.7.6官方文檔 寫道
setCacheStorageMethod() will return a boolean true on success, false on failure (for example if trying to cache to APC when APC is not enabled).
?setCacheStorageMethod()方法會返回一個BOOL型變數用於表示是否成功設定(比如,如果APC不能使用的時候,你設定使用APC緩衝,將會返回false)
?
PHPExcel1.7.6官方文檔 寫道
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.
?
每一個worksheet都會有一個獨立的緩衝,當一個worksheet執行個體化時,就會根據設定或配置的緩衝方式來自動建立。一旦你開始讀取一個檔案或者你已經建立了第一個worksheet,就不能在改變緩衝的方式了。
?
PHPExcel1.7.6官方文檔 寫道
Currently, the following caching methods are available.
?目前,有以下幾種緩衝方式可以使用:
?
PHPExcel_CachedObjectStorageFactory::cache_in_memory;
PHPExcel1.7.6官方文檔 寫道
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.
?預設情況下,如果你不初始化任何緩衝方式,PHPExcel將使用記憶體緩衝的方式。
===============================================
?
PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;
PHPExcle1.7.6官方文檔 寫道
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.
?使用這種緩衝方式,儲存格會以序列化的方式儲存在記憶體中,這是降低記憶體使用量率效能比較高的一種方案。
===============================================
?
PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
PHPExcel1.7.6官方文檔 寫道
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.
?與序列化的方式類似,這種方法在序列化之後,又進行gzip壓縮之後再放入記憶體中,這回跟進一步降低記憶體的使用,但是讀取和寫入時會有一些慢。
?
===========================================================
?
PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;
PHPExcel1.7.6官方文檔 寫道
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.
?當使用cache_to_discISAM這種方式時,所有的儲存格將會儲存在一個臨時的磁碟檔案中,只把他們的在檔案中的位置儲存在PHP的記憶體中,這會比任何一種緩衝在記憶體中的方式都慢,但是能顯著的降低記憶體的使用。臨時磁碟檔案在指令碼運行結束是會自動刪除。
?
===========================================================
?
PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
PHPExcel1.7.6官方文檔 寫道
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.
?類似cache_to_discISAM這種方式,使用cache_to_phpTemp時,所有的儲存格會還存在php://temp I/O流中,只把他們的位置儲存在PHP的記憶體中。PHP的php://memory包裹器將資料儲存在記憶體中,php://temp的行為類似,但是當儲存的資料大小超過記憶體限制時,會將資料儲存在臨時檔案中,預設的大小是1MB,但是你可以在初始化時修改它:
$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;$cacheSettings = array( ' memoryCacheSize ' => '8MB' );PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
PHPExcel1.7.6官方文檔 寫道
The php://temp file is automatically deleted when your script terminates.
php://temp檔案在指令碼結束是會自動刪除。
?
?
===========================================================
?
PHPExcel_CachedObjectStorageFactory::cache_to_apc;
PHPExcle1.7.6官方文檔 寫道
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.
?當使用cach_to_apc時,儲存格儲存在APC中,只在記憶體中儲存索引。APC緩衝預設逾時時間時600秒,對絕大多數應用是足夠了,當然你也可以在初始化時進行修改:
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_APC;$cacheSettings = array( 'cacheTime' => 600 );PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
PHPExcel1.7.6官方文檔 寫道
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.
?當指令碼運行結束時,所有的資料都會從APC中清楚(忽略緩衝時間),不能使用此機製作為持久緩衝。
?
?
===========================================================
PHPExcel_CachedObjectStorageFactory::cache_to_memcache
PHPExcel1.7.6官方文檔 寫道
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:
?使用cache_to_memory時,儲存格對象儲存在memcache中,只在記憶體中儲存索引。預設情況下,PHPExcel會在localhost和連接埠11211尋找memcache服務,逾時時間600秒,如果你在其他伺服器或其他連接埠運行memcache服務,可以在初始化時進行修改:
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_memcache;$cacheSettings = array( 'memcacheServer' => 'localhost', 'memcachePort' => 11211, 'cacheTime' => 600 );PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
?從初始化設定的形式上看,MS還不支援多台memcache伺服器輪詢的方式,比較遺憾。
PHPExcel1.7.6官方文檔 寫道
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.
?當指令碼結束時,所有的資料都會從memcache清空(忽略緩衝時間),不能使用該機制進行持久儲存。
?
?
===========================================================
PHPExcel_CachedObjectStorageFactory::cache_to_wincache;
PHPExcel1.7.6官方文檔 寫道
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.
?使用cache_towincache方式,儲存格對象會儲存在Wincache中,只在記憶體中儲存索引,預設情況下Wincache到期時間為600秒,對絕大多數應用是足夠了,當然也可以在初始化時修改:
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_wincache;$cacheSettings = array( 'cacheTime' => 600 );PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
PHPExcel官方文檔1.7.6 寫道
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.
?
?
呃,終於“又”寫完了,之前寫的一版,有的文字是直接從word粘過來的,帶了一大堆格式,被截斷了,悲了個劇的,翻譯文檔也不是件容易的事啊……PHPExcel還是比較強大的,最大的問題就是記憶體佔用的問題,我之前用的1.7.2,還沒有這種機制,匯出2W+資料,佔用了400M+記憶體,改成1.7.6,使用cach_to_diskISAM方式,記憶體降低到200M-,效果還是很明顯的,不過依然還是夠高的,excel檔案5.1M,就使用了200M-和未知大小的磁碟空間,PHPExcel啥時候能出一個輕量級的版本,不需要那麼多花哨的功能,只需要匯出最普通的資料的版本就好了!
?
1 樓 loserwn 2011-07-01
1. 修改一下錯別字:當指令碼運行結束時,所有的資料都會從APC中
清楚(忽略緩衝時間),不能使用此機製作為持久緩衝。
2. 我嚼著,Excel 作為M$的自有的檔案格式,內部隱藏了太多的咚咚。PHPExcel能做到如此的相容性已經實屬不易了。尤其是在不依靠.NET或者各種COM+庫的前提下。 200MB- 的memory花費還是值得的。
2 樓 silentime 2011-07-01
嗯,是啊,PHPExcel已經很強大了~~
3 樓 yhg7752 2011-10-26
$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;
$cacheSettings = array( ' memoryCacheSize ' => '8MB'
);
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
我用的是這中設定的,卻只匯出了10000條資料就不行了,我是想匯出多個excel 一共160000條資料呢 每個excel中含有10000條資料 結果只匯出了一個
4 樓 yhg7752 2011-10-26
不知道為什麼,您能幫忙解決下嗎? 謝謝了
5 樓 silentime 2011-10-30
yhg7752 寫道
不知道為什麼,您能幫忙解決下嗎? 謝謝了
看看佔用記憶體的大小,windows用工作管理員,linux用top
6 樓 ashmodeus 2012-04-25
不錯,基本上匯出一個5000行的表格記憶體就上128M超出了。