常見誤區:header('Content-Disposition: attachment') 只能觸發瀏覽器下載,**無法直接存檔到伺服器目錄**。若需伺服器端生成檔案,須使用 PHPExcel(或 PhpSpreadsheet)程式庫寫入檔案系統。
| 方式 | 輸出目標 | 適用場景 |
|---|---|---|
| HTTP Header 下載 | 瀏覽器下載資料夾 | 使用者主動匯出報表 |
| PHPExcel 寫入檔案 | 伺服器指定路徑 | 定時任務、後台自動生成、附件存檔 |
<?php
require_once 'vendor/autoload.php'; // Composer 安裝 phpoffice/phpexcel
use PHPExcel;
use PHPExcel_Writer_Excel2007;
use PHPExcel_IOFactory;
function exportExcel($data, $title = [], $filename = 'export', $fields = []) {
if (!$data || !is_array($data)) return false;
// 可選:只匯出指定欄位且保持順序
if ($fields) {
$newData = [];
foreach ($data as $row) {
$newRow = [];
foreach ($fields as $f) $newRow[$f] = $row[$f] ?? '';
$newData[] = $newRow;
}
$data = $newData;
}
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("System")->setTitle($filename);
$sheet = $objPHPExcel->getActiveSheet();
$sheet->setTitle('Sheet1');
// 寫入標題列
if ($title) {
$col = 'A';
foreach ($title as $t) {
$sheet->setCellValue($col . '1', $t);
$col++;
}
$startRow = 2;
} else {
$startRow = 1;
}
// 寫入資料
foreach ($data as $rowIdx => $row) {
$col = 'A';
foreach ($row as $val) {
$sheet->setCellValue($col . ($startRow + $rowIdx), $val);
$col++;
}
}
// 自動欄寬
foreach ($sheet->getColumnIterator() as $col) {
$sheet->getColumnDimension($col->getColumnIndex())->setAutoSize(true);
}
// 存檔 .xlsx (Excel 2007+)
$writer = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$savePath = __DIR__ . "/exports/{$filename}_" . date('Ymd_His') . ".xlsx";
$writer->save($savePath);
return $savePath;
}
// 使用範例
$data = [
['id'=>1, 'name'=>'張三', 'email'=>'zhang@example.com'],
['id'=>2, 'name'=>'李四', 'email'=>'li@example.com'],
];
exportExcel($data, ['ID','姓名','Email'], 'users', ['id','name','email']);
// 生成檔案:exports/users_20240115_143022.xlsx
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo');
$objDrawing->setDescription('Company Logo');
$objDrawing->setPath(__DIR__ . '/logo.png'); // 本地圖片路徑
$objDrawing->setCoordinates('A1');
$objDrawing->setOffsetX(10);
$objDrawing->setWidth(120);
$objDrawing->setWorksheet($sheet);
composer require phpoffice/phpspreadsheet
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$spreadsheet = new Spreadsheet();
// ... 操作 API 大同小異
$writer = new Xlsx($spreadsheet);
$writer->save($path);
setCellValueExplicit 指定資料型別,減少自動推斷開銷flush() 釋放記憶體CSV 格式:fputcsv 速度快 10 倍以上,記憶體極低Spout(串流寫入,恆定記憶體)伺服器端生成 Excel 檔案,核心在於「程式庫寫入檔案系統」而非「HTTP 下載標頭」,選對工具與格式事半功倍。