Yii Framework 整合 PHPExcel/PhpSpreadsheet 匯出 Excel 完整實作

前言

Yii 2 專案整合試算表匯出功能，推薦使用 PhpSpreadsheet (PHPExcel 繼任者)。本文示範完整整合步驟：Composer 安裝、Autoloader 修正、Controller 匯出邏輯、大量資料優化。

一、安裝 PhpSpreadsheet

composer require phpoffice/phpspreadsheet

二、設定 Autoloader 相容性 (關鍵步驟)

Yii 2 使用 Composer Autoloader + 類別映射，PhpSpreadsheet 也有自己的 Autoloader。需修正 vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Autoloader.php 的 Register() 方法：

// vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Autoloader.php
public static function Register() {
    // 原始碼註解掉
    /*
    if (function_exists('__autoload')) {
        spl_autoload_register('__autoload');
    }
    return spl_autoload_register(array('PHPExcel_Autoloader', 'Load'));
    */
    
    // 替換為：保留現有 autoloader，加入 PhpSpreadsheet
    $functions = spl_autoload_functions();
    foreach ($functions as $function) {
        spl_autoload_unregister($function);
    }
    $functions = array_merge(
        [['PhpOffice\PhpSpreadsheet\Autoloader', 'Load']],
        $functions
    );
    foreach ($functions as $function) {
        spl_autoload_register($function);
    }
    return true;
}

三、Controller 匯出動作完整範例

<?php
namespace app\controllers;

use Yii;
use yii\web\Controller;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;

class ReportController extends Controller {
    public function actionExportExcel() {
        // 1. 建立試算表
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('用戶報表');
        
        // 2. 設定標題列
        $headers = ['ID', '帳號', '姓名', 'Email', '狀態', '建立時間'];
        foreach ($headers as $i => $header) {
            $col = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($i + 1);
            $sheet->setCellValue($col . '1', $header);
        }
        
        // 3. 標題樣式
        $headerStyle = [
            'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF'], 'size' => 11],
            'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '4472C4']],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
            'borders' => ['allBorders' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN]],
        ];
        $lastCol = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(count($headers));
        $sheet->getStyle("A1:{$lastCol}1")->applyFromArray($headerStyle);
        
        // 4. 取得資料 (分批避免記憶體爆炸)
        $query = (new \yii\db\Query())
            ->select(['id', 'username', 'name', 'email', 'status', 'created_at'])
            ->from('{{%user}}')
            ->where(['status' => 1])
            ->orderBy(['id' => SORT_ASC]);
        
        $batchSize = 500;
        $row = 2;
        foreach ($query->batch($batchSize) as $users) {
            foreach ($users as $user) {
                $sheet->setCellValue('A' . $row, $user['id']);
                $sheet->setCellValue('B' . $row, $user['username']);
                $sheet->setCellValue('C' . $row, $user['name']);
                $sheet->setCellValue('D' . $row, $user['email']);
                $sheet->setCellValue('E' . $row, $user['status'] == 1 ? '啟用' : '停用');
                $sheet->setCellValue('F' . $row, date('Y-m-d H:i:s', $user['created_at']));
                $row++;
            }
        }
        
        // 5. 自動欄寬
        foreach (range('A', $lastCol) as $col) {
            $sheet->getColumnDimension($col)->setAutoSize(true);
        }
        
        // 6. 凍結首行、自動篩選
        $sheet->freezePane('A2');
        $sheet->setAutoFilter("A1:{$lastCol}1");
        
        // 7. 輸出下載
        $filename = 'users_report_' . date('Ymd_His') . '.xlsx';
        
        // 關鍵：清除輸出緩衝，避免檔案損毀
        if (ob_get_level()) ob_end_clean();
        
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header("Content-Disposition: attachment; filename*=UTF-8''" . rawurlencode($filename));
        header('Cache-Control: max-age=0');
        header('Expires: 0');
        
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');
        Yii::$app->end(); // 確保不再執行後續輸出
    }
}
?>

四、大量資料優化：ChunkRead/Write + 快取

<?php
// 匯出超大資料集 (10萬列以上)
public function actionExportLarge() {
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    
    // 啟用儲存格快取 (降低記憶體)
    $cacheMethod = \PhpOffice\PhpSpreadsheet\CachedObjectStorageFactory::cache_to_diskISAM;
    \PhpOffice\PhpSpreadsheet\Settings::setCacheStorageMethod($cacheMethod);
    
    // 關閉預計算公式
    $spreadsheet->setPreCalculateFormulas(false);
    
    // 標題...
    $headers = ['ID', 'Data'];
    foreach ($headers as $i => $h) {
        $sheet->setCellValue(\PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($i+1) . '1', $h);
    }
    
    $query = (new \yii\db\Query())->from('huge_table')->batch(1000);
    $row = 2;
    foreach ($query as $batch) {
        foreach ($batch as $item) {
            $sheet->setCellValue('A' . $row, $item['id']);
            $sheet->setCellValue('B' . $row, $item['data']);
            $row++;
        }
        // 定期釋放 (可選)
        if ($row % 5000 === 0) {
            gc_collect_cycles();
        }
    }
    
    // 輸出...
    $writer = new Xlsx($spreadsheet);
    $writer->save('php://output');
    Yii::$app->end();
}
?>

五、匯入 Excel 對應實作 (補充)

<?php
public function actionImportExcel() {
    $file = \yii\web\UploadedFile::getInstanceByName('excel_file');
    if (!$file) throw new \yii\web\BadRequestHttpException('請上傳檔案');
    
    $ext = strtolower($file->extension);
    $readerClass = $ext === 'xls' 
        ? \PhpOffice\PhpSpreadsheet\Reader\Xls::class 
        : \PhpOffice\PhpSpreadsheet\Reader\Xlsx::class;
    
    $reader = new $readerClass();
    $reader->setReadDataOnly(true); // 不載入樣式
    $spreadsheet = $reader->load($file->tempName);
    $sheet = $spreadsheet->getActiveSheet();
    
    $data = [];
    foreach ($sheet->getRowIterator(2) as $row) { // 跳過標題
        $cellIterator = $row->getCellIterator();
        $cellIterator->setIterateOnlyExistingCells(false);
        $rowData = [];
        foreach ($cellIterator as $cell) {
            $rowData[] = $cell->getValue();
        }
        if (!empty(array_filter($rowData))) $data[] = $rowData;
    }
    
    // 批次寫入資料庫...
    \Yii::$app->db->createCommand()->batchInsert('target_table', ['col1', 'col2', 'col3'], $data)->execute();
    
    return $this->asJson(['success' => true, 'imported' => count($data)]);
}
?>

六、常見問題排查

七、Yii 2 專用擴充套件 (替代方案)

• composer require fedemotta/yii2-phpexcel (舊、PHPExcel)
• composer require phpoffice/phpspreadsheet + 自行封裝 Service/Helper (推薦)
• composer require mootensai/yii2-excel-export (GridView 直接匯出)

掌握 Yii 2 + PhpSpreadsheet 整合要點，即能建立穩健、高效能的 Excel 匯出入功能，滿足後台報表、資料遷移、使用者自助下載等多元需求。