首先下载phpExcel插件
添加导出按钮
<a href="?mod=salesman_list&action=get_selected_excel&ar_id=$ar_id&keyword=$keyword&dateBegin=$dateBegin&dateEnd=$dateEnd&sirr_level=$sirr_level" class="btn btn-primary">导出Excel</a>
在项目中引入插件并接收参数
header("Access-Control-Allow-Origin: *"); // 允许任意域名发起的跨域请求 header ( "Content-type:text/html;charset=utf-8" ); error_reporting(E_ALL); //date_default_timezone_set('Asia/Shanghai');//时区 //加载PHPExcel下的PHPExcel.php文件（按照项目中所在位置进行加载） require_once '../source/include/PHPExcel/Classes/PHPExcel.php'; //设置文件属性 $objPHPExcel=new PHPExcel(); $objPHPExcel->getProperties()->setCreator($SETTING['domain']) ->setLastModifiedBy($SETTING['domain']) ->setTitle('Office 2007 XLSX Document') ->setSubject('Office 2007 XLSX Document') ->setDescription('Document for Office 2007 XLSX, generated using PHP classes.') ->setKeywords('office 2007 openxml php') ->setCategory('Result file'); //标题合并单元格设置字体 $objPHPExcel->getActiveSheet()->mergeCells('A1:k1')->getStyle('A1:k1')->getFont()->setSize(16)->setBold(true); //标题设置居中 $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //导航栏及内容设置居中 $objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('G')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('H')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('I')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('J')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('K')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //导航栏加粗 $objPHPExcel->getActiveSheet()->getStyle('A2:k2')->getFont()->setBold(true); // 设置行高 $objPHPExcel->getActiveSheet()->getRowDimension('9')->setRowHeight(20); //设置单元格宽度 $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(17); $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setAutoSize(true); //设置导航栏 $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A1','营销经理列表') ->setCellValue('A2','用户名') ->setCellValue('B2','昵称') ->setCellValue('C2','手机号') ->setCellValue('D2','等级') ->setCellValue('E2','上级') ->setCellValue('F2','分润比例') ->setCellValue('G2','限制添加经理数量') ->setCellValue('H2','所在区域') ->setCellValue('I2','备注/推荐经理') ->setCellValue('J2','状态') ->setCellValue('K2','创建时间'); //接收参数 $ar_id = $_GET['ar_id']; $dateBegin = $_GET['dateBegin']; $dateEnd = $_GET['dateEnd']; $keyword = $_GET['keyword']; $sirr_level = $_GET['sirr_level']; $where_1 = array(); $sqlwhere_1 = ''; //处理参数 if ($keyword != '') { $where_1[] = "(s.sm_userno like '%$keyword%' or s.sm_username like '%$keyword%' or s.sm_phoneno like '%$keyword%')"; } if ($sirr_level > 0) { $where_1[] = "sirr_level='$sirr_level'"; } if ($dateBegin != '') { $where_1[] = "sirr_createTime >='$dateBegin' "; } if ($dateEnd != '') { $where_1[] = "sirr_createTime<= '$dateEnd' "; } if($ar_id>0){ $where_1[] = "s.ar_id='$ar_id'"; } //生成where条件 if (count($where_1) > 0) { $sqlwhere_1 = " and " . implode(' and ', $where_1); } /* echo $sqlwhere_1; exit; */ //进行生气了查询 $sql2 = "select * from uc_salesman_installment_relation_rate a inner join uc_salesman s on a.sm_id=s.sm_id WHERE sm_state<>3 and ar_id='$ar_id' $sqlwhere_1 order by s.sm_id desc "; $salesman = $g_db->getAll($sql2); $level_array = array('1' => '一级', '2' => '二级'); $state = array('1'=>'正常', '2'=>'禁用', '3'=>'删除'); $num=0; if (count($salesman) > 0) { for($i=0;$i<count($salesman);$i++){ $num +=1; $sm_id = $salesman[$i]['sm_id']; $sirr_parent_sm_id = $salesman[$i]['sirr_parent_sm_id']; $salesman[$i]['sirr_rate'] = (float) $salesman[$i]['sirr_rate']; $salesman[$i]['sirr_level_l'] = $level_array[$salesman[$i]['sirr_level']]; $salesman[$i]['sm_state_s'] = $state[$salesman[$i]['sm_state']]; $salesman[$i]['parent_sm'] = ''; if ($sirr_parent_sm_id > 0) { $saleman_username = fun_get_saleman_username($sirr_parent_sm_id); if ($saleman_username['code'] == '1') { $salesman[$i]['parent_sm'] = $saleman_username['sm_username']; } } $sql="select * from {$SETTING['db']['prefix']}salesman_cityarea where sm_id='$sm_id'"; $sm_area=$g_db->getAll($sql); $salesman[$i]['area_cityname'] = ''; if(count($sm_area)>0){ $salesman[$i]['area_cityname']=$sm_area[0]['sc_cityname1'].' '.$sm_area[0]['sc_cityname2']; } //循环将查询的数据写入excel表（因为第一行为标题，第二行为导航栏顾从第三行开始添加数据） $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A'.($i + 3),$salesman[$i]['sm_userno']) ->setCellValue('B'.($i + 3),$salesman[$i]['sm_username']) ->setCellValue('C'.($i + 3),$salesman[$i]['sm_phoneno']) ->setCellValue('D'.($i + 3),$salesman[$i]['sirr_level_l']) ->setCellValue('E'.($i + 3),$salesman[$i]['parent_sm']) ->setCellValue('F'.($i + 3),$salesman[$i]['sirr_rate']) ->setCellValue('G'.($i + 3),$salesman[$i]['sirr_limitNum']) ->setCellValue('H'.($i + 3),$salesman[$i]['area_cityname']) ->setCellValue('I'.($i + 3),$salesman[$i]['sirr_remark']) ->setCellValue('J'.($i + 3),$salesman[$i]['sm_state_s']) ->setCellValue('K'.($i + 3),$salesman[$i]['sm_createTime']); $objPHPExcel->getActiveSheet()->getRowDimension($i + 3)->setRowHeight(20); } } //设置文件内的名称 $objPHPExcel->getActiveSheet()->setTitle('营销经理列表'); $objPHPExcel->setActiveSheetIndex(0); //设置文件的名称 $filename=urlencode('营销经理列表').'_'.date('Y-m-dHis'); //*生成xlsx文件 /*ob_end_clean();//清除缓冲区,避免乱码 header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="'.$filename.'.xlsx"'); header('Cache-Control: max-age=0'); $objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel2007');*/ /*生成xls文件*/ ob_end_clean();//清除缓冲区,避免乱码 header('Content-Type: application/vnd.ms-excel;charset=utf-8'); header("Content-Disposition:attachment;filename=test_data.xls"); header('Content-Disposition: attachment;filename="'.$filename.'.xls"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //$objWriter->save($filename); $objWriter->save('php://output');
对文件的内容添加样式：不同的版本会有冲突，有时会加载不上（样式真心不好改啊）
//Merge cells 合并分离单元格 $objPHPExcel->getActiveSheet()->mergeCells('A18:E22'); $objPHPExcel->getActiveSheet()->unmergeCells('A18:E22'); //Protect cells 保护单元格 $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);//Needs to be set to true in order to enable any worksheet protection! $objPHPExcel->getActiveSheet()->protectCells('A3:E13', 'PHPExcel'); //Set cell number formats 数字格式化 $objPHPExcel->getActiveSheet()->getStyle('E4')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE); $objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('E4'), 'E5:E13' ); //Set column widths 设置列宽度 $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12); //Set fonts 设置字体 $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara'); $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(20); $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE); $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE); //Set alignments 设置对齐 $objPHPExcel->getActiveSheet()->getStyle('D11')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY); $objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setWrapText(true); //Set column borders 设置列边框 $objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objPHPExcel->getActiveSheet()->getStyle('A10')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objPHPExcel->getActiveSheet()->getStyle('E10')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK); $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK); //Set border colors 设置边框颜色 $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->getColor()->setARGB('FF993300'); $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getTop()->getColor()->setARGB('FF993300'); $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getBottom()->getColor()->setARGB('FF993300'); $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getRight()->getColor()->setARGB('FF993300'); //Set fills 设置填充 $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080'); //Add a hyperlink to the sheet 添加链接 $objPHPExcel->getActiveSheet()->setCellValue('E26', 'www.phpexcel.net'); $objPHPExcel->getActiveSheet()->getCell('E26')->getHyperlink()->setUrl('http://www.phpexcel.net'); $objPHPExcel->getActiveSheet()->getCell('E26')->getHyperlink()->setTooltip('Navigate to website'); $objPHPExcel->getActiveSheet()->getStyle('E26')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);