Spreadsheet导出方法的实例，后续整理

Spreadsheet导出方法的实例。2021.12.7优化导出格式不匹配导致部分导出后无法打开的问题。

/**
     * 导出
     * @desc 导出
     * @method GET
     * @return string msg 提示信息
     * @return array data 返回数据
     * @return string data.data 200审核成功。400审核失败
     */
    public function export()
    {

// 查询要导出的数据
        $data = [
            [
                'id'=>1,
                'name'=>"micuer",
                'age'=>"18",
                'sex'=>"男",
                'room_name'=>"6.1班",
                'school_name'=>"西安一小",
                'create_time'=>"2021-6-15",
            ]
        ];
        // 实例化
        $spreadsheet = new Spreadsheet();

// 获取活动工作薄
        $sheet = $spreadsheet->getActiveSheet();

// 获取单元格
        $cellA = $sheet->getCell('A1');
        // 设置单元格的值
        $cellA->setValue('ID');
        // 设置 A 列 列宽
        $sheet->getColumnDimension('A')->setWidth(10);
        // 设置第一行 行高
        $sheet->getRowDimension(1)->setRowHeight(20);

$cellB = $sheet->getCell('B1');
        $cellB->setValue('学生头像');
        $sheet->getColumnDimension('B')->setWidth(10);

$cellC = $sheet->getCell('C1');
        $cellC->setValue('学生姓名');
        $sheet->getColumnDimension('C')->setWidth(10);

$cellD = $sheet->getCell('D1');
        $cellD->setValue('学生年龄');
        $sheet->getColumnDimension('D')->setWidth(10);

$cellE = $sheet->getCell('E1');
        $cellE->setValue('学生性别');
        $sheet->getColumnDimension('E')->setWidth(10);

$cellF = $sheet->getCell('F1');
        $cellF->setValue('所属班级');
        $sheet->getColumnDimension('F')->setWidth(30);

$cellG = $sheet->getCell('G1');
        $cellG->setValue('所属学校');
        $sheet->getColumnDimension('G')->setWidth(40);

$cellH = $sheet->getCell('H1');
        $cellH->setValue('创建时间');
        $sheet->getColumnDimension('H')->setWidth(30);

// 设置样式 标题栏
        $styleArray = [
            'alignment' => [
                'horizontal' => 'center', //水平居中
                'vertical' => 'center', //垂直居中
            ],
            'font' => [
                'name' => '黑体',
                'bold' => false,
                'size' => 10
            ]
        ];
        // 设置样式 正文
        $styleArrayBody = [
            'alignment' => [
                'horizontal' => 'center', //水平居中
                'vertical' => 'center', //垂直居中
            ],
            'font' => [
                'name' => '宋体',
                'bold' => false,
                'size' => 10
            ]
        ];
        // 应用样式
        $sheet->getStyle('A1')->applyFromArray($styleArray);
        $sheet->getStyle('B1')->applyFromArray($styleArray);
        $sheet->getStyle('C1')->applyFromArray($styleArray);
        $sheet->getStyle('D1')->applyFromArray($styleArray);
        $sheet->getStyle('E1')->applyFromArray($styleArray);
        $sheet->getStyle('F1')->applyFromArray($styleArray);
        $sheet->getStyle('G1')->applyFromArray($styleArray);
        $sheet->getStyle('H1')->applyFromArray($styleArray);

// 从 A2 开始填充数据
        foreach ($data as $k => $v) {
            $n = $k + 2;
            // 获取单元格
            $cellA = $sheet->getCell('A'.$n);
            // 设置单元格的值
            $cellA->setValue($v['id']);

$cellB = $sheet->getCell('B'.$n);
            $cellB->setValue('');

$cellC = $sheet->getCell('C'.$n);
            $cellC->setValue($v['name']);

$cellD = $sheet->getCell('D'.$n);
            $cellD->setValue($v['age']);

$cellE = $sheet->getCell('E'.$n);
            $cellE->setValue($v['sex']);

$cellF = $sheet->getCell('F'.$n);
            $cellF->setValue($v['room_name']);

$cellG = $sheet->getCell('G'.$n);
            $cellG->setValue($v['school_name']);

$cellH = $sheet->getCell('H'.$n);
            $cellH->setValue($v['create_time']);

// 再给表格体设置样式
            $sheet->getStyle('A'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('B'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('C'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('D'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('E'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('F'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('G'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('H'.$n)->applyFromArray($styleArrayBody);
        }

// 下载文件名
        $file_name = '回访工单导出'.date("YmdH-i-s").'.xlsx';
        header('Content-Type:application/vnd.ms-excel');
        header('Content-Disposition:attachment;filename='.$file_name);
        header('Cache-Control:max-age=0');
        // 下载
        ob_clean();
        ob_start();
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        $spreadsheet->disconnectWorksheets();/* 释放内存 */
        ob_end_flush();
        exit();
    }

注:原创不易,转载请注明出处( https://micuu.com/new/1970.html )，本站所有资源来源于网络收集，如有侵权请联系QQ245557979进行清除。

Spreadsheet导出方法的实例，后续整理

留言反馈