php PHPExcel 很慢 - 改进的方法？

PHPExcel very slow - ways to improve?

提问by SaltyNuts

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 秒生成一个文件，当运行一个结合更多信息的报告时，整个事情失败了Fatal error: Maximum execution time of 30 seconds exceeded in PHPExcel/Worksheet.php on line 1041。有时它在另一个 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?

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

回答by Mark Baker

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.

• 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()

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

• 使用该fromArray()方法比填充每个单独的单元格更有效，尤其是当您使用高级值绑定器自动设置单元格数据类型时。

• 如果您使用

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

  用

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

  以便您只访问该getActiveSheet()方法一次；或利用流畅的界面设置多个单元格，只需一次调用$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.
• It's a lot more efficient if you can apply styles to a column or a row rather than simply to a range

• 您还可以选择applyFromArray()一次性设置各种样式设置。
• 如果您可以将样式应用于列或行而不是简单地应用于范围，则效率会更高

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

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

• Use

  $objWriter->setPreCalculateFormulas(false)
  

  to disable calculating the formulae within PHPExcel itself.

• 用

  $objWriter->setPreCalculateFormulas(false)
  

  禁用计算 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 本身所能提供的更快的速度，那么这里有一个替代库列表。

回答by GreeKatrina

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()方法。从wiki 中获取和修改。

$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 用数据库中的当前数据检查电子表格中给出的数据。由于每个单元格都是单独检查的，我将样式放在一个数组中（空表示没有样式），并使用下面的循环来获取要应用样式的单元格范围。

/*
 * $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;
    }
} 

回答by JaredC

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++;
}

回答by Cas Tuyn

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.

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.

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

2. 真正帮助我的是用一个在 PHP 中递增的简单 $column_nr 变量替换我所有的 getHighestDataColumn()，我从 26 秒到 7 秒。

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

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

回答by Tadeck

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 文件的格式）本身就是一组以 *.xlsx 扩展名打包在 ZIP 存档中的 XML 文件。如果你重视你的性能并且知道你将传递什么样的数据，也许构建自己的 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 标准化过程很感兴趣，如果有关此标准的知识可以帮助您解决问题，我会很高兴。

回答by Skipp

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。

回答by Farid Movsumov

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);