您现在的位置是：网站首页> php专栏工具分享

tp5使用PhpSpreadsheet扩展导出excel

亦然2019-09-21 14:43:33【工具分享】人已围观

简介PhpSpreadsheet是一个纯PHP类库，它提供了一组类，允许您从不同的电子表格文件格式（如Excel和LibreOffice Calc）读取和写入。

最近项目上有需求，实现用查询的数据可以直接导出成excel。在此记录一下使用PhpSpreadsheet把数据导出的过程。

一：composer安装PhpSpreadsheet组件

#版本号也可忽略
composer require phpoffice/phpspreadsheet 版本号

安装完成之后，会在vendor文件夹下有个phpoffice文件。

如果composer安装卡住，需要更换一下国内源，下方是阿里云的镜像源地址。

composer config -g repo.packagist composer https://mirrors.aliyun.com/composer/

二：tp5简单导出实例

首先在控制器中引入类库。

//实际项目中，可根据情况引入
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

在控制器中，定义导出方法，如下：

 public function daochu()
    {
        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        //设置工作表标题名称
        $worksheet->setTitle('测试表');
        //表头
        //设置单元格内容
        $worksheet->setCellValueByColumnAndRow(1, 1, '测试');
        $worksheet->setCellValueByColumnAndRow(1, 2, '序号');
        $worksheet->setCellValueByColumnAndRow(2, 2, 'ip地址');
        $worksheet->setCellValueByColumnAndRow(3, 2, '子域名');
        $worksheet->setCellValueByColumnAndRow(4, 2, '标题');
        //合并单元格
        $worksheet->mergeCells('A1:E1');

        $styleArray = [
            'font' => [
                'bold' => true
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
            ],
        ];
        //设置单元格样式
        $worksheet->getStyle('A1')->applyFromArray($styleArray)->getFont()->setSize(28);

        $worksheet->getStyle('A2:E2')->applyFromArray($styleArray)->getFont()->setSize(14);
        ;
        //查询数据
        $jzInfo = db('dns')->where('domain_zhu','enn.cn')->select();
        $len = count($jzInfo);
        $j = 0;
        for ($i=0; $i < $len; $i++) {
            $j = $i + 3; //从表格第3行开始
            $worksheet->setCellValueByColumnAndRow(1, $j, $jzInfo[$i]['id']);
            $worksheet->setCellValueByColumnAndRow(2, $j, $jzInfo[$i]['ip']);
            $worksheet->setCellValueByColumnAndRow(3, $j, $jzInfo[$i]['domain']);
            $worksheet->setCellValueByColumnAndRow(4, $j, $jzInfo[$i]['title']);
        }

        $styleArrayBody = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => '666666'],
                ],
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
            ],
        ];
        $total_jzInfo = $len + 2;
        //添加所有边框/居中
        $worksheet->getStyle('A1:C'.$total_jzInfo)->applyFromArray($styleArrayBody);
        $filename = '测试表.xlsx';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');//禁止缓存
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
    }

测试结果如下：