PHP导入Excel内存溢出咋办

wen PHP项目 2026-08-11 156

本文目录导读：

PHP导入Excel内存溢出咋办

使用更好的库
使用文件流读取（推荐）
转换为CSV格式（最节省内存）
使用Box/Spout库（专为大数据设计）
内存优化技巧
完整示例：分块读取Excel
建议的选择

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