使用PHP导出百万行MySQL表至XLS时系统卡顿、文件损坏求解决

首先，你遇到的问题核心在于PHPExcel本身的内存模型——它会把整个Excel文档的所有数据、样式都加载到内存中处理，百万行+20列的数据量完全超出了它的承载能力，必然会导致内存溢出、系统卡顿，最终生成损坏的文件。而且PHPExcel已经在2017年停止维护了，官方推荐使用它的继任者PhpSpreadsheet，不过我们先从最实用的方案说起：

方案一：导出为CSV格式（最推荐，轻量化高效） #

CSV是纯文本格式，Excel完全支持打开，而且不需要处理任何样式，内存占用极低，处理百万级数据毫无压力。代码示例：

// 设置表头（补全你的20列表头）
$headers = ['COL 1', 'COL 2', 'COL 3', 'COL 4', 'COL 5', ...];
// 输出HTTP头，让浏览器下载CSV
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename="excel_report.csv"');

// 打开输出流
$output = fopen('php://output', 'w');
// 写入表头
fputcsv($output, $headers);

// 分块读取MySQL数据，避免一次性加载百万行到内存
$batchSize = 1000; // 每次读取1000行
$offset = 0;
do {
    $Qry = $con->query("SELECT * FROM `table_1` LIMIT $offset, $batchSize");
    $rowCount = 0;
    while ($row = $Qry->fetch_array(MYSQLI_ASSOC)) {
        // 把数据库行转为数组，顺序和表头对应（补全20列）
        $dataRow = [
            $row['col_1'],
            $row['col_2'],
            $row['col_3'],
            $row['col_4'],
            $row['col_5'],
            ...
        ];
        fputcsv($output, $dataRow);
        $rowCount++;
    }
    $offset += $batchSize;
} while ($rowCount === $batchSize);

fclose($output);
exit;

为什么这个方案好用？ #

• 内存占用几乎可以忽略，因为每次只处理1000行数据
• 代码简单，没有复杂的Excel对象操作
• 导出速度极快，不会出现系统卡顿
• Excel可以直接打开CSV，也可以另存为.xls/.xlsx格式

方案二：使用PhpSpreadsheet流式写入（保留Excel格式） #

如果你必须导出为真正的Excel文件（需要样式、格式），推荐使用PhpSpreadsheet的流式写入功能，它不会把整个文档加载到内存，而是逐行写入输出流。

首先确保你已经通过Composer安装了PhpSpreadsheet：

composer require phpoffice/phpspreadsheet

然后是代码示例：

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xls;

// 创建表格对象
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();

// 设置表头（补全20列）
$headers = ['COL 1', 'COL 2', 'COL 3', 'COL 4', 'COL 5', ...];
$colIndex = 1;
foreach ($headers as $header) {
    $worksheet->setCellValueByColumnAndRow($colIndex, 1, $header);
    // 批量设置表头样式（可选）
    $worksheet->getStyleByColumnAndRow($colIndex, 1)->applyFromArray([
        'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'color' => ['rgb' => 'CCCCCC']],
        'font' => ['bold' => true]
    ]);
    $colIndex++;
}

// 分块读取数据并写入
$batchSize = 1000;
$offset = 0;
$rowIndex = 2; // 从第二行开始写数据
do {
    $Qry = $con->query("SELECT * FROM `table_1` LIMIT $offset, $batchSize");
    $rowCount = 0;
    while ($row = $Qry->fetch_array(MYSQLI_ASSOC)) {
        $colIndex = 1;
        // 逐列写入数据（补全20列）
        $worksheet->setCellValueByColumnAndRow($colIndex++, $rowIndex, $row['col_1']);
        $worksheet->setCellValueByColumnAndRow($colIndex++, $rowIndex, $row['col_2']);
        $worksheet->setCellValueByColumnAndRow($colIndex++, $rowIndex, $row['col_3']);
        $worksheet->setCellValueByColumnAndRow($colIndex++, $rowIndex, $row['col_4']);
        $worksheet->setCellValueByColumnAndRow($colIndex++, $rowIndex, $row['col_5']);
        
        // 批量设置单元格样式（比如自动换行）
        $worksheet->getStyleByColumnAndRow(2, $rowIndex)->getAlignment()->setWrapText(true);
        $worksheet->getStyleByColumnAndRow(3, $rowIndex)->getAlignment()->setWrapText(true);
        
        $rowIndex++;
        $rowCount++;
    }
    $offset += $batchSize;
    // 手动清理内存，优化性能
    gc_collect_cycles();
} while ($rowCount === $batchSize);

// 设置HTTP头，导出文件
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="excel_report.xls"');
header('Cache-Control: max-age=0');

// 流式写入输出
$writer = new Xls($spreadsheet);
$writer->save('php://output');
exit;

方案三：PHPExcel的内存优化（不推荐，因为已废弃） #

如果你暂时无法切换到PhpSpreadsheet，可以尝试以下优化，但效果有限：

• 分块读取数据库数据，避免一次性加载百万行
• 禁用不必要的样式，或者批量设置样式（不要逐单元格设置）
• 避免频繁调用getStyle()，尽量一次性设置整列/整行样式

优化后的PHPExcel代码示例： #

set_include_path(get_include_path() . PATH_SEPARATOR . 'Classes/');
include 'PHPExcel/IOFactory.php';

$objPHPExcel = new PHPExcel();
$sheet = $objPHPExcel->getActiveSheet();

// 批量设置表头和样式（补全20列）
$headers = ['COL 1', 'COL 2', 'COL 3', 'COL 4', 'COL 5', ...];
$colLetters = ['A','B','C','D','E', ...];
foreach ($headers as $index => $header) {
    $col = $colLetters[$index];
    $sheet->SetCellValue($col.'1', $header);
}
// 一次性设置表头样式
$sheet->getStyle('A1:'.$colLetters[count($headers)-1].'1')->applyFromArray($headerColor);

// 分块读取数据
$batchSize = 1000;
$offset = 0;
$rowCount = 1;
do {
    $Qry = $con->query("SELECT * FROM `table_1` LIMIT $offset, $batchSize");
    $currentBatchRows = 0;
    while($row = $Qry->fetch_array()){
        $rowCount++;
        // 批量写入单元格数据（补全20列）
        $sheet->SetCellValue('A'.$rowCount, $row['col_1']);
        $sheet->SetCellValue('B'.$rowCount, $row['col_2']);
        $sheet->SetCellValue('C'.$rowCount, $row['col_3']);
        $sheet->SetCellValue('D'.$rowCount, $row['col_4']);
        $sheet->SetCellValue('E'.$rowCount, $row['col_5']);
        
        $currentBatchRows++;
    }
    $offset += $batchSize;
    // 清理内存
    gc_collect_cycles();
} while ($currentBatchRows === $batchSize);

// 批量设置列样式（不要逐单元格设置）
$sheet->getStyle('B2:'.$colLetters[1].$rowCount)->getAlignment()->setWrapText(true);
$sheet->getStyle('C2:'.$colLetters[2].$rowCount)->getAlignment()->setWrapText(true);

// 批量设置列宽
foreach ($colLetters as $col) {
    $sheet->getColumnDimension($col)->setAutoSize(true);
}

// 导出文件
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="excel_report.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;

注意事项： #

即使做了这些优化，PHPExcel处理百万级数据还是很容易遇到内存瓶颈，所以优先推荐方案一或方案二。

内容的提问来源于stack exchange，提问作者Dhileep