设置20个单元格类型后，PhpExcel停止工作 [英] PhpExcel stops working after setting 20 cell types

问题描述

我有一个脚本生成一个小的xls表（〜25x15）。它包含百分比和字符串，我有一个if运算符将当前单元格设置为百分比类型，代码如下：

  $ this- > objPHPExcel-> getActiveSheet（） - >的getStyle（$ coords）使用 - > getNumberFormat（） - > setFormatCode（ '0.00％'）; 
  

但是当我导出并查看文件时，我看到它只能设置类型和样式约20细胞。而其余的都是默认设置。我调试它，并意识到问题不在我的逻辑。我读了关于增加php缓存的内存 - 尝试了但它没有工作。请帮忙，因为我需要出口至少15倍的桌子。感谢提前！

解决方案

PHPExcel分配相当多的内存

虽然PHPExcel是一个美丽的库，使用它可能需要大量的内存分配给PHP。

根据这个线程，只是 5个单元格可能会呈现6 MByte的内存使用量：

 <？php 
 require_once'php / PHPExcelSVN / PHPExcel / IOFactory.php'; 
 $ objPHPExcel = PHPExcel_IOFactory :: load（php / tinytest.xlsx）; 
 $ objPHPExcel-> setActiveSheetIndex（0）; 
 $ objPHPExcel-> getActiveSheet（） - > setCellValue（'D2'，50）; 
 echo $ objPHPExcel-> getActiveSheet（） - > getCell（'D8'） - > getCalculatedValue（）。 
; 
 echo date（'H：i：s'）。 内存使用量高峰： （memory_get_peak_usage（true）/ 1024/1024）。 MB\r\\\
; 
？> 
 
我得到6MB的内存使用。 
  

另一个用户甚至失败了一个256MByte的内存设置。

虽然PHPExcel提供了减少其内存占用的方式，但所有减少量也是如此我的情况很小 github上的此页面提供PHPExcel的缓存管理选项的详细信息。例如，此设置将序列化，GZIPs是工作表的单元格结构：

  $ cacheMethod = PHPExcel_CachedObjectStorageFactory :: cache_in_memory_gzip; 
 
 PHPExcel_Settings :: setCacheStorageMethod（$ cacheMethod）; 
  

PHPExcel的FAQ 解释：

致命错误：允许的xxx字节的内存大小耗尽（尝试
分配yyy字节）在aaa的zzz中

PHPExcel持有电子表格的内存表示，所以它
容易受到PHP的内存限制。可以通过在php.ini文件中编辑memorylimit
伪指令的值，或者使用iniset（'memory_limit'，
'128M'）来增加PHP中提供的
的内存您的代码（ISP允许）;

一些读者和作者比其他人更快，他们还使用
不同的内存量。您可以在不同版本的PHPExcel中找到
相对性能和内存使用情况的不同读者和
Writers的指示，这里是
http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=234150

如果您已经将内存增加到最大值，或者不能更改
内存限制，那么该板上的讨论会描述一些可以使用的
方法应用于减少脚本的内存使用
使用PHPExcel
http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=242712

测量PHP Excel的结果

我测试了PHPExcel示例文件 [01simple.php] [5] 并进行了一些快速测试。

消费 92 KByte ：

  for（$ n = 0; $ n <200; $ n ++）{
 $ objPHPExcel-> setActiveSheetIndex（0） - > setCellValue（'A'。$ n，'Miscellaneous glyphs'）; 
} 
  

消耗 4164 KBytes

$ （$ n = 0; $ n <200; $ n ++）{
$ objPHPExcel-> setActiveSheetIndex（0） - > ; setCellValue（'A'。$ n，'Miscellaneous glyphs'）;
$ objPHPExcel-> getActiveSheet（） - > getStyle（'A'。$ n） - > getAlignment（） - > setWrapText（true）;
}

如果一个执行该片段多次不变，每个片段消耗大约4 MB。

检查您的应用程序在逻辑上正确

为确保您的应用程式逻辑上正确，我建议先增加PHP内存：

  ini_set（'memory_limit'，'32M'）; 
  

在我的情况下，我必须导出以导出在线评估应用程序的结果数据。虽然水平不到100个单元格，但我需要导出多达10.000行。虽然细胞数量很大，但我的每个细胞都有一个数字或一个3个字符的字符串 - 没有公式，没有样式。

如果内存受限或大型电子表格

在我的情况下，没有一个缓存选项减少了所需的数量。此外，应用程序的运行时间也大大增加。

最后我不得不切换到旧式的CSV数据文件导出。

I have a script that generates a little xls table (~25x15). It contains percentages and strings and i have an if operator that sets the current cell as percentage type with this code:

$this->objPHPExcel->getActiveSheet()->getStyle($coords)->getNumberFormat()->setFormatCode('0.00%');

But when i export and look at the file I see it only managed to set type and style about 20 cells. And all the rest are with default settings. I debugged it and realized the problem isn't in my logic. I read about increasing php cache memory - tried it but it didn't work. Please help because i need to export at least 15 times larger table. Thanks in advance!

解决方案

PHPExcel allocates quite some memory

While PHPExcel is a beautiful library, using it may require huge amounts of memory allocated to PHP.

According to this thread, just 5 cells may render 6 MByte of memory usage:

<?php
require_once 'php/PHPExcelSVN/PHPExcel/IOFactory.php';
$objPHPExcel = PHPExcel_IOFactory::load("php/tinytest.xlsx");
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('D2', 50);
echo $objPHPExcel->getActiveSheet()->getCell('D8')->getCalculatedValue() . "
";
echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB\r\n";
?>

I get 6MB of memory usage.

Another user even failed with a 256MByte memory setting.

While PHPExcel provides ways to reduce its memory footprint, all reductions turned out to be too small in my case. This page on github provides details of PHPExcel's cache management options. For example, this setting serializes and the GZIPs the cell-structure of a worksheet:

$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;

PHPExcel_Settings::setCacheStorageMethod($cacheMethod);

PHPExcel's FAQ explains this:

Fatal error: Allowed memory size of xxx bytes exhausted (tried to allocate yyy bytes) in zzz on line aaa

PHPExcel holds an "in memory" representation of a spreadsheet, so it is susceptible to PHP's memory limitations. The memory made available to PHP can be increased by editing the value of the memorylimit directive in your php.ini file, or by using iniset('memory_limit', '128M') within your code (ISP permitting);

Some Readers and Writers are faster than others, and they also use differing amounts of memory. You can find some indication of the relative performance and memory usage for the different Readers and Writers, over the different versions of PHPExcel, here http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=234150

If you've already increased memory to a maximum, or can't change your memory limit, then this discussion on the board describes some of the methods that can be applied to reduce the memory usage of your scripts using PHPExcel http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=242712

Measurement results for PHP Excel

I instrumented the PHPExcel example file [01simple.php][5] and did some quick testing.

Consume 92 KByte:

for( $n=0; $n<200; $n++ ) {
    $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A' . $n, 'Miscellaneous glyphs');
}

Consumes 4164 KBytes:

for( $n=0; $n<200; $n++ ) {
    $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A' . $n, 'Miscellaneous glyphs');
    $objPHPExcel->getActiveSheet()->getStyle('A' . $n)->getAlignment()->setWrapText(true);
}

If one executes this fragment several times unchanged, each fragment consumes around 4 MBytes.

Checking your app is logically correct

To ensure, that your app is logically correct, I'd propose to increase PHP memory first:

ini_set('memory_limit', '32M');

In my case, I have to export to export result data of an online assessment application. While there are less than 100 cells horizontally, I need to export up to several 10.000 rows. While the amount of cells was big, each of my cells holds a number or a string of 3 characters - no formulas, no styles.

In case of strong memory restrictions or large spreadsheets

In my case, none of the cache options reduced the amount as much as required. Plus, the runtime of the application grew enormously.

Finally I had to switch over to old fashioned CSV-data file exports.

这篇关于设置20个单元格类型后，PhpExcel停止工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！