從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的按鈕

---

 
  td> 

編號	種類	名稱< /td>

 

匯出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內，重複區域

---

...

 
  

編號	種類	名稱< /td>
>

...

---

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 "& lt;td>名稱

編號

種類

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

}
echo "

";
}

---

範例檔案

資料來源