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'); } ```