导出excel数据-蒲公英云

导出excel数据

青旅半醒 2022-07-15 07:30 306阅读 0赞

*@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 Hs”);

$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;
\}