Excel_copy.php

<?php
/**
 * Created by PhpStorm.
 * User: chouchou
 * Date: 2019-6-23
 * Time: 23:53
 */

namespace app\admin\controller;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use app\admin\model\StaffModel;
use app\admin\model\ElectricMeter;
use app\admin\model\BaseStationModel;
use think\facade\Session;
use think\Controller;
use think\Db;

class Excel extends Controller
{

    /**
     * 使用PHPEXECL导入
     *
     * @param string $file      文件地址
     * @param int    $sheet     工作表sheet(传0则获取第一个sheet)
     * @param int    $columnCnt 列数(传0则自动获取最大列)
     * @param array  $options   操作选项
     *                          array mergeCells 合并单元格数组
     *                          array formula    公式数组
     *                          array format     单元格格式数组
     *
     * @return array
     * @throws Exception
     */
     public function importExecl(string $file = '', int $sheet = 0, int $columnCnt = 0, &$options = [])
    {
                 ini_set ('memory_limit', '1000M');
                $extension = strtolower(pathinfo($file, PATHINFO_EXTENSION));//判断导入表格后缀格式
                  // 有Xls和Xlsx格式两种
                if ($extension == 'xlsx') {
                $objReader =IOFactory::createReader('Xlsx');
                  $objPHPExcel = $objReader->load($file);
      
                } elseif($extension == 'xls'){
                $objReader =IOFactory::createReader('Xls');
                 $objPHPExcel = $objReader->load($file);
    
                }else{
                  $this->error('必须为excel表格，且必须为xls或者xlsx格式！');

                }

             //$filename可以是上传的表格，或者是指定的表格
            $sheet = $objPHPExcel->getSheet(0);   //excel中的第一张sheet
            $highestRow = $sheet->getHighestRow();       // 取得总行数
            // $highestColumn = $sheet->getHighestColumn();   // 取得总列数
          
              if($objPHPExcel->getActiveSheet()->getCell("A1")->getValue()!=='基站编号'){
                      return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("B1")->getValue()!=='基站名称'){
                       return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("C1")->getValue()!=='项目编号'){
                      return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("D1")->getValue()!=='电表类型'){
                       return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("E1")->getValue()!=='电表识别号'){
                       return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("F1")->getValue()!=='缴费号'){
                      return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("G1")->getValue()!=='倍率'){
                       return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("H1")->getValue()!=='电表初始度数'){
                      return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("I1")->getValue()!=='电表备注'){
                       return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("J1")->getValue()!=='抄表单单价'){
                      return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("K1")->getValue()!=='首次查表日期'){
                       return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("L1")->getValue()!=='是否是协议价'){
                       return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("M1")->getValue()!=='协议价'){
                       return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("N1")->getValue()!=='协议名称'){
                       return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("O1")->getValue()!=='协议编号'){
                       return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("P1")->getValue()!=='开始时间'){
                       return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("Q1")->getValue()!=='结束时间'){
                       return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("R1")->getValue()!=='签订日期'){
                      return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("S1")->getValue()!=='备注'){
                       return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("T1")->getValue()!=='缴费周期'){
                      return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("U1")->getValue()!=='业务员'){
                      return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("V1")->getValue()!=='电表地址'){
                      return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("W1")->getValue()!=='甲方联系人'){
                     return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("Y1")->getValue()!=='机房位置'){
                      return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("Z1")->getValue()!=='物业位置'){
                      return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("AA1")->getValue()!=='收款单位'){
                     return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("AB1")->getValue()!=='出租单位名称'){
                      return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("AC1")->getValue()!=='备注'){
                      return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("AD1")->getValue()!=='合同编号'){
                      return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("AE1")->getValue()!=='确认合同日期'){
                      return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("AF1")->getValue()!=='合同有效时间'){
                     return ['code' =>5,'message' => '模板错误'];
                      
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("AG1")->getValue()!=='机房二维码'){
                     return ['code' =>5,'message' => '模板错误'];
                      
                    }



            $usersExits = [];
            //循环读取excel表格，整合成数组。如果是不指定key的二维，就用$data[i][j]表示。
            $create_time=time();
            $electric_meter['create_time']=$create_time;
            $contract['create_time'] = $create_time;
            $agreement['create_time'] = $create_time;
            $engine_room['create_time'] = $create_time;
            $electric_meter['update_time']=$create_time;
            $contract['update_time'] = $create_time;
            $agreement['update_time'] = $create_time;
            $engine_room['update_time'] = $create_time;
            for ($j = 2; $j <= $highestRow; $j++) {
             

            //站号
            $station_sp_code=$objPHPExcel->getActiveSheet()->getCell("A".$j)->getValue();
            
            //站名
            $station_name=$objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue();
         
            //项目编号 
            $proj_number=$objPHPExcel->getActiveSheet()->getCell("C".$j)->getValue();
          
            //通过站号 、站名、项目编号确认电表的station_id
            $electric_meter['station_id']=(new ElectricMeter)->getStationID($proj_number);
          
           //电表类型 electric_meter
            $emt_id=$objPHPExcel->getActiveSheet()->getCell("D".$j)->getValue();
            $electric_meter['emt_id']=(new ElectricMeter)->getEmtId($emt_id);


            //电表识别号   electric_meter
            $number=$objPHPExcel->getActiveSheet()->getCell("E".$j)->getValue();
            $electric_meter['number']=isset($number)?$number:'';

            //缴费号  electric_meter
            $pay_number=$objPHPExcel->getActiveSheet()->getCell("F".$j)->getValue();
            $electric_meter['pay_number']=isset($pay_number)?$pay_number:'';


            //倍率   electric_meter
            $multiple=$objPHPExcel->getActiveSheet()->getCell("G".$j)->getValue();
            $electric_meter['multiple']=isset($multiple)?$multiple:'';

            //电表初始度数   electric_meter
            $init_degree=$objPHPExcel->getActiveSheet()->getCell("H".$j)->getValue();
            $electric_meter['init_degree']=isset($init_degree)?$init_degree:'';  


            //电表备注  electric_meter
            $text=$objPHPExcel->getActiveSheet()->getCell("I".$j)->getValue();
            $electric_meter['remark']=isset($text)?$text:'';

           //抄表单单价 electric_meter
            $unit_price=$objPHPExcel->getActiveSheet()->getCell("J".$j)->getValue();
            $electric_meter['unit_price']=isset($unit_price)?$unit_price:'';

            //首次查表日期 转换为时间戳first_cp_date     electric_meter
            $first_cp_date= $objPHPExcel->getActiveSheet()->getCell("K".$j)->getValue();
              if($first_cp_date){
           $electric_meter['first_cp_date']=strtotime($first_cp_date);
              }else{
                 $electric_meter['first_cp_date']=0;
              }
          

            //是否是协议价   electric_meter
            $is_protocol=$objPHPExcel->getActiveSheet()->getCell("L".$j)->getValue();
           if($is_protocol=='是'){
              $electric_meter['is_protocol']=1;
           }else{
              $electric_meter['is_protocol']=0;
           }
             
            //协议价    electric_meter
            $protocol_price=$objPHPExcel->getActiveSheet()->getCell("M".$j)->getValue();
            $electric_meter['protocol_price']=isset($protocol_price)?$protocol_price:'';
         
            //协议名称
            $contract_name=$objPHPExcel->getActiveSheet()->getCell("N".$j)->getValue();
            $agreement['name']=isset($contract_name)?$contract_name:'';
            
            //协议编号
            $contract_number=$objPHPExcel->getActiveSheet()->getCell("O".$j)->getValue();
            $agreement['number']=isset($contract_number)?$contract_number:'';

            //开始时间
            $contract_start_date= $objPHPExcel->getActiveSheet()->getCell("P".$j)->getValue();
            if($contract_start_date){
             $agreement['start_time']=strtotime($contract_start_date);
            }else{
               $agreement['start_time']=0;
            }
        

            //结束时间
            $contract_end_date= $objPHPExcel->getActiveSheet()->getCell("Q".$j)->getValue();
            if($contract_end_date){
                  $agreement['end_time']=strtotime($contract_end_date);
            }else{
              $agreement['end_time']=0;
            }
           

            //签订日期
            $contract_sign_date= $objPHPExcel->getActiveSheet()->getCell("R".$j)->getValue();
            if($contract_sign_date){
          $agreement['sign_time']=strtotime($contract_sign_date);
            }else{
               $agreement['sign_time']=0;
            }
         
            

            //备注
            $contract_remark=$objPHPExcel->getActiveSheet()->getCell("S".$j)->getValue();
            $agreement['remark']=isset($contract_remark)?$contract_remark:'';

           // 缴费周期   electric_meter
            $pay_cycle=$objPHPExcel->getActiveSheet()->getCell("T".$j)->getValue();
            $electric_meter['pay_cycle']=isset($pay_cycle)?$pay_cycle:'';

            //业务员   electric_meter
            $staff_id=$objPHPExcel->getActiveSheet()->getCell("U".$j)->getValue();
    
            $electric_meter['staff_id']=(new ElectricMeter)->getStaffId($staff_id);
 
            //电表地址   electric_meter
            $address=$objPHPExcel->getActiveSheet()->getCell("V".$j)->getValue();
            $electric_meter['address']=isset($address)?$address:'';
            
            //合同附件id
            $electric_meter['photo_id']=1;
            //甲方联系人    engine_room
            $contact=$objPHPExcel->getActiveSheet()->getCell("W".$j)->getValue();
            $engine_room['contact']=isset($contact)?$contact:'';
 
            //甲方联系方式    engine_room
            $tel=$objPHPExcel->getActiveSheet()->getCell("X".$j)->getValue();
            $engine_room['tel']=isset($tel)?$tel:'';
            $engine_room['station_id']=$electric_meter['station_id'];
            //机房位置      engine_room
            $position=$objPHPExcel->getActiveSheet()->getCell("Y".$j)->getValue();
            $engine_room['position']=isset($position)?$position:'';

            //物业位置    engine_room
            $property_position=$objPHPExcel->getActiveSheet()->getCell("Z".$j)->getValue();
            $engine_room['property_position']=isset($property_position)?$property_position:'';

           // 实际收款单位 engine_room
            $receive=$objPHPExcel->getActiveSheet()->getCell("AA".$j)->getValue();
            $engine_room['receive']=isset($receive)?$receive:'';

            //出租单位名称  engine_room
            $rent=$objPHPExcel->getActiveSheet()->getCell("AB".$j)->getValue();
            $engine_room['rent']=isset($rent)?$rent:'';
            //机房名称
            $engine_room['name']=isset($rent)?$rent:'';
            //备注   engine_room
            $remark=$objPHPExcel->getActiveSheet()->getCell("AC".$j)->getValue();
            $engine_room['remark']=isset($remark)?$remark:'';

            //合同编号  contract
            $number=$objPHPExcel->getActiveSheet()->getCell("AD".$j)->getValue();
            $contract['number']=isset($number)?$number:'contract-add-000000';
          
            //确认合同日期   contract
             //业务员id
            $agreement['u_id'] = $electric_meter['staff_id'];
            $agreement['type']=2;
            
            $agreement['file_id']=1;
            $agreement['d_id'] = (new ElectricMeter)->getDepartId($electric_meter['staff_id']);
            $agreement['file_id'] = 0;
            $time =  $contract_end_date - $contract_start_date;
            $agreement['period'] = ceil($time/(3600*24*30));
            $sign_date= $objPHPExcel->getActiveSheet()->getCell("AE".$j)->getValue();
            if($sign_date){
                $contract['start_time']=strtotime($sign_date);
                $contract['end_time']=strtotime($sign_date);
                $contract['sign_time']=strtotime($sign_date);
            }else{
              $contract['start_time']=0;
              $contract['end_time']=0;
              $contract['sign_time']=0;
            }
           
            //合同有效时间   contract
            $period=$objPHPExcel->getActiveSheet()->getCell("AF".$j)->getValue();
            $contract['period']=isset($period)?$period:'';
            
            $qrcode=$objPHPExcel->getActiveSheet()->getCell("AG".$j)->getValue();
            $electric_meter['qrcode']=isset($qrcode)?$qrcode:'';


            $contract['name'] = '新增电表合同';
            $contract['u_id'] = $electric_meter['staff_id'];

            $contract['d_id'] = (new ElectricMeter)->getDepartId($electric_meter['staff_id']);;
            $contract['type'] = 1;
            $contract['file_id'] = 0;
            $contract['remark'] = '';
   
         
              $electricMeterAll[] = $electric_meter;
              $contractAll[] = $contract;
              $agreementAll[] = $agreement;
              $engineRoomAll[] = $engine_room;
            
             
       
        }
       
          Db::name('contract')->insertAll($contractAll);
          Db::name('engine_room')->insertAll($engineRoomAll);
          Db::name('contract')->insertAll($agreementAll);
       
              $contractID = Db::name('contract')->field('id')->where("create_time='$create_time'")->select();
              $engineRoomID = Db::name('engine_room')->field('id')->where("create_time='$create_time'")->select();
              $agreementID = Db::name('contract')->field('id')->where("create_time='$create_time' and type=2")->select();

           $electricMeterAllList = array();
          foreach ($electricMeterAll  as  $key => $val){
             $electricMeterAllList[]= array_merge($val,array('er_id'=>$engineRoomID[$key]['id']));
          }
          foreach ($electricMeterAllList as $key => $value) {
                $electricMeterAllListb[] = array_merge($value,array('contract_id'=>$contractID[$key]['id']));
          }

          foreach ($electricMeterAllListb as $key => $value) {
                $electricMeterAllListc[] = array_merge($value,array('protocol_id'=>$agreementID[$key]['id']));
          }
          $sucelectric= Db::name('electric_meter')->insertAll($electricMeterAllListc);
            $data['total']=$highestRow-1;
            $data['suc']=$sucelectric;
            $data['er']=$highestRow-1-$sucelectric;
            return ['code' =>200,'message' => '导入成功' ,'data' => $data];
    
    }


 //基站信息导入
   public function baseExecl(string $file = '', int $sheet = 0, int $columnCnt = 0, &$options = [])
    {
              ini_set ('memory_limit', '1000M');
                $extension = strtolower(pathinfo($file, PATHINFO_EXTENSION));//判断导入表格后缀格式
                  // 有Xls和Xlsx格式两种
                if ($extension == 'xlsx') {
                $objReader =IOFactory::createReader('Xlsx');
                  $objPHPExcel = $objReader->load($file);
      
                } elseif($extension == 'xls'){
                $objReader =IOFactory::createReader('Xls');
                 $objPHPExcel = $objReader->load($file);
    
                }else{
                  $this->error('必须为excel表格，且必须为xls或者xlsx格式！');

                }



             //$filename可以是上传的表格，或者是指定的表格
            $sheet = $objPHPExcel->getSheet(0);   //excel中的第一张sheet
            $highestRow = $sheet->getHighestRow();       // 取得总行数
            // $highestColumn = $sheet->getHighestColumn();   // 取得总列数
          
              if($objPHPExcel->getActiveSheet()->getCell("A1")->getValue()!=='基站名称'){
                      return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("B1")->getValue()!=='基站编号'){
                       return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("C1")->getValue()!=='项目编号'){
                      return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("D1")->getValue()!=='需求号'){
                       return ['code' =>5,'message' => '模板错误'];
                    }
                   
                    if($objPHPExcel->getActiveSheet()->getCell("E1")->getValue()!=='运营商'){
                       return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("F1")->getValue()!=='业务线'){
                      return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("G1")->getValue()!=='区域'){
                       return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("H1")->getValue()!=='基站状态'){
                      return ['code' =>5,'message' => '模板错误'];
                    }

                    if($objPHPExcel->getActiveSheet()->getCell("I1")->getValue()!=='经度'){
                       return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("J1")->getValue()!=='纬度'){
                      return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("K1")->getValue()!=='项目地址'){
                       return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("L1")->getValue()!=='墙面天线数'){
                       return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("M1")->getValue()!=='杆数'){
                       return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("N1")->getValue()!=='箱体数'){
                       return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("O1")->getValue()!=='光缆公里数'){
                       return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("P1")->getValue()!=='RRU数量'){
                       return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("Q1")->getValue()!=='RRU方位角'){
                       return ['code' =>5,'message' => '模板错误'];
                    }
                             
                    if($objPHPExcel->getActiveSheet()->getCell("R1")->getValue()!=='施工队类型'){
                      return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("S1")->getValue()!=='施工联系方式'){
                       return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("T1")->getValue()!=='部门'){
                      return ['code' =>5,'message' => '模板错误'];
                    }
                    if($objPHPExcel->getActiveSheet()->getCell("U1")->getValue()!=='经理'){
                      return ['code' =>5,'message' => '模板错误'];
                    }
                      if($objPHPExcel->getActiveSheet()->getCell("V1")->getValue()!=='业务员'){
                      return ['code' =>5,'message' => '模板错误'];
                    }

                     if($objPHPExcel->getActiveSheet()->getCell("W1")->getValue()!=='需求来源'){
                      return ['code' =>5,'message' => '模板错误'];
                    }
                      if($objPHPExcel->getActiveSheet()->getCell("X1")->getValue()!=='公司上游客户'){
                      return ['code' =>5,'message' => '模板错误'];
                    }
                     if($objPHPExcel->getActiveSheet()->getCell("Y1")->getValue()!=='频段'){
                      return ['code' =>5,'message' => '模板错误'];
                    }
                     if($objPHPExcel->getActiveSheet()->getCell("Z1")->getValue()!=='创建时间'){
                      return ['code' =>5,'message' => '模板错误'];
                    }
                  
                  



            $usersExits = [];
            //循环读取excel表格，整合成数组。如果是不指定key的二维，就用$data[i][j]表示。
            $create_time=time();
         
         
            $station['update_time']=$create_time;
            $detai['update_time'] = $create_time;
            $vendor['update_time'] = $create_time;
            for ($j = 2; $j <= $highestRow; $j++) {
            //基站名称 
            $station_name=$objPHPExcel->getActiveSheet()->getCell("A".$j)->getValue();
              $station['station_name']=isset($station_name)?$station_name:'';

            //基站编号
            $station_sp_code=$objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue();
            $station['station_sp_code']=isset($station_sp_code)?$station_sp_code:'';

            // 项目编号
          $proj_number=$objPHPExcel->getActiveSheet()->getCell("C".$j)->getValue();
          if(empty($proj_number)){
               return ['code' =>7,'message' => '项目编号不能为空'];
           }
            $ojnumber =(new BaseStationModel)->getproId($proj_number);
            if($ojnumber){
            return ['code' =>8,'message' => '该项目编号：'.$proj_number.'已存在，不能重复添加'];
            }
             $station['proj_number']=$proj_number;
           //需求号
            $sp_req_code=$objPHPExcel->getActiveSheet()->getCell("D".$j)->getValue();
           
             $station['sp_req_code']=isset($sp_req_code)?$sp_req_code:'';

            //运营商
            $original_sp_id=$objPHPExcel->getActiveSheet()->getCell("E".$j)->getValue();
            if($original_sp_id){
             $station['original_sp_id']=(new BaseStationModel)->getoriginalById($original_sp_id);
            }else{
               $station['original_sp_id']=0;
            }
           

            //业务线
            $business_line_id=$objPHPExcel->getActiveSheet()->getCell("F".$j)->getValue();
          if($business_line_id){
             $station['business_line_id']=(new BaseStationModel)->getbussById($business_line_id);
           }else{
            $station['business_line_id']=0;
           }
             
            //区域
            $area_id=$objPHPExcel->getActiveSheet()->getCell("G".$j)->getValue();
            if($area_id){

             $station['area_id']=(new BaseStationModel)->getregionById($area_id);
            }else{
              $station['area_id']=0;
            }
            //基站状态
            $base_station_state=$objPHPExcel->getActiveSheet()->getCell("H".$j)->getValue();
            if($base_station_state){
            $station['base_station_state']=(new BaseStationModel)->getstatusById($base_station_state);
            }else{
               $station['base_station_state']=0;
            }
           

            //经度
            $longitude=$objPHPExcel->getActiveSheet()->getCell("I".$j)->getValue();
            $detai['longitude']=isset($longitude)?$longitude:'';

           //纬度
            $latitude=$objPHPExcel->getActiveSheet()->getCell("J".$j)->getValue();
            $detai['latitude']=isset($latitude)?$latitude:'';

            // 项目地址
            $location= $objPHPExcel->getActiveSheet()->getCell("K".$j)->getValue();
            $station['location']=isset($location)?$location:'';

              // 墙面天线数
            $antenna_number=$objPHPExcel->getActiveSheet()->getCell("L".$j)->getValue();
            $detai['antenna_number']=isset($antenna_number)?$antenna_number:'0';
            // 杆数
            $rod_number=$objPHPExcel->getActiveSheet()->getCell("M".$j)->getValue();
            $detai['rod_number']=isset($rod_number)?$rod_number:'0';
         
            // 箱体数
            $box_number=$objPHPExcel->getActiveSheet()->getCell("N".$j)->getValue();
            $detai['box_number']=isset($box_number)?$box_number:'0';
            
           // 光缆公里数
            $optical_cable=$objPHPExcel->getActiveSheet()->getCell("O".$j)->getValue();
            $detai['optical_cable']=isset($optical_cable)?$optical_cable:'0';

           // RRU数量
            $rru_number= $objPHPExcel->getActiveSheet()->getCell("P".$j)->getValue();
            $detai['rru_number']=isset($rru_number)?$rru_number:'0';
           // RRU方位角  
            $rru_zimuth= $objPHPExcel->getActiveSheet()->getCell("Q".$j)->getValue();
            $detai['rru_zimuth']=isset($rru_zimuth)?$rru_zimuth:'0';
            // 施工队类型
            $type= $objPHPExcel->getActiveSheet()->getCell("R".$j)->getValue();
             if($type=='自建'){
              $vendor['type']=1;
             }elseif($type=='外围'){
               $vendor['type']=2;
             }elseif($type=='挂靠'){
               $vendor['type']=3;
             
             }else{
               $vendor['type']=0;
             }

            // 施工联系方式
            $construction_number=$objPHPExcel->getActiveSheet()->getCell("S".$j)->getValue();
            $vendor['construction_number']=isset($construction_number)?$construction_number:'';

            // 部门
           $department_id=$objPHPExcel->getActiveSheet()->getCell("T".$j)->getValue();
           if($department_id){
  $station['department_id']=(new BaseStationModel)->getdepartById($department_id);
           }else{
            $station['department_id']=0;
           }
         
             // 经理
            $three_level_manager=$objPHPExcel->getActiveSheet()->getCell("U".$j)->getValue();
            $vendor['three_level_manager']=isset($three_level_manager)?$three_level_manager:'';
            // 业务员
            $salesman=$objPHPExcel->getActiveSheet()->getCell("V".$j)->getValue();
            $vendor['salesman']=isset($salesman)?$salesman:'';
       
               // 需求来源
           $sources_id=$objPHPExcel->getActiveSheet()->getCell("W".$j)->getValue();
           if($sources_id){
 $station['sources_id']=(new BaseStationModel)->getsourceId($sources_id);
           }else{
            $station['sources_id']=0;
           }
          

                // 公司上游客户
           $customers_id=$objPHPExcel->getActiveSheet()->getCell("X".$j)->getValue();
         
           if($customers_id){
  $station['customers_id']=(new BaseStationModel)->getcustomersId($customers_id);
           }else{
            $station['customers_id']=0;
           }

                 // 频段
           $frequencyband_id=$objPHPExcel->getActiveSheet()->getCell("Y".$j)->getValue();
           if($frequencyband_id){
           $detai['frequencyband_id']=(new BaseStationModel)->getfrequencybandId($frequencyband_id);
           }else{
            $detai['frequencyband_id']=0;
           }
          
          
           $add_date= strtotime($objPHPExcel->getActiveSheet()->getCell("Z".$j)->getValue());
           if($add_date){
            $station['create_time']=$add_date;
             $detai['create_time'] = $add_date;
            $vendor['create_time'] = $add_date;
           }else{
             $station['create_time']=$create_time;
             $detai['create_time'] = $create_time;
            $vendor['create_time'] = $create_time;
           }

              $stationAll[] = $station;
              $detaiAll[] = $detai;
              $vendorAll[] = $vendor;
   
            
             
       
        }
       
         $sucelectric= Db::name('station_info')->insertAll($stationAll);

              $station_info = Db::name('station_info')->field('station_id,original_sp_id')->where("update_time='$create_time'")->select();
   

           $detaiList = array();
          foreach ($detaiAll  as  $key => $val){
            $value = array_merge($val,array('stationid'=>$station_info[$key]['station_id']));
            $detaiList[] = $value;
          }
                   Db::name('station_info_detai')->insertAll($detaiList);


          $vendinfoList = array();
          foreach ($vendorAll  as  $k => $vo){
            $value = array_merge($vo,array('vendor_id'=>$station_info[$k]['station_id']));
            $vendinfoList[] = $value;
          }
           Db::name('vendor_info')->insertAll($vendinfoList);

            $data['total']=$highestRow-1;
            $data['suc']=$sucelectric;
            $data['er']=$highestRow-1-$sucelectric;
            return ['code' =>200,'message' => '导入成功' ,'data' => $data];
    
    }



    /**
     * Excel导出，TODO 可继续优化
     *
     * @param array  $datas      导出数据，格式['A1', => 'XXXX公司报表', 'B1' => '序号']
     * @param string $fileName   导出文件名称
     * @param array  $options    操作选项，例如：
     *                           bool   print       设置打印格式
     *                           string freezePane  锁定行数，例如表头为第一行，则锁定表头输入A2
     *                           array  setARGB     设置背景色，例如['A1',, 'C1']
     *                           array  setWidth    设置宽度，例如['A' => 30, 'C' => 20]
     *                           bool   setBorder   设置单元格边框
     *                           array  mergeCells  设置合并单元格，例如['A1:J1' => 'A1:J1']
     *                           array  formula     设置公式，例如['F2' => '=IF(D2>0,E42/D2,0)']
     *                           array  format      设置格式，整列设置，例如['A' => 'General']
     *                           array  alignCenter 设置居中样式，例如['A1',, 'A2']
     *                           array  bold        设置加粗样式，例如['A1',, 'A2']
     *                           string savePath    保存路径，设置后则文件保存到服务器，不通过浏览器下载
     */
    public function exportExcel(array $datas, string $fileName = '', array $options = []): bool
    {
        try {
            if (empty($datas)) {
                return false;
            }

            set_time_limit(0);
            /** @var Spreadsheet $objSpreadsheet */
            $objSpreadsheet = app(Spreadsheet::class);
            /* 设置默认文字居左，上下居中 */
            $styleArray = [
                'alignment' => [
                    'horizontal' => Alignment::HORIZONTAL_LEFT,
                    'vertical'   => Alignment::VERTICAL_CENTER,
                ],
            ];
            $objSpreadsheet->getDefaultStyle()->applyFromArray($styleArray);
            /* 设置Excel Sheet */
            $activeSheet = $objSpreadsheet->setActiveSheetIndex(0);

            /* 打印设置 */
            if (isset($options['print']) && $options['print']) {
                /* 设置打印为A4效果 */
                $activeSheet->getPageSetup()->setPaperSize(PageSetup:: PAPERSIZE_A4);
                /* 设置打印时边距 */
                $pValue = 1 / 2.54;
                $activeSheet->getPageMargins()->setTop($pValue / 2);
                $activeSheet->getPageMargins()->setBottom($pValue * 2);
                $activeSheet->getPageMargins()->setLeft($pValue / 2);
                $activeSheet->getPageMargins()->setRight($pValue / 2);
            }

            /* 行数据处理 */
            foreach ($datas as $sKey => $sItem) {
                /* 默认文本格式 */
                $pDataType = DataType::TYPE_STRING;

                /* 设置单元格格式 */
                if (isset($options['format']) && !empty($options['format'])) {
                    $colRow = Coordinate::coordinateFromString($sKey);

                    /* 存在该列格式并且有特殊格式 */
                    if (isset($options['format'][$colRow[0]]) &&
                        NumberFormat::FORMAT_GENERAL != $options['format'][$colRow[0]]) {
                        $activeSheet->getStyle($sKey)->getNumberFormat()
                            ->setFormatCode($options['format'][$colRow[0]]);

                        if (false !== strpos($options['format'][$colRow[0]], '0.00') &&
                            is_numeric(str_replace(['￥', ','], '', $sItem))) {
                            /* 数字格式转换为数字单元格 */
                            $pDataType = DataType::TYPE_NUMERIC;
                            $sItem     = str_replace(['￥', ','], '', $sItem);
                        }
                    } elseif (is_int($sItem)) {
                        $pDataType = DataType::TYPE_NUMERIC;
                    }
                }

                $activeSheet->setCellValueExplicit($sKey, $sItem, $pDataType);

                /* 存在:形式的合并行列，列入A1:B2，则对应合并 */
                if (false !== strstr($sKey, ":")) {
                    $options['mergeCells'][$sKey] = $sKey;
                }
            }

            unset($datas);

            /* 设置锁定行 */
            if (isset($options['freezePane']) && !empty($options['freezePane'])) {
                $activeSheet->freezePane($options['freezePane']);
                unset($options['freezePane']);
            }

            /* 设置宽度 */
            if (isset($options['setWidth']) && !empty($options['setWidth'])) {
                foreach ($options['setWidth'] as $swKey => $swItem) {
                    $activeSheet->getColumnDimension($swKey)->setWidth($swItem);
                }

                unset($options['setWidth']);
            }

            /* 设置背景色 */
            if (isset($options['setARGB']) && !empty($options['setARGB'])) {
                foreach ($options['setARGB'] as $sItem) {
                    $activeSheet->getStyle($sItem)
                        ->getFill()->setFillType(Fill::FILL_SOLID)
                        ->getStartColor()->setARGB(Color::COLOR_YELLOW);
                }

                unset($options['setARGB']);
            }

            /* 设置公式 */
            if (isset($options['formula']) && !empty($options['formula'])) {
                foreach ($options['formula'] as $fKey => $fItem) {
                    $activeSheet->setCellValue($fKey, $fItem);
                }

                unset($options['formula']);
            }

            /* 合并行列处理 */
            if (isset($options['mergeCells']) && !empty($options['mergeCells'])) {
                $activeSheet->setMergeCells($options['mergeCells']);
                unset($options['mergeCells']);
            }

            /* 设置居中 */
            if (isset($options['alignCenter']) && !empty($options['alignCenter'])) {
                $styleArray = [
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical'   => Alignment::VERTICAL_CENTER,
                    ],
                ];

                foreach ($options['alignCenter'] as $acItem) {
                    $activeSheet->getStyle($acItem)->applyFromArray($styleArray);
                }

                unset($options['alignCenter']);
            }

            /* 设置加粗 */
            if (isset($options['bold']) && !empty($options['bold'])) {
                foreach ($options['bold'] as $bItem) {
                    $activeSheet->getStyle($bItem)->getFont()->setBold(true);
                }

                unset($options['bold']);
            }

            /* 设置单元格边框，整个表格设置即可，必须在数据填充后才可以获取到最大行列 */
            if (isset($options['setBorder']) && $options['setBorder']) {
                $border    = [
                    'borders' => [
                        'allBorders' => [
                            'borderStyle' => Border::BORDER_THIN, // 设置border样式
                            'color'       => ['argb' => 'FF000000'], // 设置border颜色
                        ],
                    ],
                ];
                $setBorder = 'A1:' . $activeSheet->getHighestColumn() . $activeSheet->getHighestRow();
                $activeSheet->getStyle($setBorder)->applyFromArray($border);
                unset($options['setBorder']);
            }

            $fileName = !empty($fileName) ? $fileName : (date('YmdHis') . '.xlsx');

            if (!isset($options['savePath'])) {
                /* 直接导出Excel，无需保存到本地，输出07Excel文件 */
                header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                header(
                    "Content-Disposition:attachment;filename=" . iconv(
                        "utf-8", "GB2312//TRANSLIT", $fileName
                    )
                );
                header('Cache-Control: max-age=0');//禁止缓存
                $savePath = 'php://output';
            } else {
                $savePath = $options['savePath'];
            }

            ob_clean();
            ob_start();
            $objWriter = IOFactory::createWriter($objSpreadsheet, 'Xlsx');
            $objWriter->save($savePath);
            /* 释放内存 */
            $objSpreadsheet->disconnectWorksheets();
            unset($objSpreadsheet);
            ob_end_flush();

            return true;
        } catch (Exception $e) {
            return false;
        }
    }

    public function export($data,$name){
       ini_set ('memory_limit', '1000M');
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

//        $sheet->setCellValue('A1','station_name 基站名称');
//        $sheet->setCellValue('B1','station_sp_code 基站编号');
//        $sheet->setCellValue('C1','proj_number 项目编号');
//        $sheet->setCellValue('D1','number 编号');
//        $sheet->setCellValue('E1','emt_id 类型');
//        $sheet->setCellValue('F1','pay_number 缴费号');
//        $sheet->setCellValue('G1','multiple 倍率');
//        $sheet->setCellValue('H1','pay_cycle 交费周期');
//        $sheet->setCellValue('I1','unit_price 电表单价');
//        $sheet->setCellValue('J1','protocol_price 协议单价');
//        $sheet->setCellValue('K1','address 电表地址 地址');
//        $sheet->setCellValue('L1','remark 备注');
//        $sheet->setCellValue('M1','create_time 创建时');
//        $sheet->setCellValue('N1','init_degree 初始度数');
//        $sheet->setCellValue('O1','first_cp_date 第一次抄表日期');
//        $sheet->setCellValue('P1','is_protocol 是否是协议价');
//        $sheet->setCellValue('Q1','staff_name 员工姓名');
//        $sheet->setCellValue('R1','er_name 机房名称');
//        $sheet->setCellValue('S1','er_position 机房位置');
//        $sheet->setCellValue('T1','qrcode 二维码');
//        $sheet->setCellValue('U1','er_contact 联系人');
//        $sheet->setCellValue('V1','er_tel 联系电话');
//        $sheet->setCellValue('W1','property_position 物业位置');
//        $sheet->setCellValue('X1','receive 实际收款单位');
//        $sheet->setCellValue('Y1','rent 出租单位');
//        $sheet->setCellValue('Z1','er_remark 机房备注');
//        $sheet->setCellValue('AA1','contract_number 合同编号');
//        $sheet->setCellValue('AB1','contract_sign_time 合同开始时间');
//        $sheet->setCellValue('AC1','period 合同周期');
//        $sheet->setCellValue('AD1','protocol_name 协议名称');
//        $sheet->setCellValue('AE1','protocol_number 协议编号');
//        $sheet->setCellValue('AF1','protocol_sign_date 协议签订时间');
//        $sheet->setCellValue('AG1','protocol_start_date 协议开始时间');
//        $sheet->setCellValue('AH1','protocol_end_date 协议结束时间');
//        $sheet->setCellValue('AI1','protocol_remark 协议备注');
//        $sheet->setCellValue('AJ1','init_amount 初始电表额度');
//        $sheet->setCellValue('AK1','qrcode 二维码');
        $sheet->setCellValue('A1','基站名称');
        $sheet->setCellValue('B1','基站编号');
        $sheet->setCellValue('C1','项目编号');
        $sheet->setCellValue('D1','表号');
        $sheet->setCellValue('E1','二维码');
        $sheet->setCellValue('F1','类型');
        $sheet->setCellValue('G1','缴费号');
        $sheet->setCellValue('H1','倍率');
        $sheet->setCellValue('I1','交费周期');
        $sheet->setCellValue('J1','电表单价');
        $sheet->setCellValue('K1','协议单价');
        $sheet->setCellValue('L1','电表地址');
        $sheet->setCellValue('M1','备注');
        $sheet->setCellValue('N1','创建时');
        $sheet->setCellValue('O1','初始度数');
        $sheet->setCellValue('P1','第一次抄表日期');
        $sheet->setCellValue('Q1','是否是协议价');
        $sheet->setCellValue('R1','员工姓名');
        $sheet->setCellValue('S1','机房名称');
        $sheet->setCellValue('T1','机房位置');
        $sheet->setCellValue('U1','二维码');
        $sheet->setCellValue('V1','联系人');
        $sheet->setCellValue('W1','联系电话');
        $sheet->setCellValue('X1','物业位置');
        $sheet->setCellValue('Y1','实际收款单位');
        $sheet->setCellValue('Z1','出租单位');
        $sheet->setCellValue('AA1','机房备注');
        $sheet->setCellValue('AB1','合同编号');
        $sheet->setCellValue('AC1','合同开始时间');
        $sheet->setCellValue('AD1','合同周期');
        $sheet->setCellValue('AE1','协议名称');
        $sheet->setCellValue('AF1','协议编号');
        $sheet->setCellValue('AG1','协议签订时间');
        $sheet->setCellValue('AH1','协议开始时间');
        $sheet->setCellValue('AI1','协议结束时间');
        $sheet->setCellValue('AJ1','协议备注');
        $sheet->setCellValue('AK1','初始电表额度');
        $sheet->setCellValue('AL1','二维码');


        for ($i = 0; $i < count($data); $i++) {
            $sheet->setCellValue('A'.($i+2),$data[$i]['station_name']);
            $sheet->setCellValue('B'.($i+2),$data[$i]['station_sp_code']);
            $sheet->setCellValue('C'.($i+2),$data[$i]['proj_number']);
            $sheet->setCellValue('D'.($i+2),$data[$i]['number']);
            $sheet->setCellValue('E'.($i+2),$data[$i]['qrcode']);
            $sheet->setCellValue('F'.($i+2),$data[$i]['emt_name']);
            $sheet->setCellValue('G'.($i+2),$data[$i]['pay_number']);
            $sheet->setCellValue('H'.($i+2),$data[$i]['multiple']);
            $sheet->setCellValue('I'.($i+2),$data[$i]['pay_cycle']);
            $sheet->setCellValue('J'.($i+2),$data[$i]['unit_price']);
            $sheet->setCellValue('K'.($i+2),$data[$i]['protocol_price']);
            $sheet->setCellValue('L'.($i+2),$data[$i]['address']);
            $sheet->setCellValue('M'.($i+2),$data[$i]['remark']);
            $sheet->setCellValue('N'.($i+2),$data[$i]['create_time']);
            $sheet->setCellValue('O'.($i+2),$data[$i]['init_degree']);
            $sheet->setCellValue('P'.($i+2),$data[$i]['first_cp_date']);
            $sheet->setCellValue('Q'.($i+2),$data[$i]['isprotocol']);
            $sheet->setCellValue('R'.($i+2),$data[$i]['staff_name']);
            $sheet->setCellValue('S'.($i+2),$data[$i]['er_name']);
            $sheet->setCellValue('T'.($i+2),$data[$i]['er_position']);
            $sheet->setCellValue('U'.($i+2),$data[$i]['qrcode']);
            $sheet->setCellValue('V'.($i+2),$data[$i]['er_contact']);
            $sheet->setCellValue('W'.($i+2),$data[$i]['er_tel']);
            $sheet->setCellValue('X'.($i+2),$data[$i]['property_position']);
            $sheet->setCellValue('Y'.($i+2),$data[$i]['receive']);
            $sheet->setCellValue('Z'.($i+2),$data[$i]['rent']);
            $sheet->setCellValue('AA'.($i+2),$data[$i]['er_remark']);
            $sheet->setCellValue('AB'.($i+2),$data[$i]['contract_number']);
            $sheet->setCellValue('AC'.($i+2),$data[$i]['contract_sign_time']);
            $sheet->setCellValue('AD'.($i+2),$data[$i]['period'].'月');
            $sheet->setCellValue('AE'.($i+2),$data[$i]['protocol_name']);
            $sheet->setCellValue('AF'.($i+2),$data[$i]['protocol_number']);
            $sheet->setCellValue('AG'.($i+2),$data[$i]['protocol_sign_date']);
            $sheet->setCellValue('AH'.($i+2),$data[$i]['protocol_start_date']);
            $sheet->setCellValue('AI'.($i+2),$data[$i]['protocol_end_date']);
            $sheet->setCellValue('AJ'.($i+2),$data[$i]['protocol_remark']);
            $sheet->setCellValue('AK'.($i+2),$data[$i]['init_amount']);
        }

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$name.'.xlsx"');
        header('Cache-Control: max-age=0');
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);

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

     public function baseexport($data,$name){
      ini_set ('memory_limit', '1000M');
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('A1','基站名称 station_name');
        $sheet->setCellValue('B1','基站编号 station_sp_code');
        $sheet->setCellValue('C1','运营商 operator_name');
        $sheet->setCellValue('D1','需求号 sp_req_code');
        $sheet->setCellValue('E1','基站状态 cate_name');
        $sheet->setCellValue('F1','需求来源 sources_name');
        $sheet->setCellValue('G1','上游客户 customers_name');
        $sheet->setCellValue('H1','项目编号 proj_number');
        $sheet->setCellValue('I1','经度 longitude');
        $sheet->setCellValue('J1','纬度 latitude');
        $sheet->setCellValue('K1','业务线 business_name');
        $sheet->setCellValue('L1','区域 region_name');
        $sheet->setCellValue('M1','项目地址 location');
        $sheet->setCellValue('N1','墙面天线数 antenna_number');
        $sheet->setCellValue('O1','杆数 rod_number');
        $sheet->setCellValue('P1','箱体数 box_number');
        $sheet->setCellValue('Q1','RRU数量 rru_number');
        $sheet->setCellValue('R1','RRU方位角 rru_zimuth');
        $sheet->setCellValue('S1','光缆公里数 optical_cable');
        $sheet->setCellValue('T1','施工队类型 type');
        $sheet->setCellValue('U1','施工联系方式 construction_number');
        $sheet->setCellValue('V1','部门 name');
        $sheet->setCellValue('W1','经理 three_level_manager');
        $sheet->setCellValue('X1','业务员 salesman');
      

        for ($i = 0; $i < count($data); $i++) {
            $sheet->setCellValue('A'.($i+2),$data[$i]['station_name']);
            $sheet->setCellValue('B'.($i+2),$data[$i]['station_sp_code']);
            $sheet->setCellValue('C'.($i+2),$data[$i]['operator_name']);

            $sheet->setCellValue('D'.($i+2),$data[$i]['sp_req_code']);
            $sheet->setCellValue('E'.($i+2),$data[$i]['cate_name']);
            $sheet->setCellValue('F'.($i+2),$data[$i]['sources_name']);
            $sheet->setCellValue('G'.($i+2),$data[$i]['customers_name']);
            $sheet->setCellValue('H'.($i+2),$data[$i]['proj_number']);
            $sheet->setCellValue('I'.($i+2),$data[$i]['longitude']);
            $sheet->setCellValue('J'.($i+2),$data[$i]['latitude']);
            $sheet->setCellValue('K'.($i+2),$data[$i]['business_name']);
            $sheet->setCellValue('L'.($i+2),$data[$i]['region_name']);
            $sheet->setCellValue('M'.($i+2),$data[$i]['location']);
            $sheet->setCellValue('N'.($i+2),$data[$i]['antenna_number']);
            $sheet->setCellValue('O'.($i+2),$data[$i]['rod_number']);
            $sheet->setCellValue('P'.($i+2),$data[$i]['box_number']);
            $sheet->setCellValue('Q'.($i+2),$data[$i]['rru_number']);
            $sheet->setCellValue('R'.($i+2),$data[$i]['rru_zimuth']);
            $sheet->setCellValue('S'.($i+2),$data[$i]['optical_cable']);

            $sheet->setCellValue('T'.($i+2),$data[$i]['type']);
            $sheet->setCellValue('U'.($i+2),$data[$i]['construction_number']);
            $sheet->setCellValue('V'.($i+2),$data[$i]['name']);
            $sheet->setCellValue('W'.($i+2),$data[$i]['three_level_manager']);
            $sheet->setCellValue('X'.($i+2),$data[$i]['salesman']);

        }

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$name.'.xlsx"');
        header('Cache-Control: max-age=0');
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);

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