PHP 讀取 Excel (.xls/.xlsx) 的標準作法是使用 PhpSpreadsheet (PHPExcel 後繼者,PHPExcel 已停止維護)。本文示範從安裝、載入、讀取儲存格、轉換為二維陣列的完整流程。
composer require phpoffice/phpspreadsheet
<?php
require_once 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Exception as ReaderException;
$filePath = './data.xlsx';
try {
// 自動偵測檔案類型 (Xls, Xlsx, Csv, Ods...)
$reader = IOFactory::createReaderForFile($filePath);
// 僅讀取資料,不載入格式/圖表,節省記憶體
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load($filePath);
} catch (ReaderException $e) {
die('檔案讀取失敗: ' . $e->getMessage());
}
// 取得第一個工作表
$sheet = $spreadsheet->getSheet(0);
// 取得最大行/列
$highestRow = $sheet->getHighestRow(); // 如 100
$highestColumn = $sheet->getHighestColumn(); // 如 'F'
$highestColumnIndex = PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // 6
// 逐列讀取轉為二維陣列
$data = [];
for ($row = 1; $row <= $highestRow; $row++) {
$rowData = [];
for ($col = 1; $col <= $highestColumnIndex; $col++) {
$cellValue = $sheet->getCellByColumnAndRow($col, $row)->getValue();
// 處理日期儲存格 (Excel 日期為浮點數)
$cell = $sheet->getCellByColumnAndRow($col, $row);
if (\PhpOffice\PhpSpreadsheet\Shared\Date::isDateTime($cell)) {
$cellValue = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($cellValue)->format('Y-m-d');
}
$rowData[] = $cellValue;
}
$data[] = $rowData;
}
// $data[0] 為標題列,$data[1] 起為資料列
var_dump($data);
// 僅讀取特定範圍 (如前 1000 列)
$reader->setReadFilter(new \PhpOffice\PhpSpreadsheet\Reader\ReadFilter());
class MyReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter {
public function readCell($column, $row, $worksheetName = '') {
return $row >= 1 && $row <= 1000; // 只讀前 1000 列
}
}
$reader->setReadFilter(new MyReadFilter());
SET NAMES utf8mb4,PhpSpreadsheet 內部已支援 UTF-8。Date::isDateTime() 判斷並轉換。$sheet->getMergeCells() 取得合併區塊,需自行複製值到對應座標。setReadDataOnly(true) + ReadFilter 分批讀取,或改用 Box/Spout 流式讀取庫。$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', '姓名')->setCellValue('B1', '年齡');
$sheet->fromArray([['小明', 25], ['小華', 30]], null, 'A2');
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('export.xlsx'); // 或 'Xls' 產出 .xls
採用 PhpSpreadsheet + Composer 自動載入,是現代 PHP 專案處理 Excel 的標準且可維護做法。