<?php

$config = array(

	'host'=>'127.0.0.1',

	'username'=>'root',

	'password'=>'',

	'database'=>'phpexcel',

	'charset'=>'utf8'

);

<?php

class Db {

    private static $_instance;

    private $conn = null;

    private function __construct() {

        require "./dbconfig.php";

        $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_query('set names '.$config['charset']);

    }

    //查询结果集

    public function getResult($sql) {

        $resource = mysql_query($sql,$this->conn) or die(mysql_error());

        $res = array();

        while($row = mysql_fetch_assoc($resource)) {

            $res[] = $row;

        }

        return $res;

    }

    //单例模式

    public static function getInstance() {

        if (!self::$_instance instanceof self) {

            self::$_instance = new self();

        }

        return self::$_instance;

    }

    private function __clone() {

        trigger_error('Clone is not allow!',E_USER_ERROR);

    }

}

<?php

header("Content-Type:text/html;charset=utf-8");

require "./db.php";

require "./PHPExcel/PHPExcel.php";

//连接数据库

$db = Db::getInstance();

//实例化excel类

$objPHPExcel = new PHPExcel();

//创建sheet

for ($i=1; $i<=3; $i++) {

    if ($i > 1) { //默认已经有一个sheet，从第二个开始创建

        $objPHPExcel->createSheet();

    }

    $objPHPExcel->setActiveSheetIndex($i-1);

    //获得当前活动sheet的操作对象

    $objSheet = $objPHPExcel->getActiveSheet();

    //设置sheet标题

    $objSheet->setTitle($i.'年级');

    //从数据库查询数据

    $Db = Db::getInstance();

    //查询每个年级的活动数据

    $data = $Db->getResult("select * from user where grade = ".$i);

    $objSheet->setCellValue("A1","姓名")->setCellValue("B1","分数")->setCellValue("C1","班级");

    //添加数据

    $j = 2;

    foreach ($data as $value) {

        $objSheet->setCellValue("A".$j,$value['username'])->setCellValue("B".$j,$value['score'])->setCellValue("C".$j,$value['grade']);

        $j++;

    }

}

//按照指定格式生成excel文件

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');//生成excel 2007

$objWriter->save('class.xlsx');

//如果需要输出到浏览器，请打开以下注释和注释掉save行

//输出到浏览器

/*browser_export('Excel7','export.xlsx');

$objWriter->save('php://output');

function browser_export($type,$exportName) {

    //输出到浏览器

    if ($type == 'Excel5') {

        header('Content-Type: application/vnd.ms-excel');//excel03

    } else {

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//excel07

    }

    header('Content-Disposition: attachment;filename="'.$exportName.'"');//文件名称

    header('Cache-Control: max-age=0');//禁止浏览器缓存

}*/