PHPExcel非常慢 - 改进方法？

I am generating reports in .xlsx using PHPExcel. It was okay in the initial testing stages with small data sets (tens of rows, 3 sheets), but now when using it on a real production data with over 500 rows in each sheet, it becomes incredibly slow. 48 seconds to generate a file, and when running a report that combines more information, the whole thing fails with Fatal error: Maximum execution time of 30 seconds exceeded in PHPExcel/Worksheet.php on line 1041. Sometimes it's in another PHPExcel file, so I doubt the exact location is that relevant.

我正在使用PHPExcel在.xlsx中生成报告。在初始测试阶段可以使用小数据集（数十行，3张），但现在当在每张表中超过500行的实际生产数据上使用它时，它变得异常缓慢。 48秒生成一个文件，当运行一个结合了更多信息的报告时，整个事情失败并发生致命错误：1041行的PHPExcel / Worksheet.php超过了30秒的最大执行时间。有时它在另一个PHPExcel文件中，所以我怀疑确切的位置是相关的。

Ideally, I would want to speed it up somehow, if possible. If not, then at least increase the execution limit for this script.

理想情况下，如果可能的话，我想以某种方式加快速度。如果没有，那么至少增加该脚本的执行限制。

The only suggestions I have seen so far was to style in ranges instead of individual cells. Unfortunately, I already do my styling in ranges and it is rather minimal too. Any other suggestions?

到目前为止我看到的唯一建议是在范围而不是单个单元格中设置样式。不幸的是，我已经在范围内做了我的造型，它也是相当小的。还有其他建议吗？

7 个解决方案

#1

Is it populating the worksheet? or saving? that you find too slow?

是填充工作表吗？还是节约？你发现太慢了？

How are you populating the spreadsheet with the data?

您如何使用数据填充电子表格？

• Using the fromArray() method is more efficient than populating each individual cell, especially if you use the Advanced Value Binder to set cell datatypes automatically.
• 使用fromArray（）方法比填充每个单独的单元格更有效，尤其是在使用Advanced Value Binder自动设置单元格数据类型时。

• If you're setting values for every individual cell in a sheet using

  如果您正在使用工作表中的每个单元格设置值

  $objPHPExcel->getActiveSheet()->setCellValue('A1',$x);
  $objPHPExcel->getActiveSheet()->setCellValue('B1',$y);
  

  use

  使用

  $sheet = $objPHPExcel->getActiveSheet();
  $sheet->setCellValue('A1',$x);
  $sheet->setCellValue('B1',$y);
  

  so that you're only accessing the getActiveSheet() method once; or take advantage of the fluent interface to set multiple cells with only a single call to $objPHPExcel->getActiveSheet()

  这样你只能访问一次getActiveSheet（）方法;或利用fluent接口设置多个单元格，只需调用$ objPHPExcel-> getActiveSheet（）

  $objPHPExcel->getActiveSheet()->setCellValue('A1',$x)
                                ->setCellValue('B1',$y);
  

You've commented on applying styles to ranges of cells:

您已经评论过将样式应用于单元格范围：

• You also have the option to use applyFromArray() to set a whole variety of style settings in one go.
• 您还可以选择使用applyFromArray（）一次性设置各种样式设置。
• It's a lot more efficient if you can apply styles to a column or a row rather than simply to a range
• 如果您可以将样式应用于列或行而不是简单地应用于范围，则效率会更高

If you're using formulae in your workbook, when saving:

如果您在工作簿中使用公式，则在保存时：

• Use

  使用

  $objWriter->setPreCalculateFormulas(false)
  

  to disable calculating the formulae within PHPExcel itself.

  禁用在PHPExcel本身内计算公式。

Those are just a few hints to help boost performance, and there's plenty more suggested in the forum threads. They won't all necessarily help, too much depends on your specific workbook to give any absolutes, but you should be able to improve that slow speed. Even the little notebook that I use for development can write a 3 worksheet, 20 column, 2,000 row Excel 2007 file faster than your production server.

这些只是帮助提升性能的一些提示，并且在论坛主题中还有更多建议。它们不一定都会有所帮助，太多取决于你的具体工作手册给予任何绝对，但你应该能够提高那么慢的速度。即使是我用于开发的小型笔记本也可以比生产服务器更快地编写3个工作表，20列，2,000行Excel 2007文件。

EDIT

编辑

If it was possible to simply improve the speed of PHPExcel itself, I'd have done so long ago. As it is, I'm constantly performance testing to see how its speed can be improved. If you want faster speeds than PHPExcel itself can give, then there's a list of alternative libraries here.

如果可以简单地提高PHPExcel本身的速度，我很久以前就已经这么做了。事实上，我经常进行性能测试，看看它的速度如何提高。如果你想要比PHPExcel本身更快的速度，那么这里有一个替代库列表。

#2

I ran into this issue as well. Thought I'd throw my two cents in since this question gets so many views.

我也遇到了这个问题。我以为我会抛出两分钱，因为这个问题得到了很多观点。

Setting Cell Values

Instead of setting the value for each cell individually, use the fromArray() method. Taken and modified from the wiki.

不要单独设置每个单元格的值，而是使用fromArray（）方法。从维基采取和修改。

$arrayData = array(
array(NULL, 2010, 2011, 2012),
array('Q1',   12,   15,   21),
array('Q2',   56,   73,   86),
array('Q3',   52,   61,   69),
array('Q4',   30,   32,    0),
);

$as = $objPHPExcel->getActiveSheet();

$as->fromArray(
    $arrayData,  // The data to set
    NULL,        // Array values with this value will not be set
    'C3'         // Top left coordinate of the worksheet range where
                 //    we want to set these values (default is A1)
);

Styling Cells

Static

静态的

It is also quicker to apply the styles for a range, than to set the style for each cell individually (noticing a pattern??).

应用范围的样式也比单独设置每个单元格的样式（注意模式??）更快。

$default_style = array(
    'font' => array(
        'name' => 'Verdana',
        'color' => array('rgb' => '000000'),
        'size' => 11
    ),
    'alignment' => array(
        'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER
    ),
    'borders' => array(
        'allborders' => array(
            'style' => \PHPExcel_Style_Border::BORDER_THIN,
            'color' => array('rgb' => 'AAAAAA')
        )
    )
);

// Apply default style to whole sheet
$as->getDefaultStyle()->applyFromArray($default_style);

$titles = array(
    'Name',
    'Number',
    'Address',
    'Telephone'
);

$title_style = array(
    'font' => array(
        'bold' => true
    ),
    'fill' => array(
        'type' => \PHPExcel_Style_Fill::FILL_SOLID,
        'startcolor' => array('rgb' => '5CACEE')
    ),
    'alignment' => array(
        'wrap' => true
    )
);

$as->fromArray($titles, null, 'A1'); // Add titles

$last_col = $as->getHighestColumn(); // Get last column, as a letter

// Apply title style to titles
$as->getStyle('A1:'.$last_col.'1')->applyFromArray($title_style);

Dynamically

动态

I use PHPExcel to check the data given in the spreadsheet with the current data in the database. Since each cell is checked individually, I put the styles in an array (null for no style), and used the loop below to get the range of cells to apply the style to.

我使用PHPExcel检查电子表格中给出的数据以及数据库中的当前数据。由于每个单元格都是单独检查的，因此我将样式放在一个数组中（null表示没有样式），并使用下面的循环来获取要应用样式的单元格范围。

/*
 * $row is previously set in a loop iterating through each 
 *     row from the DB, which is equal to a spreadsheet row.
 * $styles = array(0 => 'error', 1 => 'error', 2 => null, 3 => 'changed', ...);
 */
$start = $end = $style = null;
foreach ($styles as $col => $s) {
    if (!$style && !$s) continue;
    if ($style === $s) {
        $end = $col;
    } else {
        if ($style) {
            $array = null;
            switch ($style) {
                case 'changed':
                    $array = $this->changed_style;
                    break;
                case 'error':
                    $array = $this->error_style;
                    break;
                case 'ignored':
                    $array = $this->ignored_style;
                    break;
            }
            if ($array) { 
                $start = \PHPExcel_Cell::stringFromColumnIndex($start);
                $end = \PHPExcel_Cell::stringFromColumnIndex($end);
                $as->getStyle($start.$row.':'.$end.$row)->applyFromArray($array);
            }
        }
        $start = $end = $col;
        $style = $s;
    }
} 

#3

I was running into the same issue - had about 450 rows with 11 columns of data that I was trying to write, and I kept running up against the 30-second timeout. I was able to get the execution time down to 2 seconds or less by adding all of my new rows in bulk, and then going through and setting the cell content after the fact. In other words, I insert 450 rows in one call to insertNewRowBefore(), and then loop through and set content within those rows later.

我遇到了同样的问题 - 有大约450行，我试图写入11列数据，并且我一直在对抗30秒超时。通过批量添加所有新行，然后通过并在事后设置单元格内容，我能够将执行时间缩短到2秒或更短。换句话说，我在insertNewRowBefore（）的一次调用中插入450行，然后循环并稍后在这些行中设置内容。

Like so:

像这样：

$num_rows = count($output_rows);
$last_row = $sheet->getHighestRow();
$row = $last_row + 1;
$sheet->insertNewRowBefore($row, $num_rows);
// Now add all of the rows to the spreadsheet
foreach($output_rows as $line) {
    $i = 0;
    foreach($line as $val) {
        // Do your setCellValue() or setCellValueByColumnAndRow() here
        $i++;
    }
    $row++;
}

#4

I am in no means an expert in using PHPExcel, but the OfficeOpenXML format (the format of *.xlsx files) is itself a group of XML files packed in ZIP archive with *.xlsx extension. If you value your performance and know what kind of data you will be passing, maybe it is a better idea to build own XLSX generator, stripped down to the most important functions, maybe making some calculations on database layer etc. instead of parsing the whole document.

我绝不是使用PHPExcel的专家，但OfficeOpenXML格式（* .xlsx文件的格式）本身就是一组打包在ZIP存档中的XML文件，扩展名为* .xlsx。如果你重视你的性能并知道你将传递什么样的数据，也许最好建立自己的XLSX生成器，剥离到最重要的功能，可能在数据库层等上做一些计算而不是解析整个文件。

To do it, you can begin with analyzing files generated using smaller data sets (by changing extension from *.xlsx into *.zip, unpacking it and browsing through the contents of the single files). That way you could determine what you really need and generate it yourself (by creating appropriate XML files and packing them into ZIP archive, then renaming to have *.xlsx extension).

要做到这一点，您可以从分析使用较小数据集生成的文件开始（通过将扩展名从* .xlsx更改为* .zip，解压缩并浏览单个文件的内容）。通过这种方式，您可以确定自己真正需要的内容并自行生成（通过创建适当的XML文件并将其打包到ZIP存档中，然后重命名为* .xlsx扩展名）。

There is also specification of OfficeOpenXML, which is large (a couple thousands of pages), thus I do not propose reading it unless you really want to. Creating files to match the way they were generated by PHPExcel should be enough.

还有OfficeOpenXML的规范，它很大（几千页），因此除非你真的想要，否则我不打算阅读它。创建文件以匹配PHPExcel生成它们的方式就足够了。

The solution mentioned above does not include any PHPExcel-related tips, because I am not an expert in it. I have been previously interested in OOXML standarization process however, and would be happy if knowledge about this standard would help you solve your problem.

上面提到的解决方案不包括任何与PHPExcel相关的提示，因为我不是它的专家。我以前对OOXML标准化过程感兴趣，如果有关此标准的知识可以帮助您解决问题，我会很高兴。

#5

For a XLSX export with columns a - amj (~800) and only ~50 rows I also ran into the 30 second boundary. To test my program, I limited the amount of rows processed to just 7, which worked in 25 sec.

对于列a - amj（~800）且只有~50行的XLSX导出，我也遇到了30秒的边界。为了测试我的程序，我将处理的行数限制为7，这在25秒内有效。

1. going from individual $objPHPExcel->getActiveSheet() to $sheet (first advice) it actually increased the time on a limited amount of rows from 25 sec to 26 sec.

   从单个$ objPHPExcel-> getActiveSheet（）到$ sheet（第一个建议）它实际上将有限行数的时间从25秒增加到26秒。

2. What really helped me was replacing all my getHighestDataColumn() with a simple $column_nr variable that is incremented in PHP, I went from 26 sec to 7 sec.

   真正帮助我的是用一个简单的$ column_nr变量替换我所有的getHighestDataColumn（），该变量在PHP中递增，我从26秒变为7秒。

After that I was able to process all 50 rows in 11 sec.

之后我能够在11秒内处理所有50行。

#6

I had the exact same issue. Got a 5000 row, 32 column CSV file that took forever to process. It turns out almost all the time spent "processing" is actually the character encoding which is set to encode everything to UTF8 by default. So if you go into your config\excel.php file and scroll down to encoding, just set it as:

我有完全相同的问题。获得了5000行，32列CSV文件，需要永久处理。事实证明，几乎所有用于“处理”的时间实际上是字符编码，默认情况下设置为将所有内容编码为UTF8。因此，如果您进入config \ excel.php文件并向下滚动到编码，只需将其设置为：

/*
|--------------------------------------------------------------------------
| Import encoding
|--------------------------------------------------------------------------
*/
    'encoding' => array(

        'input'  => '',
        'output' => ''

    ),

With this alone - the above mentioned file takes around 8sec to process. You might want to warn your client to save the CSV correctly though.

仅凭这一点 - 上述文件需要大约8秒才能处理。您可能希望警告您的客户端正确保存CSV。

#7

In my case I increased performance by changing cache storage method to in memory gzip cache_in_memory_gzip

在我的例子中，我通过将缓存存储方法更改为内存gzip cache_in_memory_gzip来提高性能

$cm = \PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
\PHPExcel_Settings::setCacheStorageMethod($cm);