详解thinkphp实现excel数据的导入导出（附完整案例）

概述本篇文章主要介绍了thinkphp实现excel数据的导入导出，具有一定的参考价值，感兴趣的小伙伴们可以参考一下。

实现步骤：

一：在http://PHPexcel.codeplex.com/下载最新PHPExcel放到vendor下，注意位置：ThinkPHP\Extend\vendor\PHPExcel\PHPExcel.PHP。

二：导出excel代码实现

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");

$obj<a href="https://www.jb51.cc/tag/PHP/" target="_blank" >PHP</a>Excel = new <a href="https://www.jb51.cc/tag/PHP/" target="_blank" >PHP</a>Excel();$cell<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a> = 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');$obj<a href="https://www.jb51.cc/tag/PHP/" target="_blank" >PHP</a>Excel->getActiveSheet(0)->mergeCells('A1:'.$cell<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>[$cellNum-1].'1');//合并<a href="https://m.jb51.cc/tag/danyuange/" target="_blank" >单元格</a>// $obj<a href="https://www.jb51.cc/tag/PHP/" target="_blank" >PHP</a>Excel->setActiveSheetIndex(0)->setCellValue('A1',$exp<a href="https://m.jb51.cc/tag/Title/" target="_blank" >Title</a>.' Export time:'.date('Y-m-d H:i:s')); for($i=0;$i<$cellNum;$i++){  $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cell<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>[$i].'2',$e<a href="https://www.jb51.cc/tag/xpc/" target="_blank" >xpc</a>ell<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>[$i][1]); }  // Miscellaneous glyphs,UTF-8  for($i=0;$i<$d<a href="https://www.jb51.cc/tag/atan/" target="_blank" >atan</a>um;$i++){ for($j=0;$j<$cellNum;$j++){  $objPHPExcel->getActiveSheet(0)->setCellValue($cell<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>[$j].($i+3),$exp<a href="https://m.jb51.cc/tag/table/" target="_blank" >table</a>Data[$i][$e<a href="https://www.jb51.cc/tag/xpc/" target="_blank" >xpc</a>ell<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>[$j][0]]); }       } <a href="https://m.jb51.cc/tag/header/" target="_blank" >header</a>('pragma:pub<a href="https://m.jb51.cc/tag/li/" target="_blank" >li</a>c');<a href="https://m.jb51.cc/tag/header/" target="_blank" >header</a>('Content-type:app<a href="https://m.jb51.cc/tag/li/" target="_blank" >li</a>cation/vnd.ms-excel;charset=utf-8;<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>="'.$xls<a href="https://m.jb51.cc/tag/Title/" target="_blank" >Title</a>.'.xls"');<a href="https://m.jb51.cc/tag/header/" target="_blank" >header</a>("Content-<a href="https://www.jb51.cc/tag/dis/" target="_blank" >dis</a><a href="https://m.jb51.cc/tag/position/" target="_blank" >position</a>:attachment;<a href="https://m.jb51.cc/tag/file/" target="_blank" >file</a><a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>=$<a href="https://m.jb51.cc/tag/file/" target="_blank" >file</a><a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>.xls");//attachment<a href="https://m.jb51.cc/tag/xinchuangkou/" target="_blank" >新窗口</a>打印in<a href="https://m.jb51.cc/tag/li/" target="_blank" >li</a>ne本窗口打印$objWriter = <a href="https://www.jb51.cc/tag/PHP/" target="_blank" >PHP</a>Excel_IOFactory::createWriter($obj<a href="https://www.jb51.cc/tag/PHP/" target="_blank" >PHP</a>Excel,'Excel5'); $objWriter->save('<a href="https://www.jb51.cc/tag/PHP/" target="_blank" >PHP</a>://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->F<a href="https://m.jb51.cc/tag/IE/" target="_blank" >IE</a>ld('<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a>,true<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>,sex,res_<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a>,sp_<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a>,class,year,city,company,zhicheng,zhiwu,<a href="https://www.jb51.cc/tag/jib/" target="_blank" >jib</a><a href="https://m.jb51.cc/tag/IE/" target="_blank" >IE</a>,tel,qq,email,ho<a href="https://www.jb51.cc/tag/nor/" target="_blank" >nor</a>,remark')->select();foreach ($xlsData as $k => $v){  $xlsData[$k]['sex']=$v['sex']==1?'男':'女';}$this->exportExcel($xls<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>,$xlsCell,$xlsData);

}

第三：导入excel数据代码

array('xlsx','xls'),'savePath'=>'./Public/upload/','saveRule'=>'time',); $upload = new Uploadfile($config); if (!$upload->upload()) { $this->error($upload->getErrorMsg()); } else { $info = $upload->getUploadfileInfo();

  }  <a href="https://www.jb51.cc/tag/vend/" target="_blank" >vend</a>or("<a href="https://www.jb51.cc/tag/PHP/" target="_blank" >PHP</a>Excel.<a href="https://www.jb51.cc/tag/PHP/" target="_blank" >PHP</a>Excel");    $<a href="https://m.jb51.cc/tag/file/" target="_blank" >file</a>_<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>=$info[0]['savepath'].$info[0]['save<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>'];    $objReader = <a href="https://www.jb51.cc/tag/PHP/" target="_blank" >PHP</a>Excel_IOFactory::createReader('Excel5');    $obj<a href="https://www.jb51.cc/tag/PHP/" target="_blank" >PHP</a>Excel = $objReader->load($<a href="https://m.jb51.cc/tag/file/" target="_blank" >file</a>_<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>,$encode='utf-8');    $sheet = $obj<a href="https://www.jb51.cc/tag/PHP/" target="_blank" >PHP</a>Excel->getSheet(0);    $highestRow = $sheet->getHighestRow(); // 取得总行数    $highestC<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>umn = $sheet->getHighestC<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>umn(); // 取得总列数    for($i=3;$i<=$highestRow;$i++)    {        $data['account']= $data['truename'] = $objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue();       $sex = $obj<a href="https://www.jb51.cc/tag/PHP/" target="_blank" >PHP</a>Excel->getActiveSheet()->getCell("C".$i)->getValue();      // $data['res_<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a>']  = $obj<a href="https://www.jb51.cc/tag/PHP/" target="_blank" >PHP</a>Excel->getActiveSheet()->getCell("D".$i)->getValue();      $data['class'] = $obj<a href="https://www.jb51.cc/tag/PHP/" target="_blank" >PHP</a>Excel->getActiveSheet()->getCell("E".$i)->getValue();      $data['year'] = $obj<a href="https://www.jb51.cc/tag/PHP/" target="_blank" >PHP</a>Excel->getActiveSheet()->getCell("F".$i)->getValue();      $data['city']= $obj<a href="https://www.jb51.cc/tag/PHP/" target="_blank" >PHP</a>Excel->getActiveSheet()->getCell("G".$i)->getValue();      $data['company']= $obj<a href="https://www.jb51.cc/tag/PHP/" target="_blank" >PHP</a>Excel->getActiveSheet()->getCell("H".$i)->getValue();      $data['zhicheng']= $obj<a href="https://www.jb51.cc/tag/PHP/" target="_blank" >PHP</a>Excel->getActiveSheet()->getCell("I".$i)->getValue();      $data['zhiwu']= $obj<a href="https://www.jb51.cc/tag/PHP/" target="_blank" >PHP</a>Excel->getActiveSheet()->getCell("J".$i)->getValue();      $data['<a href="https://www.jb51.cc/tag/jib/" target="_blank" >jib</a><a href="https://m.jb51.cc/tag/IE/" target="_blank" >IE</a>']= $obj<a href="https://www.jb51.cc/tag/PHP/" target="_blank" >PHP</a>Excel->getActiveSheet()->getCell("K".$i)->getValue();      $data['ho<a href="https://www.jb51.cc/tag/nor/" target="_blank" >nor</a>']= $obj<a href="https://www.jb51.cc/tag/PHP/" target="_blank" >PHP</a>Excel->getActiveSheet()->getCell("L".$i)->getValue();      $data['tel']= $obj<a href="https://www.jb51.cc/tag/PHP/" target="_blank" >PHP</a>Excel->getActiveSheet()->getCell("M".$i)->getValue();      $data['qq']= $obj<a href="https://www.jb51.cc/tag/PHP/" target="_blank" >PHP</a>Excel->getActiveSheet()->getCell("N".$i)->getValue();      $data['email']= $obj<a href="https://www.jb51.cc/tag/PHP/" target="_blank" >PHP</a>Excel->getActiveSheet()->getCell("O".$i)->getValue();      $data['remark']= $obj<a href="https://www.jb51.cc/tag/PHP/" target="_blank" >PHP</a>Excel->getActiveSheet()->getCell("P".$i)->getValue();      $data['sex']=$sex=='男'?1:0;      $data['res_<a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a>'] =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("请<a href="https://m.jb51.cc/tag/xuanze/" target="_blank" >选择</a><a href="https://www.jb51.cc/tag/shangchuan/" target="_blank" >上传</a>的<a href="https://www.jb51.cc/tag/wenjian/" target="_blank" >文件</a>");  }  

}

四、模板代码

导出数据并生成excel

最后下载：href="http://xiazai.jb51.cc/201612/yuanma/excel_jb51.zip">demo下载

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持内存溢出。

总结

以上是内存溢出为你收集整理的详解thinkphp实现excel数据的导入导出（附完整案例）全部内容，希望文章能够帮你解决详解thinkphp实现excel数据的导入导出（附完整案例）所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。