萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

PHPExcel (PhpSpreadsheet) 讀寫 Excel 實戰入門指南

套件選擇

  • 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* 標頭。
copyright © 萬盛學電腦網 all rights reserved