实现步骤：
一：在http://phpexcel.codeplex.com/下载最新phpexcel放到vendor下，注意位置：thinkphpextendvendorphpexcelphpexcel.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>
最后下载：demo下载
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持硕编程。
【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:254677821)！