从数据库创建 excel 并作为电子邮件附件发送

这是我从数据库表中获取数据的代码

$sql = "select * from tbl_input_values where 1";
$result = mysql_query($sql);
$resultRows = mysql_num_rows($result);

以下是我用来将文件下载为 excel 格式的标题:

header("Content-Type: application/vnd.ms-excel");   
header("Content-Disposition: attachment; filename=test.xls");

header("Pragma: no-cache");
header("Expires: 0");

我获取记录的代码如下:

if($resultRows>0){
    $sep = "t"; //tabbed character
    for ($i = 0; $i < mysql_num_fields($result); $i++) {
        echo mysql_field_name($result,$i) . "t";
    }
    print("n");
    while($row = mysql_fetch_row($result)){
        $schema_insert = "";
        for($j=0; $j<mysql_num_fields($result);$j++){
            if(!isset($row[$j]))
                $schema_insert .= "NULL".$sep;
            elseif ($row[$j] != "")
                $schema_insert .= "$row[$j]".$sep;
            else
                $schema_insert .= "".$sep;
        }
        $schema_insert = str_replace($sep."$", "", $schema_insert);
        $schema_insert = preg_replace("/rn|nr|n|r/", " ", $schema_insert);
        $schema_insert .= "t"; 
        print(trim(str_replace(',', " ", $schema_insert)));
        print "n";
    }
}

代码运行良好。

所以现在我必须通过电子邮件发送这个附件而不是下载它。表示当用户想要创建 excel 文件时,excel 将通过电子邮件发送到给定的电子邮件 ID。

那么使用电子邮件功能的更好方法是什么?

stack overflow Creating excel from database and sending as email attachment
原文答案

答案:

作者头像

可能它可以帮助你....

if($resultRows>0){
$sep = "t"; //tabbed character
for ($i = 0; $i < mysql_num_fields($result); $i++) {
    echo mysql_field_name($result,$i) . "t";
}
print("n");
while($row = mysql_fetch_row($result)){
    $schema_insert = "";
    for($j=0; $j<mysql_num_fields($result);$j++){
        if(!isset($row[$j]))
            $schema_insert .= "NULL".$sep;
        elseif ($row[$j] != "")
            $schema_insert .= "$row[$j]".$sep;
        else
            $schema_insert .= "".$sep;
    }
    $schema_insert = str_replace($sep."$", "", $schema_insert);
    $schema_insert = preg_replace("/rn|nr|n|r/", " ", $schema_insert);
    $schema_insert .= "t"; 
    print(trim(str_replace(',', " ", $schema_insert)));
    print "n";
}

 file_put_contents('$path_to_excelfile', $schema_insert);
  }

$to = 'youraddress@example.com';

     $subject = 'Test email with attachment';

     $random_hash = md5(date('r', time()));

     $headers = "From: webmaster@example.comrnReply-To: webmaster@example.com";

     $headers .= "rnContent-Type: multipart/mixed; boundary="PHP-mixed-".$random_hash.""";

     $attachment = chunk_split(base64_encode(file_get_contents('path to file')));

   mail( $to, $subject, $message, $headers ); 

参考 http://webcheatsheet.com/PHP/send_email_text_html_attachment.php

作者头像
**Code to create excel in php:**
$dtime=date('Y-m-d H-i-s');
$dtimeFile=date('Y-m-d-H-i-s');
$headerTab ="Col1 t Col2t Col3n";
$rowRecords='';
$rowRecords .=preg_replace("/r|n|t/"," ",$Col1)."t".preg_replace("/r|n|t/", " ",$Col2)."t".preg_replace("/r|n|t/", " ",Col3). "tn";
date_default_timezone_set('America/Los_Angeles');
$filename="Your File Name-".$dtimeFile.".xls";
$path='/pathOfFile/';
$csv_handler = fopen ($path.$filename,'w');
fwrite ($csv_handler,$headerTab);
fwrite ($csv_handler,$rowRecords);
fclose ($csv_handler);

**Code to send html email with attached excel in php:**
$file = $path.$filename;
$file_size = filesize($file);
$handle = fopen($file, "r");
$content = fread($handle, $file_size);
fclose($handle);
$content = chunk_split(base64_encode($content));
$uid = md5(uniqid(time()));
$headers = "From: from@gmail.com"."rn";
$headers.= "Bcc: bcc@gmail.com"."rn";
$headers.= "MIME-Version: 1.0rn";
$headers.= "Content-Type: multipart/mixed; boundary="".$uid.""rnrn";
$headers .= "This is a multi-part message in MIME format.rn";
$headers .= "--".$uid."rn";
$headers .= "Content-type:text/html; charset=iso-8859-1rn";
$headers .= "Content-Transfer-Encoding: 7bitrnrn";
$headers .= $msg."rnrn";
$headers .= "--".$uid."rn";
$headers .= "Content-Type: application/octet-stream; name="".$filename.""rn";
$headers .= "Content-Transfer-Encoding: base64rn";
$headers .= "Content-Disposition: attachment; filename="".$filename.""rnrn";
$headers .= $content."rnrn";
$headers .= "--".$uid."--"; 

$date=date("Y-m-d");
if(mail($to,"Mail heading--".$date,$msg,$headers)){
    echo "Mailed successfully";
}
else
{
    echo "Mailed couldn't be sent"; 
}