设置20个单元格类型后，PhpExcel停止工作

问题描述

我有一个脚本生成一个小的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的内存使用。 
  

另一个设置。

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

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

解释：

测量PHP Excel的结果

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

消费 92 KByte ：

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

消耗 4164 KBytes

$ （$ n = 0; $ 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:

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停止工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！