您现在的位置：程式碼之家 >> 編程語言 > >> 網頁程式設計 >> PHP 程式設計 >> PHP 相關知識

PHPExcel 库操作 Excel 完整教程：創建、樣式、圖片、導出下載

編輯：PHP 相關知識

PHPExcel 库操作 Excel 完整教程：創建、樣式、圖片、導出下載

PHPExcel（現已併入 PhpSpreadsheet）是 PHP 操作 Excel 最強大的庫，支持 .xls/.xlsx 讀寫、豐富樣式、圖片嵌入、公式計算等。本文整理核心用法速查。

基礎初始化與保存

require 'PHPExcel.php';
require 'PHPExcel/Writer/Excel2007.php'; // .xlsx
// require 'PHPExcel/Writer/Excel5.php';  // .xls

$objPHPExcel = new PHPExcel();
// 設置文檔屬性
$objPHPExcel->getProperties()
    ->setCreator('Author')
    ->setLastModifiedBy('Author')
    ->setTitle('Report')
    ->setSubject('Report')
    ->setDescription('Generated by PHPExcel')
    ->setKeywords('excel php')
    ->setCategory('Report');

// 保存文件
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save('report.xlsx');

// 直接瀏覽器下載
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="report.xls"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');

工作表與單元格操作

切換/創建 Sheet：setActiveSheetIndex(0) / createSheet()
設置 Sheet 標題：getActiveSheet()->setTitle('Sheet1')
寫入值：setCellValue('A1', 'String') / setCellValue('A2', 123) / setCellValue('C5', '=SUM(C2:C4)')（公式）
合並/拆分：mergeCells('A1:E22') / unmergeCells('A28:B28')
保護單元格：getProtection()->setSheet(true); protectCells('A3:E13', 'password')

樣式深度定制

類別	代碼示例
數字格式	`getStyle('E4')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);`
列寬	`getColumnDimension('B')->setAutoSize(true); getColumnDimension('D')->setWidth(12);`
字體	`getStyle('B1')->getFont()->setName('Candara')->setSize(20)->setBold(true)->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);`
對齊	`getStyle('D11')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); getStyle('A18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);`
邊框	`getStyle('A4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);`
邊框顏色	`getStyle('D13')->getBorders()->getLeft()->getColor()->setARGB('FF993300');`
填充顏色	`getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FF808080');`

插入圖片

$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo')->setDescription('Logo');
$objDrawing->setPath('./images/logo.jpg')->setHeight(36);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

$objDrawing2 = new PHPExcel_Worksheet_Drawing();
$objDrawing2->setName('Paid')->setPath('./images/paid.png');
$objDrawing2->setCoordinates('B15')->setOffsetX(110)->setRotation(25);
$objDrawing2->getShadow()->setVisible(true)->setDirection(45);
$objDrawing2->setWorksheet($objPHPExcel->getActiveSheet());