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

实现步骤：

一：在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-8for（$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下载

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