PHP生成Excel报表并邮件发送：附件缺失问题求助

问题描述 #

我有一段PHP代码，可从数据库读取数据生成Excel文件，希望将该Excel作为附件发送邮件。当前问题是仅能实现文件下载，收到的邮件却无附件。收到的邮件原始内容如下：

--90215ae30d47b3d98d4505ee5035e618
Content-Transfer-Encoding: 7bit

This is a MIME encoded message.
--90215ae30d47b3d98d4505ee5035e618
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: 8bit

--90215ae30d47b3d98d4505ee5035e618
Content-Type: application/octet-stream; name="test.xls"
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="test.xls"

MQkwMzAyMDE4NTIzCU1HTkdQUDg0QTE4QzM1MVcJTUFHTkFOTyBESSBTQU4gTElPICAgICAgICAg
ICAgICAgICAgICAgIAlHSVVTRVBQRSAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgCQ==

--90215ae30d47b3d98d4505ee5035e618--

原代码：

<?php
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=test.xls");
header("Pragma: no-cache");
header("Expires: 0");
 
$db2 = db2_connect();
$sep = "\t";
 
echo "Pr0001 \t Cdclie \t CodFis \t \n ";

$data = "Select t1.Pr0001, t2.CDCLIE, t1.CODFIS FROM MYLIB.ANAGR001F AS t1"; 

$result = db2_exec($db2, $data);
 
while ($row = db2_fetch_both($result)) {
    $schema_insert = "";
    $schema_insert .= "$row[0]".$sep;
    $schema_insert .= "$row[1]".$sep;
    $schema_insert .= "$row[2]".$sep;
    
    $schema_insert = str_replace($sep."$", "", $schema_insert);
    $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
    print(trim(str_replace(',', " ", $schema_insert)));
    print "\n";
}

file_put_contents('test.xls', $schema_insert);

$to = "yanez25@libero.it";
$from = "robert_tr13@libero.it";
$subject = "test mail";
$separator = md5(date('r', time()));
// carriage return type (we use a PHP end of line constant)
$eol = PHP_EOL;

// attachment name
$filename = "test.xls";         
 
$attachment = chunk_split(base64_encode(file_get_contents('test.xls')));

// main header
$headers  = "From: ".$from.$eol;
$headers .= "MIME-Version: 1.0".$eol;
$headers .= "Content-Type: multipart/mixed; boundary=\"".$separator."\"";
 
$body = "--".$separator.$eol;
$headers .= "Content-type:text/plain; charset=iso-8859-1".$eol;
$body .= "Content-Transfer-Encoding: 7bit".$eol.$eol;
$body .= "This is a MIME encoded message.".$eol;

// message
$body .= "--".$separator.$eol;
$body .= "Content-Type: text/html; charset=\"iso-8859-1\"".$eol;
$body .= "Content-Transfer-Encoding: 8bit".$eol.$eol;

// attachment
$body .= "--".$separator.$eol;
$body .= "Content-Type: application/octet-stream;      name=\"".$filename."\"".$eol;
$body .= "Content-Transfer-Encoding: base64".$eol;
$body .= "Content-Disposition: attachment;  filename=\"".$filename."\"".$eol.$eol;
$body .= $attachment.$eol;
$body .= "--".$separator."--";

if (mail($to, $subject, $body, $headers)) {
    echo "mail send ... OK";
} else {
    echo "mail send ... ERROR";
}
?>

问题原因 #

1. Excel文件内容不完整：file_put_contents只写入了循环最后一行的数据，因为每次循环都会重置$schema_insert，之前的行未被保存。
2. 邮件头部结构错误：将Content-type:text/plain写入了主$headers，破坏了multipart/mixed的MIME结构，该类型的头部不应包含具体内容类型，内容类型需放在body的各个分段中。
3. 输出顺序冲突：开头的header和echo/print会向浏览器输出Excel内容，这部分输出会混入邮件发送流程，干扰邮件结构。

修正后的代码 #

<?php
// 先注释下载相关输出，避免干扰邮件发送
// header("Content-Type: application/vnd.ms-excel");
// header("Content-Disposition: attachment; filename=test.xls");
// header("Pragma: no-cache");
// header("Expires: 0");
 
$db2 = db2_connect();
$sep = "\t";

// 初始化完整的Excel内容变量
$excel_content = "Pr0001 \t Cdclie \t CodFis \n";

$data = "Select t1.Pr0001, t2.CDCLIE, t1.CODFIS FROM MYLIB.ANAGR001F AS t1"; 
$result = db2_exec($db2, $data);
 
while ($row = db2_fetch_both($result)) {
    $schema_insert = "";
    $schema_insert .= "$row[0]".$sep;
    $schema_insert .= "$row[1]".$sep;
    $schema_insert .= "$row[2]".$sep;
    
    $schema_insert = str_replace($sep."$", "", $schema_insert);
    $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
    $schema_insert = trim(str_replace(',', " ", $schema_insert));
    
    // 将每行数据追加到完整内容中
    $excel_content .= $schema_insert . "\n";
    // 如需同时支持浏览器下载，取消下一行注释
    // print $schema_insert . "\n";
}

// 写入完整的Excel内容到文件
file_put_contents('test.xls', $excel_content);

$to = "yanez25@libero.it";
$from = "robert_tr13@libero.it";
$subject = "test mail";
$separator = md5(date('r', time()));
$eol = PHP_EOL;

$filename = "test.xls";         
$attachment = chunk_split(base64_encode(file_get_contents('test.xls')));

// 修正邮件头部，移除错误的Content-type
$headers  = "From: ".$from.$eol;
$headers .= "MIME-Version: 1.0".$eol;
$headers .= "Content-Type: multipart/mixed; boundary=\"".$separator."\"".$eol;
 
$body = "--".$separator.$eol;
$body .= "Content-type:text/plain; charset=iso-8859-1".$eol;
$body .= "Content-Transfer-Encoding: 7bit".$eol.$eol;
$body .= "This is a MIME encoded message.".$eol;

// 添加HTML邮件内容（不需要可删除）
$body .= "--".$separator.$eol;
$body .= "Content-Type: text/html; charset=\"iso-8859-1\"".$eol;
$body .= "Content-Transfer-Encoding: 8bit".$eol.$eol;
$body .= "<p>这是测试邮件，附件是生成的Excel文件</p>".$eol;

// 附件部分，使用正确的Excel MIME类型
$body .= "--".$separator.$eol;
$body .= "Content-Type: application/vnd.ms-excel; name=\"".$filename."\"".$eol;
$body .= "Content-Transfer-Encoding: base64".$eol;
$body .= "Content-Disposition: attachment; filename=\"".$filename."\"".$eol.$eol;
$body .= $attachment.$eol;
$body .= "--".$separator."--";

if (mail($to, $subject, $body, $headers)) {
    echo "邮件发送成功";
    // 如需同时触发下载，取消下方注释
    // header("Content-Type: application/vnd.ms-excel");
    // header("Content-Disposition: attachment; filename=test.xls");
    // echo $excel_content;
} else {
    echo "邮件发送失败";
}
?>

额外提示 #

• 如需同时支持浏览器下载和邮件发送，可在邮件发送成功后再输出下载header和Excel内容，避免输出顺序混乱。
• 附件的Content-Type使用application/vnd.ms-excel能让邮件客户端正确识别文件类型，比application/octet-stream更合适。
• 确保服务器对test.xls所在目录有写入权限，否则file_put_contents会失败，导致附件为空。

内容的提问来源于stack exchange，提问作者user3046528