PhpSpreadsheet 导出图片到 Excel

之前使用的是PHPExcel来做PHP程序的数据导出，但是ThinkPHP5版本就改成了PhpSpreadsheet，文档因为是英文的所以看不太懂，今天解决的是PhpSpreadsheet 导出图片到 Excel。

代码1：

 

      /**

 * [export 导出数据]

 */

public function export() {

 

//查询数据

$data = $this->model->field('id,user_id,thumb,content,create_time')->with(['user'])->order('create_time desc')->select();

 

//处理数据

if(!empty($data)){

foreach ($data as $key => $value) {

if(!empty($value['thumb'])){

$data[$key]['thumb'] = json_decode($value['thumb'], true);

}

//ip

/*if(isset($value['ip']) && !empty($value['ip'])){

$data[$key]['ip'] = getIpInfo($value['ip']);

}*/

}

}

 

//创建一个新的excel文档

$newExcel = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

        

//获取当前操作sheet的对象

        $objSheet = $newExcel->getActiveSheet();

 

        //设置当前sheet的标题

        $objSheet->setTitle('意见反馈');

 

        //设置宽度为true,不然太窄了

        $newExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);

        $newExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);

        $newExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);

        $newExcel->getActiveSheet()->getColumnDimension('D')->setWidth(40);

        $newExcel->getActiveSheet()->getColumnDimension('E')->setWidth(30);

 

        //设置第一栏的标题

        $objSheet->setCellValue('A1', 'ID')

                 ->setCellValue('B1', '用户')

                 ->setCellValue('C1', '图片')

                 ->setCellValue('D1', '内容')

                 ->setCellValue('E1', '时间');

 

        //第二行起，每一行的值,setCellValueExplicit是用来导出文本格式的。

        //->setCellValueExplicit('C' . $key, $val['admin_password']PHPExcel_Cell_DataType::TYPE_STRING),可以用来导出数字不变格式

        if(!empty($data)){

        foreach ($data as $key => $val) {

            $key = $key + 2;

            

            //设置第行高度

         $newExcel->getActiveSheet()->getRowDimension($key)->setRowHeight(65);

 

         //设置行值

            $objSheet->setCellValue('A' . $key, $val['id'])

                     ->setCellValue('B' . $key, $val['username'])

                     //->setCellValue('C' . $key, $val['thumb'][0])

                     ->setCellValue('D' . $key, $val['content'])

                     ->setCellValue('E' . $key, $val['create_time']);

 

            //处理图片

            if(!empty($val['thumb'][0])){

             $thumb = str_replace(request()->domain(), '.', $val['thumb'][0]);

             $drawing[$key] = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();

                $drawing[$key]->setName('图片');

                $drawing[$key]->setDescription('图片');

                $drawing[$key]->setPath($thumb);

                $drawing[$key]->setWidth(80);

                $drawing[$key]->setHeight(80);

                $drawing[$key]->setCoordinates('C'.$key);

                $drawing[$key]->setOffsetX(0);

                $drawing[$key]->setOffsetY(0);

                $drawing[$key]->setWorksheet($newExcel->getActiveSheet());

            } else {

             $objSheet->setCellValue('C' . $key, '');

            }

        }

        } else {

         $this->error('暂无数据');

        }

 

        //导出

        $filename = '意见反馈';

        $format = 'Xlsx';

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

        header("Content-Disposition: attachment;filename=" . $filename . date('Y-m-d') . '.' . strtolower($format));

        header('Cache-Control: max-age=0');

        $objWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($newExcel, $format);

        return $objWriter->save('php://output');

 

        //通过php保存在本地的时候需要用到

        //$objWriter->save($dir.'/demo.xlsx');

 

        //以下为需要用到IE时候设置

        // If you're serving to IE 9, then the following may be needed

        //header('Cache-Control: max-age=1');

        // If you're serving to IE over SSL, then the following may be needed

        //header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past

        //header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified

        //header('Cache-Control: cache, must-revalidate'); // HTTP/1.1

        //header('Pragma: public'); // HTTP/1.0

        exit;

}

 

代码2：

    public function export($data)

    {

        $spreadsheet = new Spreadsheet();

        $sheet = $spreadsheet->getActiveSheet();

        //设置sheet的名字  两种方法

        $sheet->setTitle('phpspreadsheet——demo');

        $spreadsheet->getActiveSheet()->setTitle('Hello');

        //设置第一行小标题

        $k = 1;

        $sheet->setCellValue('A' . $k, '问题');

        $sheet->setCellValue('B' . $k, '选项');

        $sheet->setCellValue('C' . $k, '答案');

        $sheet->setCellValue('D' . $k, '图片');

 

        // 设置个表格宽度

        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(16);

        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(80);

        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(15);

        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(20);

 

        // 垂直居中

        $spreadsheet->getActiveSheet()->getStyle('A')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);

        $spreadsheet->getActiveSheet()->getStyle('B')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);

        $spreadsheet->getActiveSheet()->getStyle('C')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);

        $spreadsheet->getActiveSheet()->getStyle('D')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);

 

        $info = $data;

        //  设置A单元格的宽度 同理设置每个

        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(20);

        //  设置第三行的高度

        $spreadsheet->getActiveSheet()->getRowDimension('3')->setRowHeight(50);

        //  A1水平居中

        $styleArray = [

            'alignment' => [

                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,

            ],

        ];

        $sheet->getStyle('A1')->applyFromArray($styleArray);

        //  将A3到D4合并成一个单元格

        $spreadsheet->getActiveSheet()->mergeCells('A3:D4');

        //  拆分合并单元格

        $spreadsheet->getActiveSheet()->unmergeCells('A3:D4');

        //  将A2到D8表格边框 改变为红色

        $styleArray = [

            'borders' => [

                'outline' => [

                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,

                    'color' => ['argb' => 'FFFF0000'],

                ],

            ],

        ];

        //  $sheet->getStyle('A2:E8')->applyFromArray($styleArray);

        //  设置超链接

        //  $sheet->setCellValue('D6', 'www.baidu.com');

        //  $spreadsheet->getActiveSheet()->setCellValue('E6', 'www.baidu.com');

        //  循环赋值

        $k = 2;

        foreach ($info as $key => $value) {

            $sheet->setCellValue('A' . $k, $value['question']);

            $sheet->setCellValue('B' . $k, $value['question_options']);

            $sheet->setCellValue('C' . $k, $value['answer']);

 

            $img = self::curlGet($value['img']);

            $dir = public_path('/temp/image/');

            $file_info = pathinfo($value['img']);

            if (!empty($file_info['basename'])) { //过滤非文件类型

                $basename = $file_info['basename'];

                is_dir($dir) OR mkdir($dir, 0777, true); //进行检测文件是否存在

                file_put_contents($dir . $basename, $img);

 

                $drawing[$k] = new Drawing();

                $drawing[$k]->setName('Logo');

                $drawing[$k]->setDescription('Logo');

                $drawing[$k]->setPath($dir . $basename);

                $drawing[$k]->setWidth(80);

                $drawing[$k]->setHeight(80);

                $drawing[$k]->setCoordinates('D'.$k);

                $drawing[$k]->setOffsetX(12);

                $drawing[$k]->setOffsetY(12);

                $drawing[$k]->setWorksheet($spreadsheet->getActiveSheet());

            } else {

                $sheet->setCellValue('D' . $k, '');

            }

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

            $k++;

        }

        $file_name = date('Y-m-d', time()) . rand(1000, 9999);

        //  第一种保存方式

        /*$writer = new Xlsx($spreadsheet);

        //保存的路径可自行设置

        $file_name = '../'.$file_name . ".xlsx";

        $writer->save($file_name);*/

        //  第二种直接页面上显示下载

        $file_name = $file_name . ".xls";

        header('Content-Type: application/vnd.ms-excel');

        header('Content-Disposition: attachment;filename="' . $file_name . '"');

        header('Cache-Control: max-age=0');

        $writer = IOFactory::createWriter($spreadsheet, 'Xls');

        //  注意createWriter($spreadsheet, 'Xls') 第二个参数首字母必须大写

        $writer->save('php://output');

    }

 

    public function getClient(){

        $client = new Client();

        return $client;

    }

 

    public static function curlGet($url)

    {

        $ch = curl_init();

        curl_setopt($ch, CURLOPT_URL, $url);

        curl_setopt($ch, CURLOPT_HEADER, 0);

        curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);

        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false); // 这个是重点 请求https。

        $data = curl_exec($ch);

        curl_close($ch);

        return $data;

    }