Excel 匯入匯出是企業級應用的標配功能。從早期 PHPExcel (已停維護) 到現代 PhpSpreadsheet,再到輕量級 CSV 流式處理,本文全方位解析各類方案優缺點、記憶體優化技巧、大數據分批處理與實戰代碼範例。
| 方案 | 優點 | 缺點 | 適用場景 |
|---|---|---|---|
| PhpSpreadsheet | 功能完整 (樣式、公式、圖表、多工作表)、支援 .xlsx/.xls/.ods/.csv | 記憶體佔用極大 (整份載入記憶體)、大檔案極慢 | 中小檔案 (<5MB, <5萬行)、複雜格式、樣式需求 |
| Box/Spout | 流式讀寫、常數記憶體、速度快、支援 .xlsx/.ods/.csv | 不支援複雜樣式/公式/圖表、無 .xls 支援 | 大數據匯出 (>10萬行)、高效能需求、簡單格式 |
| fputcsv/fgetcsv | 原生函數、零依賴、極低記憶體、最快 | 純文字、無樣式/多工作表、編碼需自行處理 | 超大數據 (>100萬行)、極簡 CSV、記憶體受限環境 |
| League/Csv | 物件導向 CSV 處理、流式、功能豐富 (過濾、轉換) | 僅 CSV | 複雜 CSV 處理管線 |
<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
// ===== 匯出 =====
function exportUsers(PDO $pdo, string $outputPath): void {
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('用戶列表');
// 標題列樣式
$headers = ['ID', '姓名', 'Email', '部門', '入職日期', '狀態'];
$sheet->fromArray([$headers], null, 'A1');
$sheet->getStyle('A1:F1')->applyFromArray([
'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'startColor' => ['rgb' => '4472C4']],
'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER],
]);
// 分批查詢寫入 (避免一次撈太多資料)
$stmt = $pdo->query("SELECT id, name, email, dept, hired_at, status FROM users ORDER BY id");
$row = 2;
while ($user = $stmt->fetch(PDO::FETCH_ASSOC)) {
$sheet->fromArray([array_values($user)], null, "A{$row}");
$row++;
// 每 1000 行刷新一次 (可選)
if ($row % 1000 === 0) {
$spreadsheet->garbageCollect();
}
}
// 自動欄寬
foreach (range('A', 'F') as $col) {
$sheet->getColumnDimension($col)->setAutoSize(true);
}
// 寫入檔案
$writer = new Xlsx($spreadsheet);
$writer->save($outputPath);
$spreadsheet->disconnectWorksheets(); // 釋放記憶體
}
// ===== 匯入 =====
function importUsers(string $filePath, PDO $pdo): array {
$spreadsheet = IOFactory::load($filePath);
$sheet = $spreadsheet->getActiveSheet();
$results = ['success' => 0, 'failed' => 0, 'errors' => []];
// 迭代資料列 (跳過標題列)
foreach ($sheet->getRowIterator(2) as $row) {
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false); // 確保空單元格也迭代
$rowData = [];
foreach ($cellIterator as $cell) {
$rowData[] = $cell->getValue();
}
// 驗證與寫入
try {
[$id, $name, $email, $dept, $hired, $status] = $rowData;
if (!$name || !$email) throw new Exception('必填欄位缺失');
$pdo->prepare("INSERT INTO users (name, email, dept, hired_at, status) VALUES (?,?,?,?,?)")
->execute([$name, $email, $dept, $hired, $status ?? 'active']);
$results['success']++;
} catch (Throwable $e) {
$results['failed']++;
$results['errors'][] = "第 {$row->getRowIndex()} 行:{$e->getMessage()}";
}
}
$spreadsheet->disconnectWorksheets();
return $results;
}
<?php
require 'vendor/autoload.php';
use Box\Spout\Writer\Xlsx\Writer as XlsxWriter;
use Box\Spout\Reader\Xlsx\Reader as XlsxReader;
use Box\Spout\Common\Entity\Row;
use Box\Spout\Common\Entity\Cell;
// ===== 記憶體常數的大量匯出 =====
function exportLarge(PDO $pdo, string $outputPath): void {
$writer = new XlsxWriter();
$writer->openToFile($outputPath);
// 標題列
$writer->addRow(new Row([
new Cell('ID'), new Cell('姓名'), new Cell('Email'), new Cell('部門')
]));
$stmt = $pdo->query("SELECT id, name, email, dept FROM users");
$batch = [];
while ($user = $stmt->fetch(PDO::FETCH_ASSOC)) {
$batch[] = new Row(array_map(fn($v) => new Cell($v), array_values($user)));
if (count($batch) >= 1000) {
$writer->addRows($batch);
$batch = [];
}
}
if ($batch) $writer->addRows($batch);
$writer->close();
}
// ===== 流式讀取大檔案 =====
function importLarge(string $filePath, PDO $pdo): array {
$reader = new XlsxReader();
$reader->open($filePath);
$results = ['success' => 0, 'failed' => 0];
$firstRow = true;
foreach ($reader->getSheetIterator() as $sheet) {
foreach ($sheet->getRowIterator() as $row) {
if ($firstRow) { $firstRow = false; continue; } // 跳過標題
$cells = $row->getCells();
if (count($cells) < 2) continue;
try {
$name = $cells[0]->getValue();
$email = $cells[1]->getValue();
if (!$name || !$email) throw new Exception('必填缺失');
$pdo->prepare("INSERT INTO users (name, email) VALUES (?,?)")->execute([$name, $email]);
$results['success']++;
} catch (Throwable $e) {
$results['failed']++;
}
}
}
$reader->close();
return $results;
}
<?php
// ===== 匯出 CSV (UTF-8 with BOM 相容 Excel) =====
function exportCsv(PDO $pdo, string $outputPath): void {
$fp = fopen($outputPath, 'wb');
// 寫入 BOM 讓 Excel 正確識別 UTF-8
fwrite($fp, "\xEF\xBB\xBF");
// 標題
fputcsv($fp, ['ID', '姓名', 'Email', '部門', '入職日期']);
$stmt = $pdo->query("SELECT id, name, email, dept, hired_at FROM users");
while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
fputcsv($fp, $row);
}
fclose($fp);
}
// ===== 匯入 CSV (流式、低記憶體) =====
function importCsv(string $filePath, PDO $pdo): array {
$fp = fopen($filePath, 'rb');
if (!$fp) throw new RuntimeException("無法開啟檔案");
// 偵測/跳過 BOM
$bom = fread($fp, 3);
if ($bom !== "\xEF\xBB\xBF") {
rewind($fp); // 無 BOM 回到開頭
}
$results = ['success' => 0, 'failed' => 0, 'errors' => []];
$header = fgetcsv($fp); // 讀取標題列
$pdo->beginTransaction();
try {
$stmt = $pdo->prepare("INSERT INTO users (name, email, dept) VALUES (?,?,?)");
while (($row = fgetcsv($fp)) !== false) {
if (count($row) < 2) continue;
[$name, $email, $dept] = $row;
if (!$name || !$email) {
$results['errors'][] = "行資料不完整";
$results['failed']++;
continue;
}
$stmt->execute([$name, $email, $dept ?? '']);
$results['success']++;
}
$pdo->commit();
} catch (Throwable $e) {
$pdo->rollBack();
throw $e;
}
fclose($fp);
return $results;
}
LIMIT 1000 OFFSET ... 或游標 PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false (MySQLnd)。$spreadsheet->disconnectWorksheets() 釋放循環引用。$spreadsheet->getCalculationEngine()->flushInstance()。IOFactory::load($file, ReadOnly::class) (PhpSpreadsheet 1.18+)。\PhpOffice\PhpSpreadsheet\Settings::setCacheStorageMethod('Memory') (預設) vs 'MemoryGZip' vs 'Redis' 等。<?php
// 匯出給 Excel 開啟:必須加 BOM
fwrite($fp, "\xEF\xBB\xBF"); // UTF-8 BOM
// 或用 iconv 轉 Big5 (舊版 Excel 相容)
$line = iconv('UTF-8', 'BIG-5//IGNORE', $line);
fputcsv($fp, explode(',', $line)); // 手動分割 (簡化)
// 匯入時自動偵測編碼
function detectEncoding(string $file): string {
$content = file_get_contents($file, false, null, 0, 10000);
$enc = mb_detect_encoding($content, ['UTF-8', 'BIG-5', 'GBK', 'CP950'], true);
return $enc ?? 'UTF-8';
}
<?php
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\IReadFilter;
class ChunkReadFilter implements IReadFilter {
private int $startRow, $endRow;
public function __construct(int $start, int $chunk) {
$this->startRow = $start;
$this->endRow = $start + $chunk;
}
public function readCell($column, $row, $worksheetName = ''): bool {
return $row >= $this->startRow && $row < $this->endRow;
}
}
$reader = new Xlsx();
$reader->setReadFilter(new ChunkReadFilter(2, 1000)); // 從第 2 行起,每次 1000 行
$reader->setReadDataOnly(true); // 僅讀數值,忽略樣式 (極大加速)
$spreadsheet = $reader->load('huge.xlsx');
// 處理當前 chunk...
$spreadsheet->disconnectWorksheets();
// 迴圈下一個 chunk...
application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)、副檔名、檔案大小、getimagesize() 不適用,用 finfo 或嘗試載入。=、+、-、@ 開頭的儲存格會被 Excel 執行。防護:fputcsv($fp, array_map(fn($v) => (str_starts_with($v, '=') || str_starts_with($v, '+') || str_starts_with($v, '-') || str_starts_with($v, '@')) ? "'$v" : $v, $row))。選對工具、用對流式處理、注意編碼與安全,Excel 匯入匯出就不再是效能噩夢。根據資料量、格式需求、記憶體限制,靈活選擇 PhpSpreadsheet/Spout/原生 CSV,構建穩健的資料交換管線!