PHP CSV 匯入匯出最佳實踐：League\Csv、大檔案串流、編碼轉換、欄位對應、資料驗證與進度回呼

CSV 仍是企業系統資料交換主流格式。原生 `fgetcsv`/`fputcsv` 缺乏編碼處理、欄位對應、驗證、錯誤收集。本文採用 League\Csv 9+ 提供串流讀寫、編碼自動轉換、欄位映射、規則驗證、分批匯入、進度回呼，解決百萬列匯入記憶體不足、中文亂碼、格式不一致問題。

一、安裝與基本配置

composer require league/csv ^9.16

二、匯出：串流輸出、自定義標頭、編碼、分批

<?php
use League\Csv\Writer;
use League\Csv\OutputStream;

function exportUsersCsv(PDO $pdo, string $filename, callable $progress = null): void {
    $stmt = $pdo->query('SELECT id, name, email, created_at, status FROM users ORDER BY id');
    
    $writer = Writer::createFromFileObject(new \SplTempFileObject());
    $writer->setOutputEncoding('UTF-8'); // 內部用 UTF-8
    $writer->setDelimiter(',');
    $writer->setEnclosure('"');
    $writer->setEscapeChar('\\');
    $writer->insertOne(['ID', '姓名', 'Email', '註冊時間', '狀態']); // 標頭
    
    // 輸出到瀏覽器 (串流)
    $output = OutputStream::create('php://output', 'w');
    $output->setOutputEncoding('BIG-5'); // 瀏覽器/Excel 常用 BIG-5 或 UTF-8 with BOM
    
    // 寫入 BOM (Excel UTF-8 識別)
    $output->write("\xEF\xBB\xBF");
    
    $count = 0;
    while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
        $row[3] = date('Y-m-d H:i:s', strtotime($row[3]));
        $row[4] = $row[4] == 1 ? '啟用' : '停用';
        $writer->insertOne($row);
        if (++$count % 1000 === 0) $progress?.($count);
    }
    $writer->output($filename); // 觸發下載
}
?>

三、匯入：串流讀取、欄位映射、驗證、錯誤收集、分批寫入資料庫

<?php
use League\Csv\Reader;
use League\Csv\Statement;

class CsvImportResult {
    public int $success = 0;
    public int $failed = 0;
    public array $errors = []; // [rowNum => [field => message]]
}

function importUsersCsv(string $filePath, PDO $pdo, callable $progress = null): CsvImportResult {
    $result = new CsvImportResult();
    
    $reader = Reader::createFromPath($filePath, 'r');
    $reader->setHeaderOffset(0); // 第一行為標頭
    $reader->setEncoding('UTF-8', 'BIG-5'); // 輸入 BIG-5 -> 內部 UTF-8
    
    // 欄位映射：CSV 標頭 -> 資料庫欄位
    $map = [
        'ID' => 'id',
        '姓名' => 'name',
        'Email' => 'email',
        '註冊時間' => 'created_at',
        '狀態' => 'status',
    ];
    
    // 驗證規則
    $rules = [
        'name' => [fn($v) => mb_strlen($v) <= 50, '姓名過長'],
        'email' => [fn($v) => filter_var($v, FILTER_VALIDATE_EMAIL), 'Email 格式錯誤'],
        'created_at' => [fn($v) => strtotime($v) !== false, '日期格式錯誤'],
        'status' => [fn($v) => in_array($v, ['啟用','停用'], true), '狀態值無效'],
    ];
    
    $stmt = $pdo->prepare('INSERT INTO users (name, email, created_at, status) VALUES (?, ?, ?, ?)');
    $batch = [];
    $batchSize = 500;
    
    foreach ($reader->getRecords() as $offset => $record) {
        $rowNum = $offset + 2; // 標頭佔第 1 行
        $data = [];
        $rowErrors = [];
        
        foreach ($map as $csvHeader => $dbCol) {
            $value = $record[$csvHeader] ?? '';
            if (isset($rules[$dbCol])) {
                [$validator, $msg] = $rules[$dbCol];
                if (!$validator($value)) $rowErrors[$dbCol] = $msg;
            }
            $data[$dbCol] = $value;
        }
        
        if ($rowErrors) {
            $result->failed++;
            $result->errors[$rowNum] = $rowErrors;
            $progress?.($rowNum, false, $rowErrors);
            continue;
        }
        
        $batch[] = [$data['name'], $data['email'], $data['created_at'], $data['status'] == '啟用' ? 1 : 0];
        
        if (count($batch) >= $batchSize) {
            $pdo->beginTransaction();
            foreach ($batch as $params) {
                $stmt->execute($params);
                $result->success++;
            }
            $pdo->commit();
            $batch = [];
            $progress?.($rowNum, true, null);
        }
    }
    
    // 剩餘批次
    if ($batch) {
        $pdo->beginTransaction();
        foreach ($batch as $params) {
            $stmt->execute($params);
            $result->success++;
        }
        $pdo->commit();
    }
    
    return $result;
}
?>

四、進階：Statement 篩選、轉換、分塊處理

<?php
use League\Csv\Statement;

$stmt = (new Statement())
    ->offset(1000)      // 跳過前 1000 筆
    ->limit(500)        // 只處理 500 筆
    ->where(fn($row) => $row['status'] === '啟用'); // 篩選

foreach ($stmt->process($reader) as $record) {
    // 已套用篩選
}

// 自定義轉換器
$reader->addFormatter(fn($row) => array_map('trim', $row)); // 全欄位 trim
$reader->addFormatter(fn($row) => [
    ...$row,
    'full_name' => $row['first_name'].' '.$row['last_name'], // 衍生欄位
]);
?>

五、編碼自動偵測與 BOM 處理

<?php
function detectEncoding(string $filePath): string {
    $handle = fopen($filePath, 'rb');
    $bom = fread($handle, 4);
    fclose($handle);
    return match (true) {
        str_starts_with($bom, "\xEF\xBB\xBF") => 'UTF-8',
        str_starts_with($bom, "\xFF\xFE") => 'UTF-16LE',
        str_starts_with($bom, "\xFE\xFF") => 'UTF-16BE',
        default => 'BIG-5', // 台灣/香港預設
    };
}

// 使用
$encoding = detectEncoding($filePath);
$reader->setEncoding('UTF-8', $encoding);
?>

六、效能基準 (100 萬列、10 欄位)