TP5.0.2有关导入导出excel表格

博主之前做勤工助学系统的时候的导入导出的模板功能

先总结下几个问题与难点:
1、execl表格类型大小的判定
2、加载模板后的每个表格的属性（如A1、B1等）一定要确认
3、与数据库的数据交互一定要添加事务处理
4、最后就是一些基础的配置了
查考模板
勤工助学考勤表
学生积分统计表

首先引入库类

use PHPExcel_IOFactory;
use PHPExcel;

导入视图:

  
考勤数据上传

导入方法:

           public function importExcel(){vendor ( "PHPExcel.PHPExcel" ); // 获取PHPExcel类$excel = new \PHPExcel ();$file = request ()->file ( 'file' );if(empty($file)){$this->error("文件不能为空","index/bmbb/import"); }$info = $file->validate ( [ 'size' => 15678,'ext' => 'xlsx,xls,csv' ] )->move ( ROOT_PATH . 'public' . DS . 'excel' );if ($info) {$exclePath = $info->getSaveName (); // 获取文件名$file_name = ROOT_PATH . 'public' . DS . 'excel' . DS . $exclePath; // 上传文件的地址$objReader = \PHPExcel_IOFactory::createReader ( 'Excel2007' );$obj_PHPExcel = $objReader->load ( $file_name, $encode = 'utf-8' ); // 加载文件内容,编码utf-8// echo "
";$excel_array = $obj_PHPExcel->getsheet ( 0 )->toArray (); // 转换为数组格式$arr = reset ( $excel_array );//获得标题$yeararr=explode("年",trim($arr[0]));  //切割字符串获得日期$moutharr=explode("月",substr(trim($arr[0]), 7));$day=explode("日",substr($arr[0], 11));if((float)$moutharr[0]<10){$moutharr[0]="0".$moutharr[0];}$date=$yeararr[0]."-".$moutharr[0];  //年月for ($i=0;$i<3;$i++){ //删除不需要的数组unset($excel_array[$i]);}$arr=array_values($excel_array);//重新建立索引$mesarr = reset ( $excel_array );//拿第一条数组  下表4开始是日期1，34为31unset ( $arr [0] );$arr=array_values($arr);//判断要导入的文件if(input('sub')=="导入考勤数据"){$trans_result = false;try{Db::startTrans();foreach ($arr as $k =>$v){$res["status"]="审核通过";for ($i=4;$i<35;$i++){if($v[$i]!=null){$res["studentId"]=$v[2];$res["worktime"]=(float)$v[$i];$res["date"]=$date."-".$v[$i]." ".date("H:i:s");$res["admin"]=$v[38];$list=Db::table('sign')->insert($res);}}}}catch (\Exception $e) {$trans_result = false;//判断中间是否有错误	}}else if(input('sub')=="导入积分数据"){$date=$yeararr[0]."-".$moutharr[0]."-".$day[0];//日$trans_result = false;try{Db::startTrans();foreach ($arr as $k =>$v){if($v[1]!=null){$data["xh"]=$v[1];//取出学号$data["xzjf"]=$v[2];//取出新增积分$data["bz"]=$v[3];//取出备注$data['shij']=$date;$list=Db::table('xsjfjlb')->insert($data);}}}catch (\Exception $e) {$trans_result = false;//判断中间是否有错误}}if ($trans_result === false) {Db::rollback();$this->error('导入失败，格式错误','index/Bmbb/import');} else {Db::commit();unset($info);//一定要unset之后才能进行删除操作，否则请求会被拒绝$this->success('导入成功','index/Bmbb/import');} }}
 
导出模板视图:
 
 
 
 
方法函數:
 
	public function exportAll() // 下载报表模板
{vendor ( "PHPExcel.PHPExcel.PHPExcel" );vendor ( "PHPExcel.PHPExcel.Writer.IWriter" );vendor ( "PHPExcel.PHPExcel.Writer.Abstract" );vendor ( "PHPExcel.PHPExcel.Writer.Excel5" );vendor ( "PHPExcel.PHPExcel.Writer.Excel2007" );vendor ( "PHPExcel.PHPExcel.IOFactory" );// 判断要下载的模板if (input ( 'sub' ) == "考勤汇总表下载") {$temPath = iconv ( 'utf-8', 'gb2312', 'temp/1.xlsx' ); // 以public/index.php为参照算路径$newname = "勤工助学模板.xlsx";$phpexcel = PHPExcel_IOFactory::createReader ( "Excel2007" )->load ( $temPath );$year = input ( 'year' );$month = input ( 'month' );$phpexcel->setActiveSheetIndex ( 0 )->setCellValue ( 'A1', $year . "年" . $month . "月勤工助学固定岗位考核汇总" ); // 标题头部$phpexcel->setActiveSheetIndex ( 0 )->setCellValue ( 'E3', "日期" ); // 标题头部$phpexcel->setActiveSheetIndex ( 0 )->setCellValue ( 'A3', "部门名称(盖章）：" . input ( 'xueyuan' ) ); // 标题头部$data ['status'] = "审核通过";$data ['xueyuan'] = input ( 'xueyuan' );$list = db ( 'sign' )->where ( $data )->join ( "zhb", "zhb.zh=sign.studentId" )->join ( "xsxx", "sign.studentId=xsxx.xh" )->field ( 'studentId,zhb.xm,jhzh' )->group ( 'studentId,zhb.xm,jhzh' )->select ();if (floatval ( $month ) < 10) {$month = "0" . $month;}$n = 5; // A5开始$index = 1; // 序号$arr = array ('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' );$arr = explode ( ",", $arr [0] ); // 1 到 31foreach ( $list as $k => $v ) {// 单次循环出学生信息，这里数据库设计有问题$phpexcel->setActiveSheetIndex ( 0 )->setCellValue ( 'A' . $n, $index ); // 序号$phpexcel->setActiveSheetIndex ( 0 )->setCellValue ( 'B' . $n, $v ['xm'] ); // 姓名$phpexcel->setActiveSheetIndex ( 0 )->setCellValue ( 'C' . $n, $v ['studentId'] ); // 学号$phpexcel->setActiveSheetIndex ( 0 )->setCellValue ( 'D' . $n, $v ['jhzh'] ); // 建行银行// 内嵌循环$data2 ['studentId'] = $v ['studentId'];$data2 ['date'] = [ 'like',$year . '-' . $month . '%' ];$data2 ['status'] = "审核通过";$list2 = db ( 'sign' )->where ( $data2 )->join ( 'gwb', 'gwb.xh=sign.studentId' )->join ( 'mathmoney', 'mathmoney.gwlx=gwb.gwlx' )->field ( 'studentId,worktime,date,mathmoney.gwlx,mathmoney.hour,mathmoney.max' )->select ();foreach ( $list2 as $k2 => $v2 ) {$str = explode ( "-", $v2 ['date'] );$str = explode ( ' ', $str [2] ); // $str[0]为当月日期$phpexcel->setActiveSheetIndex ( 0 )->setCellValue ( $arr [$str [0] - 1] . $n, $v2 ['worktime'] ); // 工作当日$sum += $v2 ['worktime'];}$phpexcel->setActiveSheetIndex ( 0 )->setCellValue ( "AJ" . $n, $sum ); // 当月工作总时长// dump($sum);if ($v2 ['gwlx'] == '临时性轮岗') {$money = $sum * $v2 ['hour'];if ($money > $v2 ['max']) {$money = $v2 ['max'];}} else {$money = $sum * $v2 ['hour'];if ($money > $v2 ['max']) { // 超标$money = $v2 ['max'];} else {if ($sum < 20) {$money = $money * 0.9; // 未达到优}}}$phpexcel->setActiveSheetIndex ( 0 )->setCellValue ( "AK" . $n, $money ); // 当月工作总时长if ($sum >= 20) {$phpexcel->setActiveSheetIndex ( 0 )->setCellValue ( "AL" . $n, '优' ); // 当月工作表现} else {$phpexcel->setActiveSheetIndex ( 0 )->setCellValue ( "AL" . $n, '良' ); // 当月工作表现}$sum = 0;$n ++;$index ++;}} else if (input ( 'sub' ) == "学生积分汇总表下载") {$temPath = iconv ( 'utf-8', 'gb2312', 'temp/2.xlsx' ); // 以public/index.php为参照算路径$newname = "学生积分模板.xlsx";$phpexcel = PHPExcel_IOFactory::createReader ( "Excel2007" )->load ( $temPath );$phpexcel->setActiveSheetIndex ( 0 )->setCellValue ( 'A3', "部门名称(盖章）：" . input ( 'xueyuan' ) );$where ['xueyuan'] = input ( 'xueyuan' );$jflist = db ( "xsjfjlb" )->// ->where($con)join ( 'xsxx', 'xsxx.xh=xsjfjlb.xh' )->field ( 'xsxx.xh,SUM(xzjf) as jf,bz,xueyuan' )->where ( $where )->group ( 'xh' )->select ();$num = 1;$p = 5;foreach ( $jflist as $kjf => $k ) {$phpexcel->setActiveSheetIndex ( 0 )->setCellValue ( 'A' . $p, $num );$phpexcel->setActiveSheetIndex ( 0 )->setCellValue ( 'B' . $p, $k ['xh'] );$phpexcel->setActiveSheetIndex ( 0 )->setCellValue ( 'C' . $p, $k ['jf'] );$phpexcel->setActiveSheetIndex ( 0 )->setCellValue ( 'D' . $p, $k ['bz'] );$num ++;$p ++;}}// $temPath = "temp/勤工助学考勤表汇总表.xls";//以public/index.php为参照算路径// 检查文件路径if (! file_exists ( $temPath )) {$this->error ( '模板不存在' );return;}// 加载模板$filename = iconv ( 'utf-8', 'gb2312', $newname );$objWriter = new \PHPExcel_Writer_Excel2007 ( $phpexcel );$this->outPut ( $filename, "Excel2007" );$objWriter->save ( 'php://output' );}
                        

                        

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！