從PHP網頁將資料匯出成Excel檔

woff · 發表於 2012-6-13 23:55:40

自己試了很久....才試出我要的方式 -- 呼叫資料庫的資料，按連結轉成excel檔
做法：
資料庫名稱test，資料表ta和tb
資料表ta

aid	aclass	aname
1	1	蘋果
2	1	鳳梨
3	2	波蔡
4	2	小白菜
5	3	豬肉
6	3	牛肉
7	4	金魚
8	4	吳郭魚

資料表tb

aclass	classname
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>";
}

複製代碼

帳號		自動登錄		找回密碼
密碼				註冊

[教學] 從PHP網頁將資料匯出成Excel檔