thinkphp实现excel数据的导入导出(附完整案例)

实现方法步骤一:
thinphp3.2 excel 使用phpexcel导入导出
下载压缩包(或者大家也可以到phpexcel下载最新的)解压到 ThinkPHP\Library\Vendor\
然后在项目根目录新建目录uploads
一下代码导入导出 我用到的是mongodb大家可以根据需求修改下! 

  1. <?php
  2. namespace Home\Controller;
  3. use Think\Controller;
  4. class ExcelController extends Controller
  5. {
  6.     // 导出数据到excel表
  7.     public function index()
  8.     { // excel表头
  9.   
  10.         $headArr[] = ‘_id’;
  11.         $headArr[] = ‘id’;
  12.         $headArr[] = ‘title’;
  13.         $headArr[] = ‘content’;
  14.         $headArr[] = ‘time’;
  15.         // 数据
  16.         $data = D(‘NewsMongo’)->limit(‘100’)->select();
  17.         $filename = “goods_list”;
  18.         $this->getExcel($filename, $headArr, $data); // $filename excel名称 $headArr excel表头
  19.     }
  20.     public function import()
  21.     {
  22.         $this->display();
  23.     }
  24.     public function upload()
  25.     {
  26.         header(“Content-Type:text/html;charset=utf-8”);
  27.         $upload = new \Think\Upload(); // 实例化上传类
  28.         $upload->maxSize = 3145728; // 设置附件上传大小
  29.         $upload->exts = array(
  30.             ‘xls’,
  31.             ‘xlsx’
  32.         ); // 设置附件上传类
  33.         $upload->savePath = ‘/’; // 设置附件上传目录
  34.                                  // 上传文件
  35.         $info = $upload->uploadOne($_FILES[‘excelData’]);
  36.         $filename = ‘./Uploads’ . $info[‘savepath’] . $info[‘savename’];
  37.         $exts = $info[‘ext’];
  38.         // print_r($info);exit;
  39.         if (! $info) { // 上传错误提示错误信息
  40.             $this->error($upload->getError());
  41.         } else { // 上传成功
  42.             $this->goods_import($filename, $exts);
  43.             $this->success(‘导入成功’);
  44.         }
  45.     }
  46.     public function save_import($data)
  47.     {
  48.         foreach ($data as $key => $val) {
  49.             if ($key > 2) {
  50.                 $datas[‘id’] = $val[‘A’];
  51.                 $datas[’tile’] = ‘title’;
  52.                 $datas[‘content’] = $val[‘D’];
  53.                 $datas[‘time’] = date(‘y-m-d h:i:s’, time()); 
  54.                 D(‘NewsMongo’)->add($datas);
  55.             }
  56.         }
  57.     }
  58.     private function getExcel($fileName, $headArr, $data)
  59.     {
  60.         vendor(‘PHPExcel’);
  61.         $date = date(“Y_m_d”, time());
  62.         $fileName .= “_{$date}.xls”;
  63.         $objPHPExcel = new \PHPExcel();
  64.         $objProps = $objPHPExcel->getProperties();
  65.         // 设置表头
  66.         $key = ord(“A”);
  67.         foreach ($headArr as $v) {
  68.             $colum = chr($key);
  69.             $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . ‘1’, $v);
  70.             $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . ‘1’, $v);
  71.             $key += 1;
  72.         }
  73.         $column = 2;
  74.         $objActSheet = $objPHPExcel->getActiveSheet();
  75.         // print_r($data);exit;
  76.         foreach ($data as $key => $rows) { // 行写入
  77.             $span = ord(“A”);
  78.             foreach ($rows as $keyName => $value) { // 列写入
  79.                 $j = chr($span);
  80.                 $objActSheet->setCellValue($j . $column, $value);
  81.                 $span ++;
  82.             }
  83.             $column ++;
  84.         }
  85.         
  86.         $fileName = iconv(“utf-8”, “gb2312”, $fileName);
  87.         // 重命名表
  88.         // 设置活动单指数到第一个表,所以Excel打开这是第一个表
  89.         $objPHPExcel->setActiveSheetIndex(0);
  90.         header(‘Content-Type: application/vnd.ms-excel’);
  91.         header(“Content-Disposition: attachment;filename=\”$fileName\””);
  92.         header(‘Cache-Control: max-age=0’);
  93.         
  94.         $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel5’);
  95.         $objWriter->save(‘php://output’); // 文件通过浏览器下载
  96.         exit();
  97.     }
  98.     protected function goods_import($filename, $exts = ‘xls’)
  99.     {
  100.         // 导入PHPExcel类库,因为PHPExcel没有用命名空间,只能inport导入
  101.         vendor(‘PHPExcel’);
  102.         // 创建PHPExcel对象,注意,不能少了\
  103.         $PHPExcel = new \PHPExcel();
  104.         // 如果excel文件后缀名为.xls,导入这个类
  105.         if ($exts == ‘xls’) {
  106.             $PHPReader = new \PHPExcel_Reader_Excel5();
  107.         } else 
  108.             if ($exts == ‘xlsx’) {
  109.                 $PHPReader = new \PHPExcel_Reader_Excel2007();
  110.             }
  111.         
  112.         // 载入文件
  113.         $PHPExcel = $PHPReader->load($filename);
  114.         // 获取表中的第一个工作表,如果要获取第二个,把0改为1,依次类推
  115.         $currentSheet = $PHPExcel->getSheet(0);
  116.         // 获取总列数
  117.         $allColumn = $currentSheet->getHighestColumn();
  118.         // 获取总行数
  119.         $allRow = $currentSheet->getHighestRow();
  120.         // 循环获取表中的数据,$currentRow表示当前行,从哪行开始读取数据,索引值从0开始
  121.         for ($currentRow = 1; $currentRow <= $allRow; $currentRow ++) {
  122.             // 从哪列开始,A表示第一列
  123.             for ($currentColumn = ‘A’; $currentColumn <= $allColumn; $currentColumn ++) {
  124.                 // 数据坐标
  125.                 $address = $currentColumn . $currentRow;
  126.                 // 读取到的数据,保存到数组$arr中
  127.                 $data[$currentRow][$currentColumn] = $currentSheet->getCell($address)->getValue();
  128.             }
  129.         }
  130.         $this->save_import($data);
  131.     }
  132. }
  133. ?>
复制代码

以下是导入库的提交页面,大家可以美化下用

  1.  <!Doctype html><html xmlns=http://www.w3.org/1999/xhtml>
  2.   <html>
  3.   <head>                        
  4.   <meta http-equiv=Content-Type content=“text/html;charset=utf-8”>
  5.   <meta http-equiv=X-UA-Compatible content=“IE=edge,chrome=1”>
  6.   <meta content=always name=referrer>
  7.     <script language=“javascript” type=“text/javascript” src=“http://www.php100.com/statics/js//php100/js/jquery.js”></script>
  8.   <title>excel</title>
  9.   
  10.   <body>
  11.   <form id=“addform” action=“{:U(‘excel/upload’)}” method=“post” enctype=“multipart/form-data”>
  12.   
  13.         <div class=“control-group”>
  14.           <label>Excel表格:</label>
  15.                 <input type=“file” name=“excelData” value=“”  datatype=“*4-50”  nullmsg=“请填写产品!” errormsg=“不能少于4个字符大于50个汉字”/>
  16.               
  17.         </div>
  18.    <div class=“control-group”>
  19.           <img style=display:none; src=“images/loading.gif” />
  20.           <input type=“submit” class=“btn btn-primary Sub” value=“导入” />
  21.         </div>
  22.     </form>
  23.       </body>
  24.     </html>


实现方法步骤二:

一:在http://phpexcel.codeplex.com/下载最新PHPExcel放到Vendor下,注意位置:ThinkPHP\Extend\Vendor\PHPExcel\PHPExcel.php。
二:导出excel代码实现

  1. /**方法**/
  2. function  index(){
  3.         $this->display();
  4.     }
  5. public function exportExcel($expTitle,$expCellName,$expTableData){
  6.         $xlsTitle = iconv(‘utf-8’, ‘gb2312’, $expTitle);//文件名称
  7.         $fileName = $_SESSION[‘account’].date(‘_YmdHis’);//or $xlsTitle 文件名称可根据自己情况设定
  8.         $cellNum = count($expCellName);
  9.         $dataNum = count($expTableData);
  10.         vendor(“PHPExcel.PHPExcel”);
  11.        
  12.         $objPHPExcel = new PHPExcel();
  13.         $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’);
  14.         
  15.         $objPHPExcel->getActiveSheet(0)->mergeCells(‘A1:’.$cellName[$cellNum1].‘1’);//合并单元格
  16.        // $objPHPExcel->setActiveSheetIndex(0)->setCellValue(‘A1′, $expTitle.’  Export time:’.date(‘Y-m-d H:i:s’));  
  17.         for($i=0;$i<$cellNum;$i++){
  18.             $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].‘2’, $expCellName[$i][1]); 
  19.         } 
  20.           // Miscellaneous glyphs, UTF-8   
  21.         for($i=0;$i<$dataNum;$i++){
  22.           for($j=0;$j<$cellNum;$j++){
  23.             $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3), $expTableData[$i][$expCellName[$j][0]]);
  24.           }             
  25.         }  
  26.         
  27.         header(‘pragma:public’);
  28.         header(‘Content-type:application/vnd.ms-excel;charset=utf-8;name=”‘.$xlsTitle.‘.xls”‘);
  29.         header(“Content-Disposition:attachment;filename=$fileName.xls”);//attachment新窗口打印inline本窗口打印
  30.         $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel5’);  
  31.         $objWriter->save(‘php://output’); 
  32.         exit;   
  33.     }
  34. /**
  35.      *
  36.      * 导出Excel
  37.      */
  38.     function expUser(){//导出Excel
  39.         $xlsName  = “User”;
  40.         $xlsCell  = array(
  41.         array(‘id’,‘账号序列’),
  42.         array(‘truename’,‘名字’),
  43.         array(‘sex’,‘性别’),
  44.         array(‘res_id’,‘院系’),
  45.         array(‘sp_id’,‘专业’),
  46.         array(‘class’,‘班级’),
  47.         array(‘year’,‘毕业时间’),
  48.         array(‘city’,‘所在地’),
  49.         array(‘company’,‘单位’),
  50.         array(‘zhicheng’,‘职称’),
  51.         array(‘zhiwu’,‘职务’),
  52.         array(‘jibie’,‘级别’),
  53.         array(‘tel’,‘电话’),
  54.         array(‘qq’,‘qq’),
  55.         array(’email’,‘邮箱’),
  56.         array(‘honor’,‘荣誉’),
  57.         array(‘remark’,‘备注’)    
  58.         );
  59.         $xlsModel = M(‘Member’);
  60.     
  61.         $xlsData  = $xlsModel->Field(‘id,truename,sex,res_id,sp_id,class,year,city,company,zhicheng,zhiwu,jibie,tel,qq,email,honor,remark’)->select();
  62.         foreach ($xlsData as $k => $v)
  63.         {
  64.             $xlsData[$k][‘sex’]=$v[‘sex’]==1?‘男’:‘女’;
  65.         }
  66.         $this->exportExcel($xlsName,$xlsCell,$xlsData);
  67.          
  68.     }
复制代码

第三:导入excel数据代码

  1. function impUser(){
  2.         if (!empty($_FILES)) {
  3.             import(“@.ORG.UploadFile”);
  4.             $config=array(
  5.                 ‘allowExts’=>array(‘xlsx’,‘xls’),
  6.                 ‘savePath’=>‘./Public/upload/’,
  7.                 ‘saveRule’=>‘time’,
  8.             );
  9.             $upload = new UploadFile($config);
  10.             if (!$upload->upload()) {
  11.                 $this->error($upload->getErrorMsg());
  12.             } else {
  13.                 $info = $upload->getUploadFileInfo();
  14.                 
  15.             }
  16.         
  17.             vendor(“PHPExcel.PHPExcel”);
  18.                 $file_name=$info[0][‘savepath’].$info[0][‘savename’];
  19.                 $objReader = PHPExcel_IOFactory::createReader(‘Excel5’);
  20.                 $objPHPExcel = $objReader->load($file_name,$encode=‘utf-8’);
  21.                 $sheet = $objPHPExcel->getSheet(0);
  22.                 $highestRow = $sheet->getHighestRow(); // 取得总行数
  23.                 $highestColumn = $sheet->getHighestColumn(); // 取得总列数
  24.                 for($i=3;$i<=$highestRow;$i++)
  25.                 {   
  26.                    $data[‘account’]= $data[‘truename’] = $objPHPExcel->getActiveSheet()->getCell(“B”.$i)->getValue();  
  27.                     $sex = $objPHPExcel->getActiveSheet()->getCell(“C”.$i)->getValue();
  28.                    // $data[‘res_id’]    = $objPHPExcel->getActiveSheet()->getCell(“D”.$i)->getValue();
  29.                     $data[‘class’] = $objPHPExcel->getActiveSheet()->getCell(“E”.$i)->getValue();
  30.                     $data[‘year’] = $objPHPExcel->getActiveSheet()->getCell(“F”.$i)->getValue();
  31.                     $data[‘city’]= $objPHPExcel->getActiveSheet()->getCell(“G”.$i)->getValue();
  32.                     $data[‘company’]= $objPHPExcel->getActiveSheet()->getCell(“H”.$i)->getValue();
  33.                     $data[‘zhicheng’]= $objPHPExcel->getActiveSheet()->getCell(“I”.$i)->getValue();
  34.                     $data[‘zhiwu’]= $objPHPExcel->getActiveSheet()->getCell(“J”.$i)->getValue();
  35.                     $data[‘jibie’]= $objPHPExcel->getActiveSheet()->getCell(“K”.$i)->getValue();
  36.                     $data[‘honor’]= $objPHPExcel->getActiveSheet()->getCell(“L”.$i)->getValue();
  37.                     $data[‘tel’]= $objPHPExcel->getActiveSheet()->getCell(“M”.$i)->getValue();
  38.                     $data[‘qq’]= $objPHPExcel->getActiveSheet()->getCell(“N”.$i)->getValue();
  39.                     $data[’email’]= $objPHPExcel->getActiveSheet()->getCell(“O”.$i)->getValue();
  40.                     $data[‘remark’]= $objPHPExcel->getActiveSheet()->getCell(“P”.$i)->getValue();
  41.                     $data[‘sex’]=$sex==‘男’?1:0;
  42.                     $data[‘res_id’] =1;
  43.                     
  44.                     $data[‘last_login_time’]=0;
  45.                     $data[‘create_time’]=$data[‘last_login_ip’]=$_SERVER[‘REMOTE_ADDR’];
  46.                     $data[‘login_count’]=0;
  47.                     $data[‘join’]=0;
  48.                     $data[‘avatar’]=;
  49.                     $data[‘password’]=md5(‘123456’);              
  50.                     M(‘Member’)->add($data);
  51.          
  52.                 } 
  53.                  $this->success(‘导入成功!’);
  54.         }else
  55.             {
  56.                 $this->error(“请选择上传的文件”);
  57.             }    
  58.          
  59.     }
复制代码

四、模板代码

  1. <html>
  2.     <head>
  3.         
  4.     </head>
  5.     <body>
  6.     <P><a href=“{:U(‘Index/expUser’)}” >导出数据并生成excel</a></P><br/>
  7.         <form action=“{:U(‘Index/impUser’)}” method=“post” enctype=“multipart/form-data”>
  8.             <input type=“file” name=“import”/>
  9.             <input type=“hidden” name=“table” value=“tablename”/>
  10.             <input type=“submit” value=“导入”/>
  11.         </form>
  12.     </body>
  13.     
  14. </html>

下载地址:thinkphp实现excel数据的导入导出(附完整案例)