PHP 使用 PhpSpreadsheet 導出 Excel 完整實作

PHPExcel 已停止維護，**現代專案請使用 PhpSpreadsheet**（其官方繼任者）。本文提供完整導出實作：樣式、多工作表、大資料分塊、記憶體優化、下載回應。

安裝

composer require phpoffice/phpspreadsheet

基礎導出實作

<?php
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Font;

function exportUsers(array $users, string $filename = 'users.xlsx'): void
{
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('用戶列表');

    // 標題列
    $headers = ['ID', '姓名', 'Email', '註冊時間', '狀態'];
    $sheet->fromArray($headers, null, 'A1');

    // 標題樣式
    $headerStyle = [
        'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF'], 'size' => 12],
        'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '4472C4']],
        'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
        'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]],
    ];
    $sheet->getStyle('A1:E1')->applyFromArray($headerStyle);

    // 資料
    $data = [];
    foreach ($users as $i => $user) {
        $row = $i + 2;
        $data[] = [
            $user['id'],
            $user['name'],
            $user['email'],
            $user['created_at']->format('Y-m-d H:i:s'),
            $user['status'] ? '啟用' : '停用',
        ];
    }
    $sheet->fromArray($data, null, 'A2');

    // 資料區域樣式
    $lastRow = count($users) + 1;
    $sheet->getStyle("A2:E{$lastRow}")->applyFromArray([
        'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]],
        'alignment' => ['vertical' => Alignment::VERTICAL_CENTER],
    ]);

    // 自動欄寬
    foreach (range('A', 'E') as $col) {
        $sheet->getColumnDimension($col)->setAutoSize(true);
    }

    // 凍結首行
    $sheet->freezePane('A2');

    // 輸出下載
    $writer = new Xlsx($spreadsheet);
    
    // 清除輸出緩衝
    if (ob_get_level()) ob_end_clean();

    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment; filename="' . rawurlencode($filename) . '"');
    header('Cache-Control: max-age=0');
    header('Expires: 0');

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

// 使用
$users = [
    ['id' => 1, 'name' => '張三', 'email' => 'zhang@example.com', 'created_at' => new DateTime(), 'status' => true],
    ['id' => 2, 'name' => '李四', 'email' => 'li@example.com', 'created_at' => new DateTime('-1 day'), 'status' => false],
];
exportUsers($users);

大資料量導出優化（分塊寫入、低記憶體）

<?php
function exportLargeDataset(Generator $dataGenerator, string $filename): void
{
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    
    // 寫入標題
    $sheet->fromArray(['ID', 'Name', 'Value'], null, 'A1');
    
    $row = 2;
    $batchSize = 1000;
    $batch = [];

    foreach ($dataGenerator as $record) {
        $batch[] = [$record['id'], $record['name'], $record['value']];
        
        if (count($batch) >= $batchSize) {
            $sheet->fromArray($batch, null, "A{$row}");
            $row += $batchSize;
            $batch = [];
            
            // 定期釋放記憶體
            if ($row % 50000 === 0) {
                gc_collect_cycles();
            }
        }
    }
    
    // 餘數
    if ($batch) {
        $sheet->fromArray($batch, null, "A{$row}");
    }

    $writer = new Xlsx($spreadsheet);
    // ... 輸出標頭與 save('php://output')
}

多工作表導出

getActiveSheet();
$sheet1->setTitle('本月訂單');
$sheet1->fromArray($ordersThisMonth, null, 'A1');

// 工作表 2
$sheet2 = $spreadsheet->createSheet();
$sheet2->setTitle('上月訂單');
$sheet2->fromArray($ordersLastMonth, null, 'A1');

// 工作表 3
$sheet3 = $spreadsheet->createSheet();
$sheet3->setTitle('統計摘要');
$sheet3->fromArray($summary, null, 'A1');

// 設定預設開啟的工作表
$spreadsheet->setActiveSheetIndex(0);

進階樣式技巧

常見問題排查

替代方案：CSV 導出（極速、低記憶體）