Home >Backend Development >PHP Tutorial >Export the contents of the database to excel in php_PHP tutorial

Export the contents of the database to excel in php_PHP tutorial

WBOYOriginal: 2016-07-13 10:46:41929browse

To export the content in the mysql database to excel, we can simply use the header to output the csv format document to export the excel file from mysql. Let's take a look at an example.

The core code is here

The code is as follows

Copy code

代码如下

复制代码

header("Content-Type: application/vnd.ms-excel; charset=UTF-8");
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header("Content-Disposition: attachment;filename=".$title .".xls ");
header("Content-Transfer-Encoding: binary ");

以下是用php将数据库导出成excel，测试完全成功

代码如下

复制代码

$DB_Server = "localhost";
$DB_Username = "root";
$DB_Password = www.bKjia.c0m;
$DB_DBName = "ishop";
$DB_TBLName = "oi_mall_payment";

$savename = date("YmjHis");
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect.");
mysql_query("Set Names 'gbk'");
$file_type = "vnd.ms-excel";
$file_ending = "xls";
header("Content-Type: application/$file_type;charset=big5");
header("Content-Disposition: attachment; filename=".$savename.".$file_ending");
//header("Pragma: no-cache");

$now_date = date("Y-m-j H:i:s");
$title = "数据库名:$DB_DBName,数据表:$DB_TBLName,备份日期:$now_date";

$sql = "Select * from $DB_TBLName";
$ALT_Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database");
$result = @mysql_query($sql,$Connect) or die(mysql_error());

echo("$titlen");
$sep = "t";
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 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";
print(trim($schema_insert));
print "n";
$i++;
}
return (true);
?>

复制代码

$DB_Server = "localhost";
$DB_Username = "root";
$DB_Password = www.bKjia.c0m;
$DB_DBName = "ishop";
$DB_TBLName = "oi_mall_payment";

$savename = date("YmjHis");
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect.");
mysql_query("Set Names 'gbk'");
$file_type = "vnd.ms-excel";
$file_ending = "xls";
header("Content-Type: application/$file_type;charset=big5");
header("Content-Disposition: attachment; filename=".$savename.".$file_ending");
//header("Pragma: no-cache");

$now_date = date("Y-m-j H:i:s");
$title = "数据库名:$DB_DBName,数据表:$DB_TBLName,备份日期:$now_date";

$sql = "Select * from $DB_TBLName";
$ALT_Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database");
$result = @mysql_query($sql,$Connect) or die(mysql_error());

echo("$titlen");
$sep = "t";
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         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";
    print(trim($schema_insert));
    print "n";
    $i++;
}
return (true);
?>

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：Yii method to implement master-slave reading and writing separation in multiple databases_php tutorialNext article：Yii method to implement master-slave reading and writing separation in multiple databases_php tutorial

See more

Export the contents of the database to excel in php_PHP tutorial

Related articles