[phpoffice]导出excel时带上图片，通用方法

杰克工作室

最近运营的一些产品需求，总是要求有商品的地方要把商品图片一块导出到excel。所以写了一个功能相对通用的方法。

 

首先先将phpoffice必须要用到的命名空间引入进来：

use PhpOffice\PhpSpreadsheet\Exception;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Exception as WriterException;
use PhpOffice\PhpSpreadsheet\style\Border;
use PhpOffice\PhpSpreadsheet\style\Alignment;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;

 

调用方法：

 

exportNew方法核心代码：

    exportNew(array $title, array $body, string $filename='', array $ext=[],  string $export_type='xlsx')
    {
        if(empty($title)){
            exit('表头数据为空');
        }
        if(empty($body)){
            exit('导出数据为空');
        }
        $title1 = array_keys($title);//字段名称
        $title2 = array_values($title);//列表头
 
        //生成列对应的数据字段名称
        foreach($title1 as $k => $v){
            $letter = intToChr($k);
            $title1_new[$letter] = $v;
        }
        unset($title1);
 
        //生成excel的表头
        foreach($title2 as $k => $v){
            $letter = intToChr($k);
            $title2_new[$letter] = $v;
        }
        unset($title2);
 
        //超大数据导出
        set_time_limit(0);
 
        //使用模板
        $spreadsheet = new Spreadsheet();
 
        //表格对象
        $worksheet = $spreadsheet->getActiveSheet();
 
        //画表头
        $startRow  = 1;
        foreach($title2_new as $k => $v){
            $width = 12;
            if(!empty($ext['column_width'])   && is_array($ext['column_width'])  ){
                foreach($ext['column_width']  as $k2 => $v2){
                    $key   = array_search($k2, $title1_new);
                    if($k == $key){
                        $width = $v2;
                    }
                }
            }
            $worksheet->getColumnDimension($k)->setWidth($width);//列宽
            $styleArray = [
                'borders' => [
                    'outline' => [
                        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                        'color' => ['argb' => '00000000'],
                    ],
                ],
                'fill' => [
                    'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR,
                    'rotation' => 90,
                    'startColor' => [
                        'argb' => '76933C',
                    ],
                    'endColor' => [
                        'argb' => '76933C',
                    ],
                ],
                'alignment' => [
                    'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                    'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
                ],
            ];
            $worksheet->getStyle($k.$startRow)->applyFromArray($styleArray);
            $worksheet->setCellValue($k.$startRow, $v);
            $worksheet->getRowDimension($startRow)->setRowHeight(25);
        }
 
        //画数据体
        foreach($body['list'] as $k => $v){//行
            $row = $startRow + $k + 1;
            foreach($title1_new as $k2 => $v2){//列
                if(empty($ext['image']['column'][$v2])){
                    $worksheet->setCellValue($k2.$row, $v[$v2]);
                }else{
                    $drawing = new Drawing();
                    //$drawing->setName($val['name']);
                    //$drawing->setDescription($val['name']);
 
                    $imgval = $v[$v2];
                    $imgurl = explode('?', $imgval)[0];
                    $fullfilename = $ext['image']['dir'] . basename($imgurl);
                    $tmp_img_arr[] = $fullfilename;
 
                    if(!is_file($fullfilename)){
                        continue;
                    }
                    $drawing->setPath($fullfilename);//注意，这里的$fullfilename必须为本地图片，如果图片在网上需要先下载下来
                    $drawing->setHeight(80);
                    //$drawing->setWidth(50);
                    $drawing->setCoordinates($ext['image']['column'][$v2] . $row);
                    $drawing->setWorksheet($worksheet);
 
                    //设置行高
                    $worksheet->getRowDimension($row)->setRowHeight(60);
                    unset($drawing);
                }
 
            }
            unset($body['list'][$k]);
        }
 
        //创建文件
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
 
        //下载表格
        $filename = empty($filename) ? date('Y-m-d_H-i', time()) : $filename;
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $filename  . '.xlsx"');
        header('Cache-Control: max-age=0');//禁止缓存
        $writer->save('php://output');
 
        //释放资源防止内存溢出
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet, $writer);
        exit();
    }

注意：$fullfilename必须为本地图片，如果你的图片是在阿里云或者七牛，需要先将这些图片下载到本地，不能实时取网上图片，否则程序运行相当慢，还可能导致图片取不到程序报错。

 

导出的excel效果：

如果还不清楚如何使用，请到群里问群主。