Nirutechnolabs

Learning & Integrating web technology and code help directory

How to use PHP & Export Data from Database to Excel and Send Email Attachment (Excel.Application)

Unknown 01:39 No comments
How to use PHP & Export Data from Database to Excel and Send Email Attachment (Excel.Application)The Learn / tutorial php programming how to using  PHP Export Data from Database to Excel and Send Email Attachment
ShotDev Focus:
- PHP  & Export Data from Database to Excel and Send Email Attachment.
Example
php_excel_mail.php
  1. <html>  
  2. <head>  
  3. <title>ShotDev.Com Tutorial</title>  
  4. </head>  
  5. <body>  
  6. <?  
  7. $objConnect = mysql_connect("localhost","root","root") or die(mysql_error());  
  8. $objDB = mysql_select_db("mydatabase");  
  9. $strSQL = "SELECT * FROM customer";  
  10. $objQuery = mysql_query($strSQL);  
  11. if($objQuery)  
  12. {  
  13. //*** Get Document Path ***//  
  14. $strPath = realpath(basename(getenv($_SERVER["SCRIPT_NAME"])))."/MyXls"; // C:/AppServ/www/myphp  
  15.   
  16. //*** Excel Document Root ***//  
  17. $strFileName = "MyExcel.xls";  
  18.   
  19. //*** Connect to Excel.Application ***//  
  20. $xlApp = new COM("Excel.Application");  
  21. $xlBook = $xlApp->Workbooks->Add();  
  22.   
  23. //*** Create Sheet 1 ***//  
  24. $xlBook->Worksheets(1)->Name = "My Customer";  
  25. $xlBook->Worksheets(1)->Select;  
  26.   
  27. //*** Width & Height (A1:A1) ***//  
  28. $xlApp->ActiveSheet->Range("A1:A1")->ColumnWidth = 10.0;  
  29. $xlApp->ActiveSheet->Range("B1:B1")->ColumnWidth = 13.0;  
  30. $xlApp->ActiveSheet->Range("C1:C1")->ColumnWidth = 23.0;  
  31. $xlApp->ActiveSheet->Range("D1:D1")->ColumnWidth = 12.0;  
  32. $xlApp->ActiveSheet->Range("E1:E1")->ColumnWidth = 13.0;  
  33. $xlApp->ActiveSheet->Range("F1:F1")->ColumnWidth = 12.0;  
  34.   
  35. //*** Report Title ***//  
  36. $xlApp->ActiveSheet->Range("A1:F1")->BORDERS->Weight = 1;  
  37. $xlApp->ActiveSheet->Range("A1:F1")->MergeCells = True;  
  38. $xlApp->ActiveSheet->Range("A1:F1")->Font->Bold = True;  
  39. $xlApp->ActiveSheet->Range("A1:F1")->Font->Size = 20;  
  40. $xlApp->ActiveSheet->Range("A1:F1")->HorizontalAlignment = -4108;  
  41. $xlApp->ActiveSheet->Cells(1,1)->Value = "Customer Report";  
  42.   
  43. //*** Header ***//  
  44. $xlApp->ActiveSheet->Cells(3,1)->Value = "CustomerID";  
  45. $xlApp->ActiveSheet->Cells(3,1)->Font->Bold = True;  
  46. $xlApp->ActiveSheet->Cells(3,1)->VerticalAlignment = -4108;  
  47. $xlApp->ActiveSheet->Cells(3,1)->HorizontalAlignment = -4108;  
  48. $xlApp->ActiveSheet->Cells(3,1)->BORDERS->Weight = 1;  
  49.   
  50. $xlApp->ActiveSheet->Cells(3,2)->Value = "Name";  
  51. $xlApp->ActiveSheet->Cells(3,2)->Font->Bold = True;  
  52. $xlApp->ActiveSheet->Cells(3,2)->VerticalAlignment = -4108;  
  53. $xlApp->ActiveSheet->Cells(3,2)->HorizontalAlignment = -4108;  
  54. $xlApp->ActiveSheet->Cells(3,2)->BORDERS->Weight = 1;  
  55.   
  56. $xlApp->ActiveSheet->Cells(3,3)->Value = "Email";  
  57. $xlApp->ActiveSheet->Cells(3,3)->Font->Bold = True;  
  58. $xlApp->ActiveSheet->Cells(3,3)->VerticalAlignment = -4108;  
  59. $xlApp->ActiveSheet->Cells(3,3)->HorizontalAlignment = -4108;  
  60. $xlApp->ActiveSheet->Cells(3,3)->BORDERS->Weight = 1;  
  61.   
  62. $xlApp->ActiveSheet->Cells(3,4)->Value = "CountryCode";  
  63. $xlApp->ActiveSheet->Cells(3,4)->Font->Bold = True;  
  64. $xlApp->ActiveSheet->Cells(3,4)->VerticalAlignment = -4108;  
  65. $xlApp->ActiveSheet->Cells(3,4)->HorizontalAlignment = -4108;  
  66. $xlApp->ActiveSheet->Cells(3,4)->BORDERS->Weight = 1;  
  67.   
  68. $xlApp->ActiveSheet->Cells(3,5)->Value = "Budget";  
  69. $xlApp->ActiveSheet->Cells(3,5)->Font->Bold = True;  
  70. $xlApp->ActiveSheet->Cells(3,5)->VerticalAlignment = -4108;  
  71. $xlApp->ActiveSheet->Cells(3,5)->HorizontalAlignment = -4108;  
  72. $xlApp->ActiveSheet->Cells(3,5)->BORDERS->Weight = 1;  
  73.   
  74. $xlApp->ActiveSheet->Cells(3,6)->Value = "Used";  
  75. $xlApp->ActiveSheet->Cells(3,6)->Font->Bold = True;  
  76. $xlApp->ActiveSheet->Cells(3,6)->VerticalAlignment = -4108;  
  77. $xlApp->ActiveSheet->Cells(3,6)->HorizontalAlignment = -4108;  
  78. $xlApp->ActiveSheet->Cells(3,6)->BORDERS->Weight = 1;  
  79.   
  80. //***********//  
  81.   
  82. $intRows = 4;  
  83. while($objResult = mysql_fetch_array($objQuery))  
  84. {  
  85.   
  86. //*** Detail ***//  
  87. $xlApp->ActiveSheet->Cells($intRows,1)->Value = $objResult["CustomerID"];  
  88. $xlApp->ActiveSheet->Cells($intRows,1)->BORDERS->Weight = 1;  
  89. $xlApp->ActiveSheet->Cells($intRows,1)->HorizontalAlignment = -4108;  
  90.   
  91. $xlApp->ActiveSheet->Cells($intRows,2)->Value = $objResult["Name"];  
  92. $xlApp->ActiveSheet->Cells($intRows,2)->BORDERS->Weight = 1;  
  93.   
  94. $xlApp->ActiveSheet->Cells($intRows,3)->Value = $objResult["Email"];  
  95. $xlApp->ActiveSheet->Cells($intRows,3)->BORDERS->Weight = 1;  
  96.   
  97. $xlApp->ActiveSheet->Cells($intRows,4)->Value = $objResult["CountryCode"];  
  98. $xlApp->ActiveSheet->Cells($intRows,4)->HorizontalAlignment = -4108;  
  99. $xlApp->ActiveSheet->Cells($intRows,4)->BORDERS->Weight = 1;  
  100.   
  101. $xlApp->ActiveSheet->Cells($intRows,5)->Value = $objResult["Budget"];  
  102. $xlApp->ActiveSheet->Cells($intRows,5)->BORDERS->Weight = 1;  
  103. $xlApp->ActiveSheet->Cells($intRows,5)->NumberFormat = "$#,##0.00";  
  104.   
  105. $xlApp->ActiveSheet->Cells($intRows,6)->Value = $objResult["Used"];  
  106. $xlApp->ActiveSheet->Cells($intRows,6)->BORDERS->Weight = 1;  
  107.   
  108. $intRows++;  
  109. }  
  110.   
  111. @unlink($strFileName); //*** Delete old files ***//  
  112.   
  113. $xlBook->SaveAs($strPath."/".$strFileName); //*** Save to Path ***//  
  114.   
  115. //*** Close & Quit ***//  
  116. $xlApp->Application->Quit();  
  117. $xlApp = null;  
  118. $xlBook = null;  
  119. $xlSheet1 = null;  
  120.   
  121. }  
  122.   
  123. mysql_close($objConnect);  
  124.   
  125. //*************** Send Email ***************//  
  126.   
  127. $strTo = "member@shotdev.com";  
  128. $strSubject = "Excel Report";  
  129. $strMessage = "Download MyXls.xls for Excel Report";  
  130.   
  131. //*** Uniqid Session ***//  
  132. $strSid = md5(uniqid(time()));  
  133.   
  134. $strHeader = "";  
  135. $strHeader .= "From: Mr.Weerachai Nukitram<webmaster@shotdev.com>\nReply-To: webmaster@shotdev.com\n";  
  136. $strHeader .= "Cc: Mr.Surachai Sirisart<surachai@shotdev.com>";  
  137. $strHeader .= "Bcc: webmaster@shotdev.com";  
  138.   
  139. $strHeader .= "MIME-Version: 1.0\n";  
  140. $strHeader .= "Content-Type: multipart/mixed; boundary=\"".$strSid."\"\n\n";  
  141. $strHeader .= "This is a multi-part message in MIME format.\n";  
  142.   
  143. $strHeader .= "--".$strSid."\n";  
  144. $strHeader .= "Content-type: text/html; charset=windows-874\n"; // or UTF-8 //  
  145. $strHeader .= "Content-Transfer-Encoding: 7bit\n\n";  
  146. $strHeader .= $strMessage."\n\n";  
  147.   
  148. $strContent1 = chunk_split(base64_encode(file_get_contents("MyXls/MyExcel.xls")));  
  149. $strHeader .= "--".$strSid."\n";  
  150. $strHeader .= "Content-Type: application/octet-stream; name=\"MyExcel.xls\"\n";  
  151. $strHeader .= "Content-Transfer-Encoding: base64\n";  
  152. $strHeader .= "Content-Disposition: attachment; filename=\"MyExcel.xls\"\n\n";  
  153. $strHeader .= $strContent1."\n\n";  
  154.   
  155. $flgSend = @mail($strTo,$strSubject,null,$strHeader); // @ = No Show Error //  
  156. if($flgSend)  
  157. {  
  158. echo "Excel Generated & Email Sending.";  
  159. }  
  160. else  
  161. {  
  162. echo "Cannot send mail.";  
  163. }  
  164. ?>  
  165. </body>  
  166. </html>  
Create a php file and save to path root-path/myphp/
Screenshot
PHP & Add Style & Export Data from Database to Excel and Send Email Attachment

No comments :

Post a Comment

Subscribe to: Post Comments ( Atom )