PHP 创建 Excel 电子表格，然后将其作为附件通过电子邮件发送
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4993300/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PHP create Excel spreadsheet and then email it as an attachment
提问by benhowdle89
I'm using the following code:
我正在使用以下代码：
<?php
$data = $_REQUEST['datatodisplay'];
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=Data.xls");
header("Pragma: no-cache");
header("Expires: 0");
echo $data;
?>
This is what i call when the user hits a submit button. But what i'm interested in doing is sending an Excel spreadsheet as an email attachment. So in this file below I would connect to a DB, select results and create the spreadsheet, then mail it as an attachment. Is that possible by tweaking the code below (i can do the mysql but just not excel creation)
当用户点击提交按钮时，这就是我所说的。但我感兴趣的是将 Excel 电子表格作为电子邮件附件发送。因此，在下面的这个文件中，我将连接到数据库，选择结果并创建电子表格，然后将其作为附件邮寄。是否可以通过调整下面的代码来实现（我可以创建 mysql 但不能创建 excel）
回答by Chris Baker
You'd need a library to create an actual Excel document, unless straight CSV is acceptable. CSV will open as a spreadsheet in Excel, but you cannot do any of the advanced stuff like formatting or formulas.
您需要一个库来创建实际的 Excel 文档，除非可以接受直接的 CSV。CSV 将在 Excel 中作为电子表格打开，但您无法执行任何高级操作，例如格式设置或公式。
I use the library PHPExcel (http://phpexcel.codeplex.com/). It allows for complete Excel functionality, including charts and formulas. It takes a bit to get it going, and the code is pretty verbose. BUT, once you get it all set up, it works like a charm.
我使用库 PHPExcel (http://phpexcel.codeplex.com/)。它允许完整的 Excel 功能，包括图表和公式。它需要一点时间才能运行，并且代码非常冗长。但是，一旦你完成所有设置，它就像一个魅力。
Here's a snippet of the code involved, this is from my implementation of PHPExcel. I am creating a summary of Paypal payments received through a site's API. I include this merely to give you an idea of the amount and nature of the code involved. As you can see, it is all OO. This is just the first PART of the code, where I am setting up column labels and the like. It goes on like this through loops to put the data in place, then another section for the footer. It makes for a V E R Y long file!
这是所涉及的代码片段，这是我对 PHPExcel 的实现。我正在创建通过网站 API 收到的 Paypal 付款的摘要。我包含这只是为了让您了解所涉及代码的数量和性质。如您所见，一切都是面向对象的。这只是代码的第一部分，我在其中设置列标签等。它通过循环继续将数据放置到位，然后是页脚的另一部分。它是一个很长的文件！
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set properties
$objPHPExcel->getProperties()->setCreator("----- Web Server");
$objPHPExcel->getProperties()->setLastModifiedBy("-----Web Server");
$objPHPExcel->getProperties()->setTitle("Paypal payment reconciliation report");
$objPHPExcel->getProperties()->setSubject("Paypal payment reconciliation report");
$objPHPExcel->getProperties()->setDescription("Paypal payment reconciliation report");
$objPHPExcel->getProperties()->setKeywords("paypal reconcile");
$objPHPExcel->getProperties()->setCategory("Reconciliation report");
// Create a first sheet, representing sales data
$objPHPExcel->setActiveSheetIndex(0);
// format the heading
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Paypal Reconciliation - ran on '.date('m/d/y', time()));
$objPHPExcel->getActiveSheet()->mergeCells('A1:C1');
$objPHPExcel->getActiveSheet()->setCellValue('E1', 'Date Range: '.date('m/d/Y', $oldest_transaction).' to '.date('m/d/Y', $newest_transaction));
$objPHPExcel->getActiveSheet()->mergeCells('E1:J1');
$objPHPExcel->getActiveSheet()->duplicateStyleArray(
array(
'font' => array(
'size' => '12',
'bold' => true
)
),
'A1:I1'
);
// add column labels
$objPHPExcel->getActiveSheet()->setCellValue('A2', '#');
$objPHPExcel->getActiveSheet()->setCellValue('B2', 'Date');
$objPHPExcel->getActiveSheet()->setCellValue('C2', 'Name');
$objPHPExcel->getActiveSheet()->setCellValue('D2', 'Gross');
$objPHPExcel->getActiveSheet()->setCellValue('E2', 'Fee');
$objPHPExcel->getActiveSheet()->setCellValue('F2', 'Net');
$objPHPExcel->getActiveSheet()->setCellValue('G2', 'Balance');
$objPHPExcel->getActiveSheet()->setCellValue('H2', 'Class');
$objPHPExcel->getActiveSheet()->setCellValue('I2', 'Item Title');
$objPHPExcel->getActiveSheet()->setCellValue('J2', '');
$objPHPExcel->getActiveSheet()->setCellValue('K2', '#');
$objPHPExcel->getActiveSheet()->setCellValue('L2', 'Time');
$objPHPExcel->getActiveSheet()->setCellValue('M2', 'Type');
$objPHPExcel->getActiveSheet()->setCellValue('N2', 'Status');
$objPHPExcel->getActiveSheet()->setCellValue('O2', 'Transaction ID');
$objPHPExcel->getActiveSheet()->setCellValue('P2', 'Paypal Receipt ID');
$objPHPExcel->getActiveSheet()->setCellValue('Q2', '--- #');
$objPHPExcel->getActiveSheet()->setCellValue('R2', 'Counterparty');
$objPHPExcel->getActiveSheet()->setCellValue('S2', 'Reference Txn ID');
$objPHPExcel->getActiveSheet()->setCellValue('T2', 'Inv #');
EDIT
编辑
By request, here is the code to actually output the Excel document I created above:
根据要求，这里是实际输出我上面创建的 Excel 文档的代码：
include 'PHPExcel/IOFactory.php';
$file_name = date('m-d-Y', $oldest_transaction).'_THRU_'.date('m-d-Y', $newest_transaction);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('/usr/web/cache/temp/'.$file_name.'.xls');
header ("location:http://www.domain.com/cache/temp/".$file_name.".xls");
回答by fishcx
I really wanted to do this withoutusing a library, so I found a good reference on this page PHP Send email with PDF attachment without creating the file?and then tweaked it to create an Excel file using a tab-delimited (\t) string.
我真的很想在不使用库的情况下做到这一点，所以我在这个页面上找到了一个很好的参考PHP Send email with PDF attachment without created the file? 然后调整它以使用制表符分隔 (\t) 字符串创建 Excel 文件。
So you could grab the data from the DB to create the string and then use this method below to send an email with the Excel attachment
因此，您可以从数据库中获取数据以创建字符串，然后使用下面的此方法发送带有 Excel 附件的电子邮件
<?php
$attachment = "testdata1 \t testdata2 \t testdata3 \t \n testdata1 \t testdata2 \t testdata3 \t ";
$to = '[email protected]';
$subject = 'Test email with attachment';
//create a boundary string. It must be unique
//so we use the MD5 algorithm to generate a random hash
$random_hash = md5(date('r', time()));
$headers = "From: [email protected]";
//add boundary string and mime type specification
$headers .= "\r\nContent-Type: multipart/mixed; boundary=\"PHP-mixed-".$random_hash."\"";
//define the body of the message.
ob_start(); //Turn on output buffering
?>
--PHP-mixed-<?php echo $random_hash; ?>
Content-Type: multipart/alternative; boundary="PHP-alt-<?php echo $random_hash; ?>"
--PHP-alt-<?php echo $random_hash; ?>
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Email Text here
--PHP-mixed-<?php echo $random_hash; ?>
Content-Type: application/ms-excel; name="test.xls"
Content-Disposition: attachment
<?php echo $attachment;
//copy current buffer contents into $message variable and delete current output buffer
$message = ob_get_clean();
//send the email
$mail_sent = @mail( $to, $subject, $message, $headers );
//if the message is sent successfully print "Mail sent". Otherwise print "Mail failed"
echo $mail_sent ? "Mail sent" : "Mail failed";
?>
回答by nidhi saxena
**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 Col2\t Col3\n";
$rowRecords='';
$rowRecords .=preg_replace("/\r|\n|\t/"," ",$Col1)."\t".preg_replace("/\r|\n|\t/", " ",$Col2)."\t".preg_replace("/\r|\n|\t/", " ",Col3). "\t\n";
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: [email protected]"."\r\n";
$headers.= "Bcc: [email protected]"."\r\n";
$headers.= "MIME-Version: 1.0\r\n";
$headers.= "Content-Type: multipart/mixed; boundary=\"".$uid."\"\r\n\r\n";
$headers .= "This is a multi-part message in MIME format.\r\n";
$headers .= "--".$uid."\r\n";
$headers .= "Content-type:text/html; charset=iso-8859-1\r\n";
$headers .= "Content-Transfer-Encoding: 7bit\r\n\r\n";
$headers .= $msg."\r\n\r\n";
$headers .= "--".$uid."\r\n";
$headers .= "Content-Type: application/octet-stream; name=\"".$filename."\"\r\n";
$headers .= "Content-Transfer-Encoding: base64\r\n";
$headers .= "Content-Disposition: attachment; filename=\"".$filename."\"\r\n\r\n";
$headers .= $content."\r\n\r\n";
$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";
}
