PHPExcel Out Of Memory

发表于 更新于 分类于 阅读次数： Disqus：

前些时候在利用PHPExcel生成excel文件的时候出了点问题，特此记录。

功能不复杂，就是从数据库取出资料，写进模板的xlsx文件里，最后输出文件流给浏览器下载：

1
2
3
4
5
6
7
8
9
10
11
12
13
$objPHPExcel = \PHPExcel_IOFactory::load('foo.xlsx');
$objPHPExcel->setActiveSheetIndex(0);
$num = 2;
foreach($query as $item){
	$objPHPExcel->getActiveSheet()
		->setCellValue('A'.$num, 'somethingA')
		->setCellValue('B'.$num, 'somethingB')
		//.....       
		->setCellValue('Z'.$num, 'somethingZ');
	$num++;
}
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');

本地开发的时候是没有问题的。当到正式环境的时候，由于数据量大了，竟然报错了，超内存了：

Fatal error: Allowed memory size of XXXX bytes exhausted (tried to allocate XXXX bytes) in XXX

网上搜索了一下解决办法，找到这个地方：php - PHPExcel runs out of 256, 512 and also 1024MB of RAM - Stack Overflow。根据回答里的说法，好像是phpexcel存在的问题：

There’s plenty been written about the memory usage of PHPExcel on the PHPExcel forum; so reading through some of those previous discussions might give you a few ideas. PHPExcel holds an “in memory” representation of a spreadsheet, and is susceptible to PHP memory limitations.

The physical size of the file is largely irrelevant… it’s much more important to know how many cells (rows*columns on each worksheet) it contains.

The “rule of thumb” that I’ve always used is an average of about 1k/cell, so a 5M cell workbook is going to require 5GB of memory. However, there are a number of ways that you can reduce that requirement. These can be combined, depending on exactly what information you need to access within your workbook, and what you want to do with it.

要占用这么多的内存啊！也有人说修改ini_set('memory_limit', 0);，可是这太不靠谱了，万一内存爆了。

万幸，有人提到了另一个在内存处理方面更加强大的工具：box/spout
，这是他的github主页：box/spout: Read and write spreadsheet files (CSV, XLSX and ODS), in a fast and scalable way。按照项目的简介：Spout is a PHP library to read and write spreadsheet files (CSV, XLSX and ODS), in a fast and scalable way. Contrary to other file readers or writers, it is capable of processing very large files while keeping the memory usage really low (less than 3MB).。感觉很不错的样子，完全可以控制内存在正常范围内！

composer了他的项目后，修改我的代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
use Box\Spout\Writer\WriterFactory as SpoutWriterFactory;
use Box\Spout\Common\Type as SpoutType;
	
$writer = SpoutWriterFactory::create(SpoutType::XLSX);
$writer->openToBrowser('bar.xlsx');
foreach($query as $item){
	$row = [
		'somethingA',
		'somethingB',
		//......
		'somethingZ'
	];
	$writer->addRow($row);
}
$writer->close();

经测试，在同样数据量的情况下，spout完全没有内存方面的压力，而且速度相比PHPExcel更快。 :)

当然，spout在表格渲染上也有一些小缺陷，比如目前我需要用到的单元格合并就没法做到；另外由于处理方式的不同，无法直接针对模板文件read进来改写，只能经过reader拷贝row进来放到writer文件。但是相对内存方面的大问题，这种可以忽略了嘿嘿。

完。