php7.27: export excel from mysql

https://stackoverflow.com/questions/15699301/export-mysql-data-to-excel-in-php

https://github.com/PHPOffice

https://www.ibm.com/developerworks/library/os-phpexcel/index.html

<!doctype html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">		
<meta charset="utf-8">
<title>execl</title>
<meta name="keywords" content="geovindu">
<meta name="description" content="涂聚文">		
</head>

<body>
<?php
$servername = "localhost";  //127.0.0.1:3306
$username = "root";
$password = "8888";
$dbname = "sakila";
//mysql and db connection

$con = new mysqli($servername, $username, $password, $dbname);

if ($con->connect_error) {  //error check
    die("Connection failed: " . $con->connect_error);
}
else
{

}


$DB_TBLName = "dt_sample"; 
$filename = "geovindu";  //your_file_name
$file_ending = "utf-8";   //file_extention
header("Content-Type: application/vnd.ms-excel;charset=utf-8");
//header("Content-Type: application/xls");  
//header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=$filename.xls");  
header("Pragma: no-cache"); 
header("Expires: 0");

$sep = "\t";

$sql="SELECT * FROM dt_sample"; 
$resultt = $con->query($sql);
print("<table><tr>"); 
while ($property = mysqli_fetch_field($resultt)) { //fetch table field name
    echo "<td>".$property->name."</td>";
}
print("</tr>");   

while($row = mysqli_fetch_row($resultt))  //fetch_table_data
{
	print("<tr>");
    $schema_insert = "";
    for($j=0; $j< mysqli_num_fields($resultt);$j++)
    {
        if(!isset($row[$j]))
            $schema_insert .="<td>". "NULL".$sep."</td>";
        elseif ($row[$j] != "")
            $schema_insert .="<td>"."$row[$j]".$sep."</td>";
        else
            $schema_insert .= "<td>".$sep."</td>";
    }
    $schema_insert = str_replace($sep."$", "", $schema_insert);
    $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
    //$schema_insert .= "</tr>";
    print(trim($schema_insert));
    print("</tr>");
}	
print "</table>";	
?>
</body>
</html>

　　

posted @ 2018-08-10 16:19  ®Geovin Du Dream Park™  阅读(299)  评论(0)    收藏  举报