<?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 = [])
{
$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' => '模板错误'];
}
$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($station_sp_code,$station_name,$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= Date::excelToTimestamp($objPHPExcel->getActiveSheet()->getCell("K".$j)->getValue());
$electric_meter['first_cp_date']=isset($first_cp_date)?$first_cp_date:'';
//是否是协议价 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= Date::excelToTimestamp($objPHPExcel->getActiveSheet()->getCell("P".$j)->getValue());
$agreement['start_time']=isset($contract_start_date)?$contract_start_date:'';
//结束时间
$contract_end_date= Date::excelToTimestamp($objPHPExcel->getActiveSheet()->getCell("Q".$j)->getValue());
$agreement['end_time']=isset($contract_end_date)?$contract_end_date:'';
//签订日期
$contract_sign_date= Date::excelToTimestamp($objPHPExcel->getActiveSheet()->getCell("R".$j)->getValue());
$agreement['sign_time']=isset($contract_sign_date)?$contract_sign_date:'';
//备注
$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:'';
//甲方联系人 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['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= Date::excelToTimestamp($objPHPExcel->getActiveSheet()->getCell("AE".$j)->getValue());
$contract['start_time']=isset($sign_date)?$sign_date:'';
$contract['end_time']=isset($sign_date)?$sign_date:'';
$contract['sign_time']=isset($sign_date)?$sign_date:'';
//合同有效时间 contract
$period=$objPHPExcel->getActiveSheet()->getCell("AF".$j)->getValue();
$contract['period']=isset($period)?$period:'';
$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 = [])
{
$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' => '模板错误'];
}
$usersExits = [];
//循环读取excel表格，整合成数组。如果是不指定key的二维，就用$data[i][j]表示。
$create_time=time();
$station['create_time']=$create_time;
$detai['create_time'] = $create_time;
$vendor['create_time'] = $create_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();
$station['proj_number']=isset($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();
$station['original_sp_id']=(new BaseStationModel)->getoriginalById($original_sp_id);
//业务线
$business_line_id=$objPHPExcel->getActiveSheet()->getCell("F".$j)->getValue();
$station['business_line_id']=(new BaseStationModel)->getbussById($business_line_id);
//区域
$area_id=$objPHPExcel->getActiveSheet()->getCell("G".$j)->getValue();
$station['area_id']=(new BaseStationModel)->getregionById($area_id);
//基站状态
$base_station_state=$objPHPExcel->getActiveSheet()->getCell("H".$j)->getValue();
$station['base_station_state']=(new BaseStationModel)->getstatusById($base_station_state);
//经度
$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:'';
// 杆数
$rod_number=$objPHPExcel->getActiveSheet()->getCell("M".$j)->getValue();
$detai['rod_number']=isset($rod_number)?$rod_number:'';
// 箱体数
$box_number=$objPHPExcel->getActiveSheet()->getCell("N".$j)->getValue();
$detai['box_number']=isset($box_number)?$box_number:'';
// 光缆公里数
$optical_cable=$objPHPExcel->getActiveSheet()->getCell("O".$j)->getValue();
$detai['optical_cable']=isset($optical_cable)?$optical_cable:'';
// RRU数量
$rru_number= $objPHPExcel->getActiveSheet()->getCell("P".$j)->getValue();
$detai['rru_number']=isset($rru_number)?$rru_number:'';
// RRU方位角
$rru_zimuth= $objPHPExcel->getActiveSheet()->getCell("Q".$j)->getValue();
$detai['rru_zimuth']=isset($rru_zimuth)?$rru_zimuth:'';
// 施工队类型
$type= $objPHPExcel->getActiveSheet()->getCell("R".$j)->getValue();
if($type=='自建'){
$vendor['type']=1;
}elseif($type=='外围'){
$vendor['type']=2;
}else{
$vendor['type']=3;
}
// 施工联系方式
$construction_number=$objPHPExcel->getActiveSheet()->getCell("S".$j)->getValue();
$vendor['construction_number']=isset($construction_number)?$construction_number:'';
// 部门
$department_id=$objPHPExcel->getActiveSheet()->getCell("T".$j)->getValue();
$station['department_id']=(new BaseStationModel)->getstatusById($department_id);
// 经理
$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:'';
$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("create_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){
$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 初始电表额度');
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]['emt_name']);
$sheet->setCellValue('F'.($i+2),$data[$i]['pay_number']);
$sheet->setCellValue('G'.($i+2),$data[$i]['multiple']);
$sheet->setCellValue('H'.($i+2),$data[$i]['pay_cycle']);
$sheet->setCellValue('I'.($i+2),$data[$i]['unit_price']);
$sheet->setCellValue('J'.($i+2),$data[$i]['protocol_price']);
$sheet->setCellValue('K'.($i+2),$data[$i]['address']);
$sheet->setCellValue('L'.($i+2),$data[$i]['remark']);
$sheet->setCellValue('M'.($i+2),$data[$i]['create_time']);
$sheet->setCellValue('N'.($i+2),$data[$i]['init_degree']);
$sheet->setCellValue('O'.($i+2),$data[$i]['first_cp_date']);
$sheet->setCellValue('P'.($i+2),$data[$i]['isprotocol']);
$sheet->setCellValue('Q'.($i+2),$data[$i]['staff_name']);
$sheet->setCellValue('R'.($i+2),$data[$i]['er_name']);
$sheet->setCellValue('S'.($i+2),$data[$i]['er_position']);
$sheet->setCellValue('T'.($i+2),$data[$i]['qrcode']);
$sheet->setCellValue('U'.($i+2),$data[$i]['er_contact']);
$sheet->setCellValue('V'.($i+2),$data[$i]['er_tel']);
$sheet->setCellValue('W'.($i+2),$data[$i]['property_position']);
$sheet->setCellValue('X'.($i+2),$data[$i]['receive']);
$sheet->setCellValue('Y'.($i+2),$data[$i]['rent']);
$sheet->setCellValue('Z'.($i+2),$data[$i]['er_remark']);
$sheet->setCellValue('AA'.($i+2),$data[$i]['contract_number']);
$sheet->setCellValue('AB'.($i+2),$data[$i]['contract_sign_time']);
$sheet->setCellValue('AC'.($i+2),$data[$i]['period'].'月');
$sheet->setCellValue('AD'.($i+2),$data[$i]['protocol_name']);
$sheet->setCellValue('AE'.($i+2),$data[$i]['protocol_number']);
$sheet->setCellValue('AF'.($i+2),$data[$i]['protocol_sign_date']);
$sheet->setCellValue('AG'.($i+2),$data[$i]['protocol_start_date']);
$sheet->setCellValue('AH'.($i+2),$data[$i]['protocol_end_date']);
$sheet->setCellValue('AI'.($i+2),$data[$i]['protocol_remark']);
$sheet->setCellValue('AJ'.($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){
$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','项目编号 proj_number');
$sheet->setCellValue('G1','经度 longitude');
$sheet->setCellValue('H1','纬度 latitude');
$sheet->setCellValue('I1','业务线 business_name');
$sheet->setCellValue('J1','区域 region_name');
$sheet->setCellValue('K1','项目地址 location');
$sheet->setCellValue('L1','墙面天线数 antenna_number');
$sheet->setCellValue('M1','杆数 rod_number');
$sheet->setCellValue('N1','箱体数 box_number');
$sheet->setCellValue('O1','RRU数量 rru_number');
$sheet->setCellValue('P1','RRU方位角 rru_zimuth');
$sheet->setCellValue('Q1','光缆公里数 optical_cable');
$sheet->setCellValue('R1','施工队类型 type');
$sheet->setCellValue('S1','施工联系方式 construction_number');
$sheet->setCellValue('T1','部门 name');
$sheet->setCellValue('U1','经理 three_level_manager');
$sheet->setCellValue('V1','业务员 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]['proj_number']);
$sheet->setCellValue('G'.($i+2),$data[$i]['longitude']);
$sheet->setCellValue('H'.($i+2),$data[$i]['latitude']);
$sheet->setCellValue('I'.($i+2),$data[$i]['business_name']);
$sheet->setCellValue('J'.($i+2),$data[$i]['region_name']);
$sheet->setCellValue('K'.($i+2),$data[$i]['location']);
$sheet->setCellValue('L'.($i+2),$data[$i]['antenna_number']);
$sheet->setCellValue('M'.($i+2),$data[$i]['rod_number']);
$sheet->setCellValue('N'.($i+2),$data[$i]['box_number']);
$sheet->setCellValue('O'.($i+2),$data[$i]['rru_number']);
$sheet->setCellValue('P'.($i+2),$data[$i]['rru_zimuth']);
$sheet->setCellValue('Q'.($i+2),$data[$i]['optical_cable']);
$sheet->setCellValue('R'.($i+2),$data[$i]['type']);
$sheet->setCellValue('S'.($i+2),$data[$i]['construction_number']);
$sheet->setCellValue('T'.($i+2),$data[$i]['name']);
$sheet->setCellValue('U'.($i+2),$data[$i]['three_level_manager']);
$sheet->setCellValue('V'.($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;
}
}