本文目录导读：
在PHP中处理大型Excel文件时,内存溢出是非常常见的问题，以下是几种解决方案，从简单到复杂：
使用更好的库
PHPExcel (已废弃) → PhpSpreadsheet
// 旧方案（不推荐）
$objPHPExcel = PHPExcel_IOFactory::load('large.xlsx'); // 加载整个文件到内存
// 新方案（使用只读模式）
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
$reader = new Xlsx();
$reader->setReadDataOnly(true); // 只读取数据，忽略样式
$reader->setReadEmptyCells(false); // 跳过空单元格
$spreadsheet = $reader->load('large.xlsx');
使用文件流读取（推荐）
方案A：使用PhpSpreadsheet的ReadFilter
use PhpOffice\PhpSpreadsheet\Reader\IReadFilter;
class ChunkReadFilter implements IReadFilter
{
private $startRow = 0;
private $endRow = 0;
public function __construct($startRow, $endRow)
{
$this->startRow = $startRow;
$this->endRow = $endRow;
}
public function readCell($column, $row, $worksheetName = '')
{
// 只读取指定行范围
return $row >= $this->startRow && $row <= $this->endRow;
}
}
// 分块读取
$inputFileType = 'Xlsx';
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
$reader->setReadDataOnly(true);
$chunkSize = 1000; // 每次读取1000行
$startRow = 2; // 从第2行开始（跳过表头）
for ($currentRow = $startRow; $currentRow <= $totalRows; $currentRow += $chunkSize) {
$chunkFilter = new ChunkReadFilter($currentRow, $currentRow + $chunkSize);
$reader->setReadFilter($chunkFilter);
$spreadsheet = $reader->load('large.xlsx');
$worksheet = $spreadsheet->getActiveSheet();
// 处理当前块的数据
foreach ($worksheet->getRowIterator() as $row) {
// 处理每一行
processRow($row);
}
$spreadsheet->disconnectWorksheets(); // 释放内存
unset($spreadsheet);
}
转换为CSV格式（最节省内存）
// 使用自定义CSV转换
function xlsxToCsv($inputFile, $outputFile = null, $chunkSize = 1000) {
// 1. 先转换xlsx为csv
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load($inputFile);
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Csv($spreadsheet);
$writer->setDelimiter(',');
$writer->setEnclosure('"');
$writer->setLineEnding("\r\n");
$writer->setSheetIndex(0);
$outputFile = $outputFile ?: str_replace('.xlsx', '.csv', $inputFile);
$writer->save($outputFile);
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
// 2. 流式读取CSV
return readCsvInChunks($outputFile);
}
function readCsvInChunks($csvFile, $chunkSize = 1000) {
$handle = fopen($csvFile, 'r');
if ($handle === false) return;
$rowCount = 0;
$chunkData = [];
while (($row = fgetcsv($handle, 0, ',')) !== false) {
$chunkData[] = $row;
$rowCount++;
if ($rowCount % $chunkSize === 0) {
processChunk($chunkData);
$chunkData = []; // 清空已处理的数据
}
}
// 处理剩余数据
if (!empty($chunkData)) {
processChunk($chunkData);
}
fclose($handle);
unlink($csvFile); // 删除临时CSV文件
}
使用Box/Spout库（专为大数据设计）
use Box\Spout\Reader\Common\Creator\ReaderEntityFactory;
// 安装: composer require box/spout
$reader = ReaderEntityFactory::createReaderFromFile('large.xlsx');
$reader->open('large.xlsx');
foreach ($reader->getSheetIterator() as $sheet) {
foreach ($sheet->getRowIterator() as $row) {
$cells = $row->getCells();
$rowData = [];
foreach ($cells as $cell) {
$rowData[] = $cell->getValue();
}
// 处理每一行数据
processRow($rowData);
// 手动释放内存
unset($cells, $rowData);
}
}
$reader->close();
内存优化技巧
// 1. 增加PHP内存限制（临时）
ini_set('memory_limit', '512M');
// 2. 使用强制垃圾回收
gc_enable();
gc_collect_cycles();
// 3. 及时释放变量
$spreadsheet->disconnectWorksheets();
unset($spreadsheet, $worksheet);
clearstatcache();
// 4. 优化数据库插入
function processInBatches($data, $batchSize = 1000) {
$batch = [];
foreach ($data as $row) {
$batch[] = $row;
if (count($batch) >= $batchSize) {
insertBatch($batch); // 批量插入
$batch = [];
gc_collect_cycles(); // 强制GC
}
}
if (!empty($batch)) {
insertBatch($batch);
}
}
完整示例：分块读取Excel
class ExcelImportHelper
{
private $filePath;
private $chunkSize;
private $reader;
public function __construct($filePath, $chunkSize = 500)
{
$this->filePath = $filePath;
$this->chunkSize = $chunkSize;
$this->initReader();
}
private function initReader()
{
$this->reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$this->reader->setReadDataOnly(true);
}
public function import(callable $rowCallback)
{
$totalRows = $this->getTotalRows();
for ($startRow = 2; $startRow <= $totalRows; $startRow += $this->chunkSize) {
$filter = new ChunkReadFilter($startRow, $startRow + $this->chunkSize - 1);
$this->reader->setReadFilter($filter);
$spreadsheet = $this->reader->load($this->filePath);
$worksheet = $spreadsheet->getActiveSheet();
foreach ($worksheet->getRowIterator() as $row) {
$rowData = [];
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);
foreach ($cellIterator as $cell) {
$rowData[] = $cell->getValue();
}
// 调用回调处理每一行
$rowCallback($rowData);
}
$spreadsheet->disconnectWorksheets();
unset($spreadsheet, $worksheet);
gc_collect_cycles();
}
}
private function getTotalRows()
{
$spreadsheet = $this->reader->load($this->filePath);
$worksheet = $spreadsheet->getActiveSheet();
$totalRows = $worksheet->getHighestRow();
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
return $totalRows;
}
}
// 使用示例
$helper = new ExcelImportHelper('large.xlsx', 500);
$helper->import(function($rowData) {
// 处理每一行数据
processRow($rowData);
});
建议的选择
- 中小文件 (< 100MB)：使用PhpSpreadsheet的ReadFilter
- 大文件 (> 100MB)：转换为CSV后分块读取
- 超大文件 (> 500MB)：使用Box/Spout库或考虑数据库直接导入工具
最重要的原则是：永远不要一次性加载整个Excel文件到内存！
