PHP生成Excel并设样式

wen PHP项目 2026-08-11 3

本文目录导读：

PHP生成Excel并设样式

安装PhpSpreadsheet
基础示例：创建带样式的Excel文件
高级功能示例
完整的实用函数
导出CSV格式
注意事项

我来为您提供一个完整的PHP生成Excel并设置样式的解决方案，这里使用最常用的PhpSpreadsheet库（PHPExcel的继承者）。

安装PhpSpreadsheet

composer require phpoffice/phpspreadsheet

基础示例：创建带样式的Excel文件

<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
// 创建Spreadsheet对象
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// 设置工作表标题
$sheet->setTitle('报表数据');
// 设置列宽
$sheet->getColumnDimension('A')->setWidth(15);
$sheet->getColumnDimension('B')->setWidth(20);
$sheet->getColumnDimension('C')->setWidth(15);
$sheet->getColumnDimension('D')->setWidth(30);
// === 设置表头样式 ===
$headerStyle = [
    'font' => [
        'bold' => true,
        'size' => 12,
        'color' => ['rgb' => 'FFFFFF'],
    ],
    'fill' => [
        'fillType' => Fill::FILL_SOLID,
        'startColor' => ['rgb' => '4A90D9'],
    ],
    'alignment' => [
        'horizontal' => Alignment::HORIZONTAL_CENTER,
        'vertical' => Alignment::VERTICAL_CENTER,
    ],
    'borders' => [
        'allBorders' => [
            'borderStyle' => Border::BORDER_THIN,
            'color' => ['rgb' => 'CCCCCC'],
        ],
    ],
];
// 设置表头数据
$headers = ['ID', '姓名', '年龄', '备注'];
foreach ($headers as $key => $header) {
    $column = chr(65 + $key); // A, B, C...
    $sheet->setCellValue($column . '1', $header);
}
// 应用表头样式
$sheet->getStyle('A1:D1')->applyFromArray($headerStyle);
$sheet->getRowDimension('1')->setRowHeight(30);
// === 设置数据区域样式 ===
$dataStyle = [
    'alignment' => [
        'horizontal' => Alignment::HORIZONTAL_LEFT,
        'vertical' => Alignment::VERTICAL_CENTER,
    ],
    'borders' => [
        'allBorders' => [
            'borderStyle' => Border::BORDER_THIN,
            'color' => ['rgb' => 'CCCCCC'],
        ],
    ],
];
// 添加示例数据
$data = [
    [1, '张三', 25, '开发部'],
    [2, '李四', 28, '测试部'],
    [3, '王五', 30, '运维部'],
    [4, '赵六', 22, '产品部'],
    [5, '孙七', 35, '设计部'],
];
// 写入数据并设置样式
$row = 2;
foreach ($data as $record) {
    foreach ($record as $col => $value) {
        $cell = chr(65 + $col) . $row;
        $sheet->setCellValue($cell, $value);
    }
    // 设置每行高度
    $sheet->getRowDimension($row)->setRowHeight(25);
    $row++;
}
// 应用数据区域样式
$lastRow = $row - 1;
$dataRange = 'A2:D' . $lastRow;
$sheet->getStyle($dataRange)->applyFromArray($dataStyle);
// === 高级样式设置 ===
// 1. 设置数字格式
$sheet->getStyle('C2:C' . $lastRow)
    ->getNumberFormat()
    ->setFormatCode(NumberFormat::FORMAT_NUMBER);
// 2. 设置条件格式（可选）
$condStyle = new \PhpOffice\PhpSpreadsheet\Style\Conditional();
$condStyle->setConditionType(\PhpOffice\PhpSpreadsheet\Style\Conditional::CONDITION_CELLIS);
$condStyle->setOperatorType(\PhpOffice\PhpSpreadsheet\Style\Conditional::OPERATOR_GREATERTHAN);
$condStyle->addCondition(28);
$condStyle->getStyle()->getFont()->getColor()->setARGB('FF0000');
$condStyle->getStyle()->getFill()->setFillType(Fill::FILL_SOLID);
$condStyle->getStyle()->getFill()->getStartColor()->setARGB('FFEB3C');
$sheet->getStyle('C2:C' . $lastRow)->setConditionalStyles([$condStyle]);
// 3. 冻结首行
$sheet->freezePane('A2');
// 4. 自动筛选
$sheet->setAutoFilter('A1:D' . $lastRow);
// 5. 设置打印区域
$sheet->setPrintArea('A1:D' . $lastRow);
// === 保存Excel文件 ===
$writer = new Xlsx($spreadsheet);
// 设置响应头
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="' . urlencode('报表_' . date('Y-m-d') . '.xlsx') . '"');
header('Cache-Control: max-age=0');
$writer->save('php://output');
exit;

高级功能示例

合并单元格和旋转文字

<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Font;
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// 合并单元格
$sheet->mergeCells('A1:F1');
$sheet->setCellValue('A1', '年度销售报表');
$sheet->getStyle('A1')->getFont()->setSize(18)->setBold(true);
$sheet->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
$sheet->getRowDimension('1')->setRowHeight(40);
// 竖排文字
$sheet->mergeCells('A2:A6');
$sheet->setCellValue('A2', '竖排文字');
$sheet->getStyle('A2')->getAlignment()
    ->setHorizontal(Alignment::HORIZONTAL_CENTER)
    ->setVertical(Alignment::VERTICAL_CENTER)
    ->setTextRotation(90); // 旋转90度
// 对角线样式
$sheet->getStyle('A1')->getBorders()->getDiagonal()->setBorderStyle('thin');
$sheet->getStyle('A1')->getBorders()->getDiagonal()->getColor()->setARGB('FF0000');

使用背景图片和主题色

<?php
// 设置单元格背景色
$fill = new Fill();
$fill->setFillType(Fill::FILL_GRADIENT_LINEAR);
$fill->setStartColor(new \PhpOffice\PhpSpreadsheet\Style\Color('FF0000'));
$fill->setEndColor(new \PhpOffice\PhpSpreadsheet\Style\Color('0000FF'));
$sheet->getStyle('A1')->setFill($fill);
// 设置图案填充
$sheet->getStyle('B2')->getFill()
    ->setFillType(Fill::FILL_PATTERN_STRIPED)
    ->setStartColor(new \PhpOffice\PhpSpreadsheet\Style\Color('FFFF00'))
    ->setEndColor(new \PhpOffice\PhpSpreadsheet\Style\Color('00FFFF'));

完整的实用函数

<?php
/**
 * 生成带样式的Excel报表
 * 
 * @param array $headers 表头数据 ['字段名' => '标题']
 * @param array $data 数据数组
 * @param string $title 报表标题
 * @return \PhpOffice\PhpSpreadsheet\Spreadsheet
 */
function createStyledExcel(array $headers, array $data, string $title) {
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    // 设置标题
    $sheet->setTitle('Sheet1');
    // 表头样式
    $headerStyle = [
        'font' => [
            'bold' => true,
            'size' => 11,
            'color' => ['rgb' => '1F4E78'],
        ],
        'fill' => [
            'fillType' => Fill::FILL_SOLID,
            'startColor' => ['rgb' => 'D9E1F2'],
        ],
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_CENTER,
            'vertical' => Alignment::VERTICAL_CENTER,
        ],
        'borders' => [
            'allBorders' => [
                'borderStyle' => Border::BORDER_THIN,
                'color' => ['rgb' => 'B4C6E7'],
            ],
        ],
    ];
    // 写入表头
    $colIndex = 1;
    foreach ($headers as $value) {
        $cell = $sheet->getCellByColumnAndRow($colIndex, 1);
        $cell->setValue($value);
        $colIndex++;
    }
    // 应用表头样式
    $headerColumn = range('A', 'Z')[$colIndex - 2];
    $sheet->getStyle("A1:$headerColumn" . '1')->applyFromArray($headerStyle);
    $sheet->getRowDimension('1')->setRowHeight(28);
    // 数据样式
    $dataStyle = [
        'font' => [
            'size' => 10,
        ],
        'alignment' => [
            'vertical' => Alignment::VERTICAL_CENTER,
        ],
        'borders' => [
            'allBorders' => [
                'borderStyle' => Border::BORDER_THIN,
                'color' => ['rgb' => 'DDEBF7'],
            ],
        ],
    ];
    // 写入数据
    $row = 2;
    foreach ($data as $rowData) {
        $colIndex = 1;
        foreach ($rowData as $value) {
            $sheet->setCellValueByColumnAndRow($colIndex, $row, $value);
            $colIndex++;
        }
        $row++;
    }
    // 应用数据样式
    $lastRow = max($row - 1, 1);
    $sheet->getStyle("A2:$headerColumn" . $lastRow)->applyFromArray($dataStyle);
    // 自动调整列宽
    foreach (range('A', $headerColumn) as $col) {
        $sheet->getColumnDimension($col)->setAutoSize(true);
    }
    // 设置交替行颜色（斑马纹）
    for ($i = 2; $i <= $lastRow; $i++) {
        if ($i % 2 == 0) {
            $sheet->getStyle("A$i:$headerColumn$i")
                ->getFill()
                ->setFillType(Fill::FILL_SOLID)
                ->setStartColor(['rgb' => 'F2F7FC']);
        }
    }
    return $spreadsheet;
}
// 使用示例
$headers = ['ID', '姓名', '部门', '薪资', '日期'];
$data = [
    [1, '张三', '技术部', 10000, '2024-01-15'],
    [2, '李四', '市场部', 9000, '2024-01-16'],
    [3, '王五', '财务部', 11000, '2024-01-17'],
];
$spreadsheet = createStyledExcel($headers, $data, '员工薪资表');
// 保存
$writer = new Xlsx($spreadsheet);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="员工薪资表.xlsx"');
$writer->save('php://output');

导出CSV格式

<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Writer\Csv;
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('file.xlsx');
$writer = new Csv($spreadsheet);
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="data.csv"');
$writer->save('php://output');

注意事项

性能优化：处理大量数据时，可以使用 setReadDataOnly() 和 setReadEmptyCells(false) 来提高性能。
内存问题：对于大文件，可以考虑使用 PhpSpreadsheet\Reader 逐行读取。
样式复制：使用 $sheet->getStyle('A1')->applyFromArray($styleArray) 可以快速应用样式。
字体支持：确保使用系统支持的字体，如宋体、微软雅黑等。

这个解决方案提供了完整的Excel生成和样式设置功能，满足了大多数业务需求,可以根据具体需求调整样式和功能。