PHPExcel 是早期 PHP 操作試算表最廣泛使用的函式庫,支援讀寫 xls (BIFF)、xlsx (OOXML)、CSV、ODS 等格式。雖然官方已於 2017 年停止維護,轉由 PhpSpreadsheet 繼承,但許多舊專案仍在使用。本文整理 PHPExcel 核心操作範例,並標註遷移建議。
php_zip, php_xml, php_gd2 (圖表/圖片支援)composer require phpoffice/phpexcel (已棄用,建議改 phpoffice/phpspreadsheet)PHPExcel:主物件,代表整個試算表檔案PHPExcel_Worksheet:單一工作表PHPExcel_Cell:單一儲存格PHPExcel_IOFactory:統一讀寫入口,自動偵測格式PHPExcel_Style_*:樣式相關類別 (字型、對齊、邊框、填滿等)<?php
require_once 'vendor/autoload.php'; // Composer 自動載入
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;
// 建立試算表物件
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('產品清單');
// 設定文件屬性
$spreadsheet->getProperties()
->setCreator('系統管理員')
->setLastModifiedBy('系統管理員')
->setTitle('產品匯出報表')
->setSubject('產品匯出報表')
->setDescription('自動產生的產品資料匯出')
->setKeywords('產品 匯出 Excel');
// 標題列資料
$titles = ['ID', '產品名稱', '分類', '單價', '庫存', '建立時間'];
foreach ($titles as $colIndex => $title) {
$col = $colIndex + 1; // 1-based
$sheet->setCellValueByColumnAndRow($col, 1, $title);
}
// 標題列樣式
$titleRange = 'A1:' . $sheet->getCellByColumnAndRow(count($titles), 1)->getCoordinate();
$sheet->getStyle($titleRange)->applyFromArray([
'font' => [
'bold' => true,
'color' => ['argb' => 'FFFFFFFF'],
'size' => 12,
],
'alignment' => [
'horizontal' => Alignment::HORIZONTAL_CENTER,
'vertical' => Alignment::VERTICAL_CENTER,
],
'borders' => [
'allBorders' => [
'borderStyle' => Border::BORDER_THIN,
'color' => ['argb' => 'FF000000'],
],
],
'fill' => [
'fillType' => Fill::FILL_SOLID,
'startColor' => ['argb' => 'FF4472C4'], // 藍色標題列
],
]);
// 模擬資料
$data = [
[1, '筆記型電腦', '3C電子', 25000, 50, '2024-01-15'],
[2, '無線滑鼠', '3C電子', 800, 200, '2024-02-20'],
[3, '機械鍵盤', '3C電子', 3200, 80, '2024-03-10'],
[4, '27吋螢幕', '3C電子', 12000, 30, '2024-01-28'],
];
// 寫入資料列
foreach ($data as $rowIndex => $row) {
$rowNum = $rowIndex + 2; // 從第 2 列開始
foreach ($row as $colIndex => $value) {
$col = $colIndex + 1;
$sheet->setCellValueByColumnAndRow($col, $rowNum, $value);
}
// 偶數列淡色背景
if ($rowIndex % 2 === 0) {
$range = 'A' . $rowNum . ':' . $sheet->getCellByColumnAndRow(count($titles), $rowNum)->getCoordinate();
$sheet->getStyle($range)->getFill()
->setFillType(Fill::FILL_SOLID)
->getStartColor()->setARGB('FFF2F2F2');
}
}
// 自動調整欄寬
foreach (range(1, count($titles)) as $col) {
$sheet->getColumnDimensionByColumn($col)->setAutoSize(true);
}
// 輸出檔案
$filename = 'products_' . date('Ymd_His') . '.xlsx';
$writer = new Xlsx($spreadsheet);
$writer->save($filename);
// 若為 Web 下載,改用:
// header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
// header('Content-Disposition: attachment;filename="' . $filename . '"');
// header('Cache-Control: max-age=0');
// $writer->save('php://output');
echo "檔案已產生:$filename";
<?php
require_once 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
$inputFile = 'products_20240115.xlsx';
try {
// 自動偵測格式並載入
$spreadsheet = IOFactory::load($inputFile);
$sheet = $spreadsheet->getActiveSheet();
echo "工作表標題: " . $sheet->getTitle() . PHP_EOL;
echo "尺寸: " . $sheet->calculateWorksheetDimension() . PHP_EOL;
// 迭代所有儲存格 (含空白)
foreach ($sheet->getRowIterator() as $row) {
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false); // 包含空白儲存格
$rowData = [];
foreach ($cellIterator as $cell) {
$rowData[] = $cell->getValue();
}
echo implode(' | ', $rowData) . PHP_EOL;
}
// 或僅讀取有資料的區塊 (高效能)
$highestRow = $sheet->getHighestRow();
$highestCol = $sheet->getHighestColumn();
$highestColIdx = Coordinate::columnIndexFromString($highestCol);
for ($row = 1; $row <= $highestRow; $row++) {
$rowData = [];
for ($col = 1; $col <= $highestColIdx; $col++) {
$cell = $sheet->getCellByColumnAndRow($col, $row);
$rowData[] = $cell->getValue();
}
// 處理資料...
}
} catch (Exception $e) {
echo "讀取失敗: " . $e->getMessage();
}
SET NAMES utf8mb4// 增加記憶體限制 (臨時)
ini_set('memory_limit', '512M');
// 或使用 cell caching 減少記憶體
$cacheMethod = \PhpOffice\PhpSpreadsheet\CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = ['memoryCacheSize' => '16MB'];
\PhpOffice\PhpSpreadsheet\Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
// 大量資料建議分批寫入,或改用 CSV 格式
// 明確設定儲存格格式
$sheet->getStyle('D2:D100')->getNumberFormat()
->setFormatCode('#,##0'); // 數值千分位
$sheet->getStyle('F2:F100')->getNumberFormat()
->setFormatCode('YYYY-MM-DD'); // 日期格式
// 或使用內建常數
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
$sheet->getStyle('F2:F100')->getNumberFormat()
->setFormatCode(NumberFormat::FORMAT_DATE_YYYYMMDD);
PHPExcel 已不再維護,新專案強烈建議直接使用 PhpSpreadsheet:
PHPExcel → PhpOffice\PhpSpreadsheetPHPExcel_ → 移除,改用命名空間IOFactory 用法相同'font' => ['bold' => true] 維持相容)PHPExcel/PhpSpreadsheet 提供豐富的試算表操作功能,適合報表匯出、資料匯入、樣板產生等場景。掌握核心 API (建立工作表、寫入儲存格、套用樣式、讀取迭代) 即可應付大多數需求。處理大量資料時務必注意記憶體管理,善用快取機制或改用串流式讀寫。