laravelExcel3.1使用笔记（个人当做记录用的不建议看，乱乱乱 重要的事情说三遍）

调用

$resArr = [
			AfterSheet::class       => function(AfterSheet $event) use(
				$marginTop,$marginLeft,$marginBottom,$marginRight,$header,$footer,  //页边距
				$column,   //列宽
				$rowHeightArr,  //行高
				$fontNameDefault,
				$fontSizeDefault,
				$fontSizeProject,
		
				//材料数据
				$projectObj,
				$projectMaterialTypeObj,
				$materialArr,
				$user
	   
			){ 
			//设置页面属性
				$this->setPageProperty($event,$this->fileName,$marginTop,$marginBottom,$marginLeft,$marginRight,$header,$footer,true,PageSetup::ORIENTATION_LANDSCAPE,PageSetup::PAPERSIZE_A4,51,SheetView::SHEETVIEW_NORMAL);
				//设置列宽
				foreach ($column as  $key=> $value){
					$event->sheet->getDelegate()->getColumnDimension($key)->setWidth($value);
				}
			}

common

<?php


namespace App\Exports;


use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Worksheet\HeaderFooter;
use PhpOffice\PhpSpreadsheet\Worksheet\HeaderFooterDrawing;
use PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing;

class ExporstCommon
{

    /**
     * @content  设置excel 属性
     * @param $event object
     * @param $title string excel标题
     */
    public function setProperty($event,$title)
    {
            // Handle by a closure.
                $event->writer->getProperties()->setCreator('Patrick');

                //设置excel的属性：
                //创建人
                $event->writer->getProperties()->setCreator("S&W");
                //最后修改人
                $event->writer->getProperties()->setLastModifiedBy("S&W");
                //标题
                $event->writer->getProperties()->setTitle($title);
                //题目
                $event->writer->getProperties()->setSubject("白皮书");
                //描述
                $event->writer->getProperties()->setDescription("该文档于".date('Y年m月d日 H时i分s秒').'使用PHP生成');
                //关键字
                $event->writer->getProperties()->setKeywords("office ".date('Y')." 白皮书");
                //种类
                $event->writer->getProperties()->setCategory("白皮书");

    }


    /**
     * @content 设置页面属性
     * @param $event object
     * @param $title  string 页面标题
     * @param $marginTop float
     * @param $marginBottom float
     * @param $marginLeft float
     * @param $marginRight float
     * @param $header float
     * @param $footer float
     * @param $pageCentered bool  页面集中居中方式 true 为水平  false  为垂直
     * @param $orientation string 纸张方向  in PageSetup::ORIENTATION_LANDSCAPE
     * @param $paperSize float  纸张大小 in PageSetup::PAPERSIZE_A4
     * @param $scale integer 页面缩放比例  as 100
     * @param $ViewModel string 视图预览模式  in SheetView::SHEETVIEW_PAGE_BREAK_PREVIEW
     */
    public function setPageProperty($event,$title,$marginTop,$marginBottom,$marginLeft,$marginRight,$header,$footer,$pageCentered,$orientation,$paperSize,$scale,$ViewModel)
    {
        //文件标题
        $event->sheet->getDelegate()->setTitle($title);
        //设置页边距
//                $this->exporstCommonObj->set
        $event->sheet->getDelegate()->getPageMargins()->setTop($marginTop);       //上边距
        $event->sheet->getDelegate()->getPageMargins()->setBottom($marginBottom); //下
        $event->sheet->getDelegate()->getPageMargins()->setLeft($marginLeft);      //左
        $event->sheet->getDelegate()->getPageMargins()->setRight($marginRight);//右
        $event->sheet->getDelegate()->getPageMargins()->setHeader($header);//页眉
        $event->sheet->getDelegate()->getPageMargins()->setFooter($footer);//页脚
        if($pageCentered){
            $event->sheet->getDelegate()->getPageSetup()->setHorizontalCentered(true);//居中方式，水平
        }else{
            $event->sheet->getDelegate()->getPageSetup()->setVerticalCentered(true);//居中方式，垂直
        }
        $event->sheet->getDelegate()->getPageSetup()->setOrientation($orientation);  //纸张方向 横向
        $event->sheet->getDelegate()->getPageSetup()->setPaperSize($paperSize);      //纸张大小 A4
        $event->sheet->getDelegate()->getPageSetup()->setScale($scale);      //页面设置  > 缩放 >缩放比例
        $event->sheet->getDelegate()->getSheetView()->setView($ViewModel);  //设置视图模式为分页预览


    }


    /**
     * @content 设置行高
     * @param $event object
     * @param $row integer 行数
     * @param $height float 行高
     */
    public function setRowHeight($event ,$row,$height)
    {
        $event->sheet->getDelegate()->getRowDimension($row)->setRowHeight($height);
    }

    /**
     * @content 设置列宽
     * @param $event object
     * @param $column array
     */
    public function setColumnWidth($event,$column)
    {
        foreach ($column as  $key=> $value){
            $event->sheet->getDelegate()->getColumnDimension($key)->setWidth($value);
        }
    }


    /**
     * @content 设置单元格样式
     * @param $event object
     * @param $pCellCoordinate string   as :"A3:M4"
     * @param string $fontName
     * @param bool $fontBold
     * @param $fonSizes float
     * @param $horizontal string  in Alignment::HORIZONTAL_RIGHT
     * @param $vertical string in Alignment::VERTICAL_CENTER
     * @param bool $wrapText
     */
    public function setStyle($event,$pCellCoordinate,$fontName,$fontBold=true,$fonSizes,$horizontal,$vertical,$wrapText=true,$fontColor=[])
    {
        $event->sheet->getDelegate()->getStyle($pCellCoordinate)->applyFromArray([
            'font' => [
                'name' => $fontName??'微软雅黑',  //字体
                'bold' => $fontBold,  //加粗
                /*'italic' => true,  //斜体
                'underline' => Font::UNDERLINE_SINGLE,  //下划线
                'strikethrough' => false,*/
                'size'=>$fonSizes,
                'color'=>$fontColor,

            ],
            'alignment' => [ //居中
                'horizontal' => $horizontal, //右对齐
                'vertical' => $vertical,
                'wrapText' => $wrapText,  //文字换行
            ],
           
            
        ]);
    }





    /**
     * @content 设置边框
     * @param $event object
     * @param $pCellCoordinate string   as :"A3:M4"
     * @param $bottom array|bool|null
     * @param $top  array|bool|null
     * @param $left array|bool|null
     * @param $right array|bool|null
     */
    public function setStyleBorder($event,$pCellCoordinate,$top,$bottom,$left,$right)
    {
        $style = [];
        if($bottom){
            $style['bottom'] = [
                'borderStyle' =>$bottom['borderStyle'],
                'color' =>$bottom['color']??[ 'argb' => '00000000'   ],
            ];
        }

        if($top){
            $style['top'] = [
                'borderStyle' =>$top['borderStyle'],
                'color' =>$top['color']??[ 'argb' => '00000000'   ],
            ];
        }
        if($left){
            $style['left'] = [
                'borderStyle' =>$left['borderStyle'],
                'color' =>$left['color']??[ 'argb' => '00000000'   ],
            ];
        }
        if($right){
            $style['right'] = [
                'borderStyle' =>$right['borderStyle'],
                'color' =>$right['color']??[ 'argb' => '00000000'   ],
            ];
        }
        if(count($style)>0){
            $event->sheet->getDelegate()->getStyle($pCellCoordinate)->applyFromArray(['borders'=>$style ]);
        }
    }


    public function setRigntHeardImg($event,$leftimgSizeArr,$rightimgSizeArr)
    {
        $event->sheet->getDelegate()->getHeaderFooter()->setOddHeader('&L&G'.'&R&G');

        $imgUrlLeft = env('IMG_URL').$leftimgSizeArr['imgUrl'];
        $imgWidthLeft = $leftimgSizeArr['imgWidth'];
        $imgHeightLeft = $leftimgSizeArr['imgHeight'];
        $imgOffsetXLeft = $leftimgSizeArr['imgOffsetX'];
        $imgOffsetYLeft = $leftimgSizeArr['imgOffsetY'];
        $imgUrlLeft .= "?x-oss-process=image/resize,m_pad,h_$imgHeightLeft,w_$imgWidthLeft,color_FFFFFF";
        $filesize = @getimagesize($imgUrlLeft);
        if($filesize){
            $imgUrlLeftContent = file_get_contents($imgUrlLeft);
            file_put_contents(storage_path().'/tmp/left.png',$imgUrlLeftContent);

            $objDrawing1  = new HeaderFooterDrawing();
            $objDrawing1->setName('PHPExcel logo');
            $objDrawing1->setPath(storage_path().'/tmp/left.png');
            $objDrawing1->setHeight($imgHeightLeft);
            $objDrawing1->setWidth($imgWidthLeft);
            $objDrawing1->setOffsetX($imgOffsetXLeft);
            $objDrawing1->setOffsetY($imgOffsetYLeft);
            $event->sheet->getDelegate()->getHeaderFooter()->addImage($objDrawing1, HeaderFooter::IMAGE_HEADER_LEFT);

        }



        $imgUrlRight =  env('IMG_URL').$rightimgSizeArr['imgUrl'];
        $imgWidthRight = $rightimgSizeArr['imgWidth'];
        $imgHeightRight = $rightimgSizeArr['imgHeight'];
        $imgOffsetXRight = $rightimgSizeArr['imgOffsetX'];
        $imgOffsetYRight = $rightimgSizeArr['imgOffsetY'];
        $imgUrlRight .= "?x-oss-process=image/resize,m_pad,h_$imgHeightRight,w_$imgWidthRight,color_FFFFFF";
        $filesize = @getimagesize($imgUrlLeft);
        if($filesize){
            $imgUrlRightContent = file_get_contents($imgUrlRight);
            file_put_contents(storage_path().'/tmp/right.png',$imgUrlRightContent);


            $objDrawing = new HeaderFooterDrawing();
            $objDrawing->setName('PHPExcel logo');
            $objDrawing->setPath(storage_path().'/tmp/right.png');
            $objDrawing->setHeight($imgHeightRight);
            $objDrawing->setWidth($imgWidthRight);
            $objDrawing->setOffsetX($imgOffsetXRight);
            $objDrawing->setOffsetY($imgOffsetYRight);
            $event->sheet->getDelegate()->getHeaderFooter()->addImage($objDrawing, HeaderFooter::IMAGE_HEADER_RIGHT);

        }




    }
    
    
    /**
     * @content 设置图片 （本地）
     * @param $event object
     * @param $pcell string as:G9
     * @param $imgUrl string imgurl
     * @param $imgHeight
     * @param $imgWidth
     * @param $imgOffsetX
     * @param $imgOffsetY
     */
    public function setImg($event,$pcell,$imgUrl,$imgHeight,$imgWidth,$imgOffsetX,$imgOffsetY)
    {
        $imgUrl = env('IMG_URL').$imgUrl;
        $img = $imgUrl;

        $img = imagecreatefrompng($img);
        $width = imagesx($img);
        $height = imagesy($img);


        $objDrawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
        $objDrawing->setName('图片');
        $objDrawing->setDescription('图片');
        $objDrawing->setPath($img);
        $objDrawing->setHeight($imgHeight);
        $objDrawing->setWidth($imgWidth);
        $objDrawing->setOffsetX($imgOffsetX??0);
        $objDrawing->setOffsetY($imgOffsetY??0);

        $objDrawing->setCoordinates($pcell);
        $objDrawing->setWorksheet($event->sheet->getDelegate());



    }
    
    
    
    

    /**
     * @content 设置图片 (外网)
     * @param $event object
     * @param $pcell string as:G9
     * @param $imgUrl string imgurl
     * @param $imgHeight
     * @param $imgWidth
     * @param $imgOffsetX
     * @param $imgOffsetY
     */
    public function setImg1($event,$pcell,$imgUrl,$imgHeight,$imgWidth,$imgOffsetX,$imgOffsetY)
    {
        $imgUrl .= "?x-oss-process=image/resize,m_pad,h_$imgHeight,w_$imgWidth,color_FFFFFF";
        $imgUrl = env('IMG_URL').$imgUrl;
        $im = @getimagesize($imgUrl);
        if($im){

        $img = '';
        switch ($im[2])
        {
            case 1;
                $img = @imagecreatefromgif($imgUrl);
                break;
            case 2;
                $img = @imagecreatefromjpeg($imgUrl);
                break;
            case 3;
                $img = @imagecreatefrompng($imgUrl);
                break;
        }

        $image = imagecreatetruecolor($imgWidth, $imgHeight); //创建一个彩色的底图
        //2.上色
        $color=imagecolorallocate($image,255,255,255);
        //3.设置透明
        imagecolortransparent($image,$color);
        imagefill($image,0,0,$color);


        imagecopyresampled($image, $img, 0, 0, 0, 0,$imgWidth,$imgHeight,$im[0], $im[1]);


//        imagepng($image);
//        imagedestroy($image);


        /* $width = imagesx($img);
         $height = imagesy($img);*/
        $objDrawing = new MemoryDrawing();
        $objDrawing->setName('Sample image');
        $objDrawing->setDescription('Sample image');
        $objDrawing->setImageResource($image);
        $objDrawing->setWidth($imgWidth);
        $objDrawing->setHeight($imgHeight);
        $objDrawing->setOffsetX($imgOffsetX);
        $objDrawing->setOffsetY($imgOffsetY);
        $objDrawing->setRenderingFunction(MemoryDrawing::RENDERING_DEFAULT);//渲染方法

        $objDrawing->setCoordinates($pcell);
        $objDrawing->setWorksheet($event->sheet->getDelegate());

        //释放图片资源
//        imagedestroy($im);
      /*  imagedestroy($image);
        imagedestroy($img);*/
        }

    }
    /*public function setImg1($event,$pcell,$imgUrl,$imgHeight,$imgWidth,$imgOffsetX,$imgOffsetY)
    {

        $im = getimagesize($imgUrl);

        $img = '';
        switch ($im[2])
        {
            case 1;
                $img = @imagecreatefromgif($imgUrl);
                break;
            case 2;
                $img = @imagecreatefromjpeg($imgUrl);
                break;
            case 3;
                $img = @imagecreatefrompng($imgUrl);
                break;
        }

        $image = imagecreatetruecolor($imgWidth, $imgHeight); //创建一个彩色的底图

        //透明背景变黑处理
        //2.上色
        $color=imagecolorallocate($image,255,255,255);
        //3.设置透明
        imagecolortransparent($image,$color);
        imagefill($image,0,0,$color);


        imagecopyresampled($image, $img, 0, 0, 0, 0,$imgWidth,$imgHeight,$im[0], $im[1]);


//        imagepng($image);
//        imagedestroy($image);


       /* $width = imagesx($img);
        $height = imagesy($img);*/
        /*$objDrawing = new MemoryDrawing();
        $objDrawing->setName('Sample image');
        $objDrawing->setDescription('Sample image');
        $objDrawing->setImageResource($image);
        $objDrawing->setWidth($imgWidth);
        $objDrawing->setHeight($imgHeight);
        $objDrawing->setOffsetX($imgOffsetX);
        $objDrawing->setOffsetY($imgOffsetY);
        $objDrawing->setRenderingFunction(MemoryDrawing::RENDERING_DEFAULT);//渲染方法

        $objDrawing->setCoordinates($pcell);
        $objDrawing->setWorksheet($event->sheet->getDelegate());

        //释放图片资源
//        imagedestroy($im);
        imagedestroy($image);
        imagedestroy($img);*/

    /*}*/

     public function resizejpg($imgsrc,$imgdst,$imgwidth,$imgheight)
    {
        //$imgsrc jpg格式图像路径 $imgdst jpg格式图像保存文件名 $imgwidth要改变的宽度 $imgheight要改变的高度
        //取得图片的宽度,高度值
        $imgsrc = env('IMG_URL').$imgsrc;
        $arr = @getimagesize($imgsrc);
        header("Content-type: image/jpg");
        $imgWidth = $imgwidth;
        $imgHeight = $imgheight;
        // Create image and define colors
        $imgsrc = imagecreatefromjpeg($imgsrc);
        $image = imagecreatetruecolor($imgWidth, $imgHeight); //创建一个彩色的底图
        imagecopyresampled($image, $imgsrc, 0, 0, 0, 0,$imgWidth,$imgHeight,$arr[0], $arr[1]);
        imagepng($image);
        imagedestroy($image);
    }
	
	/**
	 * @content 阿拉伯数字转为中文数字（1-1 亿范围）123=> 一百二十三）
	 * @param $num
	 * @return mixed|string
	 */
	public function numToWord($num){
		$chiNum = array('零', '一', '二', '三', '四', '五', '六', '七', '八', '九');
		$chiUni = array('','十', '百', '千', '万', '十', '百', '千', '亿');
		$chiStr = '';
		$num_str = (string)$num;
		$count = strlen($num_str);
		$last_flag = true; //上一个 是否为0
		$zero_flag = true; //是否第一个
		$temp_num = null; //临时数字
		$chiStr = '';//拼接结果
		if ($count == 2) {//两位数
			$temp_num = $num_str[0];
			$chiStr = $temp_num == 1 ? $chiUni[1] : $chiNum[$temp_num].$chiUni[1];
			$temp_num = $num_str[1];
			$chiStr .= $temp_num == 0 ? '' : $chiNum[$temp_num];
		}else if($count > 2){
			$index = 0;
			for ($i=$count-1; $i >= 0 ; $i--) {
				$temp_num = $num_str[$i];
				if ($temp_num == 0) {
					if (!$zero_flag && !$last_flag ) {
						$chiStr = $chiNum[$temp_num]. $chiStr;
						$last_flag = true;
					}
					
					if($index == 4 && $temp_num == 0){
						$chiStr = "万".$chiStr;
					}
				}else{
					if($i == 0 && $temp_num == 1 && $index == 1 && $index == 5){
						$chiStr = $chiUni[$index%9] .$chiStr;
					}else{
						$chiStr = $chiNum[$temp_num].$chiUni[$index%9] .$chiStr;
					}
					$zero_flag = false;
					$last_flag = false;
				}
				$index ++;
			}
		}else{
			$chiStr = $chiNum[$num_str[0]];
		}
		return $chiStr;
	}

}