<?php 

//引入PHPExcel文件
require("./PHPExcel.php");
//引入mysql数据库操作类
require("./db.php");

//实例化db操作类
$db = new db($phpexcel);
//实例化PHPExcel对象,相当于创建了一个新的excel
$objExcel = new PHPExcel();
//获得内置表对象
$objSheet = $objExcel->getActiveSheet();
    
    //得到所有年级数
    $gradeinfo = $db->getAllGrade();
    //设置初始位置
    $index = 0;
    //设置单元格内容默认居中显示
    $objSheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    //设置默认字体大小
    $objSheet->getDefaultStyle()->getFont()->setName("微软雅黑")->setSize(14);
    //设置\'年级\'字体
    $objSheet->getStyle("A2:Z2")->getFont()->setSize(18)->setBold(true);
    //设置\'班级\'字体
    $objSheet->getStyle("A3:Z3")->getFont()->setSize(16)->setBold(true);

    //遍历年级
    foreach ($gradeinfo as $key => $value) {
        //获得年级所在列
        $gradeindex = getCells($index * 2);
        //设置\'年级\'字样
        $objSheet->setCellValue($gradeindex."2","高".$value[\'grade\']);    
        //获得班级信息    
        $classinfo = $db->getClassByGrade($value[\'grade\']);
        //遍历班级
        foreach ($classinfo as $k => $v) {
            $info = $db->getResultByClass($v[\'class\'],$value[\'grade\']);
            //获得每个班学生姓名所在位置
            $nameindex = getCells($index * 2);;
            //获得每个班学生成绩所在位置
            $scoreindex = getCells($index * 2 + 1);

            //进行\'班级\'单元格的合并
            $objSheet->mergeCells($nameindex."3:".$scoreindex."3");
            //进行\'班级\'单元格颜色的填充
            $objSheet->getStyle($nameindex."3:".$scoreindex."3")->getFill()->setFillType(PHPEXCEL_Style_Fill::FILL_SOLID)->getStartColor()->setRGB("CDCDB4");
            //进行\'班级\'单元格边框设置
            $objSheet->getStyle($nameindex."3:".$scoreindex."3")->applyFromArray(getBorderStyle("e3df51"));

            //设置\'班级\'字样
            $objSheet->setCellValue($nameindex."3",$v[\'class\']);
            //设置\'姓名\'和\'成绩\'字样
            $objSheet->setCellValue($nameindex."4","姓名")->setCellValue($scoreindex."4","成绩");
            //设置初始位置
            $j = 5;
            //填充学生信息
            foreach ($info as $k1 => $v1) {
                $objSheet->setCellValue($nameindex.$j,$v1[\'username\'])
                ->setCellValue($scoreindex.$j,$v1[\'score\']);
                $j++;
            }
            $index++;
        }

        //获得\'年级\'合并末尾列
        $endindex = getCells($index * 2 - 1);
        //合并\'年级\'单元格
        $objSheet->mergeCells($gradeindex."2:".$endindex."2");
        //进行\'年级\'单元格颜色填充
        $objSheet->getStyle($gradeindex."2:".$endindex."2")->getFill()->setFillType(PHPEXCEL_Style_Fill::FILL_SOLID)->getStartColor()->setRGB("DD7E6B");
        //进行\'年级\'单元格边框设置
        $objSheet->getStyle($gradeindex."2:".$endindex."2")->applyFromArray(getBorderStyle("e3df51"));
    }



    //创建写入对象
    $objWriter = PHPExcel_IOFactory::createWriter($objExcel,\'Excel5\');
    //保存文件
    $objWriter->save("./style.xlsx");


    //输出到浏览器
    function browser_export($type,$filename){
        if($type == "Excel5"){
        //告诉游览器将要输出excel03文件
          header(\'Content-Type: applicationnd.ms-excel\');
          }else{
              //告诉游览器将要输出excel07文件
              header(\'Content-Type: applicationnd.openxmlformats-officedocument.spreadsheetml.sheet\');
          }
          //告诉游览器将要输出文件的名称
        header(\'Content-Disposition: attachment;filename="\'.$filename.\'"\');
        //禁止缓存
        header(\'Cache-Control: max-age=0\');
    }


    //从A到Z的排序
    function getCells($index){
        $arr = range(\'A\',\'Z\');
        return $arr[$index];
    }

    //获取不同的边框样式
    function getBorderStyle($color){
        $styleArray = array(
            \'borders\'=>array(
                \'outline\'=>array(
                \'style\'=>PHPExcel_Style_Border::BORDER_THICK,//边框是粗的 
                // \'style\' => PHPExcel_Style_Border::BORDER_THIN,//细边框 
                \'color\'=>array(\'rgb\'=>$color),
                ),
            ),
        );
    return $styleArray; 
    }


 ?>

<?php 

//引入配置文件
require("./dbconfig.php");

class db{

    public $conn = null;

    //构造方法
    public function __construct($config){
        //连接数据库
        $this->conn = @mysql_connect($config[\'host\'],$config[\'username\'],$config[\'password\']) or die(mysql_error());

        //选择数据库
        mysql_select_db($config[\'database\'],$this->conn) or die(mysql_error());

        //设定mysql编码
        mysql_query("set names ".$config[\'charset\']) or die(mysql_error());

    }

    //根据传入的sql语句查询结果集
    public function getResult($sql){
        //进行sql语句查询
        $resource = mysql_query($sql,$this->conn) or die(mysql_error());
        //定义返回的结果集数组
        $res = array();
        //返回从结果集取得的行生成的关联数组
        while(($row = mysql_fetch_assoc($resource)) != false){
            //将数组赋值给$reset(array)
            $res[] = $row;
        }
        //将结果集数组
        return $res;
    }

    //根据年级进行数据查询
    public function getDataByGrade($grade){
        $sql = "select username,score,class from user where grade=".$grade." order by score desc";
        $result = self::getResult($sql);
        return $result;
    }

    //查询所有的年级数
    public function getAllGrade(){
        $sql = "select distinct(grade) from user order by grade asc";
        //通过调用getResult方法得到年级信息
        $res = $this->getResult($sql);
        //返回查询的结果
        return $res;
    }

    //根据年级查询所有的班级
    public function getClassByGrade($grade){
        $sql = "select distinct(class) from user where grade = ".$grade." order by class asc";
        //通过调用getResult方法得到班级信息
        $res = $this->getResult($sql);
        //返回查询结果
        return $res;
    }

    //根据班级查询所有学生信息
    public function getResultByClass($class,$grade){
        $sql = "select username,score from user where class = ".$class." and grade = ".$grade." order by class asc";
        //通过调用getResult方法得到班级所有学生信息
        $res = $this->getResult($sql);
        //返回查询结果
        return $res;
    }

}




 ?>