使用 PHPExcel 庫同時支援導出 Excel 2003 (BIFF) 與 2007+ (OOXML) 格式,含欄寬、頁首頁尾、頁面設定完整範例。
php_zip, php_xml, php_gd2<?php
require_once 'common/excel/PHPExcel.php';
require_once 'common/excel/PHPExcel/Writer/Excel2007.php';
require_once 'common/excel/PHPExcel/Writer/Excel5.php';
require_once 'common/excel/PHPExcel/IOFactory.php';
$objExcel = new PHPExcel();
// 文件屬性
$objExcel->getProperties()
->setCreator('Andy')
->setLastModifiedBy('Andy')
->setTitle('Office 2003 XLS Test Document')
->setSubject('Test Document')
->setDescription('Generated by PHPExcel')
->setKeywords('office php excel')
->setCategory('Test result');
$objExcel->setActiveSheetIndex(0);
$sheet = $objExcel->getActiveSheet();
// 表頭
$headers = ['編號', '推廣代碼', '訪問來源', 'IP', '訪問時間'];
foreach ($headers as $i => $title) {
$col = chr(ord('A') + $i);
$sheet->setCellValue($col . '1', $title);
}
// 資料寫入 (假設 $links_list 為資料陣列)
foreach ($links_list as $k => $v) {
$row = $k + 2;
$sheet->setCellValue('A' . $row, $v['id']);
$sheet->setCellValue('B' . $row, $v['num']);
$sheet->setCellValue('C' . $row, $v['referer']);
$sheet->setCellValue('D' . $row, $v['ip']);
$sheet->setCellValue('E' . $row, $v['dateline']);
}
// 欄寬設定
$widths = ['A' => 10, 'B' => 10, 'C' => 70, 'D' => 15, 'E' => 15];
foreach ($widths as $col => $w) {
$sheet->getColumnDimension($col)->setWidth($w);
}
// 頁首頁尾
$sheet->getHeaderFooter()->setOddHeader('&L&BPersonal cash register&RPrinted on &D');
$sheet->getHeaderFooter()->setOddFooter('&L&B' . $objExcel->getProperties()->getTitle() . '&RPage &P of &N');
// 頁面設定
$sheet->getPageSetup()
->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT)
->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
// 輸出下載
$timestamp = time();
$format = isset($_GET['ex']) ? $_GET['ex'] : '2003';
if ($format === '2007') {
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="links_out' . $timestamp . '.xlsx"');
$writer = PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
} else {
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="links_out' . $timestamp . '.xls"');
$writer = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
}
header('Cache-Control: max-age=0');
$writer->save('php://output');
exit;
?>
php://output 直接輸出至瀏覽器,不佔用伺服器磁碟。Excel5 產生 .xls (BIFF),Excel2007 產生 .xlsx (OOXML)。PhpSpreadsheet 的串流寫入器。PHPExcel 官方已於 2017 年停止維護,請遷移至 PhpSpreadsheet,API 高度相容,支援 PHP 7.1+ 且效能更好。