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

ThinkPHP  /  管理员 发布于 8年前   178

实现步骤：

一：在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;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("请选择上传的文件");      }         }

四、模板代码

            
导出数据并生成excel

    
                      
    

最后下载：demo下载

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

您可能感兴趣的文章:

• thinkPHP实现将excel导入到数据库中的方法
• ThinkPHP基于PHPExcel导入Excel文件的方法