thinkPHP-Spreadsheet导出Excel-PDF添加图片

最新推荐文章于 2023-07-06 07:30:00 发布

lyx32609

最新推荐文章于 2023-07-06 07:30:00 发布

阅读量590

点赞数

分类专栏： thinkPHP PHP 文章标签： excel pdf

本文链接：https://blog.csdn.net/lyx32609/article/details/128329450

版权

PHP 同时被 2 个专栏收录

12 篇文章 0 订阅

订阅专栏

thinkPHP

7 篇文章 0 订阅

订阅专栏

thinkPHP-Spreadsheet导出Excel-PDF添加图片


    public  function exportData($head, $body, $name = '', $version = '2007',$title='导出记录')
    {
        try {
            // 输出 Excel 文件头
            if(empty($name)){
                $name =date('Y-m-d-H-i-s');
            }

            $spreadsheet   = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();
            $sheetPHPExcel = $spreadsheet->setActiveSheetIndex(0);
            $char_index    = range('A', 'Z');
            //处理超过26列
            $a = 'A';
            foreach ($char_index as $item){
                $char_index[] = $a . $item;
            }
            $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(15);
            $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(15);

            // Excel 表格头
            foreach ($head as $key => $val) {
                $sheetPHPExcel->setCellValue("{$char_index[$key]}1", $val);
            }

            $spreadsheet->getActiveSheet()->setTitle($title);

            // Excel body 部分
            foreach ($body as $key => $val) {
                $row = $key + 2;
                $col = 0;
                foreach ($val as $k => $v) {
                    // $spreadsheet->getActiveSheet()->setCellValue("{$char_index[$col]}{$row}", $v);

                    $isImg = app()->getRootPath().'public'.$v;
                    $orImg = pathinfo($isImg);
                    if ($v && isset($orImg['extension']) && (($orImg['extension'] == 'jpg') || ($orImg['extension'] == 'png') || ($orImg['extension'] == 'gif'))){
                        $drawing[$row] = new Drawing();
                        $drawing[$row]->setName(isset($val['avatar']) && $val['avatar'] ? $val['avatar'] : 'Logo');
                        $drawing[$row]->setDescription(isset($val['avatar']) && $val['avatar'] ? $val['avatar'] : 'Logo');
                        $drawing[$row]->setPath($isImg);
                        $drawing[$row]->setWidth(80);
                        $drawing[$row]->setHeight(80);
                        $drawing[$row]->setCoordinates("{$char_index[$col]}".$row);
                        $drawing[$row]->setOffsetX(12);
                        $drawing[$row]->setOffsetY(12);
                        $drawing[$row]->setWorksheet($spreadsheet->getActiveSheet());
                    } else {
                        $sheet->setCellValue("{$char_index[$col]}{$row}", $v);
                    }

                    $sheet->getRowDimension($row)->setRowHeight(80); 

                    $col++;
                }
            }

            // 版本差异信息
            $version_opt = [
                '2007' => [
                    'mime'       => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
                    'ext'        => '.xlsx',
                    'write_type' => 'Xlsx',
                ],
                '2003' => ['mime'       => 'application/vnd.ms-excel',
                           'ext'        => '.xls',
                           'write_type' => 'Xls',
                ],
                'pdf'  => ['mime'       => 'application/pdf',
                           'ext'        => '.pdf',
                           'write_type' => 'PDF',
                ],
                'ods'  => ['mime'       => 'application/vnd.oasis.opendocument.spreadsheet',
                           'ext'        => '.ods',
                           'write_type' => 'OpenDocument',
                ],
            ];

            header('Content-Type: ' . $version_opt[$version]['mime']);
            header('Content-Disposition: attachment;filename="' . $name . $version_opt[$version]['ext'] . '"');
            header('Cache-Control: max-age=0');

            $objWriter = IOFactory::createWriter($spreadsheet, 'Xlsx');
            return $objWriter->save('php://output');
        } catch (Exception $e) {
            return $e->getMessage();
        }
    }