TShopping

 找回密碼
 註冊
搜索
查看: 996|回復: 0

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

[複製鏈接]
發表於 2012-6-13 23:55:40 | 顯示全部樓層 |閱讀模式
 
Push to Facebook Push to Plurk Push to Twitter 
自己試了很久....才試出我要的方式 -- 呼叫資料庫的資料,按連結轉成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的按鈕


  1. <html>
  2. <head><title> 匯出excel檔</title></head>
  3. <body>
  4. <table width="100%" border="1">
  5.   <tr><td>編號</td><td>種類</td><td>名稱< /td></tr>
  6.   <tr><td>&nbsp;</td>td>&nbsp;</td><td>&nbsp;</td></tr>
  7. </table>
  8. <p><a href='test.php?act=download'>匯出excel</a></p>
  9. </body>
  10. </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


  1. <?php require_once('Connections/testbata.php'); ?>
  2. <?php
  3. if (!function_exists("GetSQLValueString")) {
  4. function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
  5. {
  6.   $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  7.   $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
  8.   switch ($theType) {
  9.     case "text":
  10.       $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
  11.       break;   
  12.     case "long":
  13.     case "int":
  14.       $theValue = ($theValue != "") ? intval($theValue) : "NULL";
  15.       break;
  16.     case "double":
  17.       $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
  18.       break;
  19.     case "date":
  20.       $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
  21.       break;
  22.     case "defined":
  23.       $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
  24.       break;
  25.   }
  26.   return $theValue;
  27. }
  28. }

  29. mysql_select_db($database_testbata, $testbata);
  30. $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";
  31. $Recordset1 = mysql_query($query_Recordset1, $testbata) or die(mysql_error());
  32. $row_Recordset1 = mysql_fetch_assoc($Recordset1);
  33. $totalRows_Recordset1 = mysql_num_rows($Recordset1);

  34. mysql_select_db($database_testbata, $testbata);
  35. $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";
  36. $Recordset2 = mysql_query($query_Recordset2, $testbata) or die(mysql_error());
  37. $row_Recordset2 = mysql_fetch_assoc($Recordset2);
  38. $totalRows_Recordset2 = mysql_num_rows($Recordset2);
  39. ?>
  40. <html>...</html>
複製代碼



3.將資料顯示於table內,重複區域


  1. <?php .... ?>
  2. <html>...<body>
  3. <table width="100%" border="1">
  4.   <tr><td>編號</td><td>種類</td><td>名稱< /td></tr>
  5.   <?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)); ?>
  6. </table>
  7. ...</html>
複製代碼



4.複製下面語法至<?php .... ?>內最後面

  1. if ($_GET['act']=='download') {
  2.   downloadxls();
  3.   die();
  4. }
  5. function downloadxls(){
  6. $filename="test.xls";
  7. header("Content-disposition: filename=$filename");
  8. header("Content-type: application/octetstream");
  9. header("Pragma: no-cache");
  10. header("Expires: 0");
  11. }
複製代碼



5.複製table語法貼於function內;剪下php內的Recordset2部分,貼於function內;並做修改


  1. function downloadxls(){
  2. $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";  
  3. $Recordset2 = mysql_query($query_Recordset2);
  4. $totalRows_Recordset2 = mysql_num_rows($Recordset2);

  5. $filename="test.xls";
  6. header("Content-disposition: filename=$filename");
  7. header("Content-type: application/octetstream");
  8. header("Pragma: no-cache");
  9. header("Expires: 0");

  10. echo "<table border=1px><tr><td>編號</td><td>種類</td>& lt;td>名稱</td></tr>";
  11. for ($i=0;$i<$totalRows_Recordset2;$i++)
  12. {
  13. $row_Recordset2 = mysql_fetch_array($Recordset2);
  14. echo "<tr><td>".$row_Recordset2['aid']."</td><td>".$row_Recordset2['aname']."</td><td>".$row_Recordset2['classname']."</td></tr>";
  15. $j=$i+1;

  16. }
  17. echo "</table>";
  18. }
複製代碼



 

臉書網友討論
您需要登錄後才可以回帖 登錄 | 註冊 |

本版積分規則



Archiver|手機版|小黑屋|免責聲明|TShopping

GMT+8, 2016-12-9 03:50 , Processed in 0.080510 second(s), 18 queries .

本論壇言論純屬發表者個人意見,與 TShopping綜合論壇 立場無關 如有意見侵犯了您的權益 請寫信聯絡我們。

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回復 返回頂部 返回列表