<?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("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' => '模板错误'];
}
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' => '模板错误'];
}
if($objPHPExcel->getActiveSheet()->getCell("AH1")->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:'';
//电表初始充值金额 init_amount
$init_amount=$objPHPExcel->getActiveSheet()->getCell("J".$j)->getValue();
$electric_meter['init_amount']=isset($init_amount)?$init_amount:0;
//抄表单单价 electric_meter
$unit_price=$objPHPExcel->getActiveSheet()->getCell("K".$j)->getValue();
$electric_meter['unit_price']=isset($unit_price)?$unit_price:'';
//首次查表日期 转换为时间戳first_cp_date electric_meter
// $first_cp_date= $objPHPExcel->getActiveSheet()->getCell("L".$j)->getValue();
$first_cp_date= Date::excelToTimestamp($objPHPExcel->getActiveSheet()->getCell("L".$j)->getValue());
$electric_meter['first_cp_date']=isset($first_cp_date)?$first_cp_date:'';
//是否协议价 electric_meter
$is_protocol=$objPHPExcel->getActiveSheet()->getCell("M".$j)->getValue();
if($is_protocol=='是'){
$electric_meter['is_protocol']=1;
}else{
$electric_meter['is_protocol']=0;
}
//协议价 electric_meter
$protocol_price=$objPHPExcel->getActiveSheet()->getCell("N".$j)->getValue();
$electric_meter['protocol_price']=isset($protocol_price)?$protocol_price:'';
//协议名称
$contract_name=$objPHPExcel->getActiveSheet()->getCell("O".$j)->getValue();
$agreement['name']=isset($contract_name)?$contract_name:'';
//协议编号
$contract_number=$objPHPExcel->getActiveSheet()->getCell("P".$j)->getValue();
$agreement['number']=isset($contract_number)?$contract_number:'';
//开始时间
$contract_start_date= $objPHPExcel->getActiveSheet()->getCell("Q".$j)->getValue();
if($contract_start_date){
$agreement['start_time']=Date::excelToTimestamp($contract_start_date);
}else{
$agreement['start_time']=0;
}
//结束时间
$contract_end_date= $objPHPExcel->getActiveSheet()->getCell("R".$j)->getValue();
if($contract_end_date){
$agreement['end_time']=Date::excelToTimestamp($contract_end_date);
}else{
$agreement['end_time']=0;
}
//签订日期
$contract_sign_date= $objPHPExcel->getActiveSheet()->getCell("S".$j)->getValue();
if($contract_sign_date){
$agreement['sign_time']=Date::excelToTimestamp($contract_sign_date);
}else{
$agreement['sign_time']=0;
}
//协议备注
$contract_remark=$objPHPExcel->getActiveSheet()->getCell("T".$j)->getValue();
$agreement['remark']=isset($contract_remark)?$contract_remark:'';
// 缴费周期 electric_meter
$pay_cycle=$objPHPExcel->getActiveSheet()->getCell("U".$j)->getValue();
$electric_meter['pay_cycle']=isset($pay_cycle)?$pay_cycle:'';
//业务员 electric_meter
$staff_id=$objPHPExcel->getActiveSheet()->getCell("V".$j)->getValue();
$electric_meter['staff_id']=(new ElectricMeter)->getStaffId($staff_id);
//电表地址 electric_meter
$address=$objPHPExcel->getActiveSheet()->getCell("W".$j)->getValue();
$electric_meter['address']=isset($address)?$address:'';
//合同附件id
$electric_meter['photo_id']=1;
//甲方联系人 engine_room
$contact=$objPHPExcel->getActiveSheet()->getCell("X".$j)->getValue();
$engine_room['contact']=isset($contact)?$contact:'';
//甲方联系方式 engine_room
$tel=$objPHPExcel->getActiveSheet()->getCell("Y".$j)->getValue();
$engine_room['tel']=isset($tel)?$tel:'';
$engine_room['station_id']=$electric_meter['station_id'];
//机房位置 engine_room
$position=$objPHPExcel->getActiveSheet()->getCell("Z".$j)->getValue();
$engine_room['position']=isset($position)?$position:'';
//物业位置 engine_room
$property_position=$objPHPExcel->getActiveSheet()->getCell("AA".$j)->getValue();
$engine_room['property_position']=isset($property_position)?$property_position:'';
// 实际收款单位 engine_room
$receive=$objPHPExcel->getActiveSheet()->getCell("AB".$j)->getValue();
$engine_room['receive']=isset($receive)?$receive:'';
//出租单位名称 engine_room
$rent=$objPHPExcel->getActiveSheet()->getCell("AC".$j)->getValue();
$engine_room['rent']=isset($rent)?$rent:'';
//机房名称
$engine_room['name']=isset($rent)?$rent:'';
//机房备注 engine_room
$remark=$objPHPExcel->getActiveSheet()->getCell("AD".$j)->getValue();
$engine_room['remark']=isset($remark)?$remark:'';
//合同编号 contract
$number=$objPHPExcel->getActiveSheet()->getCell("AE".$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("AF".$j)->getValue();
if($sign_date){
$contract['start_time']=Date::excelToTimestamp($sign_date);
$contract['end_time']=Date::excelToTimestamp($sign_date);
$contract['sign_time']=Date::excelToTimestamp($sign_date);
}else{
$contract['start_time']=0;
$contract['end_time']=0;
$contract['sign_time']=0;
}
//合同有效时间 contract
$period=$objPHPExcel->getActiveSheet()->getCell("AG".$j)->getValue();
$contract['period']=isset($period)?$period:'';
$qrcode=$objPHPExcel->getActiveSheet()->getCell("AH".$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'")->order('id asc')->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;
}
public function meterexport($data,$name){
ini_set ('memory_limit', '5000M');
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$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','用电量（1.05元/度）');
$sheet->setCellValue('AB1','用电量（1.09元/度）');
$sheet->setCellValue('AC1','用电量（1.123元/度）');
$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','基站问题');
$sheet->setCellValue('AM1','地址');
$sheet->setCellValue('AN1','所属移动分公司');
$sheet->setCellValue('AO1','出租单位名称');
$sheet->setCellValue('AP1','甲方联系人');
$sheet->setCellValue('AQ1','实际收款单位');
$sheet->setCellValue('AR1','联系方式');
$sheet->setCellValue('AS1','机房位置');
$sheet->setCellValue('AT1','物业位置');
$sheet->setCellValue('AU1','备注');
$sheet->setCellValue('AV1','申请确认合同额');
$sheet->setCellValue('AW1','结算日期');
$sheet->setCellValue('AX1','确认合同时间');
$sheet->setCellValue('AY1','确认收入时间');
$sheet->setCellValue('AZ1','是否有发票');
$sheet->setCellValue('BA1','合同编号');
$sheet->setCellValue('BB1','发票类型');
$sheet->setCellValue('BC1','税点');
for ($i = 0; $i < count($data); $i++) {
$sheet->setCellValue('A'.($i+2),$data[$i]['cheque_number']);
$sheet->setCellValue('B'.($i+2),$data[$i]['payment_clerk']);
$sheet->setCellValue('C'.($i+2),$data[$i]['update_time']);
$sheet->setCellValue('D'.($i+2),$data[$i]['proj_number']);
$sheet->setCellValue('E'.($i+2),$data[$i]['payment_number']);
$sheet->setCellValue('F'.($i+2),$data[$i]['meters_number']);
$sheet->setCellValue('G'.($i+2),$data[$i]['payments_umber']);
$sheet->setCellValue('H'.($i+2),$data[$i]['settlement']);
$sheet->setCellValue('I'.($i+2),$data[$i]['base_type']);
$sheet->setCellValue('J'.($i+2),$data[$i]['is_protocol']);
$sheet->setCellValue('K'.($i+2),$data[$i]['region']);
$sheet->setCellValue('L'.($i+2),$data[$i]['network_identity']);
$sheet->setCellValue('M'.($i+2),$data[$i]['station_number']);
$sheet->setCellValue('N'.($i+2),$data[$i]['station_name']);
$sheet->setCellValue('O'.($i+2),$data[$i]['qrcode']);
$sheet->setCellValue('P'.($i+2),$data[$i]['payment_cycle']);
$sheet->setCellValue('Q'.($i+2),$data[$i]['advance_pay_time']);
$sheet->setCellValue('R'.($i+2),$data[$i]['last_number']);
$sheet->setCellValue('S'.($i+2),$data[$i]['current_number']);
$sheet->setCellValue('T'.($i+2),$data[$i]['report_electric']);
$sheet->setCellValue('U'.($i+2),$data[$i]['last_date']);
$sheet->setCellValue('V'.($i+2),$data[$i]['current_date']);
$sheet->setCellValue('W'.($i+2),$data[$i]['multiple']);
$sheet->setCellValue('X'.($i+2),$data[$i]['unit_price']);
$sheet->setCellValue('Y'.($i+2),$data[$i]['finance_unit']);
$sheet->setCellValue('Z'.($i+2),$data[$i]['finance_pay']);
$sheet->setCellValue('AA'.($i+2),$data[$i]['electricone']);
$sheet->setCellValue('AB'.($i+2),$data[$i]['electrictwo']);
$sheet->setCellValue('AC'.($i+2),$data[$i]['electricthree']);
$sheet->setCellValue('AD'.($i+2),$data[$i]['electricity_consumption']);
$sheet->setCellValue('AE'.($i+2),$data[$i]['agreed_price']);
$sheet->setCellValue('AF'.($i+2),$data[$i]['settlement_amount']);
$sheet->setCellValue('AG'.($i+2),$data[$i]['back_number']);
$sheet->setCellValue('AH'.($i+2),$data[$i]['gross_profit']);
$sheet->setCellValue('AI'.($i+2),$data[$i]['meter_type']);
$sheet->setCellValue('AJ'.($i+2),$data[$i]['meter_number']);
$sheet->setCellValue('AK'.($i+2),$data[$i]['meter_problem']);
$sheet->setCellValue('AL'.($i+2),$data[$i]['base_problem']);
$sheet->setCellValue('AM'.($i+2),$data[$i]['address']);
$sheet->setCellValue('AN'.($i+2),$data[$i]['subordinate']);
$sheet->setCellValue('AO'.($i+2),$data[$i]['rental_name']);
$sheet->setCellValue('AP'.($i+2),$data[$i]['contacts']);
$sheet->setCellValue('AQ'.($i+2),$data[$i]['receiving_unit']);
$sheet->setCellValue('AR'.($i+2),$data[$i]['contact_information']);
$sheet->setCellValue('AS'.($i+2),$data[$i]['room_location']);
$sheet->setCellValue('AT'.($i+2),$data[$i]['property_location']);
$sheet->setCellValue('AU'.($i+2),$data[$i]['remarks']);
$sheet->setCellValue('AV'.($i+2),$data[$i]['apply_contract_amount']);
$sheet->setCellValue('AW'.($i+2),$data[$i]['settlement_date']);
$sheet->setCellValue('AX'.($i+2),$data[$i]['confirmation_time']);
$sheet->setCellValue('AY'.($i+2),$data[$i]['income_time']);
$sheet->setCellValue('AZ'.($i+2),$data[$i]['is_invoice']);
$sheet->setCellValue('BA'.($i+2),$data[$i]['contract_number']);
$sheet->setCellValue('BB'.($i+2),$data[$i]['invoice_type']);
$sheet->setCellValue('BC'.($i+2),$data[$i]['tax_point']);
}
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$name.'.xls"');
header('Cache-Control: max-age=0');
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
ob_end_clean();
$writer->save('php://output');
exit;
}
//基站流转数据信息导入
public function basestation_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(); // 取得总列数
$error=0;
for ($j = 2; $j <= $highestRow; $j++) {
$data['jzProjectId']=$objPHPExcel->getActiveSheet()->getCell("A".$j)->getValue();
$data['jzRequire']=$objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue();
$data['jzCode']=$objPHPExcel->getActiveSheet()->getCell("C".$j)->getValue();
$data['jzName']=$objPHPExcel->getActiveSheet()->getCell("D".$j)->getValue();
$data['projectCode']=$objPHPExcel->getActiveSheet()->getCell("E".$j)->getValue();
$data['jzType']=$objPHPExcel->getActiveSheet()->getCell("F".$j)->getValue();
$data['jzYys']=$objPHPExcel->getActiveSheet()->getCell("G".$j)->getValue();
$data['jzUpCustom']=$objPHPExcel->getActiveSheet()->getCell("H".$j)->getValue();
$data['businessLine']=$objPHPExcel->getActiveSheet()->getCell("I".$j)->getValue();
$data['jzQy']=$objPHPExcel->getActiveSheet()->getCell("J".$j)->getValue();
$data['projectAddress']=$objPHPExcel->getActiveSheet()->getCell("K".$j)->getValue();
$data['jzLongitude']=$objPHPExcel->getActiveSheet()->getCell("L".$j)->getValue();
$data['jzLatitude']=$objPHPExcel->getActiveSheet()->getCell("M".$j)->getValue();
$data['jzState']=$objPHPExcel->getActiveSheet()->getCell("N".$j)->getValue();
$data['jzCurDept']=$objPHPExcel->getActiveSheet()->getCell("O".$j)->getValue();
$data['jzProjectOwner']=$objPHPExcel->getActiveSheet()->getCell("P".$j)->getValue();
$data['jzCurUser']=$objPHPExcel->getActiveSheet()->getCell("Q".$j)->getValue();
$data['jzCreateDateStr']=$objPHPExcel->getActiveSheet()->getCell("R".$j)->getValue();
$data['jzEwm']=$objPHPExcel->getActiveSheet()->getCell("S".$j)->getValue();
$url='http://39.155.253.70:8081/jzmessage/KingDeeaddMessage';
$accessToken=$this->check_login();
$jzdata=self::jz($data,$url,$accessToken);
dump($jzdata);
$jzdata= json_decode($jzdata,true);
if($jzdata['code']!==200){
$error++;
}
}
// $datacount['total']=$highestRow-1;
// $datacount['suc']=$highestRow-1-$error;
// $datacount['er']=$error;
// return ['code' =>200,'message' => '导入成功' ,'data' => $datacount];
}
public function check_login(){
$data['password']=371;
$data['username']=371;
$data['userType']=1;
$url="http://39.155.253.70:8081/auth/login";
$token=self::jz($data,$url,'');
$token=json_decode($token,true);
if( $token['code']==200){
return $token['data'];
}
}
static function jz($jzdata,$url,$accessToken){
if($accessToken){
$headers[] = "Authorization: Bearer ". $accessToken;
}else{
$headers=[];
}
$curl = curl_init();
//设置抓取的url
curl_setopt($curl, CURLOPT_URL, $url);
//设置头文件的信息作为数据流输出
curl_setopt($curl, CURLOPT_HEADER,0);
//设置请求头
curl_setopt($curl, CURLOPT_HTTPHEADER,$headers);
//设置获取的信息以文件流的形式返回，而不是直接输出。
curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
//设置post方式提交
curl_setopt($curl, CURLOPT_POST, 1);
//设置post数据
curl_setopt($curl, CURLOPT_POSTFIELDS, $jzdata);
//执行命令
$data = curl_exec($curl);
//关闭URL请求
curl_close($curl);
//显示获得的数据
return $data;
}
}