萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

PHP Excel 匯入匯出實戰:PHPExcel/PhpSpreadsheet、CSV 大數據、記憶體優化

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 處理管線

方案一:PhpSpreadsheet 完整範例 (中小檔案)

<?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;
}

方案二:Box/Spout 流式處理 (大數據首選)

<?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;
}

方案三:原生 CSV 流式處理 (極致效能、零依賴)

<?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;
}

記憶體優化關鍵技巧

  1. 分批查詢:LIMIT 1000 OFFSET ... 或游標 PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false (MySQLnd)。
  2. 流式寫入:Spout/fputcsv 邊讀邊寫,不載入整份資料。
  3. 釋放工作表:PhpSpreadsheet $spreadsheet->disconnectWorksheets() 釋放循環引用。
  4. 關閉公式快取:$spreadsheet->getCalculationEngine()->flushInstance()。
  5. 唯讀模式讀取:IOFactory::load($file, ReadOnly::class) (PhpSpreadsheet 1.18+)。
  6. Cell 緩存策略:\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';
}

進階:分塊讀取超大 Excel (PhpSpreadsheet ChunkReadFilter)

<?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...

安全性檢查清單

  • 上傳檔案:驗證 MIME (application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)、副檔名、檔案大小、getimagesize() 不適用,用 finfo 或嘗試載入。
  • XML 外部實體攻擊 (XXE):.xlsx 實為 ZIP 包含 XML,PhpSpreadsheet 預設已防護,但自訂 XML 解析需禁用外部實體。
  • 公式注入 (CSV Injection):匯出 CSV 時,以 =、+、-、@ 開頭的儲存格會被 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,構建穩健的資料交換管線!

copyright © 萬盛學電腦網 all rights reserved