1. 表格方式
public function getVideoExcelExport(){ ???????$dataResult = Video::select(‘id‘,‘title‘,‘price_way‘, ‘playnum‘,‘content‘,‘video_type‘)->get()->toArray(); ???????foreach ($dataResult as $k=>$v){ ???????????//0免费 1VIP 2付费 ???????????$dataResult[$k][‘price_way_text‘] = ($v[‘price_way‘] == 0)?‘免费‘:(($v[‘price_way‘] == 1)?‘VIP‘:‘付费‘); ???????????$dataResult[$k][‘video_type_text‘] = MappingUtil::getTextByVideoType($v[‘video_type‘]); ???????} ???????$headTitle = "视频记录"; ???????$title = "视频记录"; ???????$headtitle= "<tr style=‘height:50px;border-style:none;>\<th border=\"0\" style=‘height:60px;width:270px;font-size:22px;‘ colspan=‘11‘ >{$headTitle}</th></tr>"; ???????$titlename = "<tr> ???????????????????<th style=‘width:70px;‘ >ID</th> ???????????????????<th style=‘width:300px;‘ >标题</th> ???????????????????<th style=‘width:70px;‘>付费模式</th> ???????????????????<th style=‘width:70px;‘>视频类型</th> ???????????????????<th style=‘width:600px;‘>内容</th> ???????????????</tr>"; ???????$filename = $title.".xls"; ???????$this->excelData($dataResult,$titlename,$headtitle,$filename); ???} /*视频导出Excel */ ???public function excelData($datas,$titlename,$title,$filename){ ???????$columns = [‘id‘,‘title‘,‘price_way_text‘,‘video_type_text‘,‘content‘]; ???????$str = "<html xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\nxmlns:x=\"urn:schemas-microsoft-com:office:excel\"\r\nxmlns=\"http://www.w3.org/TR/REC-html40\">\r\n<head>\r\n<meta http-equiv=Content-Type content=\"text/html; charset=utf-8\">\r\n</head>\r\n<body>"; ???????$str .="<table border=1><head>".$titlename."</head>"; ???????//$str .= $title; ???????$dataResult = []; ???????foreach ($datas as $key=>$value){ ???????????foreach ($columns as $k=>$v){ ???????????????if (isset($datas[$key][$v])){ ???????????????????$dataResult[$key][$v] = $datas[$key][$v]; ???????????????} ???????????} ???????} ???????foreach ($dataResult ?as $key=> $rt ){ ???????????$str .= "<tr>"; ???????????foreach ( $rt as $k => $v ) ???????????{ ???????????????$str .= "<td>{$v}</td>"; ???????????} ???????????$str .= "</tr>\n"; ???????} ???????$str .= "</table></body></html>"; ???????header( "Content-Type: application/vnd.ms-excel; name=‘excel‘" ); ???????header( "Content-type: application/octet-stream" ); ???????header( "Content-Disposition: attachment; filename=".$filename ); ???????header( "Cache-Control: must-revalidate, post-check=0, pre-check=0" ); ???????header( "Pragma: no-cache" ); ???????header( "Expires: 0" ); ???????exit( $str ); ???}2. 不分sheet方式
use Excel; ???public function getMatchFinalTempWorkExcelExportModule(Request $request){ ???????$groupId = $request->get(‘match_group_id‘,‘‘); ???????if(empty($groupId) || !is_numeric($groupId)){ ???????????echo ‘请传入正确match_group_id‘; ???????????exit; ???????} ???????switch ($groupId){ ???????????case 1:{ ???????????????$groupName = ‘中年组‘; ???????????} ???????????case 2:{ ???????????????$groupName = ‘老年组‘; ???????????} ???????????case 3:{ ???????????????$groupName = ‘合唱组‘; ???????????} ???????????default :{ ???????????????$groupName = ‘未分组‘; ???????????} ???????} ???????$where = [ ???????????[‘final_score‘,‘>‘,0], ???????????[‘match_group_id‘,‘=‘,$groupId], ???????]; ???????$orderBy = [ ???????????[‘final_score‘,‘desc‘] ???????]; ???????$dataResultDB = MatchFinalTempWork::getFinalTempWorkForAdmin([ ???????????‘where‘=>$where, ???????????‘order_by‘=>$orderBy ???????]); ???????$dataResult = []; ???????if(!empty($dataResultDB[‘final_works‘])){ ???????????$dataResult = $dataResultDB[‘final_works‘]; ???????} ???????foreach ($dataResult as $k=>$v){ ???????????$text = ‘未分组‘; ???????????if($v[‘match_group_id‘] == 1){ ???????????????$text = ‘中年组‘; ???????????} ???????????if($v[‘match_group_id‘] == 2){ ???????????????$text = ‘老年组‘; ???????????} ???????????if($v[‘match_group_id‘] == 3){ ???????????????$text = ‘合唱组‘; ???????????} ???????????$v[‘group_name‘] = $text; ???????????$dataResult[$k] = $v; ???????} ???????$columnsHead = [ ???????????‘作品名称 ??‘ ???????????,‘编号 ??‘ ???????????,‘联系人 ??‘ ???????????,‘得分 ??‘ ???????]; ???????$columns = [ ???????????‘work_name‘ ???????????, ‘serial_no‘ ???????????, ‘contacter‘ ???????????,‘final_score‘ ???????]; ???????$cellData = []; ???????$cellData[] = $columnsHead; ???????foreach ($dataResult as $k=>$v){ ???????????$temp = []; ???????????foreach ($columns as $kc=>$vc){ ???????????????if (isset($v[$vc]) || is_null($v[$vc])){ ???????????????????$temp[$vc] = $v[$vc]; ???????????????} ???????????} ???????????$cellData[] = $temp; ???????} ???????$filename = $groupName."比赛结果"; ???????Excel::create($filename,function($excel) use ($groupName, $cellData){ ???????????$excel->sheet($groupName, function($sheet) use ($cellData){ ???????????????$sheet->rows($cellData); ???????????}); ???????})->export(‘xls‘); ???}3.分sheet方式
use Excel;//比赛作品导出 ???public function getMatchWorkExcelExportModule(){ ???????$where = [ ???????????[‘work_status‘,4], ???????????[‘original_id‘,‘>‘,0], ???????]; ???????$orderBy = [ ???????????[‘group_id‘,‘asc‘], ???????????[‘matcher_area_id‘,‘asc‘] ???????]; ???????$dataResult = MatchWork::select(‘tb_match_work.work_name‘, ‘tb_match_work.matcher_name‘,‘tb_match_work.matcher_age‘, ???????????‘tb_match_work.support_num‘, ‘tb_match_work.work_desc‘, ‘tb_match_work.contacter‘, ‘tb_match_work.mobile‘, ???????????‘tb_match_work.group_id‘,‘tb_match_work.work_url‘,‘tb_match_work.match_group_id‘ ???????????,‘city.name as city_name‘ ???????????,‘tb_match_final_work.title‘,‘tb_match_final_work.type‘ ???????????,‘tb_match_resource.title as resource_title‘ ???????) ???????????->leftjoin(‘city‘,‘tb_match_work.matcher_area_id‘,‘=‘,‘city.id‘) ???????????->leftjoin(‘tb_match_final_work‘,‘tb_match_final_work.work_id‘,‘=‘,‘tb_match_work.id‘) ???????????->leftjoin(‘tb_match_resource‘,‘tb_match_resource.id‘,‘=‘,‘tb_match_final_work.resource_id‘) ???????????->baseWhere($where) ???????????->baseOrderBy($orderBy) ???????????->get() ???????????->toArray(); ???????$areas = array_column($dataResult ,‘match_group_id‘); ???????sort($areas); ???????$areas = array_unique($areas); ???????foreach ($dataResult as $k=>$v){ ???????????$text = ‘未分组‘; ???????????if($v[‘group_id‘] == 1){ ???????????????$text = ‘中年组‘; ???????????} ???????????if($v[‘group_id‘] == 2){ ???????????????$text = ‘老年组‘; ???????????} ???????????if($v[‘group_id‘] == 3){ ???????????????$text = ‘合唱组‘; ???????????} ???????????$v[‘group_name‘] = $text; ???????????unset($v[‘group_id‘]); ???????????if(!empty($v[‘work_url‘])){ ???????????????$v[‘work_url‘] = UrlUtil::addCdnPrefix($v[‘work_url‘]); ???????????} ???????????if($v[‘type‘] == ?2){ ???????????????$v[‘title‘] = $v[‘resource_title‘]; ???????????} ???????????unset($v[‘resource_title‘]); ???????????unset($v[‘type‘]); ???????????$dataResult[$k] = $v; ???????} ???????$dataData = []; ???????$columnsHead = [‘作品名称‘,‘合唱团队名称‘,‘支持数‘ ???????????,‘作品描述‘ ???????????,‘联系人‘,‘联系电话‘,‘年龄‘,‘地区‘ ???????????,‘作品地址‘ ???????????,‘分组‘,‘复赛作品‘]; ???????$columns = [‘work_name‘, ‘matcher_name‘, ???????????‘support_num‘ ???????????, ‘work_desc‘ ???????????, ‘contacter‘, ‘mobile‘,‘matcher_age‘,‘city_name‘ ???????????, ‘work_url‘ ???????????,‘group_name‘,‘title‘]; ???????foreach ($areas as $key=>$value){ ???????????$cellData = []; ???????????$cellData[] = $columnsHead; ???????????foreach ($dataResult as $k=>$v){ ???????????????if($value == $v[‘match_group_id‘]){ ???????????????????$temp = []; ???????????????????foreach ($columns as $kc=>$vc){ ???????????????????????if (isset($v[$vc]) || is_null($v[$vc])){ ???????????????????????????$temp[$vc] = $v[$vc]; ???????????????????????} ???????????????????} ???????????????????$cellData[] = $temp; ???????????????} ???????????} ???????????$dataData[$key][‘group‘] = $value; ???????????$dataData[$key][‘cell_data‘] = $cellData; ???????} ???????$filename = "比赛记录"; ???????Excel::create($filename,function($excel) use ($dataData){ ???????????foreach ($dataData as $key=>$value){ ???????????????$cellData = $value[‘cell_data‘]; ???????????????$groupName = ‘分组‘.$value[‘group‘]; ???????????????$excel->sheet($groupName, function($sheet) use ($cellData){ ???????????????????$sheet->rows($cellData); ???????????????}); ???????????} ???????})->export(‘xls‘); ???}PHP 导出Excel三种方式 阿星小栈
原文地址：https://www.cnblogs.com/dereckbu/p/9646574.html