Php导出EXCEL 含图片

<?php
/** * GridExporterDisplayer * * @author zhusaidong <zhusaidong@gmail.com> */

namespace Zhusaidong\GridExporter;

use Encore\Admin\Grid;
use Encore\Admin\Grid\Column;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Exception;
use Maatwebsite\Excel\Concerns\WithEvents;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Encore\Admin\Grid\Exporters\AbstractExporter;
use Encore\Admin\Grid\Row;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Support\Str;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithCustomValueBinder;
use PHPExcel;
use PHPExcel_IOFactory;

class Exporter extends AbstractExporter implements FromCollection, WithHeadings, ShouldAutoSize, WithCustomValueBinder, WithEvents
{ 
    use Exportable;
    /** * @var array $columns */
    private $columns = [];
    /** * @var string $fileName 文件名 */
    private $fileName = 'Exporter.xlsx';
    /** * @var array $exclusions 排除项 */
    private $exclusions = [];
    /** * @var array $registerEvents */
    private $registerEvents = [];
    /** * 设置头部标题 * @var array */
    private $headerCellValue = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];

    private static $type;
    private static $fileNames;

    /** * @inheritDoc */
    public function headings() : array
    { 
        if(!empty($this->columns))
        { 
            return $this->columns;
        }

        $this->exclusions = collect($this->exclusions)->map(static function($exclusion)
        { 
            return Str::snake($exclusion);
        });

        $this->columns = $this->grid->visibleColumns()->mapWithKeys(static function(Column $column)
        { 
            return [$column->getName() => $column->getLabel()];
        })->except($this->exclusions);

        return $this->columns->toArray();
    }

    /** * @inheritDoc */
    public function collection()
    { 
        $lists = [];
        $this->grid->build();
        /** * @var Row $row */
        foreach($this->grid->rows() as $row)
        { 
            $data = [];

            foreach($this->columns as $key => $column)
            { 
                $data[$column] = trim(strip_tags(preg_replace(/** @lang text */ '/<script(.*)>(.*)<\/script>/iUs', '', $row->column($key))));
            }
            $lists[] = $data;
        }
        if (self::$type == 1) { 
            $this->explodeExcel($lists);
        }else{ 
            return new Collection($lists);
        }
    }



    /** * {@inheritdoc} */
    public function export()
    { 
        $this->download($this->fileName)->prepare(request())->send();

        exit;
    }

    /** * @inheritDoc * @throws Exception */
    public function bindValue(Cell $cell, $value)
    { 
        $cell->setValueExplicit($value, DataType::TYPE_STRING);

        return true;
    }

    /** * @inheritDoc */
    public function registerEvents() : array
    { 
        return $this->registerEvents;
    }

    /** * 设置文件名 * * @param string $fileName * * @return Exporter */
    public function setFileName(string $fileName) : Exporter
    { 

        if(empty(pathinfo($fileName, PATHINFO_EXTENSION)))
        { 
            $fileName .= '.xlsx';
        }
        $this->fileName = $fileName;
        return $this;
    }

    /** * 排除项 * * @param array $exclusions * * @return Exporter */
    public function setExclusions(array $exclusions) : Exporter
    { 
        $this->exclusions = array_merge_recursive($this->exclusions, $exclusions);

        return $this;
    }

    /** * 排除项 * * @param string $exclusion * * @return Exporter */
    public function setExclusion(string $exclusion) : Exporter
    { 
        $this->exclusions[] = $exclusion;

        return $this;
    }

    /** * @param array $registerEvents */
    public function setRegisterEvents(array $registerEvents) : void
    { 
        $this->registerEvents = $registerEvents;
    }

    /** * 获取 Grid exporter * * @param Grid $grid * * @return NULL|Exporter */
    public static function get(Grid $grid,$type='',$fileNames = 'Exporter.xlsx') : ?Exporter
    { 
        self::$type = $type;
        self::$fileNames = $fileNames;

        return (function()
        { 
            return $this->exporter instanceof Exporter ? $this->exporter : null;
        })->call($grid);
    }
    public function explodeExcel($dataMean)
    { 
        $objPHPExcel = new PHPExcel();
        $obj_Sheet = $objPHPExcel->getActiveSheet(); //获得当前活动sheet的活动对象
        $obj_Sheet->setTitle(self::$fileNames);//设置当前活动Sheet名称
        $styleThinBlackBorderOutline = array(
            'borders' => array(
                'allborders' => array( //设置全部边框
                    'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
                ),

            ),
        );
        $i = 2;
        $index = 0;
        $num = 0;
        $model = $objPHPExcel->setActiveSheetIndex(0);
        $pic_index = 0;
        foreach (array_keys($dataMean[0]) as $key => $val){ 

            if($val == '图一'){ 
                continue;
            }
            if(strstr($val, '图一')){ 
                $pic_value = $val;
                $pic_index = $key;
            };
            if(count($this->headerCellValue)+1 == $key){ 
                $index = 0;
                $num ++;
            }

            $cellval = null;
            if($num > 0){ 
                for ($ik =0; $ik <= $num; $ik++){ 
                    if($ik==0){ 
                        $cellval .= $this->headerCellValue[$ik];
                    }else{ 
                        $cellval .= $this->headerCellValue[$index];
                    }
                }
            }else{ 
                $cellval = $this->headerCellValue[$index];
            }
            $model->setCellValue($cellval.'1', $val);
            $index++;
        }
        foreach ($dataMean as $k => $v) { 
            // 图片位置
            /* 实例化插入图片类 */
            $objDrawing = new \PHPExcel_Worksheet_Drawing ();
            /* 设置图片路径 切记:只能是本地图片 */
            if(@getimagesize(env('APP_URL').$v[$pic_value])){ 
// if(file_exists(env('APP_URL').$v['pic_front'])){ 
                $objDrawing->setPath ('.'.$v[$pic_value]);
                /* 设置图片高度 */
                $objDrawing->setHeight ( 100 );
                /* 设置图片要插入的单元格位置 */
                $objDrawing->setCoordinates ( $this->headerCellValue[$pic_index] .$i);
                // 写入图片在指定格中的X坐标值
                $objDrawing->setOffsetX ( 20 );
                // 写入图片在指定格中的Y坐标值
                $objDrawing->setOffsetY ( 20 );
                // 设置旋转角度
                // $objDrawing->setRotation(20);
                $objDrawing->getShadow ()->setVisible ( true );
                $objDrawing->getShadow ()->setDirection ( 50 );
                $objDrawing->setWorksheet ( $objPHPExcel->getActiveSheet () );
            }

            $index = 0;
            $num = 0;
            $models = $objPHPExcel->setActiveSheetIndex(0);
            foreach (array_keys($dataMean[0]) as $key => $val){ 
                if($val == '图一'){ 
                    continue;
                }
                if(count($this->headerCellValue)+1 == $key){ 
                    $index = 0;
                    $num ++;
                }
                $cellval = null;
                if($num > 0){ 
                    for ($ik =0; $ik <= $num; $ik++){ 
                        if($ik==0){ 
                            $cellval .= $this->headerCellValue[$ik];
                        }else{ 
                            $cellval .= $this->headerCellValue[$index];
                        }
                    }
                }else{ 
                    $cellval = $this->headerCellValue[$index];
                }

                if($val == $pic_value){ 
                    $index++;
                    continue;
                }
                $models->setCellValue($cellval.$i, $v[$val]);
                $index++;
            }
            $i++;
        }

        $index = 0;
        $num = 0;
        foreach (array_keys($dataMean[0]) as $key => $val){ 
            if($val == '款式图'){ 
                continue;
            }
            if(count($this->headerCellValue)+1 == $key){ 
                $index = 0;
                $num ++;
            }
            $cellval = null;
            if($num > 0){ 
                for ($ik =0; $ik <= $num; $ik++){ 
                    if($ik==0){ 
                        $cellval .= $this->headerCellValue[$ik];
                    }else{ 
                        $cellval .= $this->headerCellValue[$index];
                    }
                }
            }else{ 
                $cellval = $this->headerCellValue[$index];
            }
            $objPHPExcel->getActiveSheet()->getstyle($cellval."1:".$this->headerCellValue[$index].$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $index++;
        }

        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(120);
        $objPHPExcel->getActiveSheet()->getColumnDimension($this->headerCellValue[$pic_index])->setWidth(20);
        $objPHPExcel->getActiveSheet()->getStyle( 'A1:'.$cellval.'1')->applyFromArray($styleThinBlackBorderOutline);
        $filename = urlencode(self::$fileNames).date('YmdHis');
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$filename.'.xls"');
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit();
    }
}
    原文作者:新老农
    原文地址: https://blog.csdn.net/xcbzsy/article/details/109569395
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系博主进行删除。
点赞