如何利用PHP將MySQl裡的資料匯出至excel檔，並用郵件夾帶附件的方式把excel檔寄出?

如何利用PHP將MySQl裡的資料匯出至excel檔，並用郵件夾帶附件的方式把excel檔寄出?

這裡我們使用 PEAR 擴充包 Spreadsheet_Excel_Writer，PEAR - PHP Extension and Application Repository 的縮寫

首先先先安裝 Spreadsheet_Excel_Writer

pear install Spreadsheet_Excel_Writer

更多有關PEAR 擴充包 Spreadsheet_Excel_Writer資訊請參考
http://pear.php.net/package/Spreadsheet_Excel_Writer/

安裝完成後，我們開始進行PHP程式撰寫

// require 必要檔案
require_once 'Spreadsheet/Excel/Writer.php';

// 打開緩衝區，先將資料緩存
ob_start();

$workbook = new Spreadsheet_Excel_Writer();
$workbook->setVersion(8, 'utf-8');
$worksheet =& $workbook->addWorksheet('Worksheet 1');
$worksheet->setInputEncoding('utf-8');

foreach ((array)$aUser as $k => $v) {
  $i = 0;
  $worksheet->write($k, $i++, $v['username']);
  $worksheet->write($k, $i++, $v['name']);
  $worksheet->write($k, $i++, $v['nick']);
  $worksheet->write($k, $i++, $v['birthday']);
  $worksheet->writeString($k, $i++, $v['phone']);
  $worksheet->writeString($k, $i++, $v['mobile']);
  $worksheet->write($k, $i++, $v['mail']);
}
$workbook->close();

// 將緩存資料存入 $sXls 變數中
$sXls = ob_get_contents();
// 結束緩衝區
ob_end_clean();
// 將緩存資料以 BASE64 編碼
$sXls = chunk_split(base64_encode($sXls));

// mail() 包含附件的方式大約如下
$aFile = array('name'=>'ms-excel', 'type'=>'application/vnd.ms-excel');
$sBoundary = uniqid( ""); // 定義分界線
$sContent = '請看附件!';
	
$sBody = '--'.$sBoundary."\r\n";
$sBody.= "Content-type: text/plain; charset=iso-8859-1\r\n";
$sBody.= "Content-transfer-encoding: 8bit\r\n";
$sBody.= $sContent."\r\n";
$sBody.= '--'.$sBoundary."\r\n";
$sBody.= 'Content-type: '.$aFile['type'].'; name='.$aFile['name']."\r\n";
$sBody.= 'Content-disposition: attachment; filename='.$aFile['name']."\r\n";
$sBody.= "Content-transfer-encoding: base64\r\n";
$sBody.= $sXls."\r\n";
$sBody.= '--'.$sBoundary."--\r\n";
	
mail($sTo, $sSubject, $sBody, $sHead);
// 打完收工!