导出excel数据

<?

/*

*@author:dudj

*@time:20160331

*@effect:通过按钮实现数据库数据的导出

*/

include "connection.class.php";

$DB_Server = '127.0.0.1';

$DB_Username = 'root';

$DB_Password = '123456';

$DB_DBName = 'tp_dudj';

$DB_TBLName = 'think_role';

$connection = new connection(array(

    'DB_Server'=>$DB_Server,

    'DB_Username'=>$DB_Username,

    'DB_Password'=>$DB_Password,

    'DB_DBName'=>$DB_DBName,

    'DB_TBLName'=>$DB_TBLName,

));

$con = $connection->connect();

//导出excel保存的名字

$savename = date("YmjHis");

//文件类型

$file_type = "vnd.ms-excel";

//以什么结尾

$file_ending = "xls";

//header头信息

header("Content-Type: application/$file_type;charset=UTf-8");

header("Content-Disposition: attachment; filename=".$savename.".$file_ending");

//备份日期

$now_date = date("Y-m-j H:i:s");

$title = "数据库名:$DB_DBName,数据表:$DB_TBLName,备份日期:$now_date";

//因为excel导出的数据 被查看的时候 是gbk的因此转码

$title = iconv("utf-8", "gb2312", $title);

$sql = "Select * from $DB_TBLName";

$result = mysql_query($sql,$con) or die(mysql_error());

echo("$title\n");

$sep = "\t";

//mysql_num_fields 获取字段

for ($i = 0; $i < mysql_num_fields($result); $i++) {

    echo mysql_field_name($result,$i) . "\t";

}

print("\n");

$i = 0;

while($row = mysql_fetch_row($result)) {

    $schema_insert = "";

    for($j=0; $j<mysql_num_fields($result);$j++) {

        if(!isset($row[$j]))

            $schema_insert .= "NULL".$sep;

        elseif ($row[$j] != "")

            $schema_insert .= "$row[$j]".$sep;

        else

            $schema_insert .= "".$sep;

    }

    $schema_insert = str_replace($sep."$", "", $schema_insert);

    $schema_insert .= "\t";

    $schema_insert = iconv("utf-8", "gb2312", $schema_insert);

    print(trim($schema_insert));

    print "\n";

    $i++;

}

exit;

?>

连接数据库

<?php

class connection

{

    const MSGTYPE_TEXT = 'text';

    private $DB_Server = "127.0.0.1";

    private $DB_Username = "root";

    private $DB_Password = "123456";

    private $DB_DBName = "tp_dudj";

    private $DB_TBLName = "think_role";

   

    public function __construct($options)

    {

        $this->DB_Server = isset($options['DB_Server'])?$options['DB_Server']:'';

        $this->DB_Username = isset($options['DB_Username'])?$options['DB_Username']:'';

        $this->DB_Password = isset($options['DB_Password'])?$options['DB_Password']:'';

        $this->DB_DBName = isset($options['DB_DBName'])?$options['DB_DBName']:'';

        $this->DB_TBLName = isset($options['DB_TBLName'])?$options['DB_TBLName']:'';

    }

    public function connect()

    {

        $con = mysql_connect($this->DB_Server,$this->DB_Username,$this->DB_Password) or die("数据库连接失败");

        mysql_select_db($this->DB_DBName,$con);

        mysql_query("set names utf8",$con);

        return $con;

    }

}

?>