引用phpexcel导入导出文件
libraries中引用phpExcel ，相关文件已保存在文件里
并自己编写LibComm文件
1 <?php 2 namespace Inner\Libraries; 3 4 5 class LibComm { 6 7 /** 8 * 图形验证码 9 * 10 * */ 11 static function Captcha(){ 12 $cache = \Config\Services::cache(); 13 $request = \Config\Services::request(); 14 //session_start(); 15 $image = imagecreatetruecolor(100, 35); //1>设置验证码图片大小的函数 16 //5>设置验证码颜色 imagecolorallocate(int im, int red, int green, int blue); 17 $bgcolor = imagecolorallocate($image,255,255,255); //#ffffff 18 //6>区域填充 int imagefill(int im, int x, int y, int col) (x,y) 所在的区域着色,col 表示欲涂上的颜色 19 imagefill($image, 0, 0, $bgcolor); 20 //10>设置变量 21 $captcha_code = ""; 22 //7>生成随机的字母和数字 23 for($i=0;$i<4;$i++){ 24 //设置字体大小 25 $fontsize = 18; 26 //设置字体颜色，随机颜色 27 $fontcolor = imagecolorallocate($image, rand(0,120),rand(0,120), rand(0,120)); //0-120深颜色 28 //设置需要随机取的值,去掉容易出错的值如0和o 29 $data ='abcdefghigkmnpqrstuvwxy123456789ABCDEFGHIJKLMNPQRSTUVWSYZ'; 30 //取出值，字符串截取方法 strlen获取字符串长度 31 $fontcontent = substr($data, rand(0,strlen($data)),1); 32 //10>.=连续定义变量 33 $captcha_code .= $fontcontent; 34 //设置坐标 35 $x = ($i*100/4)+rand(5,10); 36 $y = rand(5,10); 37 imagestring($image,$fontsize,$x,$y,$fontcontent,$fontcolor); 38 } 39 //10>存到session 40 //$_SESSION['authcode'] = $captcha_code; 41 $cache->save($request->getIPAddress(),$captcha_code); 42 //8>增加干扰元素，设置雪花点 43 for($i=0;$i<200;$i++){ 44 //设置点的颜色，50-200颜色比数字浅，不干扰阅读 45 $pointcolor = imagecolorallocate($image,rand(50,200), rand(50,200), rand(50,200)); 46 //imagesetpixel — 画一个单一像素 47 imagesetpixel($image, rand(1,99), rand(1,29), $pointcolor); 48 } 49 //9>增加干扰元素，设置横线 50 for($i=0;$i<3;$i++){ 51 //设置线的颜色 52 $linecolor = imagecolorallocate($image,rand(80,220), rand(80,220),rand(80,220)); 53 //设置线，两点一线 54 imageline($image,rand(1,99), rand(1,29),rand(1,99), rand(1,29),$linecolor); 55 } 56 57 //2>设置头部，image/png 58 header('Content-Type: image/png'); 59 //3>imagepng() 建立png图形函数 60 imagepng($image); 61 //4>imagedestroy() 结束图形函数 销毁$image 62 imagedestroy($image); 63 } 64 65 /** 66 * 数据导入 67 * @param string $file excel文件 68 * @param string $sheet 69 * @return string 返回解析数据 70 * @throws PHPExcel_Exception 71 * @throws PHPExcel_Reader_Exception 72 */ 73 static function ReadExcel($file='', $sheet = 0) { 74 //引用PHPexcel 类 75 require(APPPATH.'Libraries/PHPExcel.php'); 76 require(APPPATH.'Libraries/PHPExcel/IOFactory.php');//静态类 77 78 $file = iconv("utf-8", "gb2312", $file); //转码 79 80 if(empty($file) OR !file_exists($file)) { 81 die('file not exists!'); 82 } 83 $objRead = new \PHPExcel_Reader_Excel2007(); //建立reader对象 84 if(!$objRead->canRead($file)){ 85 $objRead = new \PHPExcel_Reader_Excel5(); 86 if(!$objRead->canRead($file)){ 87 die('No Excel!'); 88 } 89 } 90 $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'); 91 $obj = $objRead->load($file); //建立excel对象 92 $currSheet = $obj->getSheet($sheet); //获取指定的sheet表 93 $columnH = $currSheet->getHighestColumn(); //取得最大的列号 94 $columnCnt = array_search($columnH, $cellName); 95 $rowCnt = $currSheet->getHighestRow(); //获取总行数 96 $data = []; 97 for($_row=1; $_row<=$rowCnt; $_row++){ //读取内容 98 if($_row > 1) { 99 for ($_column = 0; $_column <= $columnCnt; $_column++) { 100 $cellId = $cellName[$_column] . $_row; 101 $cellValue = $currSheet->getCell($cellId)->getValue(); 102 if ($cellValue instanceof \PHPExcel_RichText) { //富文本转换字符串 103 $cellValue = $cellValue->__toString(); 104 } 105 106 $data[$_row - 2][$cellName[$_column]] = $cellValue; 107 } 108 } 109 } 110 return $data; 111 } 112 113 /** 114 * 数据导出 115 * @param array $title 标题行名称 116 * @param array $data 导出数据 117 * @param string $fileName 文件名 118 * @param string $savePath 保存路径 119 * @param $type 是否下载 false--保存 true--下载 120 * @return string 返回文件全路径 121 * @throws \PHPExcel_Exception 122 * @throws PHPExcel_Reader_Exception 123 */ 124 static function ExportExcel($title=array(), $data=array(), $savePath='./', $isDown=true){ 125 126 require_once (APPPATH.'Libraries/PHPExcel.php'); 127 require_once (APPPATH.'Libraries/PHPExcel/IOFactory.php');//静态类 128 $obj = new \PHPExcel(); 129 //横向单元格标识 130 $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ','BA','BB','BC','BD','BE','BF','BG','BH','BI','BJ','BK','BL','BM','BN','BO','BP','BQ','BR','BS','BT','BU','BV','BW','BX','BY','BZ','CA','CB','CC','CD','CE','CF','CG','CH','CI','CJ','CK','CL','CM','CN','CO','CP','CQ','CR','CS','CT','CU','CV','CW','CX','CY','CZ','DA','DB','DC','DD','DE','DF','DG','DH','DI','DJ','DK','DL','DM','DN','DO','DP','DQ','DR','DS','DT','DU','DV','DW','DX','DY','DZ'); 131 $obj->getActiveSheet(0)->setTitle(date('YmdHis')); //设置sheet名称 132 $_row = 1; //设置纵向单元格标识 133 134 if($title){ 135 $i = 0; 136 foreach($title AS $v){ //设置列标题 137 $obj->setActiveSheetIndex(0)->setCellValue($cellName[$i].$_row, $v); 138 $i++; 139 } 140 $_row++; 141 } 142 143 //填写数据 144 if($data){ 145 $i = 0; 146 foreach($data AS $_v){ 147 $j = 0; 148 foreach($_v AS $_cell){ 149 $obj->getActiveSheet(0)->setCellValue($cellName[$j] . ($i+$_row), $_cell); 150 $j++; 151 } 152 $i++; 153 } 154 } 155 156 //文件名处理 157 $fileName = uniqid(time(),true); 158 159 $objWrite = \IOFactory::createWriter($obj, 'Excel2007'); 160 //$objWrite = \IOFactory::createWriter($obj, 'Excel5'); 161 162 if($isDown){ //网页下载 163 header('pragma:public'); 164 header("Content-Disposition:attachment;filename=$fileName.xls"); 165 $objWrite->save('php://output');exit; 166 } 167 168 $_fileName = iconv("utf-8", "gb2312", $fileName); //转码 169 $_savePath = $savePath.$_fileName.'.xlsx'; 170 $objWrite->save($_savePath); 171 172 return $fileName.'.xlsx'; 173 } 174 175 /** 176 * object to array 177 * @param array $object 标题行名称 178 * @return array 返回文件全路径 179 */ 180 static function obj2array(&$object) { 181 $object = json_decode( json_encode( $object),true); 182 return $object; 183 } 184 185 /** 186 * 时间转换 187 * 188 * @param int $time 时间 189 * @return string 返回文本 190 * */ 191 static function TranTime($time) { 192 $C = date("m-d H:i",$time); 193 $T = date("Y-m-d H:i",$time); 194 $H = date("H:i",$time); 195 $D = date("d",$time); 196 $CD = date("d"); 197 $time = time() - $time; 198 if ($time < 60) { 199 $str = $time .' 秒前'; 200 } elseif ($time < 60 * 60) { 201 $min = floor($time/60); 202 $str = $min.' 分钟前'; 203 } elseif ($time < 60 * 60 * 24) { 204 $h = floor($time/(60*60)); 205 if($h > 12 && $CD > $D) $str = '昨天 '.$H; 206 else $str = $h.'小时前 '.$H; 207 } elseif ($time < 60 * 60 * 24 * 3) { 208 $d = floor($time/(60*60*24)); 209 if($d==1) $str = '昨天 '.$H; 210 else$str = '前天 '.$H; 211 } elseif ($time < 60 * 60 * 24 * 7) { 212 $d = floor($time/(60*60*24)); 213 $str = $d.' 天前 '.$H; 214 } elseif ($time < 60 * 60 * 24 * 30) { 215 $str = $C; 216 } else { 217 $str = $T; 218 } 219 return $str; 220 } 221 222 static function Qrcode(){ 223 require_once (APPPATH.'libraries/phpqrcode/qrlib.php');//静态类 224 $obj = new \QRtools(); 225 return $obj; 226 } 227 228 static function qrCode_($path,$code,$type_code){ 229 log_message('error','进入comm'.$path.','.$code.','.$type_code); 230 require_once (APPPATH.'libraries/phpqrcode/qrlib.php');//静态类 231 $img = new \QRcode(); 232 $value = $path; //二维码内容 233 $errorCorrectionLevel = 'Q';//容错级别 234 $matrixPointSize = 10;//生成图片大小 235 //生成二维码图片 236 $dir = iconv("UTF-8", "GBK", WRITEPATH.'/uploads/qrcode/'.$type_code);//文件夹路径 237 238 if (!file_exists($dir)){ 239 mkdir ($dir,0777,true); 240 } 241 242 $N = '/uploads/qrcode/'.$type_code.'/'.$code.'.png'; 243 $name = WRITEPATH.'/uploads/qrcode/'.$type_code.'/'.$code.'.png'; 244 $img->png($value, $name, $errorCorrectionLevel, $matrixPointSize, 2); 245 // return; 246 $logo = WRITEPATH.'/logo.png'; // logo在框架的public目录中 247 $QR = $name; //已经生成的原始二维码图 248 if ($logo !== FALSE) { 249 $QR = imagecreatefromstring(file_get_contents($QR)); 250 $logo = imagecreatefromstring(file_get_contents($logo)); 251 $QR_width = imagesx($QR);//二维码图片宽度 252 $QR_height = imagesy($QR);//二维码图片高度 253 $logo_width = imagesx($logo);//logo图片宽度 254 $logo_height = imagesy($logo);//logo图片高度 255 $logo_qr_width = $QR_width / 4; 256 $scale = $logo_width/$logo_qr_width; 257 $logo_qr_height = $logo_height/$scale; 258 $from_width = ($QR_width - $logo_qr_width) / 2; 259 //重新组合图片并调整大小 260 imagecopyresampled($QR, $logo, $from_width, $from_width, 0, 0, $logo_qr_width, 261 $logo_qr_height, $logo_width, $logo_height); 262 } 263 //输出图片 264 imagepng($QR, $name); 265 return [$N,$name]; 266 } 267 268 269 270 }
导出
控制器中直接引用exportExcel导出文件
1 log_message('error',$P['userId']."导出学生信息".count($data)."条"); 2 3 $Header = ['学籍号','学校代码','年级编号','班级编号','班级名称','姓名','性别','民族','出生日期','身份证号','学生来源(省)','学生来源(市)','通讯地址','人脸照片','入学照片','人脸照片绑定状态']; 4 5 $real_path = WRITEPATH.'uploads/school/export/'.date('Ymd').'/'; 6 7 if (!file_exists($real_path)) { 8 mkdir($real_path, 0777, true); 9 } 10 11 $fp = LibComm::ExportExcel($Header,LibComm::obj2array($data),$real_path,false); 12 $URL['url'] = BASEURL.'uploads/school/export/'.date('Ymd').'/'.$fp; 13 return $this->toJson(['data' => $URL]);
自己写了exportExcel2方法支持一次导出多sheet
导入
先前端上传文件，返回上传成功的url
1 // 文件上传 2 public function dofile(){ 3 $resp = ['code'=>false,'msg'=>'上传失败!','url'=>'','error'=>1]; 4 if($this->request->getMethod() === 'post'){ 5 $file = $this->request->getFile('file'); 6 if(!in_array($file->guessExtension(),$this->allowedfile)){ 7 return $this->outJson(['error'=>1,'url'=>'上传格式有误!']); 8 } 9 if($file->getSize()>$this->filesize){ 10 return $this->outJson(['error'=>1,'url'=>'文件上传过大!']); 11 } 12 if($file->isValid() && !$file->hasMoved()){ 13 $vm_path = 'uploads/files/'.$file->getExtension().'/'.date('Ymd').'/'; 14 $real_path = WRITEPATH.$vm_path; 15 $filename = \Api\Libraries\LibComp::guid().'.'.$file->getExtension(); 16 if (!file_exists($real_path)) { 17 mkdir($real_path, 0777, true); 18 } 19 $file->move($real_path, $filename); 20 $resp = ['code'=>true,'msg'=>'上传成功!','url'=>'/'.$vm_path.$filename,'error'=>0]; 21 }else { 22 exit($file->getErrorString().'('.$file->getError().')'); 23 } 24 } 25 return $this->outJson($resp); 26 }
然后调用readExcel读取表格数据，调用model中方法执行插入
1 $P['url']=str_replace('http://campus.yantianzz.com/','',$P['url']); 2 $P['url']=str_replace('http://localhost:9013/','',$P['url']); 3 4 5 $url=WRITEPATH.$P['url']; 6 7 if(!file_exists($url)){ 8 return $this->setError('文件不存在！'); 9 }else{ 10 $data= LibComm::ReadExcel($url,0); 11 $param['data']=$data; 12 $param['ext']=substr($url, strrpos($url, '.')+1); 13 $param['school_code']=$P['school_code']; 14 15 if(!in_array($param['ext'],['xls','xlsx','csv'])){ 16 return $this->setError('文件格式错误！'); 17 } 18 log_message('error','导入学生数据'.json_encode($param,JSON_UNESCAPED_UNICODE)); 19 $db = new \Api\Models\Campus\Student(); 20 $res= $db->to_import_students($param); 21 return $this->toJson($res); 22 }
插入方法如下
1 class Student extends \Api\Models\BaseModel 2 { 3 protected $table = 'sc_students'; 4 5 protected $primaryKey = 'id'; 6 7 protected $allowedFields = ['name', 'grade_code', 'class_code', 'class_name','student_code','school_code', 'nation', 'birthdate','source','address','sex','idnum','province','city','province_code','city_code','nfc_card_no', 'nfc_card_entrytime','photo_url','createtime']; 8 9 protected $returnType = 'Api\Entities\Forms'; 10 11 public function to_import_students($param){ 12 $data = $param['data']; 13 log_message('error',json_encode($param)); 14 $total = count($data); $eff = 0; $wrong = 0;$failedname=''; 15 foreach($data as $item){ 16 if(!is_null($item['A'])){ 17 $count++; 18 $argc = [ 19 'name'=>$item['D'], 20 'school_code'=>$param['school_code'], 21 'grade_code'=>$item['A'], 22 'class_code'=>$item['B'], 23 'class_name'=>intval(substr($item['B'],-2)).'班', 24 'student_code'=>$item['C'], 25 'nation'=>$item['E'], 26 'birthdate'=>$item['G'], 27 'sex'=>$item['F'], 28 'photo_url'=>$item['L'], 29 'idnum'=>$item['H'], 30 'province'=>$item['I'], 31 'address'=>$item['K'], 32 'city'=>$item['J'] 33 ]; 34 if(strlen($argc['student_code'])!=19){ 35 $wrong++; 36 $failedname.=$argc['name'].','; 37 break; 38 } 39 40 if(!in_array($argc['grade_code'],[11,12,13,14,15,16,21,22,23,31,32,33,41,42,43,44])){ 41 $wrong++; 42 $failedname.=$argc['name'].','; 43 break; 44 } 45 46 //学生导入模板列:学号，学校代码，年级代码，班级代码，班级名称，姓名，性别，民族，生日，身份证号码，省，市 47 $argc['birthdate']=date("Y/m/d",\PHPExcel_Shared_Date::ExcelToPHP($argc['birthdate'])); 48 $argc['source']=$argc['province'].$argc['city']; 49 50 $s_db=new \Api\Models\Basic\Studsource(); 51 $s_data=$s_db->select('parent_code,code')->where('name',$argc['city'])->orWhere('parent_name',$argc['province'])->first(); 52 $argc['province_code']=$s_data->parent_code; 53 $argc['city_code']=$s_data->code; 54 55 $id=$this->select('id') 56 ->where('school_code',$argc['school_code']) 57 ->where('student_code',$argc['student_code']) 58 ->first(); 59 if($id->id){ 60 $argc['id']=$id->id; 61 } 62 $entity = new \Api\Entities\Forms($argc); 63 $resp=$this->save($entity); 64 65 if($resp['retCode'] == 0){ 66 $eff++; 67 }else{ 68 $wrong++; 69 } 70 // $resp = $this->callPro('sp_memberImport',$argc); 71 } 72 } 73 if($wrong>0){ 74 return ['retCode'=>true,'retMsg'=>'添加总条数:'.$count . ',成功导入:'.$eff.', 失败条数:'.$wrong.'. 导入失败学生名单('.$failedname.')']; 75 }else{ 76 return ['retCode'=>true,'retMsg'=>'添加总条数:'.$count . ',成功导入:'.$eff.', 失败条数:'.$wrong]; 77 } 78 79 80 }