phpspreadsheet内存溢出解决方案

本文主要是介绍phpspreadsheet内存溢出解决方案，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

2022-01-10近期，由于工作业务中，需要 同步一份xlsx的数据传到一台服务器中并做解析操作. 我们php也就用了spreadsheet这个扩展. 

分段读取

在spreadsheet官网中, 有这样一份方案. 分段读取.

// 示例
$inputFileType = 'Xls';
$inputFileName = './sampleData/example2.xls';/**  Define a Read Filter class implementing \PhpOffice\PhpSpreadsheet\Reader\IReadFilter  */
class ChunkReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter
{private $startRow = 0;private $endRow   = 0;/**  Set the list of rows that we want to read  */public function setRows($startRow, $chunkSize) {$this->startRow = $startRow;$this->endRow   = $startRow + $chunkSize;}public function readCell($column, $row, $worksheetName = '') {//  Only read the heading row, and the configured rowsif (($row == 1) || ($row >= $this->startRow && $row < $this->endRow)) {return true;}return false;}
}/**  Create a new Reader of the type defined in $inputFileType  **/
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);/**  Define how many rows we want to read for each "chunk"  **/
$chunkSize = 2048;
/**  Create a new Instance of our Read Filter  **/
$chunkFilter = new ChunkReadFilter();/**  Tell the Reader that we want to use the Read Filter  **/
$reader->setReadFilter($chunkFilter);/**  Loop to read our worksheet in "chunk size" blocks  **/
for ($startRow = 2; $startRow <= 65536; $startRow += $chunkSize) {/**  Tell the Read Filter which rows we want this iteration  **/$chunkFilter->setRows($startRow,$chunkSize);/**  Load only the rows that match our filter  **/$spreadsheet = $reader->load($inputFileName);//    Do some processing here
}

原始地址. 官方使用了ChunkReadFilter来进行读取. 但是在使用过程中. 发现只能优化掉一部分. 当读取的单元格原来越多时. 内存一样抗不住了. 所以有了我们后面一种方式.

内存优化

可以通过实现psr16规范的cache类. 来设置缓存. 例如

$cache = new MyCustomPsr16Implementation();\PhpOffice\PhpSpreadsheet\Settings::setCache($cache);

这样也能解决掉一部分.

其他优化

读取指定的工作表. 使用setLoadSheetsOnly来处理. 例如

$inputFileType = 'Excel5'; 
$inputFileName = './sampleData/example1.xls';
$sheetname = 'Data Sheet #2'; 
/**  Create a new Reader of the type defined in $inputFileType  **/
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
/**  Advise the Reader of which WorkSheets we want to load  **/ 
$objReader->setLoadSheetsOnly($sheetname); 
/**  Load $inputFileName to a PHPExcel Object  **/
$objPHPExcel = $objReader->load($inputFileName);

这篇关于phpspreadsheet内存溢出解决方案的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---