PHPExcel 實現 Excel 與資料庫雙向導入導出完整教程

PHPExcel 是經典的 PHP 試算表處理庫，支援讀寫 Excel 2007（.xlsx）與 Excel 5（.xls）格式。本文以實戰代碼示範從資料庫查詢資料、樣式化輸出、自動篩選、欄寬調整、合併儲存格、圖片插入、保護工作表等完整導出流程，並說明導入關鍵步驟。

導出 Excel 核心流程

require_once 'Classes/PHPExcel.php';
$objPHPExcel = new PHPExcel();

// 1. 設定文件屬性
$objPHPExcel->getProperties()->setCreator('Author')->setTitle('報表');

// 2. 設定工作表標題與樣式
$objPHPExcel->setActiveSheetIndex(0)
    ->setCellValue('A5', '商品編碼')
    ->setCellValue('B5', '貨號')
    ->setCellValue('C5', '商品名稱')
    ->setCellValue('D5', '採購量');
$objPHPExcel->getActiveSheet()->setTitle('祖名');

// 3. 合併儲存格、設定標題列
$objPHPExcel->getActiveSheet()->mergeCells('A1:D3')
    ->setCellValue('A1', 'zhongyi清單');
$objPHPExcel->getActiveSheet()->mergeCells('A4:D4')
    ->setCellValue('A4', '採購日期：' . date('Y-m-d'));

// 4. 定義樣式陣列
$headStyle = ['font' => ['bold' => true], 'alignment' => ['horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER]];
$titleStyle = ['font' => ['bold' => true]];
$centerStyle = ['alignment' => ['horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER]];
$thinBorder = ['borders' => ['outline' => ['style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => ['argb' => 'FF000000']]]];
$thickBorder = ['borders' => ['outline' => ['style' => PHPExcel_Style_Border::BORDER_THICK, 'color' => ['argb' => '000000']]]];

// 5. 套用樣式
$objPHPExcel->getActiveSheet()->getStyle('A1:D3')->applyFromArray($headStyle);
$objPHPExcel->getActiveSheet()->getStyle('A5:D5')->applyFromArray($titleStyle);

// 6. 寫入資料迴圈
$row_count = 5;
foreach ($dataRows as $r => $row) {
    $row_num = 6 + $r;
    $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A' . $row_num, $row['b'])
        ->setCellValue('B' . $row_num, $row['h'])
        ->setCellValue('C' . $row_num, $row['name'])
        ->setCellValue('D' . $row_num, $row['all']);
    // 套用邊框
    foreach (range('A', 'D') as $col) {
        $objPHPExcel->getActiveSheet()->getStyle($col . $row_num)->applyFromArray($thinBorder);
    }
    $row_count++;
}

// 7. 整體區域套用置中與外框
$objPHPExcel->getActiveSheet()->getStyle('A5:D' . $row_count)->applyFromArray($centerStyle);
$objPHPExcel->getActiveSheet()->getStyle('A1:D' . $row_count)->applyFromArray($thickBorder);

// 8. 進階功能
$objPHPExcel->getActiveSheet()->setAutoFilter('A5:D' . $row_count); // 自動篩選
$objPHPExcel->getActiveSheet()->getStyle('B6:B' . $row_count)->getAlignment()->setWrapText(true); // 自動換行
$objPHPExcel->getActiveSheet()->getStyle('A6:A' . $row_count)->getNumberFormat()->setFormatCode('0000000000'); // 數字格式

// 9. 工作表保護
$pwd = substr(md5(time()), 0, 8);
$objPHPExcel->getActiveSheet()->getProtection()->setPassword($pwd)->setSheet(true);

// 10. 儲存檔案
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('output.xls');

導入 Excel 關鍵步驟

$objPHPExcel = PHPExcel_IOFactory::load('upload.xls');
$sheet = $objPHPExcel->getActiveSheet();
$highestRow = $sheet->getHighestRow();
$highestCol = $sheet->getHighestColumn();

for ($row = 2; $row <= $highestRow; $row++) { // 假設第1行是標題
    $rowData = [];
    for ($col = 'A'; $col <= $highestCol; $col++) {
        $cell = $sheet->getCell($col . $row);
        $rowData[] = $cell->getValue();
    }
    // $rowData 寫入資料庫...
}

常見問題與優化

• 記憶體溢位：大檔案建議使用 PHPExcel_CachedObjectStorageFactory 啟用快取（Memory、DiskISAM、SQLite3），或改用 PhpSpreadsheet（PHPExcel 繼任者）與 XlsxReader 串流讀取。
• 日期處理：Excel 日期為數值序號，需用 PHPExcel_Shared_Date::ExcelToPHP() 轉換。
• 公式計算：寫入公式需呼叫 $objWriter->setPreCalculateFormulas(true)。
• 中文檔名下載：Header 設定 Content-Disposition: attachment; filename*=UTF-8'' . urlencode('中文報表.xls')。

遷移建議

PHPExcel 官方已停止維護，新專案強烈建議直接採用 PhpSpreadsheet，API 高度相容且效能更佳。