thinkphp实现excel数据的导入导出的实例代码
发布:smiling 来源: PHP粉丝网 添加日期:2018-06-01 10:02:25 浏览: 评论:0
实现步骤:
一:在http://phpExcel.codeplex.com/下载最新PHPExcel放到Vendor下,注意位置:ThinkPHP\Extend\Vendor\PHPExcel\PHPExcel.php。
二:导出excel代码实现
- /**方法**/
- functionindex(){
- $this->display();
- }
- publicfunctionexportExcel($expTitle,$expCellName,$expTableData){
- $xlsTitle= iconv('utf-8','gb2312',$expTitle);//文件名称
- $fileName=$_SESSION['account'].date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
- $cellNum=count($expCellName);
- $dataNum=count($expTableData);
- vendor("PHPExcel.PHPExcel");
- $objPHPExcel=newPHPExcel();
- $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');
- $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
- // $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.' Export time:'.date('Y-m-d H:i:s'));
- for($i=0;$i<$cellNum;$i++){
- $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2',$expCellName[$i][1]);
- }
- // Miscellaneous glyphs, UTF-8
- for($i=0;$i<$dataNum;$i++){
- for($j=0;$j<$cellNum;$j++){
- $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3),$expTableData[$i][$expCellName[$j][0]]);
- }
- }
- header('pragma:public');
- header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
- header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
- $objWriter= PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5');
- $objWriter->save('php://output');
- exit;
- }
- /**
- *
- * 导出Excel
- */
- functionexpUser(){//导出Excel
- $xlsName="User";
- $xlsCell=array(
- array('id','账号序列'),
- array('truename','名字'),
- array('sex','性别'),
- array('res_id','院系'),
- array('sp_id','专业'),
- array('class','班级'),
- array('year','毕业时间'),
- array('city','所在地'),
- array('company','单位'),
- array('zhicheng','职称'),
- array('zhiwu','职务'),
- array('jibie','级别'),
- array('tel','电话'),
- array('qq','qq'),
- array('email','邮箱'),
- array('honor','荣誉'),
- array('remark','备注')
- );
- $xlsModel= M('Member');
- //phpfensi.com
- $xlsData=$xlsModel->Field('id,truename,sex,res_id,sp_id,class,year,city,company,zhicheng,zhiwu,jibie,tel,qq,email,honor,remark')->select();
- foreach($xlsDataas$k=>$v)
- {
- $xlsData[$k]['sex']=$v['sex']==1?'男':'女';
- }
- $this->exportExcel($xlsName,$xlsCell,$xlsData);
- }
第三:导入excel数据代码
- functionimpUser(){
- if(!emptyempty($_FILES)) {
- import("@.ORG.UploadFile");
- $config=array(
- 'allowExts'=>array('xlsx','xls'),
- 'savePath'=>'./Public/upload/',
- 'saveRule'=>'time',
- );
- $upload=newUploadFile($config);
- if(!$upload->upload()) {
- $this->error($upload->getErrorMsg());
- }else{
- $info=$upload->getUploadFileInfo();
- }
- vendor("PHPExcel.PHPExcel");
- $file_name=$info[0]['savepath'].$info[0]['savename'];
- $objReader= PHPExcel_IOFactory::createReader('Excel5');
- $objPHPExcel=$objReader->load($file_name,$encode='utf-8');
- $sheet=$objPHPExcel->getSheet(0);
- $highestRow=$sheet->getHighestRow();// 取得总行数
- $highestColumn=$sheet->getHighestColumn();// 取得总列数
- for($i=3;$i<=$highestRow;$i++)
- {
- $data['account']=$data['truename'] =$objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue();
- $sex=$objPHPExcel->getActiveSheet()->getCell("C".$i)->getValue();
- // $data['res_id'] = $objPHPExcel->getActiveSheet()->getCell("D".$i)->getValue();
- $data['class'] =$objPHPExcel->getActiveSheet()->getCell("E".$i)->getValue();
- $data['year'] =$objPHPExcel->getActiveSheet()->getCell("F".$i)->getValue();
- $data['city']=$objPHPExcel->getActiveSheet()->getCell("G".$i)->getValue();
- $data['company']=$objPHPExcel->getActiveSheet()->getCell("H".$i)->getValue();
- $data['zhicheng']=$objPHPExcel->getActiveSheet()->getCell("I".$i)->getValue();
- $data['zhiwu']=$objPHPExcel->getActiveSheet()->getCell("J".$i)->getValue();
- $data['jibie']=$objPHPExcel->getActiveSheet()->getCell("K".$i)->getValue();
- $data['honor']=$objPHPExcel->getActiveSheet()->getCell("L".$i)->getValue();
- $data['tel']=$objPHPExcel->getActiveSheet()->getCell("M".$i)->getValue();
- $data['qq']=$objPHPExcel->getActiveSheet()->getCell("N".$i)->getValue();
- $data['email']=$objPHPExcel->getActiveSheet()->getCell("O".$i)->getValue();
- $data['remark']=$objPHPExcel->getActiveSheet()->getCell("P".$i)->getValue();
- $data['sex']=$sex=='男'?1:0;
- $data['res_id'] =1;
- $data['last_login_time']=0;
- $data['create_time']=$data['last_login_ip']=$_SERVER['REMOTE_ADDR'];
- $data['login_count']=0;
- $data['join']=0;
- $data['avatar']='';
- $data['password']=md5('123456');
- M('Member')->add($data);
- }
- $this->success('导入成功!');
- }else
- {
- $this->error("请选择上传的文件");
- }
- }
四、模板代码
- <html>
- <head>
- </head>
- <body>
- <P><ahrefahref="{:U('Index/expUser')}">导出数据并生成excel</a></P><br/>
- <formactionformaction="{:U('Index/impUser')}"method="post"enctype="multipart/form-data">
- <inputtypeinputtype="file"name="import"/>
- <inputtypeinputtype="hidden"name="table"value="tablename"/>
- <inputtypeinputtype="submit"value="导入"/>
- </form>
- </body>
- </html>
Tags: 实例 代码 数据
相关文章
- ·ThinkPHP之实例化模型(2014-04-12)
- ·ThinkPHP实例化模型(2014-04-12)
- ·ThinkPHP创建应用的一般开发流程及实例(2014-04-19)
- ·ThinkPHP实例化模型的四种方法(2014-04-19)
- ·ThinkPHP Session 使用详解及实例(2014-04-21)
- ·ThinkPHP 验证码详解及实例(2014-04-21)
- ·ThinkPHP 分页详解及分页应用实例(2014-04-21)
- ·ThinkPHP分页实例(2014-11-20)
- ·ThinkPHP 自动验证实例(2015-09-30)
- ·thinkphp5 URL和路由的功能详解与实例(2018-06-06)
- ·thinkphp标签实现bootsrtap轮播carousel实例代码(2018-08-02)
- ·Thinkphp实例化对象的四种方法比较(2018-09-14)
- ·ThinkPHP令牌验证实例(2021-02-21)
- ·ThinkPHP实例化模型的四种方法概述(2021-04-08)
- ·浅谈thinkphp的实例化模型(2021-05-05)
- ·PHP 间断执行某代码段(2014-04-11)
推荐文章
热门文章
最新评论文章
- 写给考虑创业的年轻程序员(10)
- PHP新手上路(一)(7)
- 惹恼程序员的十件事(5)
- PHP邮件发送例子,已测试成功(5)
- 致初学者:PHP比ASP优秀的七个理由(4)
- PHP会被淘汰吗?(4)
- PHP新手上路(四)(4)
- 如何去学习PHP?(2)
- 简单入门级php分页代码(2)
- php中邮箱email 电话等格式的验证(2)