thinkphp5.1+ajax实现导出Excel · PHP知识总结

>本实例是采用THINKPHP5.1+ajax+phpexcel 来实现导出Excel功能。由于ajax的返回值类型是json,text,html,xml类型，或者可以说ajax的接收类型只能是string字符串，不是流类型，所以无法实现文件下载，强行使用会使response出现乱码。因此，如果需要ajax来实现导出功能，则需要在方法中生成Excel文件，构造文件路径URL返回给ajax进行响应下载。 ``` //前端代码：获取筛选条件参数后采用post方式传递URL中，在success回调函数中进行页面重定向，重定向的地址是Excel文件路径 <a class="layui-btn layui-btn-normal" onclick="exportExcel()">导出Excel</a> <script type="text/javascript"> function exportExcel(){ $.ajax({ type: "POST", url : "{:url('admin/User/userExport')}", data: { search_key :$("#search_key").val(), search_value :$("#search_value").val(), create_time :$('#create_time').val(), pay_time :$('#pay_time').val(), shipping_time :$('#shipping_time').val(), grade :$("#grade").val(), pay_status :$("#pay_status").val(), shipping_status :$("#shipping_status").val(), status_delete :$("#status_delete").val(), order_status :$("#order_status").val(), }, success: function (res) { window.location.href = res.data.url }, error: function (error) { if (error.code == 401){ window.location.href = "/admin/login" }else { layer.msg(error.responseJSON.msg,{time: 1000, icon: 2}); } } }); } </script> ``` ``` //控制器方法 /** * 导出Excel * @param array $params */ public function UserListExport(){ if(Request::isAjax()){ $userId = get_admin_id(); $userProfileService = new UserProfileService(); $settingService = new SettingService(); $excelUtil = new ExcelUtil(); $params = Request::post(); $where = $this->formatParams($params); $list = $userProfileService->selectUserAndProfile($where); $userList = $userProfileService->formatExportList($list['userList']); $systemInfo = $settingService->getByName(SettingType::SETTING_TYPE_SYSTEM); //设置excel表头 $header = ['id','用昵称','用户等级','用户手机号','真实姓名','身份证号','性别','实名状态','推荐人姓名','推荐人手机','推荐人等级']; //调用导出类，接收返回值（文件路径） $path = $excelUtil->export($userId,'用户列表','用户',$systemInfo['excel_export'],$header,$userList); if($path){ //路径拼接域名 $webSite = $settingService->getByName(SettingType::SETTING_TYPE_WEBSITE); $path = $webSite['basehost'].$path; $response = ['url'=>$path]; return $this->ajaxSuccess('获取成功',$response); } return $this->ajaxError(); } } ``` ``` /** * 导出Excel方法 * @param int $userId 用户id * @param string $expTitle 表格标题 * @param string $expFileName 导出文件名 * @param string $expFileType 导出文件后缀:xls,csv * @param array $expHeadArr 导出文件列名 * @param array $expDataList 导出数据 */ public function export($userId,$expTitle,$expFileName,$expFileType,$expHeadArr,$expDataList){ $time = md5(date('YmdHis',time()).$userId); $expFileName .= "_".$time.".".$expFileType; //设置保存路径 $basePath = request()->env('ROOT_PATH').'public'; $baseUrl = DIRECTORY_SEPARATOR .implode(DIRECTORY_SEPARATOR, ['excel',date('Y-m-d',time())]). DIRECTORY_SEPARATOR; $path = $basePath.$baseUrl; //路径不存在则创建路径 if(!is_dir($path)){ mkdir($path, 0777, true); } $path = $path.$expFileName; $objPHPExcel = new \PHPExcel(); //设置Excel属性 $objPHPExcel->getProperties() ->setCreator("Maarten Balliauw") //创建人 ->setLastModifiedBy("Maarten Balliauw") //最后修改人 ->setTitle("Office 2007 XLSX Test Document") //设置标题 ->setSubject("Office 2007 XLSX Test Document") //设置主题 ->setDescription("Test document ") //设置备注 ->setKeywords( "office 2007 openxml php") //设置关键字 ->setCategory( "Test result file"); //设置类别 //设置Excel样式 //设置第一行（标题）合并单元格 $key = ord('A'); $num = count($expHeadArr); $lastHead = chr($key+$num); $mergeCells = 'A1:'.$lastHead.'1'; $objPHPExcel->getActiveSheet()->mergeCells($mergeCells); //单元格居中、字体大小、粗体 $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20); $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true); //设置表头信息 $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1',$expTitle); foreach($expHeadArr as $head){ $colum = chr($key); $objPHPExcel->getActiveSheet()->setCellValue($colum.'2',$head); $objPHPExcel->getActiveSheet()->getStyle($colum.'2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER); //表头文字居中 $key += 1; } //写入数据 $colum = 3; foreach($expDataList as $key =>$row){//行写入 $span = ord("A"); foreach($expDataList[$key] as $keyName => $value){//列写入 $objPHPExcel->getActiveSheet()->setCellValue(chr($span).$colum,$value); $objPHPExcel->getActiveSheet()->getStyle(chr($span).$colum)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); //文字左对齐中 $objPHPExcel->getActiveSheet()->getColumnDimension(chr($span))->setWidth(13);//设置宽度 $span ++; } $colum ++; } $objPHPExcel->setActiveSheetIndex(0); ob_end_clean();//清除缓冲区,避免乱码 //"Excel2007"生成2007版本的xlsx，"Excel5"生成2003版本的xls $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5'); //保存路径 $objWriter->save($path); $filePath = str_replace('\\','/',$baseUrl.$expFileName); return $filePath; //直接弹出提示下载，不生成文件 header('pragma:public'); header("Content-Disposition:attachment;filename=$expFileName"); header('Cache-Control: max-age=0'); $objWriter = PHPExcel\_IOFactory:: *createWriter*($objPHPExcel, 'Excel2007'); $objWriter->save( 'php://output'); } ```