使用PhpSpreadsheet(PhpExcel)生成excel表格

这几天要做一个报表下载的功能，就用到了phpSpreadsheet这个扩展，在此记录。

说明：本方法是在TP5框架内构建，方法名及字段名均为虚构，示例无复杂逻辑运算。

首先，composer安装phpSpreadsheet

composer require phpoffice/phpspreadsheet

安装完成后即可进行功能的开发

代码如下

<?php
namespace app\class\controller;

use think\Controller;
use think\Db;
use think\exception\DbException;
use service\LogService;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

/**
 * 班级控制器
 * Class Classinfo
 * @package app\class\controller
 */
class Classinfo extends Controller
{
    public function downloadExcel()
    {
        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();

        //设置单元格内容
        $worksheet->setCellValueByColumnAndRow(1, 1, '序号');
        $worksheet->setCellValueByColumnAndRow(2, 1, '学生名称');
        $worksheet->setCellValueByColumnAndRow(3, 1, '年龄');
        $worksheet->setCellValueByColumnAndRow(4, 1, '性别');
        $worksheet->setCellValueByColumnAndRow(5, 1, '班级排名');
        
        //冻结表头
        $worksheet->freezePane('A2');
        
        //获取数据库数据
        try{
            $students = Db::name('test_class')->select();
        }catch(DbException $dbException){
            $this->error('数据库错误','');
            LogService::log($dbException->getMessage());
        }

        //设置数据
        foreach($students as $k=>$v){
            $kk = $k+2;
            $worksheet->setCellValueByColumnAndRow(1, $kk, $k+1);
            $worksheet->setCellValueByColumnAndRow(2, $kk, $v['name']);
            $worksheet->setCellValueByColumnAndRow(3, $kk, $v['age']);
            $worksheet->setCellValueByColumnAndRow(4, $kk, $v['gender']);
            $worksheet->setCellValueByColumnAndRow(5, $kk, $v['ranking']);
        }

        $styleArray = [
            'font' => [
                'bold' => true
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        //设置单元格样式
        $worksheet->getStyle('A1:I1')->applyFromArray($styleArray)->getFont()->setSize(10);
        //水平居中
        $worksheet->getStyle('A2:I100')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        //垂直居中
        $worksheet->getStyle('A1:I100')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        //行高
        $worksheet->getDefaultRowDimension()->setRowHeight(26);
        //列宽
        $worksheet->getColumnDimension('A')->setWidth(15);
        $worksheet->getColumnDimension('B')->setWidth(60);
        $worksheet->getColumnDimension('C')->setWidth(20);
        $worksheet->getColumnDimension('D')->setWidth(20);
        $worksheet->getColumnDimension('E')->setWidth(20);
        
        //设置文件名
        $filename = '统计.xlsx';
        //设置header头
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');
        //生成
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        //输出
        $writer->save('php://output');
    }
}