PhpSpreadsheet详解

php excel 导出 PhpSpreadsheet详解

引入正确的文件并实例化

use PhpOffice\PhpSpreadsheet\Spreadsheet;

$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();

赋值

//给A2赋值 hello
$worksheet->setCellValueByColumnAndRow(1, 2, 'hello');

设置标题

$worksheet->setTitle('Excel导出');

字体

//将A1至D1单元格设置成粗体
$worksheet->getStyle('A1:D1')->getFont()->setBold(true);

//将A1单元格设置成粗体，黑体，10号字
$worksheet->getStyle('A1')->getFont()->setBold(true)->setName('黑体')->setSize(10);

颜色

//将文字颜色设置成红色
$worksheet->getStyle('A1')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);

列宽

//将A列宽度设置成20
$worksheet->getColumnDimension('A')->setWidth(20);

//自动计算列宽
$worksheet->getColumnDimension('A')->setAutoSize(true);

//设置默认列宽20
$worksheet->getDefaultColumnDimension()->setWidth(20);

行高

//设置第一行行高为20pt
$worksheet->getRowDimension('1')->setRowHeight(20);

//设置默认行高
$worksheet->getDefaultRowDimension()->setRowHeight(20);

对齐

use PhpOffice\PhpSpreadsheet\Style\Alignment;
$styleArray = [
    'alignment' => [
        'horizontal' => Alignment::HORIZONTAL_CENTER, //水平居中
        'vertical' => Alignment::VERTICAL_CENTER, //垂直居中
    ],
];
$worksheet->getStyle('A1')->applyFromArray($styleArray);

边框

//红色边框
use PhpOffice\PhpSpreadsheet\Style\Border;
$styleArray = [
    'borders' => [
        'outline' => [
            'borderStyle' => Border::BORDER_THICK,
            'color' => ['argb' => 'FFFF0000'],
        ],
    ],
];
$worksheet->getStyle('B2:G8')->applyFromArray($styleArray);

合并单元格

$worksheet->mergeCells('A1:A4');

拆分单元格

$worksheet->unmergeCells('A1:A4');

换行

//使用 \n 进行单元格内换行，相当于 Alt+Enter
$worksheet->getCell('A1')->setValue("hello\nworld");
$worksheet->getStyle('A1')->getAlignment()->setWrapText(true);

超链接

$spreadsheet->getActiveSheet()->setCellValue('E6', '胖坨坨的博客');
$spreadsheet->getActiveSheet()->getCell('E6')->getHyperlink()->setUrl('https://blog.csdn.net/u011167662');

使用函数

$worksheet->setCellValue('A3', '=SUM(A1:A2)');
$worksheet->setCellValue('A3', '=MAX(A1:A2)');

导出

use PhpOffice\PhpSpreadsheet\IOFactory;
$write = IOFactory::createWriter($spreadsheet, 'Xlsx');
$write->save('php://output');
exit(); //需要退出程序，否则导出的excel文件会在打开时遇到错误

部分代码

use PhpOffice\PhpSpreadsheet\Spreadsheet;

$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();

$title = 'Excel导出';
$worksheet->setTitle($title);
$worksheet->mergeCells('B2:D2');
$worksheet->getRowDimension(1)->setRowHeight(50);
$worksheet->getStyle('B2:D2')->applyFromArray([
    'alignment' => [
        'horizontal' => Alignment::HORIZONTAL_CENTER,
        'vertical' => Alignment::VERTICAL_CENTER,
    ],
    'borders' => [
        'outline' => [
           'borderStyle' => Border::BORDER_THIN,
            'color' => ['argb' => '000000']
        ],
    ],
    'font' => [
        'name' => '黑体',
        'bold' => true,
        'size' => 22
    ]
]);
$worksheet->setCellValueByColumnAndRow(2, 2, $title);
$worksheet->setCellValueByColumnAndRow(2, 3, '姓名');
$worksheet->setCellValueByColumnAndRow(3, 3, '性别');
$worksheet->setCellValueByColumnAndRow(4, 3, '年龄');
$worksheet->setCellValueByColumnAndRow(2, 4, '张三');
$worksheet->setCellValueByColumnAndRow(3, 4, '男');
$worksheet->setCellValueByColumnAndRow(4, 4, '20');


header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename=Excel导出.xlsx');
header('Cache-Control: max-age=0');

$write = IOFactory::createWriter($spreadsheet, 'Xlsx');
$write->save('php://output');
exit();

效果如下

官网文档：https://phpspreadsheet.readthedocs.io/en/stable/