php开发数据导入导出,详解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;$isetActiveSheetIndex(0)->setCellValue($cellName[$i]."2", $expCellName[$i][1]); } // Miscellaneous glyphs, UTF-8 for($i=0;$igetActiveSheet(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("请选择上传的文件"); } }

四、模板代码

导出数据并生成excel

最后下载：demo下载

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