從PHP網頁將資料匯出成Excel檔 @ 筆記本

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

雖然網路上已經有重點說明，但對於習慣使用DW的我，還是不能符合需求....

自己試了很久....才試出我要的方式 -- 呼叫資料庫的資料，按連結轉成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>

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>";

}

範例檔案

其他教學

http://www.programmer-club.com.tw/ShowSameTitleN/php/4088.html

http://272586.blogspot.com/2007/06/phpexcel.html

http://www.flycan.com/board/post7308.html

此文引用自 http://nknuahuang.spaces.live.com