從PHP網頁將資料匯出成Excel檔
自己試了很久....才試出我要的方式 -- 呼叫資料庫的資料，按連結轉成excel檔做法：
資料庫名稱test，資料表ta和tb
資料表ta
aidaclass aname
1 1蘋果
2 1鳳梨
3 2波蔡
4 2小白菜
5 3豬肉
6 3牛肉
7 4金魚
8 4吳郭魚
資料表tb
aclassclassname
1 水果
2 蔬菜
3 肉類
4 魚類
1.將表格和頁首設定好，並做一個連結，作為下載excel的按鈕
<html>
<head><title> 匯出excel檔</title></head>
<body>
<table width="100%" border="1">
<tr><td>編號</td><td>種類</td><td>名稱< /td></tr>
<tr><td> </td>td> </td><td> </td></tr>
</table>
<p><a href='test.php?act=download'>匯出excel</a></p>
</body>
</html>
2.連到資料庫，新增資料集Recordset1，用進階選項，SQL內填入：
SELECT ta.aid, ta.aname, tb.classname FROM ta LEFT JOIN tb ON ta.aclass = tb.aclass GROUP BY ta.aid, ta.aname
新增一樣的資料集，名稱為Recordset2
<?php require_once('Connections/testbata.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}
mysql_select_db($database_testbata, $testbata);
$query_Recordset1 = "SELECT ta.aid, ta.aname, tb.classname FROM ta LEFT JOIN tb ON ta.aclass = tb.aclass GROUP BY ta.aid, ta.aname";
$Recordset1 = mysql_query($query_Recordset1, $testbata) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
mysql_select_db($database_testbata, $testbata);
$query_Recordset2 = "SELECT ta.aid, ta.aname, tb.classname FROM ta LEFT JOIN tb ON ta.aclass = tb.aclass GROUP BY ta.aid, ta.aname";
$Recordset2 = mysql_query($query_Recordset2, $testbata) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);
?>
<html>...</html>
3.將資料顯示於table內，重複區域
<?php .... ?>
<html>...<body>
<table width="100%" border="1">
<tr><td>編號</td><td>種類</td><td>名稱< /td></tr>
<?php do { ?><tr><td><?php echo $row_Recordset1['aid']; ?></td><td><?php echo $row_Recordset1['aname']; ?></td><td><?php echo $row_Recordset1['classname']; ?></td></tr><?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
</table>
...</html>
4.複製下面語法至<?php .... ?>內最後面
if ($_GET['act']=='download') {
downloadxls();
die();
}
function downloadxls(){
$filename="test.xls";
header("Content-disposition: filename=$filename");
header("Content-type: application/octetstream");
header("Pragma: no-cache");
header("Expires: 0");
}
5.複製table語法貼於function內；剪下php內的Recordset2部分，貼於function內；並做修改
function downloadxls(){
$query_Recordset2 = "SELECT ta.aid, ta.aname, tb.classname FROM ta LEFT JOIN tb ON ta.aclass = tb.aclass GROUP BY ta.aid, ta.aname";
$Recordset2 = mysql_query($query_Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);
$filename="test.xls";
header("Content-disposition: filename=$filename");
header("Content-type: application/octetstream");
header("Pragma: no-cache");
header("Expires: 0");
echo "<table border=1px><tr><td>編號</td><td>種類</td>& lt;td>名稱</td></tr>";
for ($i=0;$i<$totalRows_Recordset2;$i++)
{
$row_Recordset2 = mysql_fetch_array($Recordset2);
echo "<tr><td>".$row_Recordset2['aid']."</td><td>".$row_Recordset2['aname']."</td><td>".$row_Recordset2['classname']."</td></tr>";
$j=$i+1;
}
echo "</table>";
}
頁:
[1]