PHPExcel非常慢-改进方法? [英] PHPExcel very slow - ways to improve?

问题描述

我正在使用PHPExcel在.xlsx中生成报告.在最初的测试阶段，使用较小的数据集(数十行，3张纸)就可以了，但是现在当在每张纸上有500行以上的实际生产数据上使用它时，它会变得异常缓慢. 48秒生成一个文件，并且在运行包含更多信息的报表时，整个操作失败，并显示Fatal error: Maximum execution time of 30 seconds exceeded in PHPExcel/Worksheet.php on line 1041.有时它在另一个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.

理想情况下，如果可能的话，我想以某种方式加快速度.如果不是，则至少增加此脚本的执行限制.

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?

推荐答案

是否正在填充工作表?还是省钱?你觉得太慢了?

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

如何用数据填充电子表格?

How are you populating the spreadsheet with the data?

• 使用fromArray()方法比填充每个单个单元格更有效，尤其是如果您使用Advanced Value Binder自动设置单元格数据类型.

• 如果要使用

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

使用

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

，因此您只需访问一次getActiveSheet()方法； 或利用流畅的界面仅通过一次调用$objPHPExcel->getActiveSheet()

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

您已评论过将样式应用于单元格区域:

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

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

• 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

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

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

• 使用

• Use

$objWriter->setPreCalculateFormulas(false)

禁止在PHPExcel本身中计算公式.

to disable calculating the formulae within PHPExcel itself.

这些只是一些有助于提高性能的提示，并且在论坛主题中还有很多建议.它们不一定都可以提供帮助，太多取决于您的特定工作簿来提供绝对值，但是您应该能够提高该慢速速度.即使是我用于开发的小笔记本，也可以比生产服务器更快地编写3个工作表，20列，2,000行的Excel 2007文件.

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.

编辑

如果可以简单地提高PHPExcel本身的速度，那么我早就做了.实际上，我一直在进行性能测试，以了解如何提高其速度.如果您想要更快的速度而不是PHPExcel本身可以提供的速度，那么这里有一个替代库列表.

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非常慢-改进方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！