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
<html> <head> <title>ShotDev.Com Tutorial</title> </head> <body> <? $objConnect = mysql_connect("localhost","root","root") or die(mysql_error()); $objDB = mysql_select_db("mydatabase"); $strSQL = "SELECT * FROM customer"; $objQuery = mysql_query($strSQL); if($objQuery) { //*** Get Document Path ***// $strPath = realpath(basename(getenv($_SERVER["SCRIPT_NAME"])))."/MyXls"; // C:/AppServ/www/myphp //*** Excel Document Root ***// $strFileName = "MyExcel.xls"; //*** Connect to Excel.Application ***// $xlApp = new COM("Excel.Application"); $xlBook = $xlApp->Workbooks->Add(); //*** Create Sheet 1 ***// $xlBook->Worksheets(1)->Name = "My Customer"; $xlBook->Worksheets(1)->Select; //*** Width & Height (A1:A1) ***// $xlApp->ActiveSheet->Range("A1:A1")->ColumnWidth = 10.0; $xlApp->ActiveSheet->Range("B1:B1")->ColumnWidth = 13.0; $xlApp->ActiveSheet->Range("C1:C1")->ColumnWidth = 23.0; $xlApp->ActiveSheet->Range("D1:D1")->ColumnWidth = 12.0; $xlApp->ActiveSheet->Range("E1:E1")->ColumnWidth = 13.0; $xlApp->ActiveSheet->Range("F1:F1")->ColumnWidth = 12.0; //*** Report Title ***// $xlApp->ActiveSheet->Range("A1:F1")->BORDERS->Weight = 1; $xlApp->ActiveSheet->Range("A1:F1")->MergeCells = True; $xlApp->ActiveSheet->Range("A1:F1")->Font->Bold = True; $xlApp->ActiveSheet->Range("A1:F1")->Font->Size = 20; $xlApp->ActiveSheet->Range("A1:F1")->HorizontalAlignment = -4108; $xlApp->ActiveSheet->Cells(1,1)->Value = "Customer Report"; //*** Header ***// $xlApp->ActiveSheet->Cells(3,1)->Value = "CustomerID"; $xlApp->ActiveSheet->Cells(3,1)->Font->Bold = True; $xlApp->ActiveSheet->Cells(3,1)->VerticalAlignment = -4108; $xlApp->ActiveSheet->Cells(3,1)->HorizontalAlignment = -4108; $xlApp->ActiveSheet->Cells(3,1)->BORDERS->Weight = 1; $xlApp->ActiveSheet->Cells(3,2)->Value = "Name"; $xlApp->ActiveSheet->Cells(3,2)->Font->Bold = True; $xlApp->ActiveSheet->Cells(3,2)->VerticalAlignment = -4108; $xlApp->ActiveSheet->Cells(3,2)->HorizontalAlignment = -4108; $xlApp->ActiveSheet->Cells(3,2)->BORDERS->Weight = 1; $xlApp->ActiveSheet->Cells(3,3)->Value = "Email"; $xlApp->ActiveSheet->Cells(3,3)->Font->Bold = True; $xlApp->ActiveSheet->Cells(3,3)->VerticalAlignment = -4108; $xlApp->ActiveSheet->Cells(3,3)->HorizontalAlignment = -4108; $xlApp->ActiveSheet->Cells(3,3)->BORDERS->Weight = 1; $xlApp->ActiveSheet->Cells(3,4)->Value = "CountryCode"; $xlApp->ActiveSheet->Cells(3,4)->Font->Bold = True; $xlApp->ActiveSheet->Cells(3,4)->VerticalAlignment = -4108; $xlApp->ActiveSheet->Cells(3,4)->HorizontalAlignment = -4108; $xlApp->ActiveSheet->Cells(3,4)->BORDERS->Weight = 1; $xlApp->ActiveSheet->Cells(3,5)->Value = "Budget"; $xlApp->ActiveSheet->Cells(3,5)->Font->Bold = True; $xlApp->ActiveSheet->Cells(3,5)->VerticalAlignment = -4108; $xlApp->ActiveSheet->Cells(3,5)->HorizontalAlignment = -4108; $xlApp->ActiveSheet->Cells(3,5)->BORDERS->Weight = 1; $xlApp->ActiveSheet->Cells(3,6)->Value = "Used"; $xlApp->ActiveSheet->Cells(3,6)->Font->Bold = True; $xlApp->ActiveSheet->Cells(3,6)->VerticalAlignment = -4108; $xlApp->ActiveSheet->Cells(3,6)->HorizontalAlignment = -4108; $xlApp->ActiveSheet->Cells(3,6)->BORDERS->Weight = 1; //***********// $intRows = 4; while($objResult = mysql_fetch_array($objQuery)) { //*** Detail ***// $xlApp->ActiveSheet->Cells($intRows,1)->Value = $objResult["CustomerID"]; $xlApp->ActiveSheet->Cells($intRows,1)->BORDERS->Weight = 1; $xlApp->ActiveSheet->Cells($intRows,1)->HorizontalAlignment = -4108; $xlApp->ActiveSheet->Cells($intRows,2)->Value = $objResult["Name"]; $xlApp->ActiveSheet->Cells($intRows,2)->BORDERS->Weight = 1; $xlApp->ActiveSheet->Cells($intRows,3)->Value = $objResult["Email"]; $xlApp->ActiveSheet->Cells($intRows,3)->BORDERS->Weight = 1; $xlApp->ActiveSheet->Cells($intRows,4)->Value = $objResult["CountryCode"]; $xlApp->ActiveSheet->Cells($intRows,4)->HorizontalAlignment = -4108; $xlApp->ActiveSheet->Cells($intRows,4)->BORDERS->Weight = 1; $xlApp->ActiveSheet->Cells($intRows,5)->Value = $objResult["Budget"]; $xlApp->ActiveSheet->Cells($intRows,5)->BORDERS->Weight = 1; $xlApp->ActiveSheet->Cells($intRows,5)->NumberFormat = "$#,##0.00"; $xlApp->ActiveSheet->Cells($intRows,6)->Value = $objResult["Used"]; $xlApp->ActiveSheet->Cells($intRows,6)->BORDERS->Weight = 1; $intRows++; } @unlink($strFileName); //*** Delete old files ***// $xlBook->SaveAs($strPath."/".$strFileName); //*** Save to Path ***// //*** Close & Quit ***// $xlApp->Application->Quit(); $xlApp = null; $xlBook = null; $xlSheet1 = null; } mysql_close($objConnect); //*************** Send Email ***************// $strTo = "member@shotdev.com"; $strSubject = "Excel Report"; $strMessage = "Download MyXls.xls for Excel Report"; //*** Uniqid Session ***// $strSid = md5(uniqid(time())); $strHeader = ""; $strHeader .= "From: Mr.Weerachai Nukitram<webmaster@shotdev.com>\nReply-To: webmaster@shotdev.com\n"; $strHeader .= "Cc: Mr.Surachai Sirisart<surachai@shotdev.com>"; $strHeader .= "Bcc: webmaster@shotdev.com"; $strHeader .= "MIME-Version: 1.0\n"; $strHeader .= "Content-Type: multipart/mixed; boundary=\"".$strSid."\"\n\n"; $strHeader .= "This is a multi-part message in MIME format.\n"; $strHeader .= "--".$strSid."\n"; $strHeader .= "Content-type: text/html; charset=windows-874\n"; // or UTF-8 // $strHeader .= "Content-Transfer-Encoding: 7bit\n\n"; $strHeader .= $strMessage."\n\n"; $strContent1 = chunk_split(base64_encode(file_get_contents("MyXls/MyExcel.xls"))); $strHeader .= "--".$strSid."\n"; $strHeader .= "Content-Type: application/octet-stream; name=\"MyExcel.xls\"\n"; $strHeader .= "Content-Transfer-Encoding: base64\n"; $strHeader .= "Content-Disposition: attachment; filename=\"MyExcel.xls\"\n\n"; $strHeader .= $strContent1."\n\n"; $flgSend = @mail($strTo,$strSubject,null,$strHeader); // @ = No Show Error // if($flgSend) { echo "Excel Generated & Email Sending."; } else { echo "Cannot send mail."; } ?> </body> </html>
Create a php file and save to path root-path/myphp/
Run
http://localhost/myphp/php_excel_mail.php
Screenshot