[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的按鈕
| 匯出excel
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
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);
?>
...
3.將資料顯示於table內，重複區域
...
| |
...
4.複製下面語法至內最後面
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 "
";
for ($i=0;$i<$totalRows_Recordset2;$i++)
{
$row_Recordset2 = mysql_fetch_array($Recordset2);
echo "
| ".$row_Recordset2['aid']." | ".$row_Recordset2['aname']." | ".$row_Recordset2['classname']." |
";
$j=$i+1;
";
}
