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 rows
if (($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);