套件選擇
- PHPExcel:已停止維護(2017),不建議新專案使用。
- PhpSpreadsheet:官方繼任者,支援 .xlsx/.xls/.ods/.csv,活躍維護中。
composer require phpoffice/phpspreadsheet
讀取 Excel (.xlsx/.xls)
<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
$file = 'translations.xlsx';
$spreadsheet = IOFactory::load($file);
$sheet = $spreadsheet->getActiveSheet();
// 取得最大行列
$highestRow = $sheet->getHighestRow(); // 如 100
$highestCol = $sheet->getHighestColumn(); // 如 'G'
$highestColIdx = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestCol); // 7
$data = [];
for ($row = 1; $row <= $highestRow; $row++) {
$rowData = [];
for ($col = 1; $col <= $highestColIdx; $col++) {
$cell = $sheet->getCellByColumnAndRow($col, $row);
$val = $cell->getValue();
// 處理日期儲存格
if (\PhpOffice\PhpSpreadsheet\Shared\Date::isDateTime($cell)) {
$val = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($val)->format('Y-m-d');
}
$rowData[] = $val;
}
$data[] = $rowData;
}
// $data[0] 為標題列,後續可寫入資料庫
print_r($data);
?>
寫入 Excel (.xlsx)
<?php
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('用戶匯出');
// 寫入標題
$sheet->fromArray(['ID', '姓名', 'Email', '建立時間'], null, 'A1');
// 模擬資料
$users = [
[1, '張三', 'zhang@example.com', '2024-01-15'],
[2, '李四', 'li@example.com', '2024-01-16'],
];
$sheet->fromArray($users, null, 'A2');
// 樣式:標題加粗、凍結首行、自動欄寬
$sheet->getStyle('A1:D1')->getFont()->setBold(true);
$sheet->freezePane('A2');
foreach (range('A', 'D') as $col) {
$sheet->getColumnDimension($col)->setAutoSize(true);
}
// 輸出下載
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="users.xlsx"');
header('Cache-Control: max-age=0');
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
exit;
?>
大數據量記憶體優化:Read/Write Filter
<?php
// 僅讀取特定欄位
$filter = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx\ReadFilter();
$filter->setColumns(['A', 'B', 'C']); // 只讀 A-C 欄
$reader = IOFactory::createReader('Xlsx');
$reader->setReadFilter($filter);
$spreadsheet = $reader->load('huge.xlsx');
// 串流寫入 (避免記憶體爆炸)
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->setPreCalculateFormulas(false);
$writer->save('php://output');
?>
常見問題
- 日期亂碼:Excel 內部儲存日期為浮點數,需用
Date::excelToDateTimeObject() 轉換。
- 公式不計算:
$writer->setPreCalculateFormulas(true) 或手動 $sheet->calculateFormula()。
- 中文檔名下載亂碼:參考文章 66204 設定
filename* 標頭。