PHP创建Excel电子表格，然后通过电子邮件将其作为附件发送 [英] PHP create Excel spreadsheet and then email it as an attachment

问题描述

我正在使用以下代码:

<?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;

?>

当用户点击提交按钮时，这就是我所说的.但是我有兴趣做的是将Excel电子表格作为电子邮件附件发送.因此，在下面的该文件中，我将连接到数据库，选择结果并创建电子表格，然后将其作为附件邮寄.通过调整下面的代码可以做到这一点吗(我可以执行mysql，但不能创建excel)

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)

推荐答案

除非可接受纯CSV，否则您需要一个库来创建实际的Excel文档. CSV将在Excel中以电子表格的形式打开，但是您无法执行格式或公式之类的任何高级操作.

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.

我使用PHPExcel库(http://phpexcel.codeplex.com/).它允许完整的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的实现.我正在创建通过网站的API收到的Paypal付款摘要.我将其包括在内只是为了让您对所涉及代码的数量和性质有所了解.如您所见，全部都是面向对象的.这只是代码的第一部分，我将在其中设置列标签等.它像这样通过循环将数据放置到位，然后是页脚的另一部分.这使它成为V E R Y长文件！

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!

// 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 #');

---

编辑

根据要求，以下是实际输出我在上面创建的Excel文档的代码:

By request, here is the code to actually output the Excel document I created above:

    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");

这篇关于PHP创建Excel电子表格，然后通过电子邮件将其作为附件发送的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！