运用phpExcel导出查询的数据

首先下载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);