<?php
// +----------------------------------------------------------------------
// | ThinkCMF [ WE CAN DO IT MORE SIMPLE ]
// +----------------------------------------------------------------------
// | Copyright (c) 2013-2019 http://www.thinkcmf.com All rights reserved.
// +----------------------------------------------------------------------
// | Licensed ( http://www.apache.org/licenses/LICENSE-2.0 )
// +----------------------------------------------------------------------
// | Author: Powerless < wzxaini9@gmail.com>
// +----------------------------------------------------------------------

namespace app\portal\controller;

use cmf\controller\AdminBaseController;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing;
//渲染数据到excel
class AdminExcelDataController extends AdminBaseController
{

    //巡检，日检渲染数据到表格上
    public function inspectExcelData($arr){

        //设置脚本的最大执行时间，设置为0则无时间限制
        set_time_limit(0);
        ini_set("memory_limit","-1");

        $spreadsheet = new Spreadsheet();
        $adminCommon = new AdminCommonController();
        $sheet = $spreadsheet->getActiveSheet();
        $xlsName = $arr['time'];
        //设置sheet名称
        $name = '报表';
        $name = $adminCommon->iconv_to_utf8($name);

        $sheet->setTitle($xlsName.$name);
        //合并单元格
        $sheet->mergeCells('B1:J1');
        $sheet->mergeCells('B3:B4');
        $sheet->mergeCells('B6:D6');
        $sheet->mergeCells('B7:D7');
        $sheet->mergeCells('C3:D3');
        $sheet->mergeCells('C4:D4');
        $sheet->mergeCells('F3:F4');
        $sheet->mergeCells('G3:I3');
        $sheet->mergeCells('G4:I4');

        $spreadsheet->getDefaultStyle()->getFont()->setName('微软雅黑');//字体

        //设置默认宽
        $sheet->getDefaultColumnDimension()->setWidth(8);
        //设置高度
        $sheet->getRowDimension(1)->setRowHeight(45);
//        $sheet->getRowDimension(2)->setRowHeight(55);


        //***********************画出单元格边框*****************************
//        $styleArray = [
//            'borders' => [
//                'outline' => [
//                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
//                    'color' => ['argb' => 'FFFF0000'],
//                ],
//            ],
//        ];
//        $sheet->getStyle('A1:M26')->applyFromArray($styleArray);
        //***********************画出单元格边框结束*****************************

        //***********************背景填充颜色*****************************
        $sheet->getStyle( 'A1:M8')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
        $sheet->getStyle( 'A1:M8')->getFill()->getStartColor()->setRGB('222B35');//背景颜色
        //***********************画出单元格边框结束*****************************

        //标题
        $sheet->setCellValue('B1', $arr['title']);
        $sheet->getStyle('B1')->getFont()->setSize(18);//字体大小
        $sheet->getStyle('B1')->getFont()->getColor()->setRGB('FFFFFF');//设置颜色为白色
        $sheet->getStyle('B1')->getFont()->setBold(true);//字体加粗

        //甲方logo
        $imageA = $arr['a_company']['logo'];
        if($imageA){
            $img = $this->getFileSuffix($imageA);
            $objMemory = new MemoryDrawing();
            $objMemory->setImageResource($img);
            $objMemory->setRenderingFunction(MemoryDrawing::RENDERING_DEFAULT);//渲染方法
            $objMemory->setMimeType(MemoryDrawing::MIMETYPE_DEFAULT);
            //设置宽度高度
            $objMemory->setResizeProportional(false);
            $objMemory->setHeight(40);//照片高度
            $objMemory->setWidth(40); //照片宽度
            // /*设置图片要插入的单元格*/
            $objMemory->setCoordinates('B3');
            $objMemory->setWorksheet($sheet);
        }

        $sheet->getStyle('B3:G7')->getFont()->setSize(8);//字体大小
        $sheet->getStyle('C3:G3')->getFont()->getColor()->setRGB('949494');//设置颜色为灰色
        $sheet->getStyle('B6:G6')->getFont()->getColor()->setRGB('949494');//设置颜色为灰色
        $sheet->getStyle('C4:G4')->getFont()->getColor()->setRGB('FFFFFF');//设置颜色为白色
        $sheet->getStyle('B7:G7')->getFont()->getColor()->setRGB('FFFFFF');//设置颜色为白色

        //甲方公司
        $sheet->setCellValue('C3', '甲方公司');
        //甲方公司名称
        $sheet->setCellValue('C4', $arr['a_company']['company_name']);

        //乙方logo
        $imageB = $arr['b_company']['logo'];
        if($imageB){
            $img = $this->getFileSuffix($imageB);
            $objMemory = new MemoryDrawing();
            $objMemory->setImageResource($img);
            $objMemory->setRenderingFunction(MemoryDrawing::RENDERING_DEFAULT);//渲染方法
            $objMemory->setMimeType(MemoryDrawing::MIMETYPE_DEFAULT);
            //设置宽度高度
            $objMemory->setResizeProportional(false);
            $objMemory->setHeight(40);//照片高度
            $objMemory->setWidth(40); //照片宽度
            // /*设置图片要插入的单元格*/
            $objMemory->setCoordinates('F3');
            $objMemory->setWorksheet($sheet);
        }

        //乙方公司
        $sheet->setCellValue('G3', '乙方公司');
        //乙方公司名称
        $sheet->setCellValue('G4', $arr['b_company']['company_name']);

        //项目组
        $sheet->setCellValue('B6', '参与人员');
        //项目组名称
        $sheet->setCellValue('B7', $arr['group_name']);

        //巡检日期
        $sheet->setCellValue('E6', '巡检日期');
        //巡检日期名称
        $sheet->setCellValue('E7', $arr['create_time']);

        //巡检状态
        $sheet->setCellValue('G6', '巡检状态');
        //巡检状态名称
        $sheet->setCellValue('G7', $arr['inspect_status']);

        //循环开始
        $cols = 10;
        $cells = ['C','E','G','I','K'];
        $common = new AdminCommonController();
        $state = $common::inspectStatus;
        foreach($arr['point'] as $value){
            $cols += 0;
            //巡检点名称
            $array = [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
                'textRotation' => 0,
                'wrapText' => TRUE
            ];
            $sheet->mergeCells('A'.$cols.':B'.$cols);
            $user_login = !empty($value['user_login'])?'('.$value['user_login'].')':'';
            $sheet->setCellValue('A'.$cols, $value['point_name'].$user_login."\n".$state[$value['status']]);
            $sheet->getStyle('A'.$cols)->getAlignment()->applyFromArray($array);//垂直水平居中
            //获取图片个数
            $length = count($value['images']);
            $col = ceil($length/5);
            for($i=0;$i<$col;$i++){
                $col_i = $cols+$i;
                $sheet->getRowDimension($col_i)->setRowHeight(105);
                $sheet->mergeCells('C'.$col_i.':D'.$col_i);
                $sheet->mergeCells('E'.$col_i.':F'.$col_i);
                $sheet->mergeCells('G'.$col_i.':H'.$col_i);
                $sheet->mergeCells('I'.$col_i.':J'.$col_i);
                $sheet->mergeCells('K'.$col_i.':L'.$col_i);
                $key = 0;
                foreach($value['images'] as $k=>$image_value){
                    $pre = $i+4*$i;
                    $next = $i+1+4*($i+1);
                    if($k>=$pre && $k<$next){
                        //巡检图片
                        $image = $image_value['image_url'];
                        if($image){
                            $img = $this->getFileSuffix($image);
                            $objMemory = new MemoryDrawing();
                            $objMemory->setImageResource($img);
                            $objMemory->setRenderingFunction(MemoryDrawing::RENDERING_DEFAULT);//渲染方法
                            $objMemory->setMimeType(MemoryDrawing::MIMETYPE_DEFAULT);
                            //设置宽度高度
                            $objMemory->setResizeProportional(false);
                            $objMemory->setHeight(120);//照片高度
                            $objMemory->setWidth(105); //照片宽度
                            // /*设置图片要插入的单元格*/
                            $objMemory->setCoordinates($cells[$key].$col_i);
                            $objMemory->setWorksheet($sheet);
                        }
                        $key++;
                    }
                }
            }

            $cols += $col+1;
        }
        $sheet->getStyle( 'A9:M'.$cols)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
        $sheet->getStyle( 'A9:M'.$cols)->getFill()->getStartColor()->setRGB('F3F3F4');//背景颜色浅灰色


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

        //直接从浏览器中输出star
//        header('pragma:public');
//        header("Content-Disposition:attachment;filename=出货装箱单.xls");
//        $objWrite->save('php://output');
//        exit;
        //直接从浏览器中输出end

        $file_name = $xlsName.'.xls';

        $files_url = ROOT_PATH . '/public/upload/export';
        if(!is_dir($files_url)){
            mkdir($files_url);
        }
        $file_url = ROOT_PATH . '/public/upload/export/'.$file_name;
        $objWrite->save($file_url);

        $sys = $adminCommon->getOperateSys();
        if($sys == 'Linux'){
            chmod($file_url, 0777);//适用于linux
        }
        //相对路径
        $url = 'upload/export/'.$file_name;
        return $url;
    }

    /*
     * 导出表格适用于普通有规律的数据
     * param xlsName:title名称
     * param xlsCell:二维数据xlsCell = array(
            array('id','序号'),
            array('user_nickname','昵称'),
            array('mobile','手机号'),
            array('create_time','注册时间'),
            array('last_login_time','最后登录时间'),
            array('user_status','状态'),
        );
     * param xlsData：数据库数据
     */
    public function exportExcel($xlsName,$xlsCell,$xlsData){
        $topNumber = 1;//表头有几行占用
        $starData = $topNumber+1;//数据开始行起
        $cellKey = [
            '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',
            'AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM',
            'AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ'
        ];
        vendor("PHPExcel");

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle($xlsName);
        //处理表头
        foreach ($xlsCell as $k=>$v) {
            $sheet->setCellValue($cellKey[$k].$topNumber, $v[1]);//设置表头数据
            $sheet->getStyle($cellKey[$k].$topNumber)->getFont()->setBold(true);//设置是否加粗
        }
        //设置单元格居中显示
        $styleArray = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        //表头居中
        foreach ($xlsCell as $k2=>$v2) {
            $sheet->getStyle($cellKey[$k2].'1')->applyFromArray($styleArray);
        }
        //处理数据
        foreach ($xlsData as $k=>$v) {
            foreach ($xlsCell as $k1=>$v1) {
                //设置每列数据
                $sheet->setCellValue($cellKey[$k1].($k+$starData), $v[$v1[0]]);
                //每列宽度
                $sheet->getColumnDimension($cellKey[$k1])->setWidth(30);
                //设置居中显示
                $sheet->getStyle($cellKey[$k1].($k+$starData))->applyFromArray($styleArray);
            }
        }
        $file_name = $xlsName.'.xls';
//        ob_end_clean();//清除缓冲区,避免乱码
//        header('Content-Type: application/vnd.ms-excel');//告诉浏览器将要输出excel03文件
//        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器数据excel07文件
//        header('Content-Disposition: attachment;filename='.$file_name);
//        header('Cache-Control: max-age=0');
//        $writer = new Xlsx($spreadsheet);
        $writer = IOFactory::createWriter($spreadsheet, 'Xls'); //按照指定格式生成Excel文件
//        $writer->save('php://output');直接输入到浏览器下载
        //绝对路径，不存在则创建目录
        $files_url = ROOT_PATH . '/public/upload/export';
        if(!is_dir($files_url)){
            mkdir($files_url);
        }
        $file_url = ROOT_PATH . '/public/upload/export/'.$file_name;
        $writer->save($file_url);
        //相对路径
        $url = 'upload/export/'.$file_name;
        return $url;
    }

    //获取文件后缀
    public function getFileSuffix($image_url){
        //判断文件真实类型
//        $image_url = $image_url.'?imageslim';
        $suffix = getimagesize($image_url);
        if($suffix['mime'] == 'image/png'){
            //png
            return imagecreatefrompng($image_url);
        }else{
            //jpg
            return imagecreatefromjpeg($image_url);
        }
    }

    //月检渲染数据到表格上
    public function checkExcelData($arr){
        //设置脚本的最大执行时间，设置为0则无时间限制
        set_time_limit(0);
        ini_set("memory_limit","-1");

        $spreadsheet = new Spreadsheet();
        $adminCommon = new AdminCommonController();
        $sheet = $spreadsheet->getActiveSheet();
        $xlsName = $arr['time'];
        //设置sheet名称
        $name = '报表';
        $name = $adminCommon->iconv_to_utf8($name);

        $sheet->setTitle($xlsName.$name);
        //合并单元格

        //报表名称
        $sheet->mergeCells('B1:J1');

        //甲方logo，甲方公司名称
        $sheet->mergeCells('B3:B4');
        $sheet->mergeCells('C3:D3');
        $sheet->mergeCells('C4:D4');

        //乙方logo，乙方公司名称
        $sheet->mergeCells('F3:F4');
        $sheet->mergeCells('G3:I3');
        $sheet->mergeCells('G4:I4');

        //乙方发起人姓名
        $sheet->mergeCells('B6:C6');
        $sheet->mergeCells('B7:C7');

        //参与人
        $sheet->mergeCells('F6:H6');
        $sheet->mergeCells('F7:H7');

        //月检状态
        $sheet->mergeCells('I6:J6');
        $sheet->mergeCells('I7:J7');

        //甲方员工确认人
        $sheet->mergeCells('B9:C9');
        $sheet->mergeCells('B10:C10');

        //乙方员工确认人
        $sheet->mergeCells('D9:E9');
        $sheet->mergeCells('D10:E10');


        $spreadsheet->getDefaultStyle()->getFont()->setName('微软雅黑');//字体

        //设置默认宽
        $sheet->getDefaultColumnDimension()->setWidth(8);
        //设置高度
        $sheet->getRowDimension(1)->setRowHeight(45);
//        $sheet->getRowDimension(2)->setRowHeight(55);

        //***********************背景填充颜色*****************************
        $sheet->getStyle( 'A1:M11')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
        $sheet->getStyle( 'A1:M11')->getFill()->getStartColor()->setRGB('222B35');//背景颜色
        //***********************画出单元格边框结束*****************************

        //标题
        $sheet->setCellValue('B1', $arr['title']);
        $sheet->getStyle('B1')->getFont()->setSize(18);//字体大小
        $sheet->getStyle('B1')->getFont()->getColor()->setRGB('FFFFFF');//设置颜色为白色
        $sheet->getStyle('B1')->getFont()->setBold(true);//字体加粗

        //甲方logo适用网络图片
        $imageA = $arr['a_company']['logo'];
        if($imageA){
            $img = $this->getFileSuffix($imageA);
            $objMemory = new MemoryDrawing();
            $objMemory->setImageResource($img);
            $objMemory->setRenderingFunction(MemoryDrawing::RENDERING_DEFAULT);//渲染方法
            $objMemory->setMimeType(MemoryDrawing::MIMETYPE_DEFAULT);
            //设置宽度高度
            $objMemory->setResizeProportional(false);
            $objMemory->setHeight(40);//照片高度
            $objMemory->setWidth(40); //照片宽度
            // /*设置图片要插入的单元格*/
            $objMemory->setCoordinates('B3');
            $objMemory->setWorksheet($sheet);
        }

        $sheet->getStyle('B3:J10')->getFont()->setSize(8);//字体大小
        $sheet->getStyle('C3:G3')->getFont()->getColor()->setRGB('949494');//设置颜色为灰色
        $sheet->getStyle('B6:I6')->getFont()->getColor()->setRGB('949494');//设置颜色为灰色
        $sheet->getStyle('B9:I9')->getFont()->getColor()->setRGB('949494');//设置颜色为灰色
        $sheet->getStyle('C4:G4')->getFont()->getColor()->setRGB('FFFFFF');//设置颜色为白色
        $sheet->getStyle('B7:I7')->getFont()->getColor()->setRGB('FFFFFF');//设置颜色为白色
        $sheet->getStyle('B10:I10')->getFont()->getColor()->setRGB('FFFFFF');//设置颜色为白色

        //甲方公司
        $sheet->setCellValue('C3', '甲方公司');
        //甲方公司名称
        $sheet->setCellValue('C4', $arr['a_company']['company_name']);

        //乙方logo
        $imageB = $arr['b_company']['logo'];
        if($imageB){
            $img = $this->getFileSuffix($imageB);
            $objMemory = new MemoryDrawing();
            $objMemory->setImageResource($img);
            $objMemory->setRenderingFunction(MemoryDrawing::RENDERING_DEFAULT);//渲染方法
            $objMemory->setMimeType(MemoryDrawing::MIMETYPE_DEFAULT);
            //设置宽度高度
            $objMemory->setResizeProportional(false);
            $objMemory->setHeight(40);//照片高度
            $objMemory->setWidth(40); //照片宽度
            // /*设置图片要插入的单元格*/
            $objMemory->setCoordinates('F3');
            $objMemory->setWorksheet($sheet);
        }

        //乙方公司
        $sheet->setCellValue('G3', '乙方公司');
        //乙方公司名称
        $sheet->setCellValue('G4', $arr['b_company']['company_name']);

        //乙方发起人姓名
        $sheet->setCellValue('B6', '乙方发起人');
        $sheet->setCellValue('B7', $arr['user_login']);

        //月检时间
        $sheet->setCellValue('D6', '月检时间');
        $sheet->setCellValue('D7', $arr['ins_m_time']);

        //参与人
        $sheet->setCellValue('F6', '参与人');
        $sheet->setCellValue('F7', $arr['check_name']);

        //月检状态
        $sheet->setCellValue('I6', '月检状态');
        $sheet->setCellValue('I7', $arr['status']);

        //甲方员工确认人
        $sheet->setCellValue('B9', '甲方员工确认人');
        $sheet->setCellValue('B10', $arr['a_staff_user']);

        //甲方员工确认人
        $sheet->setCellValue('D9', '甲方员工确认完成人');
        $sheet->setCellValue('D10', $arr['confirm_user']);

        //完成时间
        $sheet->setCellValue('F9', '完成时间');
        $sheet->setCellValue('F10', $arr['finish_time']);

        //工期
        $sheet->setCellValue('I9', '工期（天）');
        $sheet->setCellValue('I10', $arr['check_period']);

        $array1 = [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT,
            'textRotation' => 0,
            'wrapText' => TRUE
        ];
        $sheet->getStyle('F10')->getAlignment()->applyFromArray($array1);//垂直水平居左
        $sheet->getStyle('I10')->getAlignment()->applyFromArray($array1);//垂直水平居左

        //月检点信息图片循环开始
        $cols = 14;
        $cells = ['C','E','G','I','K'];
        $common = new AdminCommonController();
        $state = $common::inspectStatus;
        foreach($arr['check_point'] as $value){
            $cols += 0;
            //巡检点名称
            $array = [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
                'textRotation' => 0,
                'wrapText' => TRUE
            ];
            $sheet->mergeCells('A'.$cols.':B'.$cols);
            $sheet->setCellValue('A'.$cols, $value['point_name'].'('.$value['user_login'].')'."\n".$state[$value['status']]);
            $sheet->getStyle('A'.$cols)->getAlignment()->applyFromArray($array);//垂直水平居中

            //循环第二层
            foreach($value['images'] as $image_image){
                //渲染图片日期
                $col_day = $cols - 1;
                $sheet->mergeCells('C'.$col_day.':D'.$col_day);
                $sheet->setCellValue('C'.$col_day, $image_image['day']);

                //获取图片个数
                $length = count($image_image['image']);
                $col = ceil($length/5);
                for($i=0;$i<$col;$i++) {
                    $col_i = $cols + $i;
                    $sheet->getRowDimension($col_i)->setRowHeight(105);
                    $sheet->mergeCells('C' . $col_i . ':D' . $col_i);
                    $sheet->mergeCells('E' . $col_i . ':F' . $col_i);
                    $sheet->mergeCells('G' . $col_i . ':H' . $col_i);
                    $sheet->mergeCells('I' . $col_i . ':J' . $col_i);
                    $sheet->mergeCells('K' . $col_i . ':L' . $col_i);
                    $key = 0;
                    foreach($image_image['image'] as $k=>$image_value){
                        $pre = $i+4*$i;
                        $next = $i+1+4*($i+1);
                        if($k>=$pre && $k<$next){
                            //巡检图片
                            $image = $image_value['image_url'];
                            if($image){
                                $img = $this->getFileSuffix($image);
                                $objMemory = new MemoryDrawing();
                                $objMemory->setImageResource($img);
                                $objMemory->setRenderingFunction(MemoryDrawing::RENDERING_DEFAULT);//渲染方法
                                $objMemory->setMimeType(MemoryDrawing::MIMETYPE_DEFAULT);
                                //设置宽度高度
                                $objMemory->setResizeProportional(false);
                                $objMemory->setHeight(120);//照片高度
                                $objMemory->setWidth(105); //照片宽度
                                // /*设置图片要插入的单元格*/
                                $objMemory->setCoordinates($cells[$key].$col_i);
                                $objMemory->setWorksheet($sheet);
                            }
                            $key++;
                        }
                    }
                }
                $cols += $col+1;
            }
        }

        //确认完成图片循环开始
        $cols = $cols + 2;
        foreach($arr['check'] as $value){
            $cols += 1;
            //巡检点名称
            $array = [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
                'textRotation' => 0,
                'wrapText' => TRUE
            ];
            $sheet->mergeCells('A'.$cols.':B'.$cols);
            $sheet->setCellValue('A'.$cols, $value['check_name']);
            $sheet->getStyle('A'.$cols)->getAlignment()->applyFromArray($array);//垂直水平居中
            //循环第二层
            foreach($value['images'] as $image_image){
                //渲染图片日期
                $col_day = $cols - 1;
                $sheet->mergeCells('C'.$col_day.':D'.$col_day);
                $sheet->setCellValue('C'.$col_day, $image_image['day']);

                //获取图片个数
                $length = count($image_image['image']);
                $col = ceil($length/5);
                for($i=0;$i<$col;$i++) {
                    $col_i = $cols + $i;
                    $sheet->getRowDimension($col_i)->setRowHeight(105);
                    $sheet->mergeCells('C' . $col_i . ':D' . $col_i);
                    $sheet->mergeCells('E' . $col_i . ':F' . $col_i);
                    $sheet->mergeCells('G' . $col_i . ':H' . $col_i);
                    $sheet->mergeCells('I' . $col_i . ':J' . $col_i);
                    $sheet->mergeCells('K' . $col_i . ':L' . $col_i);
                    $key = 0;
                    foreach($image_image['image'] as $k=>$image_value){
                        $pre = $i+4*$i;
                        $next = $i+1+4*($i+1);
                        if($k>=$pre && $k<$next){
                            //巡检图片
                            $image = $image_value['image_url'];
                            if($image){
                                $img = $this->getFileSuffix($image);
                                $objMemory = new MemoryDrawing();
                                $objMemory->setImageResource($img);
                                $objMemory->setRenderingFunction(MemoryDrawing::RENDERING_DEFAULT);//渲染方法
                                $objMemory->setMimeType(MemoryDrawing::MIMETYPE_DEFAULT);
                                //设置宽度高度
                                $objMemory->setResizeProportional(false);
                                $objMemory->setHeight(120);//照片高度
                                $objMemory->setWidth(105); //照片宽度
                                // /*设置图片要插入的单元格*/
                                $objMemory->setCoordinates($cells[$key].$col_i);
                                $objMemory->setWorksheet($sheet);
                            }
                            $key++;
                        }
                    }
                }
                $cols += $col+1;
            }
        }
//        $sheet->getStyle( 'A12:M'.$cols)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $sheet->getStyle( 'A12:M'.$cols)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
        $sheet->getStyle( 'A12:M'.$cols)->getFill()->getStartColor()->setRGB('F3F3F4');//背景颜色浅灰色

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

        //直接从浏览器中输出star
//        header('pragma:public');
//        header("Content-Disposition:attachment;filename=出货装箱单.xls");
//        $objWrite->save('php://output');
//        exit;
        //直接从浏览器中输出end

        $file_name = $xlsName.'.xls';

        $files_url = ROOT_PATH . '/public/upload/export';
        if(!is_dir($files_url)){
            mkdir($files_url);
        }
        $file_url = ROOT_PATH . '/public/upload/export/'.$file_name;
        $objWrite->save($file_url);

        $sys = $adminCommon->getOperateSys();
        if($sys == 'Linux'){
            chmod($file_url, 0777);//适用于linux
        }
        //相对路径
        $url = 'upload/export/'.$file_name;
        return $url;
    }

    //年检渲染数据到表格上
    public function yearExcelData($arr){
        //设置脚本的最大执行时间，设置为0则无时间限制
        set_time_limit(0);
        ini_set("memory_limit","-1");

        $spreadsheet = new Spreadsheet();
        $adminCommon = new AdminCommonController();
        $sheet = $spreadsheet->getActiveSheet();
        $xlsName = $arr['time'];
        //设置sheet名称
        $name = '报表';
        $name = $adminCommon->iconv_to_utf8($name);

        $sheet->setTitle($xlsName.$name);
        //合并单元格

        //报表名称
        $sheet->mergeCells('B1:J1');

        //甲方logo，甲方公司名称
        $sheet->mergeCells('B3:B4');
        $sheet->mergeCells('C3:D3');
        $sheet->mergeCells('C4:D4');

        //乙方logo，乙方公司名称
        $sheet->mergeCells('F3:F4');
        $sheet->mergeCells('G3:I3');
        $sheet->mergeCells('G4:I4');

        //乙方发起人姓名
        $sheet->mergeCells('B6:C6');
        $sheet->mergeCells('B7:C7');

        //参与人
        $sheet->mergeCells('F6:H6');
        $sheet->mergeCells('F7:H7');

        //月检状态
        $sheet->mergeCells('I6:J6');
        $sheet->mergeCells('I7:J7');

        //甲方员工确认人
        $sheet->mergeCells('B9:C9');
        $sheet->mergeCells('B10:C10');

        //乙方员工确认人
        $sheet->mergeCells('D9:E9');
        $sheet->mergeCells('D10:E10');


        $spreadsheet->getDefaultStyle()->getFont()->setName('微软雅黑');//字体

        //设置默认宽
        $sheet->getDefaultColumnDimension()->setWidth(8);
        //设置高度
        $sheet->getRowDimension(1)->setRowHeight(45);
//        $sheet->getRowDimension(2)->setRowHeight(55);

        //***********************背景填充颜色*****************************
        $sheet->getStyle( 'A1:M11')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
        $sheet->getStyle( 'A1:M11')->getFill()->getStartColor()->setRGB('222B35');//背景颜色
        //***********************画出单元格边框结束*****************************

        //标题
        $sheet->setCellValue('B1', $arr['title']);
        $sheet->getStyle('B1')->getFont()->setSize(18);//字体大小
        $sheet->getStyle('B1')->getFont()->getColor()->setRGB('FFFFFF');//设置颜色为白色
        $sheet->getStyle('B1')->getFont()->setBold(true);//字体加粗

        //甲方logo适用网络图片
        $imageA = $arr['a_company']['logo'];
        if($imageA){
            $img = $this->getFileSuffix($imageA);
            $objMemory = new MemoryDrawing();
            $objMemory->setImageResource($img);
            $objMemory->setRenderingFunction(MemoryDrawing::RENDERING_DEFAULT);//渲染方法
            $objMemory->setMimeType(MemoryDrawing::MIMETYPE_DEFAULT);
            //设置宽度高度
            $objMemory->setResizeProportional(false);
            $objMemory->setHeight(40);//照片高度
            $objMemory->setWidth(40); //照片宽度
            // /*设置图片要插入的单元格*/
            $objMemory->setCoordinates('B3');
            $objMemory->setWorksheet($sheet);
        }

        $sheet->getStyle('B3:J10')->getFont()->setSize(8);//字体大小
        $sheet->getStyle('C3:G3')->getFont()->getColor()->setRGB('949494');//设置颜色为灰色
        $sheet->getStyle('B6:I6')->getFont()->getColor()->setRGB('949494');//设置颜色为灰色
        $sheet->getStyle('B9:I9')->getFont()->getColor()->setRGB('949494');//设置颜色为灰色
        $sheet->getStyle('C4:G4')->getFont()->getColor()->setRGB('FFFFFF');//设置颜色为白色
        $sheet->getStyle('B7:I7')->getFont()->getColor()->setRGB('FFFFFF');//设置颜色为白色
        $sheet->getStyle('B10:I10')->getFont()->getColor()->setRGB('FFFFFF');//设置颜色为白色

        //甲方公司
        $sheet->setCellValue('C3', '甲方公司');
        //甲方公司名称
        $sheet->setCellValue('C4', $arr['a_company']['company_name']);

        //乙方logo
        $imageB = $arr['b_company']['logo'];
        if($imageB){
            $img = $this->getFileSuffix($imageB);
            $objMemory = new MemoryDrawing();
            $objMemory->setImageResource($img);
            $objMemory->setRenderingFunction(MemoryDrawing::RENDERING_DEFAULT);//渲染方法
            $objMemory->setMimeType(MemoryDrawing::MIMETYPE_DEFAULT);
            //设置宽度高度
            $objMemory->setResizeProportional(false);
            $objMemory->setHeight(40);//照片高度
            $objMemory->setWidth(40); //照片宽度
            // /*设置图片要插入的单元格*/
            $objMemory->setCoordinates('F3');
            $objMemory->setWorksheet($sheet);
        }

        //乙方公司
        $sheet->setCellValue('G3', '乙方公司');
        //乙方公司名称
        $sheet->setCellValue('G4', $arr['b_company']['company_name']);

        //乙方发起人姓名
        $sheet->setCellValue('B6', '乙方发起人');
        $sheet->setCellValue('B7', $arr['user_login']);

        //年检时间
        $sheet->setCellValue('D6', '年检时间');
        $sheet->setCellValue('D7', $arr['year_m_time']);

        //参与人
        $sheet->setCellValue('F6', '参与人');
        $sheet->setCellValue('F7', $arr['year_name']);

        //年检状态
        $sheet->setCellValue('I6', '年检状态');
        $sheet->setCellValue('I7', $arr['status']);

        //甲方员工确认人
        $sheet->setCellValue('B9', '甲方员工确认人');
        $sheet->setCellValue('B10', $arr['a_staff_user']);

        //甲方员工确认人
        $sheet->setCellValue('D9', '甲方员工确认完成人');
        $sheet->setCellValue('D10', $arr['confirm_user']);

        //完成时间
        $sheet->setCellValue('F9', '完成时间');
        $sheet->setCellValue('F10', $arr['finish_time']);

        //工期
        $sheet->setCellValue('I9', '工期（天）');
        $sheet->setCellValue('I10', $arr['check_period']);

        $array1 = [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT,
            'textRotation' => 0,
            'wrapText' => TRUE
        ];
        $sheet->getStyle('F10')->getAlignment()->applyFromArray($array1);//垂直水平居左
        $sheet->getStyle('I10')->getAlignment()->applyFromArray($array1);//垂直水平居左

        //月检点信息图片循环开始
        $cols = 14;
        $cells = ['C','E','G','I','K'];
        $common = new AdminCommonController();
        $state = $common::inspectStatus;
        foreach($arr['year_point'] as $value){
            $cols += 0;
            //巡检点名称
            $array = [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
                'textRotation' => 0,
                'wrapText' => TRUE
            ];
            $sheet->mergeCells('A'.$cols.':B'.$cols);
            $sheet->setCellValue('A'.$cols, $value['point_name'].'('.$value['user_login'].')'."\n".$state[$value['status']]);
            $sheet->getStyle('A'.$cols)->getAlignment()->applyFromArray($array);//垂直水平居中

            //循环第二层
            foreach($value['images'] as $image_image){
                //渲染图片日期
                $col_day = $cols - 1;
                $sheet->mergeCells('C'.$col_day.':D'.$col_day);
                $sheet->setCellValue('C'.$col_day, $image_image['day']);

                //获取图片个数
                $length = count($image_image['image']);
                $col = ceil($length/5);
                for($i=0;$i<$col;$i++) {
                    $col_i = $cols + $i;
                    $sheet->getRowDimension($col_i)->setRowHeight(105);
                    $sheet->mergeCells('C' . $col_i . ':D' . $col_i);
                    $sheet->mergeCells('E' . $col_i . ':F' . $col_i);
                    $sheet->mergeCells('G' . $col_i . ':H' . $col_i);
                    $sheet->mergeCells('I' . $col_i . ':J' . $col_i);
                    $sheet->mergeCells('K' . $col_i . ':L' . $col_i);
                    $key = 0;
                    foreach($image_image['image'] as $k=>$image_value){
                        $pre = $i+4*$i;
                        $next = $i+1+4*($i+1);
                        if($k>=$pre && $k<$next){
                            //巡检图片
                            $image = $image_value['image_url'];
                            if($image){
                                $img = $this->getFileSuffix($image);
                                $objMemory = new MemoryDrawing();
                                $objMemory->setImageResource($img);
                                $objMemory->setRenderingFunction(MemoryDrawing::RENDERING_DEFAULT);//渲染方法
                                $objMemory->setMimeType(MemoryDrawing::MIMETYPE_DEFAULT);
                                //设置宽度高度
                                $objMemory->setResizeProportional(false);
                                $objMemory->setHeight(120);//照片高度
                                $objMemory->setWidth(105); //照片宽度
                                // /*设置图片要插入的单元格*/
                                $objMemory->setCoordinates($cells[$key].$col_i);
                                $objMemory->setWorksheet($sheet);
                            }
                            $key++;
                        }
                    }
                }
                $cols += $col+1;
            }
        }

        //确认完成图片循环开始
        $cols = $cols + 2;
        foreach($arr['check'] as $value){
            $cols += 1;
            //巡检点名称
            $array = [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
                'textRotation' => 0,
                'wrapText' => TRUE
            ];
            $sheet->mergeCells('A'.$cols.':B'.$cols);
            $sheet->setCellValue('A'.$cols, $value['check_name']);
            $sheet->getStyle('A'.$cols)->getAlignment()->applyFromArray($array);//垂直水平居中
            //循环第二层
            foreach($value['images'] as $image_image){
                //渲染图片日期
                $col_day = $cols - 1;
                $sheet->mergeCells('C'.$col_day.':D'.$col_day);
                $sheet->setCellValue('C'.$col_day, $image_image['day']);

                //获取图片个数
                $length = count($image_image['image']);
                $col = ceil($length/5);
                for($i=0;$i<$col;$i++) {
                    $col_i = $cols + $i;
                    $sheet->getRowDimension($col_i)->setRowHeight(105);
                    $sheet->mergeCells('C' . $col_i . ':D' . $col_i);
                    $sheet->mergeCells('E' . $col_i . ':F' . $col_i);
                    $sheet->mergeCells('G' . $col_i . ':H' . $col_i);
                    $sheet->mergeCells('I' . $col_i . ':J' . $col_i);
                    $sheet->mergeCells('K' . $col_i . ':L' . $col_i);
                    $key = 0;
                    foreach($image_image['image'] as $k=>$image_value){
                        $pre = $i+4*$i;
                        $next = $i+1+4*($i+1);
                        if($k>=$pre && $k<$next){
                            //巡检图片
                            $image = $image_value['image_url'];
                            if($image){
                                $img = $this->getFileSuffix($image);
                                $objMemory = new MemoryDrawing();
                                $objMemory->setImageResource($img);
                                $objMemory->setRenderingFunction(MemoryDrawing::RENDERING_DEFAULT);//渲染方法
                                $objMemory->setMimeType(MemoryDrawing::MIMETYPE_DEFAULT);
                                //设置宽度高度
                                $objMemory->setResizeProportional(false);
                                $objMemory->setHeight(120);//照片高度
                                $objMemory->setWidth(105); //照片宽度
                                // /*设置图片要插入的单元格*/
                                $objMemory->setCoordinates($cells[$key].$col_i);
                                $objMemory->setWorksheet($sheet);
                            }
                            $key++;
                        }
                    }
                }
                $cols += $col+1;
            }
        }
//        $sheet->getStyle( 'A12:M'.$cols)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $sheet->getStyle( 'A12:M'.$cols)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
        $sheet->getStyle( 'A12:M'.$cols)->getFill()->getStartColor()->setRGB('F3F3F4');//背景颜色浅灰色

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

        //直接从浏览器中输出star
//        header('pragma:public');
//        header("Content-Disposition:attachment;filename=出货装箱单.xls");
//        $objWrite->save('php://output');
//        exit;
        //直接从浏览器中输出end

        $file_name = $xlsName.'.xls';

        $files_url = ROOT_PATH . '/public/upload/export';
        if(!is_dir($files_url)){
            mkdir($files_url);
        }
        $file_url = ROOT_PATH . '/public/upload/export/'.$file_name;
        $objWrite->save($file_url);

        $sys = $adminCommon->getOperateSys();
        if($sys == 'Linux'){
            chmod($file_url, 0777);//适用于linux
        }
        //相对路径
        $url = 'upload/export/'.$file_name;
        return $url;
    }

    //独立年检渲染数据到表格上
    public function testExcelData($arr){
        //设置脚本的最大执行时间，设置为0则无时间限制
        set_time_limit(0);
        ini_set("memory_limit","-1");

        $spreadsheet = new Spreadsheet();
        $adminCommon = new AdminCommonController();
        $sheet = $spreadsheet->getActiveSheet();
        $xlsName = $arr['time'];
        //设置sheet名称
        $name = '报表';
        $name = $adminCommon->iconv_to_utf8($name);

        $sheet->setTitle($xlsName.$name);
        //合并单元格

        //报表名称
        $sheet->mergeCells('B1:J1');

        //乙方logo，乙方公司名称
        $sheet->mergeCells('B3:B4');
        $sheet->mergeCells('C3:D3');
        $sheet->mergeCells('C4:D4');

        //乙方发起人姓名
        $sheet->mergeCells('B6:C6');
        $sheet->mergeCells('B7:C7');


        $spreadsheet->getDefaultStyle()->getFont()->setName('微软雅黑');//字体

        //设置默认宽
        $sheet->getDefaultColumnDimension()->setWidth(8);
        //设置高度
        $sheet->getRowDimension(1)->setRowHeight(45);
//        $sheet->getRowDimension(2)->setRowHeight(55);

        //***********************背景填充颜色*****************************
        $sheet->getStyle( 'A1:M8')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
        $sheet->getStyle( 'A1:M8')->getFill()->getStartColor()->setRGB('222B35');//背景颜色
        //***********************画出单元格边框结束*****************************

        //标题
        $sheet->setCellValue('B1', $arr['title']);
        $sheet->getStyle('B1')->getFont()->setSize(18);//字体大小
        $sheet->getStyle('B1')->getFont()->getColor()->setRGB('FFFFFF');//设置颜色为白色
        $sheet->getStyle('B1')->getFont()->setBold(true);//字体加粗

        //甲方logo适用网络图片
        $imageB = $arr['b_company']['logo'];
        if($imageB){
            $img = $this->getFileSuffix($imageB);
            $objMemory = new MemoryDrawing();
            $objMemory->setImageResource($img);
            $objMemory->setRenderingFunction(MemoryDrawing::RENDERING_DEFAULT);//渲染方法
            $objMemory->setMimeType(MemoryDrawing::MIMETYPE_DEFAULT);
            //设置宽度高度
            $objMemory->setResizeProportional(false);
            $objMemory->setHeight(40);//照片高度
            $objMemory->setWidth(40); //照片宽度
            // /*设置图片要插入的单元格*/
            $objMemory->setCoordinates('B3');
            $objMemory->setWorksheet($sheet);
        }

        $sheet->getStyle('B3:J7')->getFont()->setSize(8);//字体大小
        $sheet->getStyle('C3:G3')->getFont()->getColor()->setRGB('949494');//设置颜色为灰色
        $sheet->getStyle('B6:I6')->getFont()->getColor()->setRGB('949494');//设置颜色为灰色
        $sheet->getStyle('C4:G4')->getFont()->getColor()->setRGB('FFFFFF');//设置颜色为白色
        $sheet->getStyle('B7:I7')->getFont()->getColor()->setRGB('FFFFFF');//设置颜色为白色

        //乙方公司
        $sheet->setCellValue('C3', '乙方公司');
        //乙方公司名称
        $sheet->setCellValue('C4', $arr['b_company']['company_name']);

        //乙方发起人姓名
        $sheet->setCellValue('B6', '乙方发起人');
        $sheet->setCellValue('B7', $arr['user_login']);

        //独立年检时间
        $sheet->setCellValue('D6', '年检时间');
        $sheet->setCellValue('D7', $arr['test_time']);

        $array1 = [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT,
            'textRotation' => 0,
            'wrapText' => TRUE
        ];
        $sheet->getStyle('F10')->getAlignment()->applyFromArray($array1);//垂直水平居左
        $sheet->getStyle('I10')->getAlignment()->applyFromArray($array1);//垂直水平居左

        //月检点信息图片循环开始
        $cols = 11;
        $cells = ['C','E','G','I','K'];
        $common = new AdminCommonController();
        $state = $common::inspectStatus;
        foreach($arr['test_point'] as $value){
            $cols += 0;
            //巡检点名称
            $array = [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
                'textRotation' => 0,
                'wrapText' => TRUE
            ];
            $sheet->mergeCells('A'.$cols.':B'.$cols);
            $sheet->setCellValue('A'.$cols, $value['spot_name'].'('.$value['user_login'].')'."\n".$state[$value['status']]);
            $sheet->getStyle('A'.$cols)->getAlignment()->applyFromArray($array);//垂直水平居中

            //循环第二层
            foreach($value['images'] as $image_image){
                //渲染图片日期
                $col_day = $cols - 1;
                $sheet->mergeCells('C'.$col_day.':D'.$col_day);
                $sheet->setCellValue('C'.$col_day, $image_image['day']);

                //获取图片个数
                $length = count($image_image['image']);
                $col = ceil($length/5);
                for($i=0;$i<$col;$i++) {
                    $col_i = $cols + $i;
                    $sheet->getRowDimension($col_i)->setRowHeight(105);
                    $sheet->mergeCells('C' . $col_i . ':D' . $col_i);
                    $sheet->mergeCells('E' . $col_i . ':F' . $col_i);
                    $sheet->mergeCells('G' . $col_i . ':H' . $col_i);
                    $sheet->mergeCells('I' . $col_i . ':J' . $col_i);
                    $sheet->mergeCells('K' . $col_i . ':L' . $col_i);
                    $key = 0;
                    foreach($image_image['image'] as $k=>$image_value){
                        $pre = $i+4*$i;
                        $next = $i+1+4*($i+1);
                        if($k>=$pre && $k<$next){
                            //巡检图片
                            $image = $image_value['image_url'];
                            if($image){
                                $img = $this->getFileSuffix($image);
                                $objMemory = new MemoryDrawing();
                                $objMemory->setImageResource($img);
                                $objMemory->setRenderingFunction(MemoryDrawing::RENDERING_DEFAULT);//渲染方法
                                $objMemory->setMimeType(MemoryDrawing::MIMETYPE_DEFAULT);
                                //设置宽度高度
                                $objMemory->setResizeProportional(false);
                                $objMemory->setHeight(120);//照片高度
                                $objMemory->setWidth(105); //照片宽度
                                // /*设置图片要插入的单元格*/
                                $objMemory->setCoordinates($cells[$key].$col_i);
                                $objMemory->setWorksheet($sheet);
                            }
                            $key++;
                        }
                    }
                }
                $cols += $col+1;
            }
        }
//        $sheet->getStyle( 'A12:M'.$cols)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $sheet->getStyle( 'A9:M'.$cols)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
        $sheet->getStyle( 'A9:M'.$cols)->getFill()->getStartColor()->setRGB('F3F3F4');//背景颜色浅灰色

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

        $file_name = $xlsName.'.xls';

        $files_url = ROOT_PATH . '/public/upload/export';
        if(!is_dir($files_url)){
            mkdir($files_url);
        }
        $file_url = ROOT_PATH . '/public/upload/export/'.$file_name;
        $objWrite->save($file_url);

        $sys = $adminCommon->getOperateSys();
        if($sys == 'Linux'){
            chmod($file_url, 0777);//适用于linux
        }
        //相对路径
        $url = 'upload/export/'.$file_name;
        return $url;
    }

    //培训演习渲染数据到表格上
    public function trainExcelData($arr,$table){
        //设置脚本的最大执行时间，设置为0则无时间限制
        set_time_limit(0);
        ini_set("memory_limit","-1");

        $spreadsheet = new Spreadsheet();
        $adminCommon = new AdminCommonController();
        $sheet = $spreadsheet->getActiveSheet();
        $xlsName = $arr['time'];
        //设置sheet名称
        $name = '报表';
        $name = $adminCommon->iconv_to_utf8($name);

        $sheet->setTitle($xlsName.$name);
        //合并单元格

        //报表名称
        $sheet->mergeCells('B1:J1');

        //甲方logo，甲方公司名称
        $sheet->mergeCells('B3:B4');
        $sheet->mergeCells('C3:D3');
        $sheet->mergeCells('C4:D4');

        //乙方logo，乙方公司名称
        $sheet->mergeCells('F3:F4');
        $sheet->mergeCells('G3:I3');
        $sheet->mergeCells('G4:I4');

        //乙方发起人姓名
        $sheet->mergeCells('B6:C6');
        $sheet->mergeCells('B7:C7');

        //乙方项目组
        $sheet->mergeCells('F6:H6');
        $sheet->mergeCells('F7:H7');

        //培训演习状态
        $sheet->mergeCells('I6:J6');
        $sheet->mergeCells('I7:J7');

        //培训演习单号
        $sheet->mergeCells('K6:L6');
        $sheet->mergeCells('K7:L7');

        //甲方领导确认人
        $sheet->mergeCells('B9:C9');
        $sheet->mergeCells('B10:C10');

        //乙方员工确认人
        $sheet->mergeCells('D9:E9');
        $sheet->mergeCells('D10:E10');


        $spreadsheet->getDefaultStyle()->getFont()->setName('微软雅黑');//字体

        //设置默认宽
        $sheet->getDefaultColumnDimension()->setWidth(8);
        //设置高度
        $sheet->getRowDimension(1)->setRowHeight(45);
//        $sheet->getRowDimension(2)->setRowHeight(55);

        //***********************背景填充颜色*****************************
        $sheet->getStyle( 'A1:M11')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
        $sheet->getStyle( 'A1:M11')->getFill()->getStartColor()->setRGB('222B35');//背景颜色
        //***********************画出单元格边框结束*****************************

        //标题
        $sheet->setCellValue('B1', $arr['title']);
        $sheet->getStyle('B1')->getFont()->setSize(18);//字体大小
        $sheet->getStyle('B1')->getFont()->getColor()->setRGB('FFFFFF');//设置颜色为白色
        $sheet->getStyle('B1')->getFont()->setBold(true);//字体加粗

        //甲方logo
        $imageA = $arr['a_company']['logo'];
        if($imageA){
            $img = $this->getFileSuffix($imageA);
            $objMemory = new MemoryDrawing();
            $objMemory->setImageResource($img);
            $objMemory->setRenderingFunction(MemoryDrawing::RENDERING_DEFAULT);//渲染方法
            $objMemory->setMimeType(MemoryDrawing::MIMETYPE_DEFAULT);
            //设置宽度高度
            $objMemory->setResizeProportional(false);
            $objMemory->setHeight(40);//照片高度
            $objMemory->setWidth(40); //照片宽度
            // /*设置图片要插入的单元格*/
            $objMemory->setCoordinates('B3');
            $objMemory->setWorksheet($sheet);
        }

        $sheet->getStyle('B3:K10')->getFont()->setSize(8);//字体大小
        $sheet->getStyle('C3:G3')->getFont()->getColor()->setRGB('949494');//设置颜色为灰色
        $sheet->getStyle('B6:K6')->getFont()->getColor()->setRGB('949494');//设置颜色为灰色
        $sheet->getStyle('B9:I9')->getFont()->getColor()->setRGB('949494');//设置颜色为灰色
        $sheet->getStyle('C4:G4')->getFont()->getColor()->setRGB('FFFFFF');//设置颜色为白色
        $sheet->getStyle('B7:K7')->getFont()->getColor()->setRGB('FFFFFF');//设置颜色为白色
        $sheet->getStyle('B10:I10')->getFont()->getColor()->setRGB('FFFFFF');//设置颜色为白色

        //甲方公司
        $sheet->setCellValue('C3', '甲方公司');
        //甲方公司名称
        $sheet->setCellValue('C4', $arr['a_company']['company_name']);

        //乙方logo
        $imageB = $arr['b_company']['logo'];
        if($imageB){
            $img = $this->getFileSuffix($imageB);
            $objMemory = new MemoryDrawing();
            $objMemory->setImageResource($img);
            $objMemory->setRenderingFunction(MemoryDrawing::RENDERING_DEFAULT);//渲染方法
            $objMemory->setMimeType(MemoryDrawing::MIMETYPE_DEFAULT);
            //设置宽度高度
            $objMemory->setResizeProportional(false);
            $objMemory->setHeight(40);//照片高度
            $objMemory->setWidth(40); //照片宽度
            // /*设置图片要插入的单元格*/
            $objMemory->setCoordinates('F3');
            $objMemory->setWorksheet($sheet);
        }

        //乙方公司
        $sheet->setCellValue('G3', '乙方公司');
        //乙方公司名称
        $sheet->setCellValue('G4', $arr['b_company']['company_name']);

        //乙方发起人姓名
        $sheet->setCellValue('B6', '甲方发起人');
        $sheet->setCellValue('B7', $arr['user_login']);

        //培训演习时间
        if($table == 'train'){
            $sheet->setCellValue('D6', '培训时间');
            $sheet->setCellValue('D7', $arr['train_time']);
        }else{
            $sheet->setCellValue('D6', '演习时间');
            $sheet->setCellValue('D7', $arr['exercise_time']);
        }


        //乙方项目组
        $sheet->setCellValue('F6', '参与人员');
        if($table == 'train'){
            $sheet->setCellValue('F7', $arr['train_name']);
        }else{
            $sheet->setCellValue('F7', $arr['exercise_name']);
        }

        //培训演习状态
        if($table == 'train'){
            $sheet->setCellValue('I6', '培训状态');
        }else{
            $sheet->setCellValue('I6', '演习状态');
        }
        $sheet->setCellValue('I7', $arr['status']);

        //培训演习单号
        if($table == 'train'){
            $sheet->setCellValue('K6', '培训单号');
        }else{
            $sheet->setCellValue('K6', '演习单号');
        }
        $sheet->setCellValue('K7', $arr['number']);
        $sheet->getStyle('K7')->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER);//解决长数字显示为科学计数法

        //乙方员工确认人
        $sheet->setCellValue('B9', '乙方员工确认人');
        $sheet->setCellValue('B10', $arr['b_staff_user']);

        //乙方员工确认完成人
        $sheet->setCellValue('D9', '乙方员工确认完成人');
        $sheet->setCellValue('D10', $arr['confirm_user']);

        //完成时间
        $sheet->setCellValue('F9', '完成时间');
        $sheet->setCellValue('F10', $arr['finish_time']);

        //工期
        $sheet->setCellValue('I9', '工期（天）');
        $sheet->setCellValue('I10', $arr['check_period']);

        $array1 = [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT,
            'textRotation' => 0,
            'wrapText' => TRUE
        ];
        $sheet->getStyle('K7')->getAlignment()->applyFromArray($array1);//垂直水平居左
        $sheet->getStyle('F10')->getAlignment()->applyFromArray($array1);//垂直水平居左
        $sheet->getStyle('I10')->getAlignment()->applyFromArray($array1);//垂直水平居左

        //循环开始
        $cols = 13;
        $cells = ['C','E','G','I','K'];
        foreach($arr['train'] as $value){
            $cols += 1;
            //巡检点名称
            $array = [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
                'textRotation' => 0,
                'wrapText' => TRUE
            ];
            $sheet->mergeCells('A'.$cols.':B'.$cols);
            $sheet->setCellValue('A'.$cols, $value['check_name']);
            $sheet->getStyle('A'.$cols)->getAlignment()->applyFromArray($array);//垂直水平居中
            //循环第二层
            foreach($value['images'] as $image_image){
                //渲染图片日期
                $col_day = $cols - 1;
                $sheet->mergeCells('C'.$col_day.':D'.$col_day);
                $sheet->setCellValue('C'.$col_day, $image_image['day']);

                //获取图片个数
                $length = count($image_image['image']);
                $col = ceil($length/5);
                for($i=0;$i<$col;$i++) {
                    $col_i = $cols + $i;
                    $sheet->getRowDimension($col_i)->setRowHeight(105);
                    $sheet->mergeCells('C' . $col_i . ':D' . $col_i);
                    $sheet->mergeCells('E' . $col_i . ':F' . $col_i);
                    $sheet->mergeCells('G' . $col_i . ':H' . $col_i);
                    $sheet->mergeCells('I' . $col_i . ':J' . $col_i);
                    $sheet->mergeCells('K' . $col_i . ':L' . $col_i);
                    $key = 0;
                    foreach($image_image['image'] as $k=>$image_value){
                        $pre = $i+4*$i;
                        $next = $i+1+4*($i+1);
                        if($k>=$pre && $k<$next){
                            //巡检图片
                            $image = $image_value['image_url'];
                            if($image){
                                $img = $this->getFileSuffix($image);
                                $objMemory = new MemoryDrawing();
                                $objMemory->setImageResource($img);
                                $objMemory->setRenderingFunction(MemoryDrawing::RENDERING_DEFAULT);//渲染方法
                                $objMemory->setMimeType(MemoryDrawing::MIMETYPE_DEFAULT);
                                //设置宽度高度
                                $objMemory->setResizeProportional(false);
                                $objMemory->setHeight(120);//照片高度
                                $objMemory->setWidth(105); //照片宽度
                                // /*设置图片要插入的单元格*/
                                $objMemory->setCoordinates($cells[$key].$col_i);
                                $objMemory->setWorksheet($sheet);
                            }
                            $key++;
                        }
                    }
                }
                $cols += $col+1;
            }
        }
        $sheet->getStyle( 'A12:M'.$cols)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
        $sheet->getStyle( 'A12:M'.$cols)->getFill()->getStartColor()->setRGB('F3F3F4');//背景颜色浅灰色

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

        //直接从浏览器中输出star
//        header('pragma:public');
//        header("Content-Disposition:attachment;filename=出货装箱单.xls");
//        $objWrite->save('php://output');
//        exit;
        //直接从浏览器中输出end

        $file_name = $xlsName.'.xls';

        $files_url = ROOT_PATH . '/public/upload/export';
        if(!is_dir($files_url)){
            mkdir($files_url);
        }
        $file_url = ROOT_PATH . '/public/upload/export/'.$file_name;
        $objWrite->save($file_url);

        $sys = $adminCommon->getOperateSys();
        if($sys == 'Linux'){
            chmod($file_url, 0777);//适用于linux
        }
        //相对路径
        $url = 'upload/export/'.$file_name;
        return $url;
    }

    //报修改造渲染数据到表格上
    public function repairExcelData($arr,$table,$party){
        //设置脚本的最大执行时间，设置为0则无时间限制
        set_time_limit(0);
        ini_set("memory_limit","-1");

        $spreadsheet = new Spreadsheet();
        $adminCommon = new AdminCommonController();
        $sheet = $spreadsheet->getActiveSheet();
        $xlsName = $arr['time'];
        //设置sheet名称
        $name = '报表';
        $name = $adminCommon->iconv_to_utf8($name);

        $sheet->setTitle($xlsName.$name);
        //合并单元格

        //报表名称
        $sheet->mergeCells('B1:J1');

        //甲方logo，甲方公司名称
        $sheet->mergeCells('B3:B4');
        $sheet->mergeCells('C3:D3');
        $sheet->mergeCells('C4:D4');

        //乙方logo，乙方公司名称
        $sheet->mergeCells('F3:F4');
        $sheet->mergeCells('G3:I3');
        $sheet->mergeCells('G4:I4');

        //乙方发起人姓名
        $sheet->mergeCells('B6:C6');
        $sheet->mergeCells('B7:C7');

        //乙方项目组
        $sheet->mergeCells('F6:H6');
        $sheet->mergeCells('F7:H7');

        //培训演习状态
        $sheet->mergeCells('I6:J6');
        $sheet->mergeCells('I7:J7');

        //培训演习单号
        $sheet->mergeCells('K6:L6');
        $sheet->mergeCells('K7:L7');

        //甲方领导确认人
        $sheet->mergeCells('B9:C9');
        $sheet->mergeCells('B10:C10');

        //乙方员工确认人
        $sheet->mergeCells('D9:E9');
        $sheet->mergeCells('D10:E10');


        $spreadsheet->getDefaultStyle()->getFont()->setName('微软雅黑');//字体

        //设置默认宽
        $sheet->getDefaultColumnDimension()->setWidth(8);
        //设置高度
        $sheet->getRowDimension(1)->setRowHeight(45);
//        $sheet->getRowDimension(2)->setRowHeight(55);

        //***********************背景填充颜色*****************************
        $sheet->getStyle( 'A1:M11')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
        $sheet->getStyle( 'A1:M11')->getFill()->getStartColor()->setRGB('222B35');//背景颜色
        //***********************画出单元格边框结束*****************************

        //标题
        $sheet->setCellValue('B1', $arr['title']);
        $sheet->getStyle('B1')->getFont()->setSize(18);//字体大小
        $sheet->getStyle('B1')->getFont()->getColor()->setRGB('FFFFFF');//设置颜色为白色
        $sheet->getStyle('B1')->getFont()->setBold(true);//字体加粗

        //甲方logo
        $imageA = $arr['a_company']['logo'];
        if($imageA){
            $img = $this->getFileSuffix($imageA);
            $objMemory = new MemoryDrawing();
            $objMemory->setImageResource($img);
            $objMemory->setRenderingFunction(MemoryDrawing::RENDERING_DEFAULT);//渲染方法
            $objMemory->setMimeType(MemoryDrawing::MIMETYPE_DEFAULT);
            //设置宽度高度
            $objMemory->setResizeProportional(false);
            $objMemory->setHeight(40);//照片高度
            $objMemory->setWidth(40); //照片宽度
            // /*设置图片要插入的单元格*/
            $objMemory->setCoordinates('B3');
            $objMemory->setWorksheet($sheet);
        }


        $sheet->getStyle('B3:K10')->getFont()->setSize(8);//字体大小
        $sheet->getStyle('C3:G3')->getFont()->getColor()->setRGB('949494');//设置颜色为灰色
        $sheet->getStyle('B6:K6')->getFont()->getColor()->setRGB('949494');//设置颜色为灰色
        $sheet->getStyle('B9:I9')->getFont()->getColor()->setRGB('949494');//设置颜色为灰色
        $sheet->getStyle('C4:G4')->getFont()->getColor()->setRGB('FFFFFF');//设置颜色为白色
        $sheet->getStyle('B7:K7')->getFont()->getColor()->setRGB('FFFFFF');//设置颜色为白色
        $sheet->getStyle('B10:I10')->getFont()->getColor()->setRGB('FFFFFF');//设置颜色为白色

        //甲方公司
        $sheet->setCellValue('C3', '甲方公司');
        //甲方公司名称
        $sheet->setCellValue('C4', $arr['a_company']['company_name']);

        //乙方logo
        $imageB = $arr['b_company']['logo'];
        if($imageB){
            $img = $this->getFileSuffix($imageB);
            $objMemory = new MemoryDrawing();
            $objMemory->setImageResource($img);
            $objMemory->setRenderingFunction(MemoryDrawing::RENDERING_DEFAULT);//渲染方法
            $objMemory->setMimeType(MemoryDrawing::MIMETYPE_DEFAULT);
            //设置宽度高度
            $objMemory->setResizeProportional(false);
            $objMemory->setHeight(40);//照片高度
            $objMemory->setWidth(40); //照片宽度
            // /*设置图片要插入的单元格*/
            $objMemory->setCoordinates('F3');
            $objMemory->setWorksheet($sheet);
        }

        //乙方公司
        $sheet->setCellValue('G3', '乙方公司');
        //乙方公司名称
        $sheet->setCellValue('G4', $arr['b_company']['company_name']);

        //甲方/乙方发起人姓名
        if($party == 0){
            $sheet->setCellValue('B6', '甲方发起人');
        }else{
            $sheet->setCellValue('B6', '乙方发起人');
        }
        $sheet->setCellValue('B7', $arr['user_login']);

        //报修改造时间
        if($table == 'repair'){
            $sheet->setCellValue('D6', '报修时间');
        }else{
            $sheet->setCellValue('D6', '改造时间');
        }
        $sheet->setCellValue('D7', $arr['create_time']);

        //乙方项目组
        $sheet->setCellValue('F6', '参与人员');
        if($table == 'repair'){
            $sheet->setCellValue('F7', $arr['repair_name']);
        }else{
            $sheet->setCellValue('F7', $arr['reform_name']);
        }

        //报修改造状态
        if($table == 'repair'){
            $sheet->setCellValue('I6', '报修状态');
        }else{
            $sheet->setCellValue('I6', '改造状态');
        }
        $sheet->setCellValue('I7', $arr['status']);

        //报修改造单号
        if($table == 'repair'){
            $sheet->setCellValue('K6', '报修单号');
        }else{
            $sheet->setCellValue('K6', '改造单号');
        }
        $sheet->setCellValue('K7', $arr['number']);
        $sheet->getStyle('K7')->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER);//解决长数字显示为科学计数法

        //报修改造确认人（甲方发起，乙方确认，乙方发起，甲方确认）
        if($party == 0){
            $sheet->setCellValue('B9', '乙方员工确认人');
        }else{
            $sheet->setCellValue('B9', '甲方员工确认人');
        }
        $sheet->setCellValue('B10', $arr['h_user']);

        //甲方员工确认完成人
        $sheet->setCellValue('D9', '甲方员工确认完成人');
        $sheet->setCellValue('D10', $arr['confirm_user']);

        //完成时间
        $sheet->setCellValue('F9', '完成时间');
        $sheet->setCellValue('F10', $arr['finish_time']);

        //工期
        $sheet->setCellValue('I9', '工期（天）');
        $sheet->setCellValue('I10', $arr['check_period']);

        $array1 = [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT,
            'textRotation' => 0,
            'wrapText' => TRUE
        ];
        $sheet->getStyle('K7')->getAlignment()->applyFromArray($array1);//垂直水平居左
        $sheet->getStyle('F10')->getAlignment()->applyFromArray($array1);//垂直水平居左
        $sheet->getStyle('I10')->getAlignment()->applyFromArray($array1);//垂直水平居左

        //循环开始
        $cols = 13;
        $cells = ['C','E','G','I','K'];
        foreach($arr['repair'] as $value){
            $cols += 1;
            //巡检点名称
            $array = [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
                'textRotation' => 0,
                'wrapText' => TRUE
            ];
            $sheet->mergeCells('A'.$cols.':B'.$cols);
            $sheet->setCellValue('A'.$cols, $value['check_name']);
            $sheet->getStyle('A'.$cols)->getAlignment()->applyFromArray($array);//垂直水平居中
            //循环第二层
            foreach($value['images'] as $image_image){
                //渲染图片日期
                $col_day = $cols - 1;
                $sheet->mergeCells('C'.$col_day.':D'.$col_day);
                $sheet->setCellValue('C'.$col_day, $image_image['day']);

                //获取图片个数
                $length = count($image_image['image']);
                $col = ceil($length/5);
                for($i=0;$i<$col;$i++) {
                    $col_i = $cols + $i;
                    $sheet->getRowDimension($col_i)->setRowHeight(105);
                    $sheet->mergeCells('C' . $col_i . ':D' . $col_i);
                    $sheet->mergeCells('E' . $col_i . ':F' . $col_i);
                    $sheet->mergeCells('G' . $col_i . ':H' . $col_i);
                    $sheet->mergeCells('I' . $col_i . ':J' . $col_i);
                    $sheet->mergeCells('K' . $col_i . ':L' . $col_i);
                    $key = 0;
                    foreach($image_image['image'] as $k=>$image_value){
                        $pre = $i+4*$i;
                        $next = $i+1+4*($i+1);
                        if($k>=$pre && $k<$next){
                            //巡检图片
                            $image = $image_value['image_url'];
                            if($image){
                                $img = $this->getFileSuffix($image);
                                $objMemory = new MemoryDrawing();
                                $objMemory->setImageResource($img);
                                $objMemory->setRenderingFunction(MemoryDrawing::RENDERING_DEFAULT);//渲染方法
                                $objMemory->setMimeType(MemoryDrawing::MIMETYPE_DEFAULT);
                                //设置宽度高度
                                $objMemory->setResizeProportional(false);
                                $objMemory->setHeight(120);//照片高度
                                $objMemory->setWidth(105); //照片宽度
                                // /*设置图片要插入的单元格*/
                                $objMemory->setCoordinates($cells[$key].$col_i);
                                $objMemory->setWorksheet($sheet);
                            }
                            $key++;
                        }
                    }
                }
                $cols += $col+1;
            }
        }
        $sheet->getStyle( 'A12:M'.$cols)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
        $sheet->getStyle( 'A12:M'.$cols)->getFill()->getStartColor()->setRGB('F3F3F4');//背景颜色浅灰色

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

        //直接从浏览器中输出star
//        header('pragma:public');
//        header("Content-Disposition:attachment;filename=出货装箱单.xls");
//        $objWrite->save('php://output');
//        exit;
        //直接从浏览器中输出end

        $file_name = $xlsName.'.xls';

        $files_url = ROOT_PATH . '/public/upload/export';
        if(!is_dir($files_url)){
            mkdir($files_url);
        }
        $file_url = ROOT_PATH . '/public/upload/export/'.$file_name;
        $objWrite->save($file_url);

        $sys = $adminCommon->getOperateSys();
        if($sys == 'Linux'){
            chmod($file_url, 0777);//适用于linux
        }
        //相对路径
        $url = 'upload/export/'.$file_name;
        return $url;
    }

}