[PHP知识库]thinkphp6 PhpSpreadsheet导出excel封装

因项目需要，自已封装了一个PhpSpreadsheet导出excel，不一定适用其他人，在此只是留记。

头部引用

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

 /**
     * 从数据库导出数据到表格
     * @param sring $title 首行标题内容
     * @param array $column        第二行列头标题
     * @param array $setWidth      第二行列头宽度
     * @param array $list          从数据库获取表格内容
     * @param array $keys          要获取的内容键名
     * @param array $lastRow       最后一行设置
     * @param string $filename     导出的文件名
     */
    public static function export(string $title, array $column, array $setWidth, array $list, array $keys, array $lastRow=[], string $filename='')
    {
        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        $count = count($column);
        // 合并首行单元格
        $worksheet->mergeCells(chr(65).'1:'.chr($count+64).'1');
        $styleArray = [
            'font'      => ['bold' => true],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER,],
        ];
        // 设置首行单元格内容
        $worksheet->setTitle($title);
        $worksheet->setCellValueByColumnAndRow(1, 1, $title);
        // 设置单元格样式
        $worksheet->getStyle(chr(65).'1')->applyFromArray($styleArray)->getFont()->setSize(18);
        $worksheet->getStyle(chr(65).'2:'.chr($count+64).'2')->applyFromArray($styleArray)->getFont()->setSize(12);
        // 设置列头内容
        foreach ($column as $key => $value) $worksheet->setCellValueByColumnAndRow($key+1, 2, $value);
        // 设置列头格式
        foreach ($setWidth as $k => $v) $worksheet->getColumnDimension(chr($k+65))->setWidth(intval($v));
        // 从数据库获取表格内容
        $len = count($list);
        $j = 0;
        for ($i=0; $i < $len; $i++){
            $j = $i + 3; //从表格第3行开始
            foreach ($keys as $kk => $vv){
                $worksheet->setCellValueByColumnAndRow($kk+1, $j, $list[$i][$vv]);
            }
        }
        $total_jzInfo = $len + 2;
        $styleArrayBody = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => '666666'],
                ],
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
            ],
        ];
        // 最后一行计算值
        if (!empty($lastRow)) {
            // 合并最后一行
            $worksheet->mergeCells(chr(65).($len+3).':'.chr(count($lastRow)+64).($len+3));
            foreach ($lastRow as $item)
            {
                $worksheet->setCellValueByColumnAndRow(array_keys($lastRow,$item)[0], $len+3, $item);
            }
            $total_jzInfo = $len + 3;
        }
        // 添加所有边框/居中
        $worksheet->getStyle(chr(65).'1:'.chr($count+64).$total_jzInfo)->applyFromArray($styleArrayBody);
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition:attachment;filename={$filename}.xlsx");
        header('Cache-Control: max-age=0');//禁止缓存
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
    }

调用如下：

        $title = '第一行合并居中标题';
        $column = ['日期','店铺','订单号','姓名','手机号','订单金额','备注'];
        $setWidh = ['15','30','30','15','15','15','15'];
        $list = $this->app->db->name($this->table)->->field($field)->order('create_at asc,id desc')->select()->toArray();
        $keys = ['create_at','shop_name','order_sn','user_name','user_phone','order_price','desc'];
        $last = ['1'=>'合计','6'=>'=SUM(F3:F'.(count($list)+2).')','7'=>'=SUM(G3:G'.(count($list)+2).')','8'=>'=SUM(H3:H'.(count($list)+2).')','9'=>'=SUM(F'.(count($list)+3).':H'.(count($list)+3).')'];
        $filename = "表格名字";
        ExcelExtend::export($title, $column, $setWidh, $list, $keys, $last, $filename);