php使用PhpSpreadsheet 导出表格

1、PhpSpreadsheet 官方网址

• https://phpspreadsheet.readthedocs.io

2、PhpSpreadsheet 安装

• composer require phpoffice/phpspreadsheet

3、导出 demo 如下

<?php
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use \PhpOffice\PhpSpreadsheet\Style\Alignment;
use \PhpOffice\PhpSpreadsheet\Style\Border;


$spreadsheet = new Spreadsheet();

$worksheet = $spreadsheet->getActiveSheet();

$title = 'Excel导出';
$worksheet->setTitle($title);
$worksheet->mergeCells('A1:J1');

$worksheet->getStyle('A1:J1')->applyFromArray([
    'font' => [
        'name' => '黑体',
        'bold' => true,
        'size' => 22
    ]
]);
XlsRichText('（标题）','关于xxxxxx的工作','A1');

$spreadsheet->getActiveSheet()->getStyle('A3:J3')->applyFromArray(['font' => ['name' => '黑体', 'bold' => true]])->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);

XlsRichText('密级','：xxx','A2');

XlsRichText('紧急程度','：xxx','J2');

$worksheet->setCellValue('A3', '序号');
$worksheet->setCellValue('B3', '任务时间');
$worksheet->setCellValue('C3', '事件');
$worksheet->setCellValue('D3', '附件');
$worksheet->setCellValue('E3', '责任人');
$worksheet->setCellValue('F3', '登记人');
$worksheet->setCellValue('G3', '操作时间');
$worksheet->setCellValue('H3', '提醒日期');
$worksheet->setCellValue('I3', '是否完成');
$worksheet->setCellValue('J3', '备注');


$result = array(
    array(
        'time' =>date('Y-m-d',time()),
        'event' =>'事件1',
        'files' =>'附件名1、附件名2、附件名3、多个附件拼接显示1',
        'person' => '责任人1',
        'registrant' => '登记人1',
        'optime'    => '操作时间1',
        'remdate'   => '提醒日期1',
        'complete'  => '是否完成1',
        'remark'    => '备注1'
    ),
    array(
        'time' =>date('Y-m-d',time()),
        'event' =>'事件2',
        'files' =>'附件名1、附件名2、附件名3、多个附件拼接显示2',
        'person' => '责任人2',
        'registrant' => '登记人2',
        'optime'    => '操作时间2',
        'remdate'   => '提醒日期2',
        'complete'  => '是否完成2',
        'remark'    => '备注2'
    ),
    array(
        'time' =>date('Y-m-d',time()),
        'event' =>'事件3',
        'files' =>'附件名1、附件名2、附件名3、多个附件拼接显示3',
        'person' => '责任人3',
        'registrant' => '登记人3',
        'optime'    => '操作时间3',
        'remdate'   => '提醒日期3',
        'complete'  => '是否完成3',
        'remark'    => '备注3'
    ),
    array(
        'time' =>date('Y-m-d',time()),
        'event' =>'事件4',
        'files' =>'附件名1、附件名2、附件名3、多个附件拼接显示4',
        'person' => '责任人4',
        'registrant' => '登记人4',
        'optime'    => '操作时间4',
        'remdate'   => '提醒日期4',
        'complete'  => '是否完成4',
        'remark'    => '备注4'
    )
);


$row = 4;       //数据起始行
foreach ($result as $k=>$v){
    $worksheet->setCellValue('A'.$row, $k+1);
    $worksheet->setCellValue('B'.$row, $v['time']);
    $worksheet->setCellValue('C'.$row, $v['event']);
    $worksheet->setCellValue('D'.$row, $v['files']);
    $worksheet->setCellValue('E'.$row,$v['person']);
    $worksheet->setCellValue('F'.$row, $v['registrant']);
    $worksheet->setCellValue('G'.$row, $v['optime']);
    $worksheet->setCellValue('H'.$row, $v['remdate']);
    $worksheet->setCellValue('I'.$row, $v['complete']);
    $worksheet->setCellValue('J'.$row, $v['remark']);

    $row++;
}

XlsRichText('操作人','：xxx','A'.$row);

XlsRichText('操作日期','：xxx','J'.$row);

$styleArray = [
    'alignment' => [
        'horizontal' => Alignment::HORIZONTAL_CENTER,   //水平居中
        'vertical' => Alignment::VERTICAL_CENTER,       //垂直居中
    ],
    'borders' => [
        'allBorders' => [   //所有边框
            'borderStyle' => Border::BORDER_THIN,
            'color' => ['argb' => '000000']
        ],
    ],
];
$worksheet->getStyle('A1:J'.$row)->applyFromArray($styleArray);

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

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


/***
 * 写入文本值
 * @param $text1
 * @param $text2
 * @param $column
 * @throws \PhpOffice\PhpSpreadsheet\Exception
 */
function XlsRichText($text1,$text2,$column){
    global $spreadsheet;

    $richText = new \PhpOffice\PhpSpreadsheet\RichText\RichText();
    $payable = $richText->createTextRun($text1);
    $payable->getFont()->setBold(true);     //加粗
    $payable->getFont()->setColor( new \PhpOffice\PhpSpreadsheet\Style\Color( \PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLUE ) );
    $payable= $richText->createTextRun($text2);
    $payable->getFont()->setBold(true);
    $spreadsheet->getActiveSheet()->getCell($column)->setValue($richText);
}