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();