详解thinkphp实现excel数据的导入导出(附完整案例)
程序员文章站
2024-04-01 18:25:04
实现步骤:
一:在http://phpexcel.codeplex.com/下载最新phpexcel放到vendor下,注意位置:thinkphp\extend\ve...
实现步骤:
一:在http://phpexcel.codeplex.com/下载最新phpexcel放到vendor下,注意位置:thinkphp\extend\vendor\phpexcel\phpexcel.php。
二:导出excel代码实现
/**方法**/ function index(){ $this->display(); } public function exportexcel($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 = new phpexcel(); $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 */ function expuser(){//导出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'); $xlsdata = $xlsmodel->field('id,truename,sex,res_id,sp_id,class,year,city,company,zhicheng,zhiwu,jibie,tel,qq,email,honor,remark')->select(); foreach ($xlsdata as $k => $v) { $xlsdata[$k]['sex']=$v['sex']==1?'男':'女'; } $this->exportexcel($xlsname,$xlscell,$xlsdata); }
第三:导入excel数据代码
function impuser(){ if (!empty($_files)) { import("@.org.uploadfile"); $config=array( 'allowexts'=>array('xlsx','xls'), 'savepath'=>'./public/upload/', 'saverule'=>'time', ); $upload = new uploadfile($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><a href="{:u('index/expuser')}" >导出数据并生成excel</a></p><br/> <form action="{:u('index/impuser')}" method="post" enctype="multipart/form-data"> <input type="file" name="import"/> <input type="hidden" name="table" value="tablename"/> <input type="submit" value="导入"/> </form> </body> </html>
最后下载:
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持。
推荐阅读
-
详解thinkphp实现excel数据的导入导出(附完整案例)
-
详解thinkphp实现excel数据的导入导出(附完整案例)
-
ThinkPHP使用PHPExcel实现Excel数据导入导出完整实例_PHP
-
ThinkPHP使用PHPExcel实现Excel数据导入导出完整实例
-
ThinkPHP 3.3.2使用PHPExcel实现Excel数据导入导出完整实例
-
ThinkPHP使用PHPExcel实现Excel数据导入导出完整实例
-
ThinkPHP使用PHPExcel实现Excel数据导入导出完整实例
-
ThinkPHP使用PHPExcel实现Excel数据导入导出完整实例_PHP
-
thinkphp实现excel数据的导入导出(修改适用3.2.3)
-
thinkphp实现excel数据的导入导出(修改适用3.2.3)