PHPExcel/PhpSpreadsheet 實現 Excel 匯入匯出完整教學：ThinkPHP/Laravel/原生通用方案

重要提示

PHPExcel 已停止維護 (2017)，現代專案請使用 **PhpSpreadsheet** (官方繼任者)，API 相容性高，支援 PHP 7.1+、.xlsx/.xls/.ods/.csv。

Composer 安裝

composer require phpoffice/phpspreadsheet

通用核心類別 (框架無關)

<?php
namespace App\Service;

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

class ExcelService
{
    /**
     * 匯入 Excel -> 陣列
     * @param string $filePath 檔案路徑
     * @param int $headerRow 標題列行號 (1-based)
     * @param int $dataStartRow 資料起始行號
     * @return array [['col1'=>val, 'col2'=>val], ...]
     */
    public function import(string $filePath, int $headerRow = 1, int $dataStartRow = 2): array
    {
        $spreadsheet = IOFactory::load($filePath);
        $sheet = $spreadsheet->getActiveSheet();

        $highestRow = $sheet->getHighestRow();
        $highestCol = $sheet->getHighestColumn();
        $colCount = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestCol);

        // 讀取標題列
        $headers = [];
        for ($col = 1; $col <= $colCount; $col++) {
            $cell = $sheet->getCellByColumnAndRow($col, $headerRow);
            $headers[] = trim($cell->getValue() ?? "col_{$col}");
        }

        // 讀取資料
        $data = [];
        for ($row = $dataStartRow; $row <= $highestRow; $row++) {
            $rowData = [];
            $hasValue = false;
            for ($col = 1; $col <= $colCount; $col++) {
                $cell = $sheet->getCellByColumnAndRow($col, $row);
                $val = $cell->getValue();
                // 處理日期/時間儲存格
                if ($cell->getDataType() === \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_NUMERIC
                    && \PhpOffice\PhpSpreadsheet\Shared\Date::isDateTime($cell)) {
                    $val = \PhpOffice\PhpSpreadsheet\Shared\Date::dateTimeToString($cell->getValue());
                }
                $rowData[$headers[$col - 1]] = $val;
                if ($val !== null && $val !== '') $hasValue = true;
            }
            if ($hasValue) $data[] = $rowData;
        }
        return $data;
    }

    /**
     * 匯出陣列 -> Excel 下載
     * @param array $data 關聯陣列陣列 [['col1'=>val, 'col2'=>val], ...]
     * @param string $fileName 下載檔名 (不含副檔名)
     * @param string $format 'Xlsx'|'Xls'|'Csv'
     * @param array $options ['sheetName', 'headerStyle', 'columnWidths']
     */
    public function export(array $data, string $fileName = 'export', string $format = 'Xlsx', array $options = []): void
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle($options['sheetName'] ?? 'Sheet1');

        if (empty($data)) {
            // 空資料只寫標題
            return;
        }

        // 寫入標題列
        $headers = array_keys($data[0]);
        foreach ($headers as $colIndex => $header) {
            $col = $colIndex + 1;
            $sheet->setCellValueByColumnAndRow($col, 1, $header);
            // 標題樣式
            $sheet->getStyleByColumnAndRow($col, 1)->applyFromArray([
                'font' => ['bold' => true],
                'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'startColor' => ['rgb' => 'E0E0E0']],
                'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER],
            ]);
        }

        // 寫入資料
        foreach ($data as $rowIndex => $row) {
            $excelRow = $rowIndex + 2; // 第 2 列開始
            foreach ($row as $colIndex => $value) {
                $col = $colIndex + 1;
                $sheet->setCellValueByColumnAndRow($col, $excelRow, $value);
            }
        }

        // 自動欄寬
        foreach (range(1, count($headers)) as $col) {
            $sheet->getColumnDimensionByColumn($col)->setAutoSize(true);
        }

        // 固定欄寬 (可選)
        if (!empty($options['columnWidths'])) {
            foreach ($options['columnWidths'] as $col => $width) {
                $sheet->getColumnDimensionByColumn($col)->setWidth($width);
            }
        }

        // 輸出下載
        $writerClass = match (strtolower($format)) {
            'xls' => Xls::class,
            'csv' => \PhpOffice\PhpSpreadsheet\Writer\Csv::class,
            default => Xlsx::class,
        };
        $writer = new $writerClass($spreadsheet);

        // HTTP 標頭
        $ext = match (strtolower($format)) {
            'xls' => 'xls',
            'csv' => 'csv',
            default => 'xlsx',
        };
        header('Content-Type: ' . ($format === 'Csv' ? 'text/csv' : 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'));
        header('Content-Disposition: attachment; filename="' . $fileName . '.' . $ext . '"');
        header('Cache-Control: max-age=0');
        header('Expires: 0');

        $writer->save('php://output');
        exit;
    }
}

ThinkPHP 6/8 整合範例

<?php
namespace app\controller;

use think\Request;
use App\Service\ExcelService;

class ExcelController
{
    public function import(Request $request)
    {
        $file = $request->file('excel_file');
        if (!$file) return json(['code' => 400, 'msg' => '請上傳檔案']);

        // 驗證副檔名
        $ext = strtolower($file->getOriginalExtension());
        if (!in_array($ext, ['xlsx', 'xls', 'csv'])) {
            return json(['code' => 400, 'msg' => '僅支援 .xlsx/.xls/.csv']);
        }

        // 移至暫存
        $path = $file->move(sys_get_temp_dir(), 'excel_' . date('YmdHis') . '.' . $ext);
        if (!$path) return json(['code' => 500, 'msg' => '上傳失敗']);

        try {
            $excel = new ExcelService();
            $data = $excel->import($path->getRealPath());

            // 寫入資料庫範例
            foreach ($data as $row) {
                // User::create($row); // 依實際模型調整
            }
            @unlink($path->getRealPath());
            return json(['code' => 200, 'msg' => '匯入成功', 'count' => count($data)]);
        } catch (\Throwable $e) {
            @unlink($path->getRealPath());
            return json(['code' => 500, 'msg' => '解析失敗: ' . $e->getMessage()]);
        }
    }

    public function export()
    {
        // $data = User::select()->toArray(); // 從資料庫查詢
        $data = [
            ['id' => 1, 'name' => '張三', 'email' => 'zhang@example.com'],
            ['id' => 2, 'name' => '李四', 'email' => 'li@example.com'],
        ];

        $excel = new ExcelService();
        $excel->export($data, 'users_' . date('Ymd'), 'Xlsx', [
            'sheetName' => '用戶列表',
            'columnWidths' => [1 => 8, 2 => 20, 3 => 30],
        ]);
        // export() 內部已 exit，這裡不會執行到
    }
}

Laravel 整合 (推薦使用 Laravel-Excel 套件)

composer require maatwebsite/excel

// 1. 建立 Export 類別
php artisan make:export UsersExport --model=App\Models\User

// 2. app/Exports/UsersExport.php
namespace App\Exports;
use App\Models\User;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;

class UsersExport implements FromCollection, WithHeadings
{
    public function collection()
    {
        return User::select('id', 'name', 'email')->get();
    }
    public function headings(): array
    {
        return ['ID', '姓名', 'Email'];
    }
}

// 3. 控制器
public function export()
{
    return Excel::download(new UsersExport, 'users.xlsx');
}

大量資料效能優化

分塊讀取範例 (低記憶體匯入)

<?php
$reader = IOFactory::createReader('Xlsx');
$reader->setReadDataOnly(true); // 不讀樣式、公式

$chunkSize = 1000;
$filter = new \PhpOffice\PhpSpreadsheet\Reader\ChunkReadFilter();
$reader->setReadFilter($filter);

for ($startRow = 2; ; $startRow += $chunkSize) {
    $filter->setRows($startRow, $chunkSize);
    $spreadsheet = $reader->load($filePath);
    $sheet = $spreadsheet->getActiveSheet();
    $data = $sheet->toArray(null, true, true, true);
    if (empty(array_filter($data, fn($r) => array_filter($r)))) break; // 空資料結束

    // 處理 $data 批次寫入 DB
    // DB::table('users')->insert($data);
    $spreadsheet->disconnectWorksheets();
    unset($spreadsheet);
    gc_collect_cycles();
}

常見錯誤與解決

掌握 PhpSpreadsheet 核心 API，配合框架整合與效能優化，即可專業處理各類 Excel 匯入匯出需求。